11GR2的delete全表的执行计划变化

delete from wxh_tbd;

73338 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3904056193

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | DELETE STATEMENT |              | 73338 |   358K|   154   (1)| 00:00:01 |
|   1 |  DELETE          | WXH_TBD      |       |       |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0016055 | 73338 |   358K|   154   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        218  recursive calls
      81526  db block gets
        255  consistent gets
          1  physical reads
   30513272  redo size
       1133  bytes sent via SQL*Net to client
       1268  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
      73338  rows processed

rollback

Rollback complete.

delete /*+ full(a) */ from wxh_tbd a;

73338 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 1781138943

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |         | 73338 |   358K|   285   (1)| 00:00:01 |
|   1 |  DELETE            | WXH_TBD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| WXH_TBD | 73338 |   358K|   285   (1)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        127  recursive calls
     228792  db block gets
       1103  consistent gets
          0  physical reads
   45173944  redo size
       1134  bytes sent via SQL*Net to client
       1285  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      73338  rows processed

可以看到新的执行计划逻辑读更少。原因不清楚为什么

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-711754/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-711754/