Create Reverse Index in Oracle

Reverse indexing is a specific type of indexing that is designed to solve a fairly common real-world problem. One of the common types of values for an index is some type of key value. Key values are often simply assigned to a row in ascending order. With some types of tables, such as tables used in online transaction processing systems, there is also a tendency to delete older rows over time. Remember that the rows in a B*-tree index are built with branch and leaf nodes, with smaller values increasing to the left of the B*-tree and higher values increasing to the right of the B*-tree. This structure, when coupled with an ever-increasing value for an index and the tendency to delete older values, will lead to the leaf nodes on the left becoming sparsely populated and the entire index becoming unbalanced. An unbalanced index is not as efficient as a balanced index, due to the wasted space.

The solution is the reverse index. A reverse index simply stores the values for the index in reverse order. A value such as 123 would be stored as 321 in a reverse index. Oracle shifts the order of the value transparently, so that a user in the previous example would input the value 123 and have the same value returned in a query.
When a reverse index is used, the natural tendency of the index toward an unbalanced state is prevented, because consecutive values are distributed through the B*-tree index structure. The following series of images illustrates the problem and the solution delivered by reverse indexes:

Oracle provides a wide range of index types and features. The correct use of indexes results in well performing and scalable database application. Conversely, if you incorrectly or unwisely implement a feature, there may be detrimental performance implications. Table 4-5 summarizes the various Oracle index types available. At first glance, this is a long list and may be somewhat overwhelming to somebody new to Oracle. Deciding which index type to use is not as daunting as it might initially seem. For most applications, you should simply use the default B-tree index type.
Note: Several of the index types listed in Table 4-5 are actually just variations on the basic, B-tree index. A reverse-key index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index valueis sequentially generated and inserted with similar values.

Of course, there are some limitations on using reverse indexes, the most apparent one that you cannot use a reverse index for sorting rows, because the actual value stored is different from the value entered and retrieved by a SQL statement.

The syntax to create a reverse index is identical to the standard syntax for creating an index, with the addition of one keyword:

CREATE INDEX	Required keywords.
index_name	Unique name for the index.
ON	Required keyword.
table_name	The unique name of the table the index will be based on.
column_list	A list of columns whose values will make up the index.
REVERSE	Required keyword for reverse indexes.

Oracle provides a wide range of index types and features. The correct use of indexes results in well performing and scalable database application. Conversely, if you incorrectly or unwisely implement a feature, there may be detrimental performance implications. The table below summarizes the various Oracle index types available. At first glance, this is a long list and may be somewhat overwhelming to somebody new to Oracle. Deciding which index type to use is not as daunting as it might initially seem. For most applications, you should simply use the default B-tree index type. Note Several of the index types listed in the table below are actually just variations on the basic, B-tree index. A reverse-key index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index value is sequentially generated and inserted with similar values.

A big disadvantage to using a reverse key index is that you can't perform range scans on these indexes. This is because the index entries are scattered all over instead of being stored sequentially. Reversing the index key values randomly distributes the index blocks across the index leaf nodes. You can certainly use the index to fetch by specific index key values and for a full index scan, however. Use reverse key indexes when your queries have equality predicates. Note also that even though the database doesn't perform a range scan when you use a reverse key index, it can perform a fast full scan^[1] of the index. There could be a slight overhead in terms of CPU usage when the database searches on a reverse key index. The reason, of course, is that the database has to reverse the order of bytes in the values of the index so they represent the actual values stored in the table. Remember that the main purpose of using a reverse key index is to eliminate contention caused when the database is inserting rows with index key values generated by a sequence. Other solutions are possible. For example, you can you can partition a hot index rather than make it into a reverse index, using the partitioning to mitigate the input/output hotspot that you otherwise would have.
You can make the decision whether to implement a reverse key index by comparing the benefits they provide (the faster insert rates due to the reduction in contention) with the biggest drawback (their inability to support range-based predicates). If your application uses a primary key that is always used through an equality predicate, a reverse key index is helpful, especially in an Oracle RAC environment. Reverse key indexes are very helpful if your application is suffering from a hot right-hand side index block. If the contention is due to a hot index root block, partitioning the index is potentially helpful because the index will end up with multiple index root blocks.
In the next lesson, you will learn about computing statistics for indexes.

[1]fast full scan:While Oracle can perform index scans for nonpartitioned indexes, this feature applies to a narrow set of queries. Partition-based index scans apply to a much broader range of queries.