常见的例子,表中记录按照creation_date作purge,且该字段上有个索引。
如果不加index_desc hint,purge job执行时间长了可能会越来越慢。如下consistent read明显要比current read多很多。是因为Index range scan从index tree的最左面开始扫描,扫描了很多空块。
SQL> DELETE FROM vltb_data_0 where creation_date < (SYSDATE - 3) and rownum < 1000;
999 rows deleted.———————————————————-
| Id | Operation | Name |
———————————————————-
| 0 | DELETE STATEMENT | |
| 1 | DELETE | RULE_OBJECT_ATTR_DATA_0 |
|* 2 | COUNT STOPKEY | |
|* 3 | INDEX RANGE SCAN| RULE_OBJECT_ATTR_DATA_0_IX3 |
———————————————————-
Predicate Information (identified by operation id):
—————————————————
2 - filter(ROWNUM<1000)
3 - access(”CREATION_DATE”Statistics
———————————————————-
1 recursive calls
21167 db block gets
427848 consistent gets
0 physical reads
1341824 redo size
1 sorts (memory)
0 sorts (disk)
999 rows processed
通过添加index_desc, Index range descending scan从index tree的中间或者右边进入扫描,更快定位到存在纪录的block.
SQL> DELETE /*+ index_desc(rule_object_attr_data_0) */ FROM vltb_data_0 where creation_date < (SYSDATE - 3) and rownum < 1000;
999 rows deleted.Statistics
———————————————————-
1 recursive calls
21177 db block gets
59 consistent gets
288 physical reads
1368592 redo size
814 bytes sent via SQL*Net to client
833 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
999 rows processed
从而避免了无用功。这样的例子时常在工作中遇到。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20577218/viewspace-700738/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20577218/viewspace-700738/