水煮oracle29----oracle中对数据的批量操作测试

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE --insert

--1、如果数据量不大，尽量少使用复杂语句

--2、可以使用/*+append*/和nologging方式，对块操作和不产生redo log

--3、如果数据量较大，可以考虑分批提交方式，来减轻undo和temp压力

--update

--1、如果数量不大的，尽量只是用forall批量操作（需要确保memory足够，以及undo足够）

--2、在用update大批量更新时，一定要对where条件中的列，做索引处理

--------------------------------

declare

  start_time number;

  end_time   number;

begin

  start_time := dbms_utility.get_time;

  insert into test

    select level id, 'good' || level, level

      from dual

    connect by level <= 2000000;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  commit;

end;

1、普通插入

time:6.77

PL/SQL procedure successfully completed

Executed in 6.782 seconds

---------------------------------

declare

  start_time number;

  end_time   number;

begin

  start_time := dbms_utility.get_time;

  insert /*+append*/ into test nologging

    select level id, 'good' || level, level

      from dual

    connect by level <= 2000000;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  commit;

end;

2、带有/*+append*/和nologging的插入

time:4.06

PL/SQL procedure successfully completed

Executed in 4.125 seconds

---------------------------------------------------

declare

  type test_table_type is table of test%rowtype index by binary_integer;

  test_table test_table_type;

  start_time number;

  end_time   number;

begin

  select * bulk collect into test_table from

  (select level id, 'good' || level, level

      from dual

    connect by level <= 2000000);

  start_time := dbms_utility.get_time;

  forall i in 1..test_table.count

  insert /*+append*/ into test nologging values(test_table(i).id,test_table(i).name,test_table(i).salary);

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  commit;

end;

3、批量插入（使用bulk collect、forall）

time:4.94

PL/SQL procedure successfully completed

Executed in 11.032 seconds

------------------------------------------------------

---update操作

-----------------------------------------------------

declare

  start_time number;

  end_time   number;

begin

  start_time := dbms_utility.get_time;

  update test set salary=salary+3;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  commit;

end;

1、普通的update

time:100.92

PL/SQL procedure successfully completed

Executed in 100.953 seconds

-------------------------------------------------------

declare

    type test_table_type is table of test%rowtype index by binary_integer;

    test_table test_table_type;

    start_time number;

    end_time   number;

  begin

    start_time := dbms_utility.get_time;

   select * bulk collect into test_table from test;

   end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    start_time := dbms_utility.get_time;

    dbms_output.put_line(test_table.count);

    forall i in 1..test_table.count

    update test set salary=test_table(i).salary+3 where id=test_table(i).id;

    end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    commit;

  end;

2、批量update（带有forall和bulk collect）

time:2.75

2000000

time:72.15

PL/SQL procedure successfully completed

Executed in 75.062 seconds

--------------------------------------------------------------------------

 declare

   type test_table_type is table of test%rowtype index by binary_integer;

    test_table test_table_type;

    start_time number;

    end_time   number;

    a number;

  begin

    start_time := dbms_utility.get_time;

    select * bulk collect into test_table from test;

    end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    start_time := dbms_utility.get_time;

    dbms_output.put_line(test_table.count);

    for a in 1..20 loop

    forall i in (a-1)*100000+1..a*100000

    update test set salary=test_table(i).salary+3 where id=test_table(i).id;

    end loop;

    end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    commit;

  end;

3、对大批量1000w级，可以使用

time:2.49

2000000

time:84.9

PL/SQL procedure successfully completed

Executed in 87.531 seconds

--------------------------------------------------------------------------

 declare

    type test_table_type is table of test%rowtype index by binary_integer;

   test_table test_table_type;

    start_time number;

    end_time   number;

    a number;

  begin

    start_time := dbms_utility.get_time;

    select * bulk collect into test_table from test;

    end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    start_time := dbms_utility.get_time;

    dbms_output.put_line(test_table.count);

    for a in 1..20 loop

    forall i in (a-1)*100000+1..a*100000

    update test set salary=test_table(i).salary+3 where id=test_table(i).id;

    commit;

    end loop;

   end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    commit;

  end;

-----------------------------------------------------------------4、中间增加了commit，在undo空间不够用时使用

time:2.48

2000000

time:91.06

PL/SQL procedure successfully completed

Executed in 93.703 seconds

------------------------------------------------------------------

declare

    type test_table_type is table of test%rowtype index by binary_integer;

    test_table test_table_type;

    start_time number;

    end_time   number;

    a number;

  begin

    start_time := dbms_utility.get_time;

    select * bulk collect into test_table from test;

    end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    start_time := dbms_utility.get_time;

    dbms_output.put_line(test_table.count);

    for a in 1..200 loop

    forall i in (a-1)*10000+1..a*10000

    update test set salary=test_table(i).salary+3 where id=test_table(i).id;

    commit;

    end loop;

    end_time := dbms_utility.get_time;

    dbms_output.put_line('time:' || (end_time - start_time) / 100);

    commit;

  end;

5、测试循环对其影响

time:2.5

2000000

time:93.09

PL/SQL procedure successfully completed

Executed in 95.734 seconds

-------------------------------------------------------------

declare

  type test_table_type is table of test%rowtype index by binary_integer;

  test_table test_table_type;

  start_time number;

  end_time   number;

  a          number;

begin

  select * bulk collect into test_table from test;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  start_time := dbms_utility.get_time;

  dbms_output.put_line(test_table.count);

  for i in 1 .. test_table.count loop

    update test

       set salary = test_table(i).salary + 3

     where id = test_table(i).id;

    a := a + 1;

    if mod(a, 500000) = 0 then

      commit;

    end if;

  end loop;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

end;

6、不使用forall的批量更新（主要是批量提交）

time:221.83

PL/SQL procedure successfully completed

Executed in 224.532 seconds

-----------------------------------------------------

-------------------------------------------------------

declare

  type test_table_type is table of test%rowtype index by binary_integer;

  test_table test_table_type;

  start_time number;

  end_time   number;

  a number;

begin

  start_time := dbms_utility.get_time;

  select * bulk collect into test_table from test;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  start_time := dbms_utility.get_time;

  dbms_output.put_line(test_table.count);

  for b in 1..20 loop

  for i in (b-1)*100000+1..b*100000 loop

  update test set salary=test_table(i).salary+3 where id=test_table(i).id;

  a:=a+1;

  if mod(a,500000)=0 then

    commit;

    end if;

  end loop;

  end loop;

  end_time := dbms_utility.get_time;

  dbms_output.put_line('time:' || (end_time - start_time) / 100);

  commit;

end;

7、测试循环影响

time:210.06

PL/SQL procedure successfully completed

Executed in 213.094 seconds

------------------------------------------------------------

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23890223/viewspace-773489/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23890223/viewspace-773489/