Databases Reference
In-Depth Information
20. In the next screenshot, there is the execution plan of the previous query:
21. Drop the table and the sequence:
DROP TABLE REVERSE_TEST;
DROP SEQUENCE REV_SEQ;
How it works...
In this recipe, we have created a small table, with a unique field
ID
populated using a
sequence with a
BEFORE INSERT
trigger.
To ensure the uniqueness of the field, we have created a
UNIQUE INDEX
.
After populating the table with some data, we have analyzed the index and queried the
statistics on it.
We have then dropped the index and deleted the data in the table, to return to the initial state.
We have created a new
UNIQUE
INDEX
with the
REVERSE
keyword, which creates the index,
storing the key values reverse order. A reverse key B-tree index stores the value in the leaf
nodes reversed (from right to left, instead of from left to right); for example, the value
123
will
be stored in the index as
321
.
We have analyzed the index again and queried the statistics, to compare with the data
previously collected. We can see that by using the reverse key index, the index is spread
across more database blocks, occupying more space on the disk. This is the exact behavior
of this kind of index—distributing index key values in different database blocks.
