Database Oracle批量数据处理

谁能告诉我，为什么我的Oracle批量数据处理基准测试在插入和更新方面没有带来任何好处，而在删除操作方面却获得了巨大(400-500%)的好处？

我正在使用一组小数据进行测试:2000条记录.时间在0.02秒到1.1秒的范围内.

谢谢！

"谢谢你，"贾斯汀说.

以下是您可以用来测试的一些代码:

create table tmp$_t1 (id number generated by default on null as identity primary key, c1 varchar2 (30), c2 varchar2 (30))
/

set timing on serveroutput on size unlimited linesize 32767 pagesize 50000 trimspool on tab off feedback on
define svTableName = tmp$_t1
define svInsertHowMany = 2000
define svUpdateWhichMod = 2
define svDeleteWhichMod = 10
truncate table &svTableName
/
<<s01_row_by_row_rbr_dml>> begin <<rbr_loop>> for iRec in (select dbms_random.string ('p', 10) c1 from dual connect by level <= &svInsertHowMany) loop
          insert into &svTableName (c1) values (iRec.c1); end loop rbr_loop; commit;
     <<update_loop>> for iUpdateRec in (select id, c1 from &svTableName) loop
          update &svTableName set c2 = substr (c1, 1, 2) || ' ## ' || id where mod (id, &svUpdateWhichMod) = 0 and id = iUpdateRec.id; end loop update_loop; commit;
     <<delete_loop>> for iDeleteRec in (select id, c1 from &svTableName) loop
          delete &svTableName where mod (id, &svDeleteWhichMod) = 0; end loop delete_loop; commit;
     end s01_row_by_row_rbr_dml;
/
undefine svInsertHowMany svFetchRate svUpdateWhichMod svDeleteWhichMod

set timing on serveroutput on size unlimited linesize 32767 pagesize 50000 trimspool on tab off feedback on
define svTableName = tmp$_t1
define svInsertHowMany = 2000
define svFetchRate = 100000
define svUpdateWhichMod = 2
define svDeleteWhichMod = 10
truncate table &svTableName
/
<<s02_obdp_dml>> declare
     cursor cOBDP is select dbms_random.string ('p', 10) c1 from dual connect by level < &svInsertHowMany; type tOBDP is table of cOBDP%rowtype; lOBDP tOBDP;
     cUpdateDelete sys_refcursor; type tOBDPRec is table of &svTableName%rowtype; lOBDPRec tOBDPRec;
     begin open cOBDP; fetch cOBDP bulk collect into lOBDP limit &svFetchRate;
     <<OBDP_loop>> loop forall iOBDPRec in lOBDP.first .. lOBDP.last insert into &svTableName (c1) values (lOBDP (iOBDPRec).c1);
          fetch cOBDP bulk collect into lOBDP limit &svInsertHowMany; exit when cOBDP%notfound; end loop OBDP_loop; close cOBDP; commit;
          open cUpdateDelete for select * from &svTableName; fetch cUpdateDelete bulk collect into lOBDPRec limit &svFetchRate;
          <<update_delete_loop>> loop forall iOBDPRec in lOBDPRec.first .. lOBDPRec.last update &svTableName
                    set c2 = substr (c1, 1, 2) || ' ## ' || trim (to_char (lOBDPRec (iOBDPRec).id))
                    where mod (lOBDPRec (iOBDPRec).id, &svUpdateWhichMod) = 0 and id = lOBDPRec (iOBDPRec).id;
               forall iOBDPRec in lOBDPRec.first .. lOBDPRec.last delete &svTableName where mod (lOBDPRec (iOBDPRec).id, &svDeleteWhichMod) = 0 and id = lOBDPRec(iOBDPRec).id;
               fetch cUpdateDelete bulk collect into lOBDPRec limit &svFetchRate; exit when cUpdateDelete%notfound;
               end loop update_delete_loop; close cUpdateDelete; commit;
     end s02_obdp_dml;
/
undefine svInsertHowMany svFetchRate svUpdateWhichMod svDeleteWhichMod

第二个匿名块包含INSERT、UPDATE和DELETE操作，这些操作将汇总计时.

为了只测试INSERT、UPDATE或DELETE，只需分别注释掉代码并重新运行.

我把它们都作为单独的代码块，但这可能只是一大堆代码.不管怎么说，我是这么想的.

Rbr="逐行"，OBDP="Oracle批量数据处理".

谢谢你的帮助.