SQL Statements: CREATE CLUSTER to CREATE SEQUENCE, 10 of 25

CREATE INDEX

Purpose

Use the CREATE INDEX statement to create an index on

One or more columns of a table, a partitioned table, an index-organized table, or a cluster
One or more scalar typed object attributes of a table or a cluster
A nested table storage table for indexing a nested table column

An index is a schema 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. Oracle supports several types of index:

Conventional (B*-tree) indexes
Bitmap indexes, which store rowids associated with a key value as a bitmap
Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.
Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include functions (built-in or user-defined).
Domain indexes, which are instances of an application-specific index of type .
See Also:

Oracle8i Concepts for a discussion of indexes

ALTER INDEX for information on modifying an index

DROP INDEX for information on dropping an index

Additional Topics

Prerequisites

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

The table or cluster to be indexed must be in your own schema.
You must have INDEX privilege on the table to be indexed.
You must have CREATE ANY INDEX system privilege.

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 tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege.

To create a domain index in your own schema, in addition to the prerequisites for creating a conventional index, you must also have EXECUTE privilege on the indextype. If you are creating a domain index in another user's schema, the index owner also must have EXECUTE privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype. See .

To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to true, and the QUERY_REWRITE_INTEGRITY parameter must be set to trusted.

See Also: CREATE INDEXTYPE

Syntax

cluster_index_clause::=

table_index_clause::=

index_expr_list::=

index_attributes::=

physical_attributes_clause::=

domain_index_clause::=

global_index_clause::=

global_partition_clause::=

local_index_clauses::=

on_range_partitioned_table_clause::=

segment_attributes_clause::=

on_hash_partitioned_table_clause::=

on_composite_partitioned_table_clause::=

index_subpartition_clause::=

parallel_clause::=

storage_clause: See storage_clause.

Keywords and Parameters

`UNIQUE`

Specify UNIQUE to indicate that the value of the column (or columns) upon which the index is based must be unique. If the index is local nonprefixed (see below), then the index key must contain the partitioning key.

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. Therefore, define UNIQUE integrity constraints on the desired columns.

Restrictions:

You cannot specify both UNIQUE and BITMAP.
You cannot specify UNIQUE for a domain index.

See Also: constraint_clause for information on integrity constraints

`BITMAP`

Specify BITMAP to indicate that index is to be created as a bitmap, rather than as a B-tree. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing.

Restrictions:

You cannot specify BITMAP when creating a global partitioned index or an index-organized table.
You cannot specify both UNIQUE and BITMAP.
You cannot specify BITMAP for a domain index.

See Also: Oracle8i Concepts and Oracle8i Performance Guide and Reference for more information about using bitmap indexes

`schema`

Specify the schema to contain the index. If you omit schema, Oracle creates the index in your own schema.

`index`

Specify the name of the index to be created. An index can contain several partitions.

`cluster_index_clause`

Use the cluster_index_clause to identify the cluster for which a cluster index is to be created. If you do not qualify cluster with schema, Oracle assumes the cluster is in your current schema. You cannot create a cluster index for a hash cluster.

See Also: CREATE CLUSTER

`table_index_clause`

Specify the table (and its attributes) on which you are defining the index. If you do not qualify table with schema, Oracle assumes the table is contained in your own schema.

You create an index on a nested table column by creating the index on the nested table storage table. Include the NESTED_TABLE_ID pseudocolumn of the storage table to create a UNIQUE index, which effectively ensures that the rows of a nested table value are distinct.

Restrictions:

If the index is local, then table must be partitioned.
If the table is index-organized, this statement creates a secondary index. You cannot specify BITMAP or REVERSE for this secondary index, and the combined size of the index key and the logical rowid should be less than half the block size.

If table is a temporary table, the index will also be temporary with the same scope (session or transaction) as table. The following restrictions apply to indexes on temporary table:

The index cannot be a partitioned index or a domain index.
You cannot specify the physical_attributes_clause or the parallel_clause.

You cannot specify LOGGING, NOLOGGING, or TABLESPACE.

See Also: CREATE TABLE and Oracle8i Concepts for more information on temporary tables

t_alias

Specify a correlation name (alias) for the table upon which you are building the index.

