Oracle indexes

As per MOS Note, 136158.1, an index data block is required to store at least two index values/entries and index values may not span multiple blocks.

Therefore, the maximum key length of an index depends on the block size.

See also ORA-01450: maximum key length (‥) exceeded.

An index is either

• non partitioned or
• partitioned.

A partitioned index is either a

• Global partitioned index, or a
• local partitioned index

A local partitioned index is either a

• local nonprefixed index or a
• local prefixed index

s.a. dba_join_ind_columns

A global index is similar to a partitioned table: depending on a criteria, multiple index-partititions are created.

The data dictionary lists available indexes in Oracle: DBA_INDEXES and their columns in dba_ind_columns.

dba_ind_columns allows to query the columns in an index.

dbms_space.create_index_cost determines the cost of creating an index (on an existing table).

An index can be created or made invisible in which case the index is not considered by the optimizer.

Function-based indexes

Automatic indexing

Bitmap indexes

The error messages

• ORA-01404: ALTER COLUMN will make an index too large
• ORA-01408: such column list already indexed
• ORA-01450: maximum key length (‥) exceeded
• ORA-02158: invalid CREATE INDEX option
• ORA-39287: Cannot alter index column list.

Database objects