降序索引浅析

--降序索引EODA@PROD1> drop table t purge;Table dropped.EODA@PROD1> set echo onEODA@PROD1> create table t as select * from all_objects;Table created.EODA@PROD1> create index t_idx on t(owner,object_type,object_name);Index created.EODA@PROD1> begin  2           dbms_stats.gather_table_stats(user, 'T', method_opt=> 'for all indexed columns');  3         end;  4         /PL/SQL procedure successfully completed.EODA@PROD1> set autotrace traceonly explainEODA@PROD1> start /tmp/opt.sqlEODA@PROD1> select owner, object_type  2  from t  3  where owner between 'T' and 'Z'  4  and object_type is not null  5  order by owner DESC, object_type DESC;Execution Plan----------------------------------------------------------Plan hash value: 2685572958-------------------------------------------------------------------------------------| Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |    |799 | 11985 |  8   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN DESCENDING| T_IDX |799 | 11985 |  8   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("OWNER">='T' AND "OWNER"<='Z')       filter("OBJECT_TYPE" IS NOT NULL)--通过降序读取索引EODA@PROD1> EODA@PROD1> select owner, object_type  2  from t  3  where owner between 'T' and 'Z'  4  and object_type is not null  5  order by owner DESC, object_type ASC;  --部分数据升序，部分数据降序排序Execution Plan----------------------------------------------------------Plan hash value: 2813023843---------------------------------------------------------------------------| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT  |  |   799 | 11985 |9  (12)| 00:00:01 ||   1 |  SORT ORDER BY  |  |   799 | 11985 |9  (12)| 00:00:01 ||*  2 |   INDEX RANGE SCAN| T_IDX |   799 | 11985 |8   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OWNER">='T' AND "OWNER"<='Z')       filter("OBJECT_TYPE" IS NOT NULL)--使用了SORT ORDER BY来排序EODA@PROD1> EODA@PROD1> create index desc_t_idx on t(owner desc,object_type asc);  --创建反向索引Index created.EODA@PROD1> EODA@PROD1> select owner, object_type  2  from t  3  where owner between 'T' and 'Z'  4  and object_type is not null  5  order by owner DESC, object_type ASC;Execution Plan----------------------------------------------------------Plan hash value: 2494308350-------------------------------------------------------------------------------| Id  | Operation | Name       | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT |      |   799 | 11985 |     3(0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| DESC_T_IDX |   799 | 11985 |     3(0)| 00:00:01 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF')  AND      SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND      SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND "OBJECT_TYPE" IS NOT      NULL)--使用了索引，没有使用sort排序

--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》