Note: This alias is required if the index_expression_list references any object type attributes or object type methods. See "Function-based Index on Type Method Example".

`index_expr_list`

The index_expr_list lets you specify the column or column expression upon which the index is based.

`index_attributes`

physical_attributes_clause

Use the physical_attributes_clause to establish values for physical and storage characteristics for the index. See CREATE TABLE .
Restriction: You cannot specify the PCTUSED parameter for an index.

PCTFREE

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

storage_clause

Use the storage_clause to establish the storage characteristics for the index.

See Also:

TABLESPACE

Specify the name of the tablespace to hold the index, index partition, or index subpartition. If you omit this clause, Oracle creates the index in the default tablespace of the owner of the schema containing the index.

For a local index, you can specify the keyword DEFAULT in place of tablespace. New partitions or subpartitions added to the local index will be created in the same tablespace(s) as the corresponding partitions or subpartitions of the underlying table.

COMPRESS

Specify COMPRESS to enable key compression, which eliminates repeated occurrence of key column values and may substantially reduce storage. Use integer to specify the prefix length (number of prefix columns to compress).

For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns.

Oracle compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns.
Restriction: You cannot specify COMPRESS for a bitmap index.

NOCOMPRESS

Specify NOCOMPRESS to disable key compression. This is the default.

NOSORT

Specify NOSORT to indicate to Oracle that the rows are stored in the database in ascending order, so that Oracle does not have to sort the rows when creating the index. If the rows of the indexed column or columns are not stored in ascending order, Oracle returns an error. For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table.
Restrictions:

You cannot specify REVERSE with this clause.

You cannot use this clause to create a cluster, partitioned, or bitmap index.

You cannot specify this clause for a secondary index on an index-organized table.

REVERSE

Specify REVERSE to store the bytes of the index block in reverse order, excluding the rowid. You cannot specify NOSORT with this clause.

You cannot reverse a bitmap index or an index-organized table.

LOGGING | NOLOGGING

Indicate whether the creation of the index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. It also specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against the index are logged or not logged. LOGGING is the default.

If index is nonpartitioned, this is the logging attribute of the index.

If index is partitioned, the logging attribute specified is

The default value of all partitions specified in the CREATE statement (unless you specify LOGGING|NOLOGGING in the PARTITION description clause)

The default value for the segments associated with the index partitions

The default value for local index partitions or subpartitions added implicitly during subsequent ALTER TABLE ... ADD PARTITION operations

In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALID and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, since the redo data is not logged. Thus if you cannot afford to lose this index, it is important to take a backup after the NOLOGGING operation.

If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the LOGGING operation will re-create the index. However, media recovery from a backup taken before the NOLOGGING operation will not re-create the index.

The logging attribute of the index is independent of that of its base table.

If you omit this clause, the logging attribute is that of the tablespace in which it resides.

See Also: Oracle8i Concepts and Oracle8i Parallel Server Concepts for more information about logging and parallel DML

ONLINE

Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.
Restriction: Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.

See Also: Oracle8i Concepts for a description of online index building and rebuilding

COMPUTE STATISTICS

Specify COMPUTE STATISTICS to collect statistics at relatively little cost during the creation of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan of execution for SQL statements.
The types of statistics collected depend on the type of index you are creating.

Note: If you create an index using another index (instead of a table), the original index might not provide adequate statistical information. Therefore, Oracle generally uses the base table to compute the statistics, which will improve the statistics but may negatively affect performance.

Additional methods of collecting statistics are available in PL/SQL packages and procedures.

See Also: Oracle8i Supplied PL/SQL Packages Reference

parallel_clause

Specify the parallel_clause if you want creation of the index to be parallelized.

Note:
The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior.

NOPARALLEL

Specify NOPARALLEL for serial execution. This is the default.

PARALLEL

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

PARALLEL integer

Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

`global_index_clause`

The global_index_clause lets you specify that the partitioning of the index is user defined and is not equipartitioned with the underlying table. By default, nonpartitioned indexes are global indexes.

PARTITION BY RANGE

Specify PARTITION BY RANGE to indicate that the global index is partitioned on the ranges of values from the columns specified in column_list. You cannot specify this clause for a local index.

(column_list)

Specify the name of the column(s) of a table on which the index is partitioned. The column_list must specify a left prefix of the index column list.

