| Lesson 5 | Reverse indexing |
| Objective | Create a reverse index. |
Create Reverse Index in Oracle
Reverse indexing is a specific type of indexing that is designed to solve a fairly common real-world problem.Key value 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.
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
The solution is the reverse index. A reverse index simply stores the values for the index in reverse order. A value such as
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 SlideShow illustrates the problem and the solution delivered by reverse indexes:
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 SlideShow illustrates the problem and the solution delivered by reverse indexes:
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.
Syntax
The syntax to create a reverse index is identical to the standard syntax for creating an index, with the addition of one keyword:
- Required keywords
- Unique name for the index
- The unique name of the table the index will be based on
- A list of columns whose values will make up the index
- Required keyword for reverse indexes
CREATE INDEX index_name ON table_name (column_list) REVERSE;
Syntax for Reverse Index
In the next lesson, you will learn about computing statistics for indexes.
Expert Oracle Indexing and Access Paths
In the next lesson, you will learn about computing statistics for indexes.
Disadvantages of Reverse Key Index
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.
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.
[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.
