Databases Reference
In-Depth Information
This feature can improve performance, because where we have a table with a field populated
by a sequence, massive inserts from multiple sessions could lead to contention issues on the
index blocks. In fact, every session tries to insert new values in the table. Because the key
values are produced by a sequence, the corresponding index entries will be adjacent in the
leaf nodes of the B-tree index (for example, the values 123, 124, 125, and so on). Storing key
values in reverse (for example, in the previous example the key values will be stored as 321,
421, 521, and so on) led to spreading the values in different leaf nodes of the index, reducing
contention issues.
Before dropping the table, we have executed two queries — first with an equality predicate,
the last with a range predicate, to analyze the execution plans generated
by the database engine.
We can see that the first query—with an equality predicate—can use the index, resulting in an
INDEX
UNIQUE SCAN
and a
TABLE
ACCESS
BY
INDEX
ROWID
operation to retrieve the data.
The last query, instead, has a range predicate. So, in the execution plan, the index is not
used and the database will do a
TABLE
ACCESS
FULL
(full table scan) operation to answer
our query.
The reason for this behavior is the particular way reversed key values are stored in the index.
In a standard B-tree index, we will have the entries for 123, 124, 125 values in the same or in
adjacent leaf nodes. In a reverse key index, the corresponding entries will be 321, 421, 521.
So the index entries for these values aren't adjacent as the values themselves, hence, the
INDEX
RANGE
SCAN
cannot be used.
There's more...
We have stated that
INDEX
RANGE
SCAN
cannot be used. This is true when we are talking
about a single column reverse key index.
When we have a reverse key index with two fields, for example,
CUST_LAST_NAME
and
CUST_FIRST_NAME
on the
CUSTOMERS
table of the
SH
schema, we could use the index
range scan operation when we execute the following query:
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL
FROM CUSTOMERS
WHERE CUST_LAST_NAME = 'Rohrback'
AND CUST_FIRST_NAME BETWEEN 'Harry' AND 'Romney';
If, we have created an index similar to the following:
CREATE INDEX IXR_CUSTOMERS
ON CUSTOMERS(CUST_LAST_NAME, CUST_FIRST_NAME) REVERSE;
