Indexes - Expert Oracle Database Architecture - page 444

The numbers will end up far away from each other. This reduces the number of RAC instances going after the
same block (the rightmost block) and reduces the number of block transfers between RAC instances. One of the
drawbacks to a reverse key index is that you cannot use it in all cases where a regular index can be applied. For
example, in answering the following predicate, a reverse key index on X would not be useful:
where x > 5
The data in the index is not sorted by X before it is stored, but rather by REVERSE(X) , hence the range scan for
X > 5 will not be able to use the index. On the other hand, some range scans can be done on a reverse key index.
If I have a concatenated index on (X, Y) , the following predicate will be able to make use of the reverse key index and
will range scan it:
where x = 5
This is because the bytes for X are reversed, and then the bytes for Y are reversed. Oracle does not reverse the
bytes of (X || Y) , but rather stores (REVERSE(X) || REVERSE(Y)) . This means all of the values for X = 5 will be
stored together, so Oracle can range scan that index to find them all.
Now, assuming you have a surrogate primary key on a table populated via a sequence, and you do not need to
use range scanning on this index—that is, you don't need to query for MAX(primary_key) , MIN(primary_key) ,
WHERE primary_key < 100 , and so on—then you could consider a reverse key index in high insert scenarios even
in a single instance of Oracle. I set up two different tests, one in a pure PL/SQL environment and one using Pro*C to
demonstrate the differences between inserting into a table with a reverse key index on the primary key and one with
a conventional index. In both cases, the table used was created with the following DDL (we will avoid contention on
table blocks by using ASSM so we can isolate the contention on the index blocks):
create tablespace assm
datafile size 1m autoextend on next 1m
segment space management auto;
create table t tablespace assm
as
select 0 id, owner, object_name, subobject_name,
object_id, data_object_id, object_type, created,
last_ddl_time, timestamp, status, temporary,
generated, secondary
from all_objects a
where 1=0;
alter table t add constraint t_pk primary key (id)
using index (create index t_pk on t(id) &indexType tablespace assm);
create sequence s cache 1000;
Whereby &indexType was replaced with either the keyword REVERSE , creating a reverse key index, or with nothing,
thus using a “regular” index. The PL/SQL that would be run by 1, 2, 5, 10, 15, or 20 users concurrently was as follows:
create or replace procedure do_sql
as
begin
for x in ( select rownum r, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY from all_objects )