CREATE INDEX

Create an index.

Syntax:

Table Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON [schema.]table_name [tbl_alias]
         (col [ASC | DESC]) index_clause index_attribs

Bitmap Join Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON [schema.]table_name [tbl_alias]
         (col_expression [ASC | DESC])
            FROM [schema.]table_name [tbl_alias]
               WHERE condition [index_clause] index_attribs

Cluster Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON CLUSTER [schema.]cluster_name index_attribs

index_clauses:

LOCAL STORE IN (tablespace)

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

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

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

INDEXTYPE IS indextype [PARALLEL int|NOPARALLEL] [PARAMETERS ('ODCI_Params')]
 {This for table index only, not bitmap join Index}

index_attribs:
any combination of the following

    NOSORT|SORT
    REVERSE
    COMPRESS int
    NOCOMPRESS
    COMPUTE STATISTICS
    [NO]LOGGING
    ONLINE
    TABLESPACE {tablespace|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'; 

"Anything you build on a large scale or with intense passion invites chaos" - Francis Ford Coppola

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

Equivalent SQL Server command:

CREATE INDEX