Descending Indexes-Indexes

Descending indexes extend the functionality of a B*Tree index. They allow for a column to be stored sorted in descending order (from big to small) in the index instead of ascending order (from small to big).

Oracle has had the ability to read an index backward for quite a while, so you may be wondering why this feature is relevant. For example, if we use a table T
$ sqlplus eoda/foo@PDB1
SQL> create table t as select * from all_objects; Table created.
SQL> create index t_idx on t(owner,object_type,object_name); Index created.
PL/SQL procedure successfully completed.and query it as follows
SQL> set autotrace traceonly explain
SQL> select owner, object_typefrom twhere owner between ‘T’ and ‘Z’and object_type is not nullorder by owner DESC, object_type DESC;Execution Plan

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:
SQL> select owner, object_typefrom twhere owner between ‘T’ and ‘Z’and object_type is not nullorder by owner DESC, object_type ASC;

Predicate Information (identified by operation id):
2 – access(“OWNER”>=’T’ AND “OWNER”<=’Z’)filter(“OBJECT_TYPE” IS NOT NULL)

Oracle isn’t able to use the index we have in place on (OWNER, OBJECT_TYPE, OBJECT_ NAME) anymore to sort the data. It could have read it backward to get the data sorted by OWNER DESC, but it needs to read it “forward” to get OBJECT_TYPE sorted ASC. Instead, it collected together all of the rows and then sorted. Enter the DESC index:
SQL> create index desc_t_idx on t(owner desc,object_type asc); Index created.
SQL> select owner, object_typefrom twhere owner between ‘T’ and ‘Z’and object_type is not nullorder by owner DESC, object_type ASC;

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)

Once more, we are able to read the data sorted, and there is no extra sort step at the end of the plan.

Note Do not be tempted to ever leave an ORDER BY off a query. Just because your query plan includes an index does not mean the data will be returned in “some order.” The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.