Index Enhancements   «Prev  Next»

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. 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 to an unbalanced Index:
    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:
Problem and Solution delivered by Reverse Indexes
1) In a normal B-tree index, the values are spread through the leaf nodes.
1) In a normal B*-tree index, the values are spread throughout the leaf nodes from lowest to highest, left to right.

2) Smaller earlier values (101, 103, 105, 107, 108, 111) are deleted from the table, the left side of the B*-tree becomes more sparsely populated than the right side.
2) Smaller earlier values (101, 103, 105, 107, 108, 111) are deleted from the table, the left side of the B*-tree becomes more sparsely populated than the right side.

3) In a reverse index, the values are spread throughout the B*-tree structure.
3) In a reverse index, the values are spread throughout the B*-tree structure

4) When these same earlier values are deleted, the B*-tree index does not become nearly as imbalanced.
4) When these same earlier values are deleted, the B*-tree index does not become nearly as imbalanced

Determining Which Type of Index to Use

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.
Table 4-5: Oracle Index Types and Feature Descriptions
Index Type Usage
B-tree Default, balanced tree index; good for high-cardinality (high degree of distinct values) columns. Use a normal B-tree index unless you have a concrete reason to use a different index type or feature.
Index organized table Efficient when most of the column values are included in the primary key. You access the index as if it were a table. The data is stored in a B-tree like structure.
Unique A form of B-tree index; used to enforce uniqueness in column values. Often used with primary key and unique key constraints, but can be created independently of constraints.
Reverse-key A form of B-tree index; useful to balance I/O in an index that has many sequential inserts.

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 to create a Reverse Index

The syntax to create a reverse index is identical to the standard syntax for creating an index, with the addition of one keyword:
Reverse index syntax
CREATE INDEX index_name ON table_name
(column_list) REVERSE;

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.

Determining Which Type of Index to Use

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.

Index Type Usage
Key-compressed Good for concatenated indexes where the leading column is often repeated; compresses leaf block entries. This feature applies to a B-tree or an IOT index.
Descending A form of B-tree index; used with indexes where corresponding column values are sorted in a descending order (the default order is ascending). You can't specify descending for a reverse key index and Oracle ignores descending if the index type is bitmap.
Bitmap Excellent in data warehouse environments with low-cardinality columns and SQL statements using many AND or OR operators in the WHERE clause. Bitmap indexes aren’t appropriate for online transaction processing (OLTP) databases where rows are frequently updated. You can’t create a unique bitmap index.
Bitmap join Useful in data warehouse environments for queries that utilize Star schema structures that join fact and dimension tables.
Function-based Good for columns that have SQL functions applied to them. This can be used with either a B-tree or bitmap index.
Indexed virtual column An index defined on a virtual column (of a table); useful for columns that have SQL functions applied to them; viable alternative to using a function-based index.
Virtual Allows you to create an index with no physical segment or extents via the NOSEGMENT clause of CREATE INDEX; useful in tuning SQL without consuming resources required to build the physical index. Any index type can be created as virtual.
Invisible The index is not visible to the query optimizer. However, the structure of the index is maintained as table data is modified. Useful for testing an index before making it visible to the application. Any index type can be created as invisible.
Global partitioned Global index across all partitions in a partitioned table or regular table. This can be a B-tree index type and can’t be a bitmap index type.
Local partitioned Local index based on individual partitions in a partitioned table. This can be either a B-tree or bitmap index type.
Domain Specific for an application or cartridge.
B-tree cluster Used with clustered tables.
Hash cluster Used with hash clusters.

Expert Oracle Indexing and Access Paths

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.
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.

SEMrush Software