标题说大数据量只是为了吸引眼球
，但表现应该是一样的，测试数据30w。
testp：该表name列存储a到z，共26条记录
testc：待删除表，30w数据，表结构如下：其中name列有索引，其他列只为了增加表尺寸
SQL> desc testc
名称
是否为空? 类型
----------------------------------------- -------- -----------
ID
NUMBER
NAME
VARCHAR2(30
REMARK
VARCHAR2(10
C1
VARCHAR2(10
C2
VARCHAR2(10
C3
VARCHAR2(10
C4
CHAR(50)
数据如下：
SQL> select name,count(*) from testc group by name order by name;
NAME
COUNT(*)
------------------------------ ----------
a
10000
b
10000
c
10000
d
10000
e
10000
f
20000
g
20000
h
20000
i
20000
j
20000
k
30000
l
30000
m
30000
n
30000
o
30000
testbak：该表用来保留testc的数据
为了缩短篇幅，方便查看，省略以下操作，该操作每步都会先执行，生成可删除的数据并清理数据缓存。
SQL> insert into testc select * from testbak;
已创建300000行。
已用时间:00: 00: 09.53
SQL> commit;
提交完成。
已用时间:00: 00: 00.01
SQL> alter system flush buffer_cache;
系统已更改。
已用时间:00: 00: 25.26
1、直接删除：
SQL> delete from testc;
已删除300000行。
已用时间:00: 00: 17.04
SQL> commit;
提交完成。
已用时间:00: 00: 00.01
2、循环删除（使用索引）：
SQL> begin
2for v in (select name from testp) loop
3delete from testc where name = v.name;
4end loop;
5commit;
6end;
7/
PL/SQL 过程已成功完成。
已用时间:00: 00: 08.21
删除同样的数据量，时间差别却很明显，why？
做sql trace看下，如下：
直接删除：
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.02
1
0
2 0
Execute1 11.2416.41 7950 79761251012300000
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total2 11.2516.44 7951 79761251014300000
循环删除：
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute 266.31 6.91 7519 2691 350436300000
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total 276.31 6.91 7519 2691 350436300000
两者之间的一致读和当前读差别很大，难道是。。。索引？？？
3、删除/重建索引：
SQL> begin
2execute immediate 'alter index IDX_TESTC_N unusable';
3delete from testc;
4commit;
5execute immediate 'alter index IDX_TESTC_N rebuild';
6end;
7/
PL/SQL 过程已成功完成。
已用时间:00: 00: 09.67
果然和索引有关！！！两种删除方式维护索引的差别竟然这么大！
和使用索引循环删除相比，重建索引的方式还是没有优势。
对于上面这三种删除方式，索引维护代价的差别在哪？
4、批量提交：
SQL> begin
2for v in 1..30 loop
3delete from testc where rownumbegin
2for v in (select name from testp) loop
3delete from testc where name = v.name;
4commit;
5end loop;
6end;
7/
PL/SQL 过程已成功完成。
已用时间:00: 00: 08.06
commit在循环里还是循环外并没有多少差别。其实这个问题tom早就说过了，频繁提交不会带来效率上的提高，甚至可能降低。
5、bulk collect
SQL> declare
2type tp is table of testp.name%type;
3v tp;
4begin
5select name bulk collect into v from testp;
6forall i in 1..v.count
7delete from testc where name = v(i);
8commit;
9end;
10/
PL/SQL 过程已成功完成。
已用时间:00: 00: 07.40
并没有像传说中的那样有效，难道。。。
SQL> create index idx_testc_in on testc (id,name);
索引已创建。
已用时间:00: 00: 00.87
SQL> begin
2for v in (select id,name from testbak) loop
3delete from testc where id = v.id and name = v.name;
4end loop;
5commit;
6end;
7/
PL/SQL 过程已成功完成。
已用时间:00: 01: 06.06
SQL> declare
2type tp is table of testp.id%type;
3type tp1 is table of testp.name%type;
4v_id tp;
5v_name tp1;
6begin
7select id,name bulk collect into v_id,v_name from testbak;
8forall i in 1..v_id.count
9delete from testc where id = v_id(i) and name = v_name(i);
10commit;
11end;
12/
PL/SQL 过程已成功完成。
已用时间:00: 00: 35.07
效果的差别出来了，也验证了我的猜测，就是当循环次数比较少时，使用forall的效果不明显，而循环次数越大效果越可观。
前面已经说过了，testbak表也是30w数据，即循环需要30w次。
forall之所以快，是由于减少了pl sql和sql引擎之间的上下文切换，以下是官方文档原话：
This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines
本帖测试了批量提交、bulk的效果，并解释了bulk的使用时机，对类似全表删除而必须用delete时，给出了三种删除方式，并对效率进行了比较。
本帖的最终目的是希望高手能解释一下粗体字所提出问题，并希望砖家拍砖。
[ 本帖最后由 homeworld80 于 2010-4-2 15:27 编辑 ]
LZ的例子有点很不明白在于你的如下两个例子等价吗？？？
1、直接删除：
SQL> delete from testc;
已删除300000行。
已用时间:00: 00: 17.04
SQL> commit;
提交完成。
已用时间:00: 00: 00.01
2、循环删除（使用索引）：
SQL> begin
2for v in (select name from testp) loop
3delete from testc where name = v.name;
4end loop;
5commit;
6end;
7/
PL/SQL 过程已成功完成。
已用时间:00: 00: 08.21
删除同样的数据量，时间差别却很明显，why？
这两个等价吗？
等价的例子应该是
delete from testc where name in ( select name from testp);
和如下来比
SQL> begin
2for v in (select name from testp) loop
3delete from testc where name = v.name;
4end loop;
5commit;
6end;
7/
或者是如下等价
delete from testc ;
和如下来比
SQL> begin
2for v in (select name from testc) loop
3delete from testc where name = v.name;
4end loop;
5commit;
6end;
7/
从理论上来说应该后者过程比前者SQL来的慢（但是海量大到一定程度，可能会导致直接delete 回滚段不足啊永远无法成功之类的，或者说还有可能为：比如不带条件的delete全表，删除1千万记录要20秒，1亿的时候忽然达到某种分水岭，就不再是20秒*10=200秒，忽然变成2000秒了，而你分成10段来做，则可确保200秒完成之类的，这个是有可能的，确实有经历过类似的事情，这个时候过程性分批做就有可能更快），今天过来加班，暂时没空，有时间实验一把。
另外你的过程关联到testp表，到底会删除testc 表多少记录，是全部？
如果是这样的话我认为应该是如下（因为扫描所有记录，索引的选择性就不高了）
delete from testc性能 >delete from testc where name in ( select name from testp)
性能最糟糕的应该是带过程的，不管FOR ALL 不FOR ALL ，如果是FOR ALL的话，大数据量能带来一点优势，但是不大可能超过SQL的写法。
马上要开会，没办法玩了（另外LZ既然是这样做实验，不妨把你构造数据的步骤也贴出来，我们可以依葫芦画瓢在我们本地实验证实一下）
[ 本帖最后由 wabjtam123 于 2010-4-10 10:29 编辑 ]