You cannot specify more than 32 columns in column_list, and the columns cannot contain the ROWID pseudocolumn or a column of type ROWID.

Note: If your enterprise has or will have databases using different character sets, use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets.

See Also: Oracle8i National Language Support Guide for more information on character set support

PARTITION partition

The PARTITION clause lets you describe the individual partitions. The number of clauses determines the number of partitions. If you omit partition, Oracle generates a name with the form SYS_Pn.

VALUES LESS THAN (value_list)

Specify the (noninclusive) upper bound for the current partition in a global index. The value_list is a comma-separated, ordered list of literal values corresponding to column_list in the partition_by_range_clause. Always specify MAXVALUE as the value_list of the last partition.
Restriction: You cannot specify this clause for a local index.

Note: If index is partitioned on a DATE column, and if the NLS date format does not specify the first two digits of the year, you must use the TO_DATE function with a 4-character format mask for the year. The NLS date format is determined implicitly by NLS_TERRITORY or explicitly by NLS_DATE_FORMAT.

See Also:

- Oracle8i National Language Support Guide for more information on these initialization parameters

- "Partitioned Table Example"

`local_index_clauses`

The local_index_clauses let you specify that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as table. Oracle automatically maintains LOCAL index partitioning as the underlying table is repartitioned.

on_range_partitioned_table_clause

Specify the name and attributes of an index on a range-partitioned table.

PARTITION partition

Specify the names of the individual partitions. The number of clauses determines the number of partitions. For a local index, the number of index partitions must be equal to the number of the table partitions, and in the same order.
If you omit partition, Oracle generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, the form SYS_Pn is used.

on_hash_partitioned_table_clause

Specify the name and attributes of an index on a hash-partitioned table. If you do not specify partition, Oracle uses the name of the corresponding base table partition, unless it conflicts with an explicitly specified name of another index partition. In this case, Oracle generates a name of the form SYS_Pnnn.
You can optionally specify TABLESPACE for all index partitions or for one or more individual partitions. If you do not specify TABLESPACE at the index or partition level, Oracle stores each index partition in the same tablespace as the corresponding table partition.

on_composite_partitioned_table_clause

Specify the name and attributes of an index on a composite-partitioned table. The first STORE IN clause specifies the default tablespace for the index subpartitions. You can override this storage by specifying a different tablespace in the index_subpartitioning_clause.
If you do not specify TABLESPACE for subpartitions either in this clause or in the index_subpartitioning_clause, Oracle uses the tablespace specified for index. If you also do not specify TABLESPACE for index, Oracle stores the subpartition in the same tablespace as the corresponding table subpartition.

STORE IN

The STORE IN clause lets you specify how index hash partitions (for a hash-partitioned index) or index subpartitions (for a composite-partitioned index) are to be distributed across various tablespaces. The number of tablespaces does not have to equal the number of index partitions. If the number of index partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.

DEFAULT

The DEFAULT clause is valid only for a local index on a hash or composite-partitioned table. This clause overrides any tablespace specified at the index level for a partition or subpartition, and stores the index partition or subpartition in the same partition as the corresponding table partition or subpartition.

index_subpartition_clause

The index_subpartition_clause lets you specify one or more tablespaces in which to store all subpartitions in partition or one or more individual subpartitions in partition. The subpartition inherits all other attributes from partition. Attributes not specified for partition are inherited from index.

domain_index_clause

Use the domain_index_clause to indicate that index is a domain index.
Restrictions:

The index_expr_list can specify only a single column.

You can define only one domain index on a column.

You cannot specify a bitmap, unique, or function-based domain index.

You cannot create a local domain index on a partitioned table.

You cannot create a domain index on a partitioned table with row movement enabled.

column

Specify the table columns or object attributes on which the index is defined. Each column can have only one domain index defined on it.
Restrictions:

You cannot create a domain index on a column of datatype REF, varray, nested table, LONG, or LONG RAW.

You can create a domain index on a column of user-defined type, but not on an attribute of a column of user-defined type if that attribute itself is a user-defined type.

indextype

Specify the name of the indextype. This name should be a valid schema object that you have already defined.

See Also: CREATE INDEXTYPE

PARAMETERS 'string'

