Databases Reference
In-Depth Information
Index Type
Usage
Key-compressed
Good for concatenated indexes where the leading column is often repeated;
compresses leaf block entries. This feature applies to a B-tree or an IOT index.
Descending
A form of B-tree index; used with indexes where corresponding column values
are sorted in a descending order (the default order is ascending). You can't
specify descending for a reverse key index and Oracle ignores descending if
the index type is bitmap.
Bitmap
Excellent in data warehouse environments with low-cardinality columns and
SQL statements using many
AND
or
OR
operators in the
WHERE
clause. Bitmap
indexes aren't appropriate for online transaction processing (OLTP) databases
where rows are frequently updated. You can't create a unique bitmap index.
Bitmap join
Useful in data warehouse environments for queries that utilize Star schema
structures that join fact and dimension tables.
Function-based
Good for columns that have SQL functions applied to them. This can be used
with either a B-tree or bitmap index.
Indexed virtual column
An index defined on a virtual column (of a table); useful for columns that have
SQL functions applied to them; viable alternative to using a function-based
index.
Virtual
Allows you to create an index with no physical segment or extents via the
NOSEGMENT
clause of
CREATE INDEX
; useful in tuning SQL without consuming
resources required to build the physical index. Any index type can be created
as virtual.
Invisible
The index is not visible to the query optimizer. However, the structure of the
index is maintained as table data is modified. Useful for testing an index
before making it visible to the application. Any index type can be created as
invisible.
Global partitioned
Global index across all partitions in a partitioned table or regular table. This
can be a B-tree index type and can't be a bitmap index type.
Local partitioned
Local index based on individual partitions in a partitioned table. This can be
either a B-tree or bitmap index type.
Domain
Specific for an application or cartridge.
B-tree cluster
Used with clustered tables.
Hash cluster
Used with hash clusters.
