Oracle7 Server SQL Reference

Contents Index Home Previous Next

CREATE INDEX

Purpose

To create an index on one or more columns of a table or a cluster. An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows.

Prerequisites

To create an index in your own schema, one of the following conditions must be true:

To create an index in another schema, you must have CREATE ANY INDEX system privilege.

Also, the owner of the schema to contain the index must have either space quota on the tablespace to contain the index or UNLIMITED TABLESPACE system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the tablespace's label and match the table's label. If the table was created at DBHIGH or DBLOW, you must explicitly set your label to DBHIGH or DBLOW. You can create an index in another user's schema if your DBMS label dominates the creation label of the other user.

Syntax

Keywords and Parameters

UNIQUE

specifies that the value of the column (or combination of columns) in the table to be indexed must be unique.

schema

is the schema to contain the index. If you omit schema, Oracle7 creates the index in your own schema.

index

is the name of the index to be created.

table

is the name of the table for which the index is to be created. If you do not qualify table with schema, Oracle7 assumes the table is contained in your own schema.

column

is the name of a column in the table. An index can have as many as 16 columns. A column of an index cannot be of datatype LONG or LONG RAW.

ASC DESC

are allowed for DB2 syntax compatibility, although indexes are always created in ascending order. Indexes on character data are created in ascending order of the character values in the database character set.

CLUSTER

specifies the cluster for which a cluster index is to be created. If you do not qualify cluster with schema, Oracle7 assumes the cluster is contained in your current schema. You cannot create a cluster index for a hash cluster.

INITRANS MAXTRANS

establishes values for these parameters for the index. See the INITRANS and MAXTRANS parameters of the CREATE TABLE command [*].

TABLESPACE

is the name of the tablespace to hold the index. If you omit this option, Oracle7 creates the index in the default tablespace of the owner of the schema containing the index.

STORAGE

establishes the storage characteristics for the index. See the STORAGE clause [*].

PCTFREE

is the percentage of space to leave free for updates and insertions within each of the index's data blocks.

NOSORT

indicates to Oracle7 that the rows are stored in the database in ascending order and therefore Oracle7 does not have to sort the rows when creating the index.

RECOVERABLE

specifies that the creation of the index will be logged in the redo log file. This is the default.

If the database is run in ARCHIVELOG mode, media recovery from a backup will recreate the index. You cannot specify RECOVERABLE when using NOARCHIVELOG mode.

UNRECOVERABLE

specifies that the creation of the index will not be logged in the redo log file. As a result, media recovery will not recreate the index.

Using this keyword makes index creation faster than using the RECOVERABLE option because redo log entries are not written.

PARALLEL

specifies the degree of parallelism for creating the index. See the parallel_clause [*].

Usage Notes

An index is an ordered list of all the values that reside in a group of one or more columns at a given time. Such a list makes queries that test the values in those columns vastly more efficient. Indexes also take up data storage space, however, and must be changed whenever the data is, so a cost-benefit analysis must be made in each case to determine whether and how indexes should be used. Oracle7 can use indexes to improve performance when:

When you initially insert rows into a new table, it is generally faster to create the table, insert the rows, and then create the index. If you create the index before inserting the rows, Oracle7 must update the index for every row inserted.

Oracle recommends that you do not explicitly define UNIQUE indexes on tables; uniqueness is strictly a logical concept and should be associated with the definition of a table. Alternatively, define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. Exceptions to this recommendation are usually performance related. For example, using a CREATE TABLE ... AS SELECT with a UNIQUE constraint is very much slower than creating the table without the constraint and then manually creating the UNIQUE index.

If indexes contain NULLs, the NULLS generally are considered distinct values. There is, however, one exception: if all the non-NULL values in two or more rows of an index are identical, the rows are considered identical; therefore, UNIQUE indexes prevent this from occurring. This does not apply if there are no non-NULL values--in other words, if the rows are entirely NULL..

When you create an index in Trusted Oracle7, it is labeled with your DBMS label.

Index Columns

An index can contain a maximum of 16 columns. The index entry becomes the concatenation of all data values from each column. You can specify the columns in any order. The order you choose is important to how Oracle7 uses the index.

When appropriate, Oracle7 uses the entire index or a leading portion of the index. Assume an index named IDX1 is created on columns A, B, and C of table TAB1 (in the order A, B, C). Oracle7 uses the index for references to columns A, B, C (the entire index); A, B; or just column A. References to columns B and C do not use the IDX1 index. Of course, you can also create another index just for columns B and C.

Multiple Indexes Per Table

Unlimited indexes can be created for a table provided that the combination of columns differ for each index. You can create more than one index using the same columns provided that you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:

CREATE INDEX emp_idx1 ON emp (ename, job);
CREATE INDEX emp_idx2 ON emp (job, ename);

You cannot create an index that references only one column in a table if another such index already exists.

Note that each index increases the processing time needed to maintain the table during updates to indexed data.

Note that there is overhead in maintaining indexes when a table is updated. Thus, updating a table with a single index will take less time than if the table had five indexes.

The NOSORT Option

The NOSORT option can substantially reduce the time required to create an index. Normal index creation first sorts the rows of the table based on the index columns and then builds the index. The sort operation is often a substantial portion of the total work involved. If the rows are physically stored in ascending order (based on the indexed column values), then the NOSORT option causes Oracle7 to bypass the sort phase of the process.

You cannot use the NOSORT option to create a cluster index.

The NOSORT option also reduces the amount of space required to build the index. Oracle7 uses temporary segments during the sort. Since a sort is not performed, the index is created with much less temporary space.

To use the NOSORT option, you must guarantee that the rows are physically sorted in ascending order. Because of the physical data independence inherent in relational database management systems, especially Oracle7, there is no way to force a physical internal order on a table. The CREATE INDEX command with the NOSORT option should be used immediately after the initial load of rows into a table.

You run no risk by trying the NOSORT option. If your rows are not in the ascending order, Oracle7 returns an error. You can issue another CREATE INDEX without the NOSORT option.

UNRECOVERABLE

The UNRECOVERABLE option may substantially reduce the time required to create a large index. This feature is particularly useful after creating a large table or cluster in parallel. For backup and recovery considerations, see Oracle7 Server Administrator's Guide.

Example I

To quickly create an index in parallel on a table that was created using a fast parallel load (so all rows are already sorted), you might issue the following statement:

CREATE INDEX i_loc
	ON big_table (akey)
	NOSORT
	UNRECOVERABLE
	PARALLEL (DEGREE 5)

Nulls

Nulls are not indexed.

Example II

Consider the following statement: 

SELECT ename 
	FROM emp 
	WHERE comm IS NULL

The above query does not use an index created on the COMM column.

Creating Cluster Indexes

Oracle7 does not automatically create an index for a cluster when the cluster is initially created. Data Manipulation Language statements cannot be issued against clustered tables until a cluster index has been created.

Example III

To create an index for the EMPLOYEE cluster, issue the following statement: 

CREATE INDEX ic_emp 
	ON CLUSTER employee

Note that no index columns are specified since the index is automatically built on all the columns of the cluster key.

Related Topics

ALTER INDEX command [*] DROP INDEX command [*] CONSTRAINT clause [*] STORAGE clause [*]

Contents Index Home Previous Next