Oracle indexes

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.

Automatic indexing

Bitmap indexes

The error messages

• ORA-01408: such column list already indexed
• ORA-02158: invalid CREATE INDEX option
• ORA-39287: Cannot alter index column list.

Database objects