Oracle 编程艺术--降序索引读书笔记

  1. 11gr2数据库的索引默认排序是asc，不是desc。

      准备测试数据  create table test as select rownum as objectid,object_name from all_objects;

      创建默认索引 create index idx_objectid_test on test(objectid);

     select objectid from test; 将得到 1,2,3,4,5... 升序数据。

2.  降序索引的作用1-去掉sort 操作

      若对上面表数据执行

      select *from test order by  objectid asc;  执行计划中没有sort操作，且显示 INDEX FULL SCAN  + TABLE ACCESS BY INDEX ROWID，用时14.42 s

     select *from test order by  objectid desc;  执行计划中没有sort操作，且显示 INDEX FULL SCAN DESCENDING  + TABLE ACCESS BY INDEX ROWID，用时14.12 s

   

 3.  降序索引作用2--多列索引

    下面的例子可以说明。

view plain

1. tony@ORA11GR2> create table t as select * from all_objects;  
2. Table created.  
3.   
4. tony@ORA11GR2> create index t_idx on t(owner,object_type,object_name);  
5. Index created.  
6.   
7. tony@ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T');  
8. PL/SQL procedure successfully completed.  
9.   
10. tony@ORA11GR2> set autotrace traceonly explain  
11. tony@ORA11GR2> select owner, object_type, object_name from t  
12.   2    where owner between 'T' and 'Z'  
13.   3      and object_type is not null  
14.   4    order by owner DESC, object_type DESC;  
15.   
16. Execution Plan  
17. ----------------------------------------------------------  
18. Plan hash value: 2685572958  
19.   
20. -------------------------------------------------------------------------------------  
21. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
22. -------------------------------------------------------------------------------------  
23. |   0 | SELECT STATEMENT            |       | 13838 |   567K|   102   (0)| 00:00:02 |  
24. |*  1 |  INDEX RANGE SCAN DESCENDING| T_IDX | 13838 |   567K|   102   (0)| 00:00:02 |  
25. -------------------------------------------------------------------------------------  
26.   
27. Predicate Information (identified by operation id):  
28. ---------------------------------------------------  
29.   
30.    1 - access("OWNER">='T' AND "OWNER"<='Z')  
31.        filter("OBJECT_TYPE" IS NOT NULL)  

Oracle会向前读索引,所以查询计划中没有排序。
但是如果有一些列按升序排列，一些列按降序排列，会有不同，如下：

view plain

1. tony@ORA11GR2> select owner, object_type, object_name from t  
2.   2    where owner between 'T' and 'Z'  
3.   3      and object_type is not null  
4.   4    order by owner DESC, object_type ASC;  
5.   
6. Execution Plan  
7. ----------------------------------------------------------  
8. Plan hash value: 2813023843  
9.   
10. -----------------------------------------------------------------------------------  
11. | Id  | Operation         | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
12. -----------------------------------------------------------------------------------  
13. |   0 | SELECT STATEMENT  |       | 13838 |   567K|       |   255   (1)| 00:00:04 |  
14. |   1 |  SORT ORDER BY    |       | 13838 |   567K|   720K|   255   (1)| 00:00:04 |  
15. |*  2 |   INDEX RANGE SCAN| T_IDX | 13838 |   567K|       |   102   (0)| 00:00:02 |  
16. -----------------------------------------------------------------------------------  
17.   
18. Predicate Information (identified by operation id):  
19. ---------------------------------------------------  
20.   
21.    2 - access("OWNER">='T' AND "OWNER"<='Z')  
22.        filter("OBJECT_TYPE" IS NOT NULL)  

可以看到现在必须执行排序操作。
但是如果改用降序索引（使用DESC关键字），有如下结果，可以看到现在不需要排序操作了。

view plain

1. tony@ORA11GR2> drop index desc_t_idx;  
2. Index dropped.  
3.   
4. tony@ORA11GR2> create index desc_t_idx on t(owner desc,object_type,object_name);  
5. Index created.  
6.   
7. tony@ORA11GR2> select owner, object_type, object_name from t  
8.   2    where owner between 'T' and 'Z'  
9.   3      and object_type is not null  
10.   4    order by owner DESC, object_type ASC;  
11.   
12. Execution Plan  
13. ----------------------------------------------------------  
14. Plan hash value: 2494308350  
15.   
16. -------------------------------------------------------------------------------  
17. | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
18. -------------------------------------------------------------------------------  
19. |   0 | SELECT STATEMENT |            | 13838 |   567K|     4   (0)| 00:00:01 |  
20. |*  1 |  INDEX RANGE SCAN| DESC_T_IDX | 13838 |   567K|     4   (0)| 00:00:01 |  
21. -------------------------------------------------------------------------------  
22.   
23. Predicate Information (identified by operation id):  
24. ---------------------------------------------------  
25.   
26.    1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF')  AND  
27.               SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )  
28.        filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND  
29.               SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND "OBJECT_TYPE" IS NOT  
30.               NULL)