In a normal, B*-tree index, the values are always stored in sorted order. The sorted nature of the B*-tree index is a requirement for the structure of the B* tree; navigation through the tree is based on the sorted values. Of course, the fact that values in an index are stored in sorted order helps to improve query performance, because a query that requests sorted results can simply use the appropriate index, if available, to avoid the overhead of resorting the data.
Prior to Oracle, all B*-tree indexes were sorted in ascending order. There are certainly times when queries might require data to be sorted in descending order. For instance, if a value were an ID number that increased over time, and a user wanted a list of orders sorted from the most recent order to the lease recent order, he or she would specify that the query return data sorted descending on the ID number.
Syntax for Descending Index
To create a descending index, you simply specify the keyword DESC at the end of a normal CREATE INDEX statement, as shown in the following Diagram:
Oracle Descending Index
In the next lesson, you will learn about some of the improvements in Oracle’s bitmapped indexes.