Oracle删除操作将永远存在

set timing on serveroutput on size unlimited linesize 32767 pagesize 50000 trimspool on tab off feedback on heading on verify on
define svTableName = tmp$_c8d4t6_2
define svInsertHowMany = 500000
define svFetchRate = 2000
define svUpdateWhichMod = 2
define svDeleteWhichMod = 10
col ts format a20
col cnt format a10
<<s01_row_by_row_rbr_dml>> begin <<rbr_loop>> for iRec in (select dbms_random.string ('p', 10) c1, level id from dual connect by level <= &svInsertHowMany) loop
          insert into &svTableName (c1) values (iRec.c1);
          if mod (iRec.id, &svFetchRate) = 0 then commit; end if; 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;
          if mod (iUpdateRec.id, &svFetchRate) = 0 then commit; end if; end loop update_loop; commit;
     <<delete_loop>> for iDeleteRec in (select id, c1 from &svTableName) loop
          delete &svTableName where mod (id, &svDeleteWhichMod) = 0;
          if mod (iDeleteRec.id, &svFetchRate) = 0 then commit; end if; end loop delete_loop; commit;
     end s01_row_by_row_rbr_dml;
/
select to_char (sysdate, 'mm/dd/yyyy hh24:mi:ss') ts from dual
/
select count (0) cnt from &svTableName
/

这个区块已经运行了几个小时。

当我检查来自另一个会话的表计数时,它显示450000,这意味着<<delete_loop>>已完成。

大约10分钟后。

剩下的时间它在干什么?

当我进行50000次划船时,我注意到了同样的现象。在这种情况下,大约1分钟后,它有45000行,但需要6分钟才能完成。

请注意,我知道这是错误的数据处理方法。我正在为管理层做基准测试。说来话长,但是,是的,我知道这是最糟糕的方法。

这里使用的表和代码是通用的,仅用于概念基准测试。有点像一个研究项目。

感谢您的帮助、专业知识和洞察力。