11g改變了DELETE語句的執行計劃

在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認識到了這一點，也在試圖改變這種情形。