Oracle Database Administration: The Essential Refe

Name

CREATE INDEX

Synopsis

CREATE [UNIQUE | BITMAP] INDEX
[schema.]index_name
 ON
{[schema.]table_name
(column [ASC |
DESC][,column [ASC | DESC] ...] |
CLUSTER [schema.]cluster_name}
   [global_index_clause]
   [local_index_clause]
   [INITRANS integer]
   [MAXTRANS integer]
   [TABLESPACE tablespace_name]
   [STORAGE (
      [INITIAL integer[K | M] ]
      [NEXT integer[K | M] ]
      [MINEXTENTS integer]
      [MAXEXTENTS {integer | UNLIMITED} ]
      [PCTINCREASE integer]
      [FREELISTS integer]
      [FREELIST GROUPS integer]
      ) ]
   [PCTFREE integer]
   [NOSORT | REVERSE]
   [LOGGING | NOLOGGING]
   [UNRECOVERABLE]

Creates an index (index_name) on one or more columns of a table (table_name) or cluster (cluster_name).

Keywords

UNIQUE

Specifies that the index will be unique. That is, every row must have a unique value across all columns in the index.

BITMAP

Specifies that the index is to be created as a bitmap index; you can specify ASC (ascending) or DESC (descending) for compatibility with DB2 syntax, but these have no effect.

CLUSTER

Specifies the cluster for which the index is to be created.

global_index_clause

Specifies that the index is to be a global partitioned index; the syntax is shown later in the global_index_clause section.

local_index_clause

Specifies that the index is to be a local partitioned index. The syntax is shown later in the local_index_clause section.

INITRANS

Changes the number of transaction entries allocated to each block in the index. The value may be in the range 1-255 and should not normally be changed from the default of 2.

MAXTRANS ...