Specify the parameter string that is passed uninterpreted to the appropriate indextype routine. The maximum length of the parameter string is 1000 characters.
Once the domain index is created, Oracle invokes this routine (see .) If the routine does not return successfully, the domain index is marked FAILED. The only operation supported on an failed domain index is DROP INDEX.

See Also: Oracle8i Data Cartridge Developer's Guide for information on these routines

Examples

`PARALLEL` Example

The following statement creates an index using 10 parallel execution servers, 5 to scan scott.emp and another 5 to populate the emp_ix index:

CREATE INDEX emp_idx
   ON scott.emp (ename)
   PARALLEL 5;

`COMPRESS` Example

To create an index with the COMPRESS clause, you might issue the following statement:

CREATE INDEX emp_idx2 ON emp(job, ename) COMPRESS 1;

The index will compress repeated occurrences of job column values.

`NOLOGGING` Example

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. (Oracle will choose the appropriate degree of parallelism.)

CREATE INDEX i_loc
   ON big_table (akey)
   NOSORT
   NOLOGGING
   PARALLEL;

Cluster Index Example

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

CREATE INDEX ic_emp ON CLUSTER employee;

No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.

`NULL` Example

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 unless it is a bitmap index.

Function-Based Index Example

The following statements creates a function-based index on the emp table based on an uppercase evaluation of the ename column:

CREATE INDEX emp_i ON emp (UPPER(ename));

To ensure that Oracle will use the index rather than performing a full table scan, be sure that the value of the function is not null in subsequent queries. For example, the statement

SELECT * FROM emp WHERE UPPER(ename) IS NOT NULL
   ORDER BY UPPER(ename);

is guaranteed to use the index, but without the WHERE clause, Oracle may perform a full table scan.

In the next statements showing index creation and subsequent query, Oracle will use index emp_fi even though the columns are in reverse order in the query:

CREATE INDEX emp_fi ON emp(cola + colb);

SELECT * FROM emp WHERE colb + cola > 500;

Function-based Index on Type Method Example

This example entails an object type rectangle containing two number attributes: length and width. The area() method computes the area of the rectangle.

CREATE TYPE rectangle AS OBJECT  
( length NUMBER, 
  width NUMBER, 
  MEMBER FUNCTION area RETURN NUMBER DETERMINISTIC 
); 
 
CREATE OR REPLACE TYPE BODY rectangle AS 
  MEMBER FUNCTION area RETURN NUMBER IS 
  BEGIN 
   RETURN (length*width); 
  END; 
END;

Now, if you create a table rectab of type rectangle, you can create a function-based index on the area() method as follows:

CREATE TABLE recttab OF rectangle; 
CREATE INDEX area_idx ON recttab x (x.area());

You can use this index efficiently to evaluate a query of the form:

SELECT * FROM recttab x WHERE x.area() > 100;

Computing Statistics Example

The following statement collects statistics on the nonpartitioned emp_indx index:

CREATE INDEX emp_indx ON emp(empno) COMPUTE STATISTICS;

The type of statistics collected depends on the type of index you are creating.

See Also: Oracle8i Concepts

Partitioned Index Example

The following statement creates a global prefixed index stock_ix on table stock_xactions with two partitions, one for each half of the alphabet. The index partition names are system generated:

CREATE INDEX stock_ix ON stock_xactions
  (stock_symbol, stock_series)
   GLOBAL PARTITION BY RANGE (stock_symbol)
     (PARTITION VALUES LESS THAN ('N') TABLESPACE ts3,
      PARTITION VALUES LESS THAN (MAXVALUE) TABLESPACE ts4);

Index on Hash-Partitioned Table Example.

This statement creates a local index on the item column of the sales table. The STORE IN clause immediately following LOCAL indicates that sales is hash partitioned. Oracle will distribute the hash partitions between the tbs1 and tbs2 tablespaces:

CREATE INDEX sales_idx ON sales(item) LOCAL
   STORE IN (tbs1, tbs2);

Index on Composite-Partitioned Table Example.

This statement creates a local index on the sales table, which is composite-partitioned. The STORAGE clause specifies default storage attributes for the index. The STORE IN clause specifies one or more default tablespaces for the index subpartitions. However, this default is overridden for the four subpartitions of partition q3_1977, because separate TABLESPACE is specified.

