水煮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;

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;

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;

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;

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;

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;

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;

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);

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;

7、測試迴圈影響

time:210.06

PL/SQL procedure successfully completed

Executed in 213.094 seconds

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

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23890223/viewspace-773489/，如需轉載，請註明出處，否則將追究法律責任。