PL/SQL procedure successfully completed.

Elapsed: 00:04:12.89

?

性能数据:

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

delete from robo
where
rowid=:1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1909761 112.14 162.39 344 1909874 2124331 1909760
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1909762 112.14 162.39 344 1909874 2124331 1909760

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 52 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 342 0.00 0.00
log file switch completion 19 0.97 12.66
log file switch (checkpoint incomplete) 76 0.97 39.72
********************************************************************************

declare
v_rowid varchar2(25);
v_sqltext varchar2(200);
cursor c1 is select rowid from robo where owner='SYS';
begin
v_sqltext := 'delete from robo where rowid=:1';
open c1;
loop
fetch c1 into v_rowid;
execute immediate v_sqltext using v_rowid;
exit when c1%notfound;
end loop;
dbms_output.put_line(c1%rowcount||' row deleted.');
close c1;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 77.46 75.43 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 77.46 75.43 0 0 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
********************************************************************************

SELECT ROWID
FROM
ROBO WHERE OWNER='SYS'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1909761 11.68 11.27 59936 2035069 0 1909760
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1909763 11.69 11.27 59937 2035070 0 1909760

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 52 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 18.98 18.98
db file sequential read 7 0.00 0.00
db file scattered read 3798 0.00 0.21

?

游标执行一次,将所有符合条件的ROWID一次性查询出来，但提取次数和删除次数与结果集相当。比起直接删除效率来得慢.

?

3. 游标处理(批量提取)