CREATE INDEX sales_idx ON sales(sale_date, item)
   STORAGE (INITIAL 1M, MAXEXTENTS UNLIMITED)
   LOCAL
   STORE IN (tbs1, tbs2, tbs3, tbs4, tbs5)
   (PARTITION q1_1997, PARTITION q2_1997,
    PARTITION q3_1997
      (SUBPARTITION q3_1997_s1 TABLESPACE ts2, 
       SUBPARTITION q3_1997_s2 TABLESPACE ts4, 
       SUBPARTITION q3_1997_s3 TABLESPACE ts6,
       SUBPARTITION q3_1997_s4 TABLESPACE ts8),
    PARTITION q4_1997,
    PARTITION q1_1998);

Bitmap Index Example

To create a bitmap partitioned index on a table with four partitions, issue the following statement:

CREATE BITMAP INDEX partno_ix

ON lineitem(partno)
TABLESPACE ts1
LOCAL (PARTITION quarter1 TABLESPACE ts2,
       PARTITION quarter2 STORAGE (INITIAL 10K NEXT 2K),
       PARTITION quarter3 TABLESPACE ts2,
       PARTITION quarter4);

Index on Nested Table Example

In the following example, UNIQUE index uniq_proj_indx is created on storage table nested_project_table. Including pseudocolumn nested_table_id ensures distinct rows in nested table column projs_managed:

CREATE TYPE proj_type AS OBJECT
   (proj_num NUMBER, proj_name VARCHAR2(20));
CREATE TYPE proj_table_type AS TABLE OF proj_type;
CREATE TABLE employee ( emp_num NUMBER, emp_name CHAR(31),
   projs_managed proj_table_type )
   NESTED TABLE projs_managed STORE AS nested_project_table;
CREATE UNIQUE INDEX uniq_proj_indx
   ON nested_project_table ( NESTED_TABLE_ID, proj_num);

`t_alias`	Specify a correlation name (alias) for the table upon which you are building the index.
	Note: This alias is required if the `index_expression_list` references any object type attributes or object type methods. See "Function-based Index on Type Method Example".

`column`	Specify the name of a column in the table. A bitmap index can have a maximum of 30 columns. Other indexes can have as many as 32 columns. Restriction: You cannot create an index on columns or attributes whose type is user-defined, `LONG`, `LONG` `RAW`, LOB, or `REF`, except that Oracle supports an index on REF type columns or attributes that have been defined with a `SCOPE` clause.
	You can create an index on a scalar object attribute column or on the system-defined `NESTED_TABLE_ID` column of the nested table storage table. If you specify an object attribute column, the column name must be qualified with the table name. If you specify a nested table column attribute, it must be qualified with the outermost table name, the containing column name, and all intermediate attribute names leading to the nested table column attribute.
