An index is a schema object that has the role to provide direct and fast access without reading the entire table. Indexes are created explicitly or automatically.
When using indexes you must consider the following tips:
- Create indexes after inserting table data and index on the correct tables / columns, that are most used in queries.
- Order index columns for performance and limit the number of indexes for each table.
- Assign index size and set storage parameters.
- Indexes are created in the default tablespace of the schema, you can specify the tablespace for each index.
- Drop indexes that are no longer useful or no longer needed.
B-tree indexes – By default, Oracle Database creates B-tree indexes(normal indexes).
CREATE INDEX index_name ON table_name(column_name);
The Bitmap indexes store the rowids column value in bits.
CREATE BITMAP INDEX bitmap_index_name ON table_name(column_name) TABLESPACE tbs_1 LOCAL(PARTITION ix_p1 TABLESPACE tbs_02, PARTITION ix_p2, PARTITION ix_p3 TABLESPACE tbs_03);
Partitioned indexes are partitions that store an entry for each value that appears in the indexed column of the table.
CREATE INDEX index_name ON table_name (column_name) GLOBAL PARTITION BY RANGE (column_name) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (MAXVALUE));
Function-based indexes are based on expressions. You can build queries that evaluate the value returned by an expression.
CREATE INDEX index_name ON table_name(UPPER(column_name));
A domain index is an instance of an index which is accessed by routines supplied by an indextype.