在11.2中，如果DELETE的时候没有限制条件，且表上存在主键的话，执行计划会变为索引全扫。
在和600聊天的时候听说了这个现象，开始的时候还不是很相信。当时600特意验证了一下，事实确实如此。
于是特意自己也做了个简单的例子：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> create table t_del as select rownum id, a.* from
dba_objects a, user_tables ;
Table created.
SQL> select count(*) from t_del;
COUNT(*)
----------
110360
SQL> alter table t_del add primary key (id);
Table altered.
SQL> explain plan for delete t_del;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1780357700
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 89885 | 1141K|
245 (1)| 00:00:03 |
| 1 |
DELETE | T_DEL |
| | | |
| 2 |
INDEX FULL SCAN| SYS_C006177 | 89885 |
1141K| 245 (1)| 00:00:03 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
13 rows selected.
SQL> explain plan for delete /*+ full(t_del) */ t_del;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2195693323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | DELETE STATEMENT |
| 89885 | 1141K| 426
(1)| 00:00:06 |
| 1 |
DELETE | T_DEL | |
| | |
| 2 |
TABLE ACCESS FULL| T_DEL | 89885 |
1141K| 426 (1)| 00:00:06 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
13 rows selected.
Oracle认为全索引扫描的代价接近全表扫描的一半，预估时间也只有全表扫描的一半。这中执行计划对于10g以前是不可想象的，既然所有的记录都要处理，通过全表扫描显然是最合适的方法，而通过索引去定位每条记录显然效率要低很多。
那么到底是Oracle改变了实现方式，还是11.2的CBO在这里犯了错误呢，真正执行一下看看效果：
SQL> set timing on
SQL> set autot trace
SQL> delete t_del;
110360 rows deleted.
Elapsed: 00:00:01.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1780357700
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 |
13 | 245 (1)| 00:00:03 |
| 1 |
DELETE | T_DEL |
| | | |
| 2 |
INDEX FULL SCAN| SYS_C006177 |
1 | 13 | 245
(1)| 00:00:03 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
Statistics
----------------------------------------------------------
247 recursive calls
124999 db block gets
395 consistent gets
270 physical reads
43099720 redo size
678 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
110360 rows processed
SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.92
SQL> delete /*+ full(t_del) */ t_del;
110360 rows deleted.
Elapsed: 00:00:04.63
Execution Plan
----------------------------------------------------------
Plan hash value: 2195693323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | DELETE STATEMENT |
| 1 | 13 |
425 (0)| 00:00:06 |
| 1 |
DELETE | T_DEL | |
| | |
| 2 |
TABLE ACCESS FULL| T_DEL | 1
| 13 | 425
(0)| 00:00:06 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
(level=2)
Statistics
----------------------------------------------------------
322 recursive calls
346841 db block gets
1731 consistent gets
593 physical reads
65160536 redo size
683 bytes sent via SQL*Net to client
604 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
110360 rows processed
SQL> rollback;
Rollback complete.
Elapsed: 00:00:01.69
显然无论从运行时间，还是db
block gets数量，或者是逻辑读或物理读的数量，11.2的全索引扫描执行路径都要远小于全表扫描的方式，甚至连产生的redo的数据量都只有全表扫描的2/3，显然Oracle更改了删除操作的处理机制，才使得全索引扫描这种看上去完全不合理的执行计划可以提高性能。
DELETE操作是Oracle所有DML中代价最大的，看来Oracle认识到了这一点，也在试图改变这种情形。