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

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.

TABLESPACE

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.

STORAGE

Specifies the physical characteristics of the index as follows:

INITIAL

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.

NEXT

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.

MINEXTENTS

Specifies the number of extents to be allocated when this index is created. The minimum and default value is 1.

MAXEXTENTS

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.

PCTINCREASE

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.

FREELISTS

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.

FREELIST GROUPS

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.

PCTFREE

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.

NOSORT

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.

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.

LOGGING (Oracle8 only)

Specifies that redo log records be written during index creation.

NOLOGGING (Oracle8 only)

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.

UNRECOVERABLE (Oracle7 only)

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.179.252