Implementing Reverse-Key Indexes- Tables and Constraints

Reverse-key indexes are similar to B-tree indexes, except that the bytes of the index key are reversed when an index entry is created. For example, if the index values are 201, 202, and 203, the reverse-key index values are 102, 202, and 302:

Index value Reverse key value

————- ——————

201 102

202 202

203 302

Reverse-key indexes can perform better in scenarios in which you need a way to evenly distribute index data that would otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid having I/O concentrated in one physical disk location within the index during large inserts of sequential values.

You cannot specify REVERSE for a bitmap index or an Index Organized Table (IOT). Use the REVERSE clause to create a reverse-key index:

SQL> create index cust_idx1 on cust(cust_id) reverse;

You can verify that an index is reverse key by running the following query:

SQL> select index_name, index_type from user_indexes;

Instead of using reverse-key indexes to solve the incremental sequence issue here, scalable sequences are also available. You can use the scalable sequences to populate your primary key.

Scalable sequences add a prefix number, pad zeros, and then has the incrementing number.

SQL> create sequence seq_scale_pk minvalue 1

maxvalue 9999999999 scale;

SQL> select seq_scale_pk.nextval; 1023760001

SQL> select seq_scale_pk.nextval; 1023760002

New connection

SQL> select seq_scale_pk.nextval; 1087420003

As you can see, a new session will change the prefix. If you are able to define your sequences for the primary key as scalable, this will be a better way to address the issue that reverse-key indexes was addressing.

Creating Key-Compressed Indexes

Index compression is useful for indexes where one or more of the columns contains highly repetitive data. Compressed indexes, in these situations, have the following advantages:

•     Reduced storage

•     More rows stored in leaf blocks, which can result in less I/O when accessing a compressed index

You cannot create a key-compressed index on a bitmap index. Suppose you have a table defined as follows:

SQL> create table users( last_name varchar2(30) ,first_name varchar2(30) ,address_id number);

You want to create a concatenated index on the LAST_NAME and FIRST_NAME columns. You know from examining the data that there is duplication in the LAST_NAME column.

The compression clause allows you only to specify how many of the left-most columns should be compressed. You cannot compress a specific indexed column without also compressing the index column before it. Use the COMPRESS N clause to create a compressed index:

SQL> create index users_idx1 on users(last_name, first_name) compress 2;

The prior line of code instructs Oracle to create a compressed index on two columns. You can verify that an index is compressed as follows:

SQL> select index_name, compression from user_indexes

where index_name like ‘USERS%’;

Here is some sample output, indicating that compression is enabled for the index: INDEX_NAME COMPRESS —————————— ——–USERS_IDX1    ENABLE