CREATE INDEX
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
] [INITRANSinteger
] [MAXTRANSinteger
] [TABLESPACEtablespace_name
] [STORAGE ( [INITIALinteger
[K | M] ] [NEXTinteger
[K | M] ] [MINEXTENTSinteger
] [MAXEXTENTS {integer
| UNLIMITED} ] [PCTINCREASEinteger
] [FREELISTSinteger
] [FREELIST GROUPSinteger
] ) ] [PCTFREEinteger
] [NOSORT | REVERSE] [LOGGING | NOLOGGING] [UNRECOVERABLE]
Creates an index (index_name) on one or more columns of a table (table_name) or cluster (cluster_name).
Specifies that the index will be unique. That is, every row must have a unique value across all columns in the index.
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.
Specifies the cluster for which the index is to be created.
Specifies that the index is to be a global partitioned index; the syntax is shown later in the global_index_clause section.
Specifies that the index is to be a local partitioned index. The syntax is shown later in the local_index_clause section.
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.
Changes the maximum number of concurrent transactions that can update a block of the index. The value may be in the range 1-255 and should not normally be changed from the default, which is a function of the Oracle blocksize.
Specifies the name of the tablespace where this index will be stored. If this parameter is omitted, the default tablespace for the schema owner will be used.
Specifies the physical characteristics of the index as follows:
Specifies the size of the first extent for this index in bytes, kilobytes, or megabytes. If the value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.
Specifies the size of the next extent in bytes, kilobytes, or megabytes. If the value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.
Specifies the number of extents to be allocated when this index is created. The minimum and default value is 1.
Specifies the maximum number of extents that may be allocated for this index. The default will vary according to the database blocksize. Specify UNLIMITED for unlimited expansion.
Specifies the percentage by which each extent will grow over the previous extent. The default is 50, which means that each extent will be one-and-one-half times larger than the previous extent.
Specifies the number of free lists contained in each freelist group in this index. The default is 1 and the maximum depends on the database blocksize.
Specifies the number of groups of free lists for this index. The default is 1. This parameter should be used only with the Parallel Server option running in parallel mode.
Changes the percentage of space in each data block that will be kept free for future updates to this index. The value may be in the range - 99 and defaults to 10.
Specifies that rows have been inserted into the database in sequential order, thus, no sorting is required when creating the index. NOSORT cannot be specified with REVERSE.
Specifies that Oracle will store the bytes of the index key in reverse order. This is useful in situations where rows are always added in increasing order to allow the index to grow evenly rather than only at one end. It is also useful in a Parallel Server environment, allowing individual instances to insert information in different blocks, thus reducing pinging. REVERSE cannot be specified with NOSORT.
Specifies that redo log records be written during index creation.
Specifies that redo log records not be written during index creation. In case of a database failure, the index cannot be recovered by applying log files, and must be recreated. This option will speed the creation of indexes.
Specifies that redo log records not be written during index creation. In the case of a database failure, the index cannot be recovered by applying log files, and must be recreated. This option will speed the creation of indexes.