`column_expression`	is an expression built from columns of `table`, constants, SQL functions, and user-defined functions. When you specify `column_expression`, you create a function-based index. Name resolution of the function is based on the schema of the index creator. User-defined functions used in `column_expression` are fully name resolved during the `CREATE` `INDEX` operation.
	After creating a function-based index, collect statistics on both the index and its base table using the `ANALYZE` statement. Oracle cannot use the function-based index until these statistics have been generated. See Also: ANALYZE
	Notes on function-based indexes: When you subsequently query a table that uses a function-based index, you must ensure in the query that `column_expression` is not null. However, Oracle will use a function-based index in a query even if the columns specified in the `WHERE` clause are in a different order than their order in the `column_expression` that defined the function-based index. See Also: The "Function-Based Index Example"
	If the function on which the index is based becomes invalid or is dropped, Oracle marks the index `DISABLED`. Queries on a `DISABLED` index fail if the optimizer chooses to use the index. DML operations on a `DISABLED` index fail unless the index is also marked `UNUSABLE` and the parameter `SKIP_UNUSABLE_INDEXES` is set to `true`. See Also: ALTER SESSION for more information on this parameter
	Oracle's use of function-based indexes is also affected by the setting of the `QUERY_REWRITE_ENABLED` session parameter. See Also: ALTER SESSION
	If a public synonym for a function, package, or type is used in `column_expression`, and later an actual object with the same name is created in the table owner's schema, then Oracle will disable the function-based index. When you subsequently enable the function-based index using `ALTER` `INDEX` ... `ENABLE` or `ALTER` `INDEX` ... `REBUILD`, the function, package, or type used in the `column_expression` will continue to resolve to the function, package, or type to which the public synonym originally pointed. It will not resolve to the new function, package, or type.
	If the definition of a function-based index generates internal conversion to character data, use caution when changing NLS parameter settings. Function-based indexes use the current database settings for NLS parameters. If you reset these parameters at the session level, queries using the function-based index may return incorrect results. Two exceptions are the collation parameters (`NLS_SORT` and `NLS_COMP`). Oracle handles the conversions correctly even if these have been reset at the session level.
	Restrictions on function-based indexes: Any user-defined function referenced in `column_expression` must be `DETERMINISTIC`.
	For a function-based globally partitioned index, the `column_expression` cannot be the partitioning key.
	All functions must be specified with parentheses, even if they have no parameters. Otherwise Oracle interprets them as column names.
	Any function you specify in `column_expression` must return a repeatable value. For example, you cannot specify the `SYSDATE` or `USER` function or the `ROWNUM` pseudocolumn.
	You cannot build a function-based index on LOB, `REF`, nested table, or varray columns. In addition, the function in `column_expression` cannot take as arguments any objects with attributes of type LOB, `REF`, nested table, or varray.
	The `column_expression` cannot contain any aggregate functions. See Also: CREATE FUNCTION and PL/SQL User's Guide and Reference
`ASC` \| `DESC`	Use `ASC` or `DESC` to indicate whether the index should be created in ascending or descending order. Indexes on character data are created in ascending or descending order of the character values in the database character set. Oracle treats descending indexes as if they were function-based indexes. You do not need the `QUERY` `REWRITE` or `GLOBAL` `QUERY` `REWRITE` privileges to create them, as you do with other function-based indexes. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement. Restriction: You cannot specify either of these clauses for a domain index. You cannot specify `DESC` for a reverse index. Oracle ignores `DESC if` index is bitmapped or if the `COMPATIBLE` initialization parameter is set to a value less than 8.1.0.

`physical_attributes_clause`	Use the `physical_attributes_clause` to establish values for physical and storage characteristics for the index. See CREATE TABLE . Restriction: You cannot specify the `PCTUSED` parameter for an index.
	`PCTFREE`	Specify the percentage of space to leave free for updates and insertions within each of the index's data blocks.
	`storage_clause`	Use the storage_clause to establish the storage characteristics for the index. See Also:
`TABLESPACE`	Specify the name of the tablespace to hold the index, index partition, or index subpartition. If you omit this clause, Oracle creates the index in the default tablespace of the owner of the schema containing the index.
	For a local index, you can specify the keyword `DEFAULT` in place of `tablespace`. New partitions or subpartitions added to the local index will be created in the same tablespace(s) as the corresponding partitions or subpartitions of the underlying table.
`COMPRESS`	Specify `COMPRESS` to enable key compression, which eliminates repeated occurrence of key column values and may substantially reduce storage. Use `integer` to specify the prefix length (number of prefix columns to compress). For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1. For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns.
	Oracle compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns. Restriction: You cannot specify `COMPRESS` for a bitmap index.
`NOCOMPRESS`	Specify `NOCOMPRESS` to disable key compression. This is the default.
`NOSORT`	Specify `NOSORT` to indicate to Oracle that the rows are stored in the database in ascending order, so that Oracle does not have to sort the rows when creating the index. If the rows of the indexed column or columns are not stored in ascending order, Oracle returns an error. For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table. Restrictions: You cannot specify `REVERSE` with this clause. You cannot use this clause to create a cluster, partitioned, or bitmap index. You cannot specify this clause for a secondary index on an index-organized table.
`REVERSE`	Specify `REVERSE` to store the bytes of the index block in reverse order, excluding the rowid. You cannot specify `NOSORT` with this clause.
	You cannot reverse a bitmap index or an index-organized table.
