By default, Oracle stores B-tree indexes in an ascending order. For example, if you have an index on column with a number data type, the smallest number would appear first in the index (left-most leaf node) and the highest numbers would be stored in the right-most leaf nodes. You can instruct Oracle to reverse this order to be descending by specifying the DESC keyword with a column. This creates a descending index. For example,
create index cust_didx1
on cust(cust_id desc);
Descending indexes are useful for queries that sort some columns in an ascending order and other columns in a descending order.
Specialized Index Types
Sometimes a B-tree index is not enough to provide the desired performance improvement.
The following are indexes that should be used under specialized circumstances:
Indexed virtual column
Each of these types of indexes is briefly introduced in the following subsections. Many of these index types are discussed in full detail later in subsequent chapters in this book.
Indexing using Oracle Database