Databases Reference
In-Depth Information
information about the column data as a real index does. The database doesn't collect optimizer statistics
for an index while it's in the invisible state. The optimizer uses the same basic default optimizer statistics
(such as index level, leaf blocks, distinct keys, and clustering factor) for a virtual index as it does for any
normal index for which you haven't collected any statistics.
You can gather statistics on an invisible
index by either converting the index into a "visible" index so the optimizer can "see" it, or set the
optimizer_use_invisible_indexes
parameter to
true
. We much prefer using an invisible index wherever
possible when you want to test the efficacy of a potential index. The one place where we do see a use for
the virtual index is when we're dealing with large tables and want to quickly find out if the optimizer will
use a potential index on that table. Developers can then run an explain plan as if the index actually exists
without having to wait for the creation of a large index.
You create a virtual index only when you want to explore what happens if you create an index—but
don't want to go through the mechanics of actually creating an index. When you create a nosegment
index, Oracle doesn't create an index segment as it does for a normal index; it simply creates an index
definition. Also, you can't rebuild or alter the index as you can in the case of a real index. Note that when
you create a nosegment index, the database populates only a few data dictionary tables and, of course,
there's no index tree associated with the index.
Several Oracle tuning tools, such as the Oracle Tuning Pack and other third-party tools, make use of
the hidden parameter
_use_nosegment_indexes
to simulate the presence of an index. The virtual indexes
let you evaluate whether the cost-based optimizer will actually use the indexes in its execution plans.
You can thus use the fake or virtual index to test whether an index may help performance without
actually using up the space for the index on disk.
Reverse Key Indexes
You can create an index as a reverse key index for performance reasons; they're especially suited for
Oracle RAC environments. A reverse key index stores the index entries with their bytes reversed. The
ROWIDs
are stored in the same format as a regular index. When you insert rows in a column where the
database populates one of the columns using an increasing sequence, each new entry will be inserted
into the same index block. When each new key value in an index is greater than the previous value, it is
said to be a
monotonically increasing value
. In a RAC database, when multiple sessions simultaneously
insert data into monotonically increasing index nodes, it leads to contention for the same index block. A
reverse key index will prevent this contention because the database inserts the successive index entries
into indexed blocks that are spread out. Although RAC environments usually use reverse key indexes,
any high volume transaction processing system that is experiencing contention for index blocks could
potentially benefit from this type of an index.
A reverse key index is actually simple: it simply reverses the index column values before inserting
(storing) into the index. In a normal B-tree index, the database inserts index values sequentially. If the
next two primary key value generated by an Oracle sequence are 12345 and 12346, for example, the
database stores both values in the same index block. While this makes for efficient index lookups and for
faster index range scans, the key point is that each insert statement must be able to access the newest
block in the index to do the insertion of the new values. If index key values are being concurrently
inserted into leaf blocks or branches of the B-tree, a leaf or branch block split could become a
serialization point. A reverse key index, on the other hand, when faced with the same index values in this
example, reverses them into 54321 and 64321 before inserting them into the index. Similarly, if a column
value is ORACLE, the database stores it in the indexed in reverse order, as ELCARO. As you can see, while
the index values 12345 and 12346 would have been stored next to each other, the values 54321 and 64321
are stored in different index leaf blocks that are spread throughout the index structure. Consequently,
even in a busy database, the insertion of sequential key values won't result in contention for the
rightmost index block (the index is also said to have a right growing tree in this case). Instead of storing
