|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Note:These operations also collect index statistics.
To create an index in your own schema, at least one of the following conditions must be true:
The table or cluster to be indexed is in your own schema.
INDEX privilege on the table to be indexed.
CREATE ANY INDEX system privilege.
To create an index in another schema, all of the following conditions must be true:
CREATE ANY INDEX system privilege.
The owner of the other schema has a quota for the tablespaces to contain the index or index partitions, or
UNLIMITED TABLESPACE system privilege.
This section contains the following topics:
You can create indexes explicitly (outside of integrity constraints) using the SQL statement
CREATE INDEX. The following statement creates an index named
emp_ename for the
ename column of the
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75);
Notice that several storage settings and a tablespace are explicitly specified for the index. If you do not specify storage options (such as
NEXT) for an index, the default storage options of the default or specified tablespace are automatically used.
See Also:Oracle Database SQL Language Reference for syntax and restrictions on the use of the
Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.
CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;
Alternatively, you can define
UNIQUE integrity constraints on the desired columns. The database enforces
UNIQUE integrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.
See Also:Oracle Database Performance Tuning Guide for more information about creating an index for performance
Oracle Database enforces a
UNIQUE key or
PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the
CREATE TABLE or
ALTER TABLE statement to create the index, but you can optionally specify a
USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.
To enable a
PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the
UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.
Note:An efficient procedure for enabling a constraint that can make use of parallelism is described in "Efficient Use of Integrity Constraints: A Procedure".
You can set the storage options for the indexes associated with
PRIMARY KEY constraints using the
USING INDEX clause. The following
CREATE TABLE statement enables a
PRIMARY KEY constraint and specifies the storage options of the associated index:
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;
If you require more explicit control over the indexes associated with
PRIMARY KEY constraints, the database lets you:
Specify an existing index that the database is to use to enforce the constraint
INDEX statement that the database is to use to create the index and enforce the constraint
These options are specified using the
INDEX clause. The following statements present some examples.
CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.
See Also:"Managing Integrity Constraints"
Create a new temporary tablespace using the
CREATE TABLESPACE or
CREATE TEMPORARY TABLESPACE statement.
TEMPORARY TABLESPACE option of the
ALTER USER statement to make this your new temporary tablespace.
Create the index using the
CREATE INDEX statement.
Drop this tablespace using the
DROP TABLESPACE statement. Then use the
ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.
Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.
You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.
The following statements illustrate online index build operations:
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
Note:Keep in mind that the time that it takes on online index build to complete is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online index builds when DML activity is low.
See Also:"Rebuilding an Existing Index"
In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked
DETERMINISTIC. Also, you just have the
EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.
Additionally, to use a function-based index:
The table must be analyzed after the index is created.
The query must be guaranteed not to need any
NULL values from the indexed expression, since
NULL values are not stored in indexes.
CREATE INDEXstores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the
ANALYZE INDEX...VALIDATE STRUCTUREstatement to validate this index.
To illustrate a function-based index, consider the following statement that defines a function-based index (
area_index) defined on the function
CREATE INDEX area_index ON rivers (area(geo));
In the following SQL statement, when
area(geo) is referenced in the
WHERE clause, the optimizer considers using the index
SELECT id, geo, area(geo), desc FROM rivers WHERE Area(geo) >5000;
Table owners should have
EXECUTE privileges on the functions used in function-based indexes.
Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an
ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled. The
ALTER INDEX...DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.
Note:An alternative to creating a function-based index is to add a virtual column to the target table and index the virtual column. See "About Tables" for more information.
Oracle Database Concepts for more information about function-based indexes
Oracle Database Advanced Application Developer's Guide for information about using function-based indexes in applications and examples of their use
Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance.
Key compression can be useful in the following situations:
You have a non-unique index where
ROWID is appended to make the key unique. If you use key compression here, the duplicate key is stored as a prefix entry on the index block without the
ROWID. The remaining rows become suffix entries consisting of only the
You have a unique multicolumn index.
You enable key compression using the
COMPRESS clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block:
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;
COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:
ALTER INDEX emp_ename REBUILD NOCOMPRESS;
See Also:Oracle Database Concepts for a more detailed discussion of key compression
If the index is partitioned, then all index partitions are marked
Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index.
The following procedure illustrates how to create unusable indexes and query the database for details about the index.
To create an unusable index:
If necessary, create the table to be indexed.
For example, create a hash-partitioned table called
hr.employees_part as follows:
sh@PROD> CONNECT hr Enter password: ** Connected. hr@PROD> CREATE TABLE employees_part 2 PARTITION BY HASH (employee_id) PARTITIONS 2 3 AS SELECT * FROM employees; Table created. hr@PROD> SELECT COUNT(*) FROM employees_part; COUNT(*) ---------- 107
Create an index with the the keyword
The following example creates a locally partitioned index on
employees_part, naming the index partitions
p2_i_emp_ename, and making
hr@PROD> CREATE INDEX i_emp_ename ON employees_part (employee_id) 2 LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename); Index created.
Optionally, verify that the index is unusable by querying the data dictionary.
The following example queries the status of index
i_emp_ename and its two partitions, showing that only partition
p2_i_emp_ename is unusable:
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS 2 FROM USER_INDEXES 3 WHERE INDEX_NAME = 'I_EMP_ENAME' 4 UNION ALL 5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS 6 FROM USER_IND_PARTITIONS 7 WHERE PARTITION_NAME LIKE '%I_EMP_ENAME%'; INDEX OR PARTITION NAME STATUS ------------------------------ -------- I_EMP_ENAME N/A P1_I_EMP_ENAME UNUSABLE P2_I_EMP_ENAME USABLE
Optionally, query the data dictionary to determine whether storage exists for the partitions.
For example, the following query shows that only index partition
p2_i_emp_ename occupies a segment. Because you created
p1_i_emp_ename as unusable, the database did not allocate a segment for it.
hr@PROD> COL PARTITION_NAME FORMAT a14 hr@PROD> COL SEG_CREATED FORMAT a11 hr@PROD> SELECT p.PARTITION_NAME, p.STATUS AS "PART_STATUS", 2 p.SEGMENT_CREATED AS "SEG_CREATED", 3 FROM USER_IND_PARTITIONS p, USER_SEGMENTS s 4 WHERE s.SEGMENT_NAME = 'I_EMP_ENAME'; PARTITION_NAME PART_STA SEG_CREATED -------------- -------- ----------- P2_I_EMP_ENAME USABLE YES P1_I_EMP_ENAME UNUSABLE NO
Oracle Database SQL Language Reference for more information on creating unusable indexes, including restrictions.
An invisible index is an index that is ignored by the optimizer unless you explicitly set the
OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to
TRUE at the session or system level.
To create an invisible index:
CREATE INDEX statement with the
The following statement creates an invisible index named
emp_ename for the
ename column of the
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) INVISIBLE;
Oracle Database SQL Language Reference for more information on creating invisible indexes