`LOGGING` \| `NOLOGGING`	Indicate whether the creation of the index will be logged (`LOGGING`) or not logged (`NOLOGGING`) in the redo log file. It also specifies that subsequent Direct Loader (SQL*Loader) and direct-load `INSERT` operations against the index are logged or not logged. `LOGGING` is the default.
	If `index` is nonpartitioned, this is the logging attribute of the index.
	If `index` is partitioned, the logging attribute specified is The default value of all partitions specified in the `CREATE` statement (unless you specify `LOGGING`\|`NOLOGGING` in the `PARTITION` description clause) The default value for the segments associated with the index partitions The default value for local index partitions or subpartitions added implicitly during subsequent `ALTER` `TABLE` ... `ADD` `PARTITION` operations
	In `NOLOGGING` mode, data is modified with minimal logging (to mark new extents `INVALID` and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, since the redo data is not logged. Thus if you cannot afford to lose this index, it is important to take a backup after the `NOLOGGING` operation.
	If the database is run in `ARCHIVELOG` mode, media recovery from a backup taken before the `LOGGING` operation will re-create the index. However, media recovery from a backup taken before the `NOLOGGING` operation will not re-create the index.
	The logging attribute of the index is independent of that of its base table.
	If you omit this clause, the logging attribute is that of the tablespace in which it resides.
	See Also: Oracle8i Concepts and Oracle8i Parallel Server Concepts for more information about logging and parallel DML
`ONLINE`	Specify `ONLINE` to indicate that DML operations on the table will be allowed during creation of the index. Restriction: Parallel DML is not supported during online index building. If you specify `ONLINE` and then issue parallel DML statements, Oracle returns an error. See Also: Oracle8i Concepts for a description of online index building and rebuilding
`COMPUTE` `STATISTICS`	Specify `COMPUTE` `STATISTICS` to collect statistics at relatively little cost during the creation of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan of execution for SQL statements. The types of statistics collected depend on the type of index you are creating.
	Note: If you create an index using another index (instead of a table), the original index might not provide adequate statistical information. Therefore, Oracle generally uses the base table to compute the statistics, which will improve the statistics but may negatively affect performance.
	Additional methods of collecting statistics are available in PL/SQL packages and procedures. See Also: Oracle8i Supplied PL/SQL Packages Reference
`parallel_clause`	Specify the `parallel_clause` if you want creation of the index to be parallelized.

`NOPARALLEL`	Specify `NOPARALLEL` for serial execution. This is the default.
`PARALLEL`	Specify `PARALLEL` if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the `PARALLEL_THREADS_PER_CPU` initialization parameter.
`PARALLEL` `integer`	Specification of `integer` indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify `integer`.

`PARTITION` `BY` `RANGE`	Specify `PARTITION` `BY` `RANGE` to indicate that the global index is partitioned on the ranges of values from the columns specified in `column_list`. You cannot specify this clause for a local index.
`(column_list)`	Specify the name of the column(s) of a table on which the index is partitioned. The `column_list` must specify a left prefix of the index column list.
	You cannot specify more than 32 columns in `column_list,` and the columns cannot contain the `ROWID` pseudocolumn or a column of type `ROWID`.
	Note: If your enterprise has or will have databases using different character sets, use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets. See Also: Oracle8i National Language Support Guide for more information on character set support
`PARTITION` `partition`	The `PARTITION` clause lets you describe the individual partitions. The number of clauses determines the number of partitions. If you omit `partition`, Oracle generates a name with the form SYS_P`n`.
`VALUES` `LESS` `THAN` (`value_list`)	Specify the (noninclusive) upper bound for the current partition in a global index. The `value_list` is a comma-separated, ordered list of literal values corresponding to `column_list` in the `partition_by_range_clause`. Always specify `MAXVALUE` as the `value_list` of the last partition. Restriction: You cannot specify this clause for a local index.
	Note: If `index` is partitioned on a `DATE` column, and if the NLS date format does not specify the first two digits of the year, you must use the `TO_DATE` function with a 4-character format mask for the year. The NLS date format is determined implicitly by `NLS_TERRITORY` or explicitly by `NLS_DATE_FORMAT`.
	See Also: - Oracle8i National Language Support Guide for more information on these initialization parameters - "Partitioned Table Example"

`on_range_partitioned_table_clause`	Specify the name and attributes of an index on a range-partitioned table.
	`PARTITION` `partition`	Specify the names of the individual partitions. The number of clauses determines the number of partitions. For a local index, the number of index partitions must be equal to the number of the table partitions, and in the same order. If you omit `partition`, Oracle generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, the form SYS_P`n` is used.
`on_hash_partitioned_table_clause`	Specify the name and attributes of an index on a hash-partitioned table. If you do not specify `partition`, Oracle uses the name of the corresponding base table partition, unless it conflicts with an explicitly specified name of another index partition. In this case, Oracle generates a name of the form `SYS_P``nnn`. You can optionally specify `TABLESPACE` for all index partitions or for one or more individual partitions. If you do not specify `TABLESPACE` at the index or partition level, Oracle stores each index partition in the same tablespace as the corresponding table partition.
`on_composite_partitioned_table_clause`	Specify the name and attributes of an index on a composite-partitioned table. The first `STORE` `IN` clause specifies the default tablespace for the index subpartitions. You can override this storage by specifying a different tablespace in the `index_subpartitioning_clause`. If you do not specify `TABLESPACE` for subpartitions either in this clause or in the `index_subpartitioning_clause`, Oracle uses the tablespace specified for `index`. If you also do not specify `TABLESPACE` for `index`, Oracle stores the subpartition in the same tablespace as the corresponding table subpartition.
`STORE` `IN`	The `STORE` `IN` `clause` lets you specify how index hash partitions (for a hash-partitioned index) or index subpartitions (for a composite-partitioned index) are to be distributed across various tablespaces. The number of tablespaces does not have to equal the number of index partitions. If the number of index partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.
	`DEFAULT`	The `DEFAULT` clause is valid only for a local index on a hash or composite-partitioned table. This clause overrides any tablespace specified at the index level for a partition or subpartition, and stores the index partition or subpartition in the same partition as the corresponding table partition or subpartition.
	`index_subpartition_clause`	The `index_subpartition_clause` lets you specify one or more tablespaces in which to store all subpartitions in `partition` or one or more individual subpartitions in `partition`. The subpartition inherits all other attributes from `partition`. Attributes not specified for `partition` are inherited from `index`.
`domain_index_clause`	Use the `domain_index_clause` to indicate that index is a domain index. Restrictions: The `index_expr_list` can specify only a single column. You can define only one domain index on a column. You cannot specify a bitmap, unique, or function-based domain index. You cannot create a local domain index on a partitioned table. You cannot create a domain index on a partitioned table with row movement enabled.
	`column`	Specify the table columns or object attributes on which the index is defined. Each `column` can have only one domain index defined on it. Restrictions: You cannot create a domain index on a column of datatype `REF`, varray, nested table, `LONG`, or `LONG` `RAW`. You can create a domain index on a column of user-defined type, but not on an attribute of a column of user-defined type if that attribute itself is a user-defined type.
	`indextype`	Specify the name of the indextype. This name should be a valid schema object that you have already defined. See Also: CREATE INDEXTYPE
	`PARAMETERS` '`string`'	Specify the parameter string that is passed uninterpreted to the appropriate indextype routine. The maximum length of the parameter string is 1000 characters. Once the domain index is created, Oracle invokes this routine (see .) If the routine does not return successfully, the domain index is marked `FAILED`. The only operation supported on an failed domain index is `DROP` `INDEX`. See Also: Oracle8i Data Cartridge Developer's Guide for information on these routines

CREATE INDEX

Purpose

Additional Topics

Prerequisites

Syntax

Keywords and Parameters

UNIQUE

BITMAP

schema

index

cluster_index_clause

table_index_clause

index_expr_list

index_attributes

global_index_clause

local_index_clauses

Examples

PARALLEL Example

COMPRESS Example

NOLOGGING Example

Cluster Index Example

NULL Example

Function-Based Index Example

Function-based Index on Type Method Example

Computing Statistics Example

Partitioned Index Example

Index on Hash-Partitioned Table Example.

Index on Composite-Partitioned Table Example.

Bitmap Index Example

Index on Nested Table Example

`UNIQUE`

`BITMAP`

`schema`

`index`

`cluster_index_clause`

`table_index_clause`

`index_expr_list`

`index_attributes`

`global_index_clause`

`local_index_clauses`

`PARALLEL` Example

`COMPRESS` Example

`NOLOGGING` Example

`NULL` Example