Database Reference
In-Depth Information
EODA@ORA12CR1> create index t_idx on t(owner,object_type,object_name);
Index created.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', method_opt=>'for all indexed columns' );
4 end;
5 /
PL/SQL procedure successfully completed.
and query it as follows
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> 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 | | 5008 | 50080 | 24 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN DESCENDING | T_IDX | 5008 | 50080 | 24 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Oracle will just read the index backward. There is no final sort step in this plan; the data is sorted. Where this
descending index feature comes into play, however, is when you have a mixture of columns, and some are sorted
ASC
(ascending) and some
DESC
(descending), for example:
EODA@ORA12CR1> 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 | | 5008 | 50080 | 24 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 5008 | 50080 | 24 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 5008 | 50080 | 24 (0)| 00:00:01 |
----------------------------------------------------------------------------
