Oracle7 Server Administrator's Guide

Creating Indexes

This section describes how to create an index, and includes the following topics:

Creating an Index Associated with a Constraint

Creating an Index Explicitly

Re-Creating an Existing Index

Before you can create a new index you must own or have the INDEX object privilege for the corresponding table. The schema that contains the index must also have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. To create an index in another user's schema, you must have the CREATE ANY INDEX system privilege.

To enable a UNIQUE key or PRIMARY KEY (which creates an associated index), the owner of the table needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.

LONG and LONG RAW columns cannot be indexed.

Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled; no action is required by the issuer of the CREATE TABLE or ALTER TABLE statement to create the index. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.

In general, it is better to create constraints to enforce uniqueness than it is to use the CREATE UNIQUE INDEX syntax. A constraint's associated index always assumes the name of the constraint; you cannot specify a specific name for a constraint index.

If you do not specify the storage options for an index, they are automatically set to the default storage options of the host tablespace.

Creating an Index Associated with a Constraint

You can set the storage options for the indexes associated with UNIQUE key and PRIMARY KEY constraints using the ENABLE clause with the USING INDEX option. The following statement defines a PRIMARY KEY constraint and specifies the associated index's storage option:

CREATE TABLE emp (

   empno NUMBER(5) PRIMARY KEY, . . . )

   ENABLE PRIMARY KEY USING INDEX

      TABLESPACE users

      PCTFREE 0;

Creating an Index Explicitly

You can create indexes explicitly (outside of integrity constraints) using the SQL command CREATE INDEX. The following statement creates an index named EMP_ENAME for the ENAME column of the EMP table:

CREATE INDEX emp_ename ON emp(ename)

   TABLESPACE users

   STORAGE (INITIAL 20K

      NEXT 20k

      PCTINCREASE 75)

   PCTFREE 0;

Notice that several storage settings are explicitly specified for the index.

Re-Creating an Existing Index

You can create an index using an existing index as the data source. Creating an index in this manner allows you to change storage characteristics, or move to a new tablespace. Re-creating an index based on an existing data source also removes intra-block fragmentation. In fact, compared to dropping the index and using the CREATE INDEX command, re-creating an existing index offers better performance.

Issue the following statement to re-create an existing index:

ALTER INDEX index name REBUILD;

The REBUILD clause must immediately follow the index name, and precede any other options. Also, the REBUILD clause cannot be used in conjunction with the DEALLOCATE STORAGE clause.

See Also: For more information on the ALTER INDEX command and optional clauses, see the Oracle7 Server SQL Reference.