CREATE INDEX

Create an index.

Syntax:

   CREATE [UNIQUE|BITMAP] INDEX [schema.]index
      ON [schema.]TABLE [tbl_alias]
         (col [ASC | DESC]) index_clause index_attribs

   CREATE [UNIQUE|BITMAP] INDEX [schema.]index
      ON [schema.]TABLE [tbl_alias]
         (col_expression [ASC | DESC]) index_clause index_attribs

   CREATE [UNIQUE|BITMAP] INDEX [schema.]index
      ON CLUSTER [schema.]cluster index_attribs

index_clauses:

INDEXTYPE IS indextype[PARAMETERS ('string')]

LOCAL STORE IN {tablespace_name|DEFAULT}

LOCAL (PARTITION [partition
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace_name|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause]
       [STORE IN {tablespace_name|DEFAULT]
       [SUBPARTITION [subpartition [TABLESPACE tablespace_name]]]])

GLOBAL PARTITION BY RANGE (col_list)
   ( PARTITION partition VALUES LESS THAN (value_list)
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace_name|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause] )

index_attribs:
any combination of the following

    NOSORT|REVERSE
    COMPRESS int
    NOCOMPRESS
    COMPUTE STATISTICS
    [NO]LOGGING
    ONLINE
    TABLESPACE {tablespace_name|DEFAULT}
    PCTFREE int
    PCTUSED int
    INITRANS int
    MAXTRANS int
    STORAGE storage_clause
    PARALLEL parallel_clause

If the PARALLEL clause is used it should be the last option.

For example:
To create a function-based index which allows case-insensitive searches.

CREATE INDEX idx_case_ins ON my_table(UPPER(empname));

SELECT * FROM my_table WHERE UPPER(empname) = 'KARL'; 

Related Commands:

INDEX - ALTER INDEX
INDEX - DROP INDEX
INDEXTYPE - CREATE INDEXTYPE

Related Views:

 DBA_INDEXES            ALL_INDEXES            USER_INDEXES 
                                                                INDEX_HISTOGRAM 
                                                                INDEX_STATS 
 DBA_INDEXTYPES           ALL_INDEXTYPES           USER_INDEXTYPES 
 DBA_INDEXTYPE_OPERATORS  ALL_INDEXTYPE_OPERATORS  USER_INDEXTYPE_OPERATORS 
 DBA_IND_COLUMNS        ALL_IND_COLUMNS        USER_IND_COLUMNS 
 DBA_IND_EXPRESSIONS    ALL_IND_EXPRESSIONS    USER_IND_EXPRESSIONS 
 DBA_IND_PARTITIONS     ALL_IND_PARTITIONS     USER_IND_PARTITIONS 
 DBA_IND_SUBPARTITIONS  ALL_IND_SUBPARTITIONS  USER_IND_SUBPARTITIONS