Indexes are database objects that improve query performance by providing fast access paths to table data. Oracle Database supports several index types, each optimized for different use cases:

• NORMAL (index type: N): B-tree indexes suitable for high-cardinality columns and range queries.
  
  
• FUNCTION-BASED NORMAL (index type: NF): B-tree indexes on expressions or functions applied to columns.
  
  
• BITMAP (index type: B): Efficient for low-cardinality columns and data warehouse environments.
  
  
• FUNCTION-BASED BITMAP (index type: BF): Bitmap indexes on expressions or functions.
  
  
• DOMAIN (index type: D): Application-specific indexes for specialized data types.
  

These tables and sample data are used in the examples below:

CREATE TABLE table_index
(
    id NUMBER(1,0)
);

CREATE TABLE table_index_unique
(
    ID NUMBER(1,0)
);

The CREATE INDEX statement creates a B-tree index by default. Non-unique indexes allow duplicate values and are commonly used to improve query performance on frequently searched columns.

CREATE INDEX idx_table_index_id ON table_index (id);

Show the indexes of the table:

SELECT index_name, index_type, uniqueness, compression, blevel, num_rows
FROM user_indexes -- user_indexes | all_indexes | dba_indexes
WHERE table_name = 'TABLE_INDEX';

Query result:

| INDEX_NAME             | INDEX_TYPE | UNIQUENESS | COMPRESSION | BLEVEL | NUM_ROWS |
+------------------------+------------+------------+-------------+--------+----------+
| IDX_TABLE_INDEX_ID     | NORMAL     | NONUNIQUE  | DISABLED    | 0      | 0        |

Unique indexes enforce uniqueness constraints and prevent duplicate values in the indexed columns. They are automatically created when you define primary key or unique constraints.

CREATE UNIQUE INDEX idx_table_index_unique ON table_index_unique (id);

Show the indexes of the table:

SELECT index_name, index_type, uniqueness, compression, blevel, num_rows
FROM user_indexes -- user_indexes | all_indexes | dba_indexes
WHERE table_name = 'TABLE_INDEX_UNIQUE';

Query result:

| INDEX_NAME             | INDEX_TYPE | UNIQUENESS | COMPRESSION | BLEVEL | NUM_ROWS |
+------------------------+------------+------------+-------------+--------+----------+
| IDX_TABLE_INDEX_UNIQUE | NORMAL     | UNIQUE     | DISABLED    | 0      | 0        |

Index statistics help the Oracle optimizer make informed decisions about query execution plans.

1. Computing Statistics During Index Creation
   The COMPUTE STATISTICS clause gathers index statistics immediately during index creation.
   

   CREATE INDEX idx_table_index_id ON table_index (id) COMPUTE STATISTICS;
   

2. Computing Statistics for Existing Indexes
   Use ANALYZE INDEX to gather statistics for existing indexes.
   

   ANALYZE INDEX idx_table_index_id COMPUTE STATISTICS;
   

1. Marking an Index as Unusable
   The UNUSABLE clause marks an index as unusable, preventing Oracle from using it in query execution plans.
   

   ALTER INDEX idx_table_index_id UNUSABLE;
   

2. Rebuilding an Index
   Index rebuilding eliminates fragmentation and can reclaim space. It also makes unusable indexes available again.
   

   ALTER INDEX idx_table_index_id REBUILD;
   

   It is possible to compute statistics while rebuilding the index:
   

   ALTER INDEX idx_table_index_id REBUILD COMPUTE STATISTICS;
   

3. Renaming an Index
   Index renaming allows you to change the index name without dropping and recreating it.
   

   ALTER INDEX idx_table_index_id RENAME TO idx_table_index_id_new;
   

Function-based indexes allow indexing on expressions or function results. They require special management operations that are not available for regular indexes.

Note: The function must be valid and marked as DETERMINISTIC, and its signature must match the signature used when the index was created.

1. Disabling Function-Based Indexes
   The DISABLE clause can only be used with function-based indexes. This temporarily prevents the optimizer from using the index without dropping it.
   

   CREATE INDEX idx_table_index_nvl_id ON table_index (NVL(id,0));
   

   ALTER INDEX idx_table_index_nvl_id DISABLE;
   

   Attempting to disable a non-function-based index will result in an ORA-02243 error:
   

   Error starting at line 1 in command:
   ALTER INDEX IDX_TABLE_INDEX_ID DISABLE
   
   Error report:
   SQL Error: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option.
   *Cause: An option other than INITRANS, MAXTRANS, or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
   *Action: Specify only legal options.
   

2. Enabling Function-Based Indexes
   The ENABLE clause re-enables a previously disabled function-based index, making it available for the optimizer to use.
   

   ALTER INDEX idx_table_index_nvl_id ENABLE;
   

The DROP INDEX statement permanently removes an index from the database.

DROP INDEX idx_table_index_id;