Database Reference
In-Depth Information
just like “regular” tables as far as your application is concerned; you use SQL to access
them as normal. IOTs are especially useful for information retrieval, spatial, and OLAP
applications. We discussed IOTs in some detail in Chapter 10.
•
B*Tree cluster indexes
: These are a slight variation of conventional B*Tree indexes. They
are used to index the cluster keys (see the section “Index Clustered Tables” in Chapter 10)
and will not be discussed again in this chapter. Rather than having a key that points to a
row, as for a conventional B*Tree, a B*Tree cluster has a cluster key that points to the block
that contains the rows related to that cluster key.
•
Descending indexes
: Descending indexes allow for data to be sorted from big-to-small
(descending) instead of small-to-big (ascending) in the index structure. We'll take a look
at why that might be important and how they work.
•
Reverse key indexes
: These are B*Tree indexes whereby the bytes in the key are reversed.
Reverse key indexes can be used to obtain a more even distribution of index entries
throughout an index that is populated with increasing values. For example, if I am using
a sequence to generate a primary key, the sequence will generate values like 987500,
987501, 987502, and so on. These sequence values are monotonic, so if I were using a
conventional B*Tree index, they would all tend to go the same right-hand-side block,
thus increasing contention for that block. With a reverse key index, Oracle will logically
index 205789, 105789, 005789, and so on instead. Oracle will reverse the bytes of the data
to be stored before placing them in the index, so values that would have been next to
each other in the index before the byte reversal will instead be far apart. This reversing of
the bytes spreads out the inserts into the index over many blocks.
•
Bitmap indexes
: Normally in a B*Tree, there is a one-to-one relationship between an index
entry and a row: an index entry points to a row. With bitmap indexes, a single index entry uses
a bitmap to point to many rows simultaneously. They are appropriate for highly repetitive data
(data with few distinct values
relative to the total number of rows in the table
) that is mostly
read-only. Consider a column that takes on three possible values—
Y
,
N
, and
NULL
—in a table
of 1 million rows. This might be a good candidate for a bitmap index, if, for example, you need
to frequently count how many rows have a value of
Y
. That is not to say that a bitmap index
on a column with 1,000 distinct values in that same table would not be valid—it certainly can
be. Bitmap indexes should never be considered in an OLTP database for concurrency-related
issues (which we'll discuss in due course). Note that bitmap indexes require the Enterprise or
Personal Edition of Oracle.
•
Bitmap join indexes
: These provide a means of denormalizing data in an index structure,
instead of in a table. For example, consider the simple
EMP
and
DEPT
tables. Someone might
ask the question, “How many people work in departments located in the city of Boston?”
EMP
has a foreign key to
DEPT
, and in order to count the employees in departments with a
LOC
value of Boston, we would normally have to join the tables to get the
LOC
column joined
to the
EMP
records to answer this question. Using a bitmap join index, we can instead index
the
LOC
column against the
EMP
table. The same caveat in regard to OLTP systems applies to a
bitmap join index as a regular bitmap index.
•
Function-based indexes
: These are B*Tree or bitmap indexes that store the computed result
of a function on a row's column(s), not the column data itself. You can consider them an
index on a virtual (or derived) column—in other words, a column that is not physically stored
in the table. These may be used to speed up queries of the form
SELECT * FROM T WHERE
FUNCTION(DATABASE_COLUMN) = SOME_VALUE
, since the value
FUNCTION(DATABASE_COLUMN)
has already been computed and stored in the index.
