Index Enhancements   «Prev  Next»

Lesson 3 Create a descending index
Objective Create a descending index.

Create Descending Index in Oracle

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:

Create Descending Index
CREATE INDEX   Required keywords.
index_name   The unique name of the index.
ON   Required keyword.
table_name   The unique name of the table on which the index is based.
column_list   A list of columns that make up the index.
DESC A keyword that denotes that the column is stored in descending order.
Oracle treats desc indexes as function bases indexes		 

CREATE INDEX index_name ON table_name
  (column_list) DESC;

Descending Indexes

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:
  1. Bitmap
  2. Bitmap join
  3. Function-based
  4. Indexed virtual column
  5. Invisible
  6. Global partitioned
  7. Local partitioned
  8. Domain
  9. B-tree cluster
  10. Hash cluster
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 modules of this course.

In the next lesson, you will learn about some of the improvements in Oracle’s bitmapped indexes.

Creating Descending Index - Exercise

Click the Exercise link below to practice creating a descending index.
Creating Descending Index - Exercise

Ad Indexing using Oracle Database