在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认识到了这一点，也在试图改变这种情形。
One Response to 11g改变了DELETE语句的执行计划