|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
CREATE CLUSTER to CREATE JAVA, 10 of 12
INDEX statement to create an index on
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:
To create an index in your own schema, one of the following conditions must be true:
INDEXprivilege on the table to be indexed.
To create an index in another schema, you must have
INDEX system privilege. Also, the owner of the schema to contain the index must have either the
TABLESPACE system privilege or space quota on the tablespaces to contain the index or index partitions.
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.
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
REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the
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
storage_clause: See storage_clause.
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.
BITMAP to indicate that
index is to be created with a bitmap for each distinct key, rather than indexing each row separately. 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.
BITMAPwhen creating a global partitioned index.
BITMAPfor a domain index.
Specify the schema to contain the index. If you omit
schema, Oracle creates the index in your own schema.
Specify the name of the index to be created.
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.
Specify the table (and its attributes) on which you are defining the index. If you do not qualify
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.
tablemust be partitioned.
REVERSEfor this secondary index, and the combined size of the index key and the logical rowid should be less than half the block size.
tableis 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:
Specify a correlation name (alias) for the table upon which you are building the index.
This alias is required if the
index_expr, specify the column or column expression upon which the index is based.
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.
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.
Restriction: You cannot create an index on columns or attributes whose type is user-defined,
RAW, LOB, or
REF, except that Oracle supports an index on REF type columns or attributes that have been defined with a
Specify 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
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.
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.
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
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.
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
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_COMP). Oracle handles the conversions correctly even if these have been reset at the session level.
Restrictions on Function-Based Indexes
column_expressioncannot be the partitioning key.
column_expressioncan be any form of expression except a scalar subquery expression
column_expressionmust return a repeatable value. For example, you cannot specify the
USERfunction or the
column_expressioncannot contain any aggregate functions.
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
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
index is bitmapped or if the
COMPATIBLE initialization parameter is set to a value less than 8.1.0.
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_clauseto establish the storage characteristics for the index.
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 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).
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 to disable key compression. This is the default.
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 on NOSORT:
REVERSEwith this clause.
REVERSE to store the bytes of the index block in reverse order, excluding the rowid.
Restrictions on REVERSE:
NOSORTwith this clause.
Specify whether the creation of the index will be logged (
LOGGING) or not logged (
NOLOGGING) in the redo log file. This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path
INSERT operations against the index are logged or not logged.
LOGGING is the default.
index is nonpartitioned, this clause specifies the logging attribute of the index.
index is partitioned, this clause determines:
CREATEstatement (unless you specify
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
If the database is run in
ARCHIVELOG mode, media recovery from a backup made before the
LOGGING operation will re-create the index. However, media recovery from a backup made 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.
ONLINE to indicate that DML operations on the table will be allowed during creation of the index.
ONLINEand then issue parallel DML statements, Oracle returns an error.
ONLINEfor a bitmap index or a cluster index.
ONLINEfor a conventional index on a
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.
Additional methods of collecting statistics are available in PL/SQL packages and procedures.
parallel_clause if you want creation of the index to be parallelized.
NOPARALLEL for serial execution. This is the default.
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.
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
global_partitioned_index clause and the
local_partitioned_index clauses to partition
The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle9i Database Administrator's Guide for a discussion of these restrictions.
global_partitioned_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.
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
Oracle9i Globalization Support Guide for more information on character set support
PARTITION clause lets you describe the individual index partitions. The number of clauses determines the number of partitions. If you omit
partition, Oracle generates a name with the form
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.
local_partitioned_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.
Specify the name and attributes of an index on a range-partitioned table.
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.
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
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
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.
Specify the name and attributes of an index on a composite-partitioned table. The first
IN clause specifies the default tablespace for the index subpartitions. You can override this storage by specifying a different tablespace in the
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
index, Oracle stores the subpartition in the same tablespace as the corresponding table subpartition.
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.
DEFAULTclause 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_clauselets you specify one or more tablespaces in which to store all subpartitions in
partitionor one or more individual subpartitions in
partition. The subpartition inherits all other attributes from
partition. Attributes not specified for
partitionare inherited from
domain_index_clause to indicate that
index is a domain index.
Specify the table columns or object attributes on which the index is defined. You can define multiple domain indexes on a single column only if the underlying indextypes are different and the indextypes support a disjoint set of user-defined operators.
Restriction: You cannot create a domain index on a column of datatype
REF, varray, nested table,
indextype, specify the name of the indextype. This name should be a valid schema object that you have already defined.
If you have installed Oracle Text, you can use various built-in indextypes to create Oracle Text domain indexes. For more information on Oracle Text and the indexes it uses, please refer to Oracle Text Reference.
PARAMETERS clause, specify the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine. The maximum length of the parameter string is 1000 characters.
When you specify this clause at the top level of the syntax, the parameters become the default parameters for the index partitions. If you specify this clause as part of the
PARTITION] clause, you override any default parameters with parameters for the individual partition.
Once the domain index is created, Oracle invokes the appropriate ODCI routine. If the routine does not return successfully, the domain index is marked
FAILED. The only operations supported on an failed domain index are
INDEX and (for non-local indexes)
Restrictions on the domain_index_clause
index_exprcan specify only a single column.
bitmap_join_index_clause to define a bitmap join index. A bitmap join index is defined on a single table. For an index key made up of dimension table columns, it stores the fact table rowids corresponding to that key. In a data warehousing environment, the table on which the index is defined is commonly referred to as a fact table, and the tables with which this table is joined are commonly referred to as dimension tables. However, a star schema is not a requirement for creating a join index.
ON clause, first specify the fact table, and then inside the parentheses specify the columns of the dimension tables on which the index is defined.
FROM clause, specify the joined tables.
WHERE clause, specify the join condition.
Restrictions: In addition to the restrictions on bitmap indexes in general (see BITMAP), the following restrictions apply to bitmap join indexes:
local_index_clausesunless the fact table is partitioned.
The following statement was used to create the
ord_customer_ix on the
customer_id column of the sample table
To create the
ord_customer_ix index with the
COMPRESS clause, you might issue the following statement:
The index will compress repeated occurrences of
customer_id column values.
The following statement collects statistics on the
ord_customer_ix index during its creation:
The type of statistics collected depends on the type of index you are creating.
If the sample table
orders had been created using a fast parallel load (so all rows are already sorted), you might issue the following statement to quickly create an index in parallel. (Oracle chooses the appropriate degree of parallelism.)
To create an index for the
personnel cluster, which was created in "Creating a Cluster Example", issue the following statement:
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.
Consider the following statement:
The above query does not use an index created on the
commission_pct column unless it is a bitmap index.
The following statement creates a function-based index on the
employees table based on an uppercase evaluation of the
See the "Prerequisites" for the privileges and parameter settings required when creating function-based indexes.
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, this statement is guaranteed to use the index:
SELECT first_name, last_name FROM employees WHERE UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);
However, 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
income_ix even though the columns are in reverse order in the query:
CREATE INDEX income_ix ON employees(salary + (salary*commission_pct)); SELECT first_name||' '||last_name "Name" FROM employees WHERE (salary*commission_pct) + salary > 15000;
The following statement uses the function created in "Using a Packaged Procedure in a Function Example" to creates a function-based index on a LOB column in the sample
pm schema. The example then collects statistics on the function-based index, and selects rows from the demo table
print_media where that
CLOB column has fewer than 100 characters.
CREATE INDEX src_idx ON print_media(text_length(ad_sourcetext)); ANALYZE INDEX src_idx COMPUTE STATISTICS; SELECT product_id FROM print_media WHERE text_length(ad_sourcetext) < 1000; PRODUCT_ID ---------- 3060 2056 3106 2268
This example entails an object type
rectangle containing two number attributes:
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
rect_tab of type
rectangle, you can create a function-based index on the
area() method as follows:
You can use this index efficiently to evaluate a query of the form:
The following statement creates a global prefixed index
amount_sold on table
sh_sales with three partitions that divide the range of costs into three groups:
CREATE INDEX cost_ix ON sales (amount_sold) GLOBAL PARTITION BY RANGE (amount_sold) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2500), PARTITION p3 VALUES LESS THAN (MAXVALUE));
The following statement creates a local index on the
product_id column of the
product_information table. The
IN clause immediately following
LOCAL indicates that
product_information is hash partitioned. Oracle will distribute the hash partitions between the
The following statement creates a local index on the
composite_sales table, which is composite-partitioned. The
STORAGE clause specifies default storage attributes for the index. However, this default is overridden for the five subpartitions of partitions
q4_2000, because separate
TABLESPACE storage is specified.
"Composite-Partitioned Table Example" for the creation of the
CREATE INDEX sales_idx ON composite_sales(time_id, prod_id) STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL (PARTITION q1_1998, PARTITION q2_1998, PARTITION q3_1998, PARTITION q4_1998, PARTITION q1_1999, PARTITION q2_1999, PARTITION q3_1999, PARTITION q4_1999, PARTITION q1_2000, PARTITION q2_2000 (SUBPARTITION pq2001, SUBPARTITION pq2002, SUBPARTITION pq2003, SUBPARTITION pq2004, SUBPARTITION pq2005, SUBPARTITION pq2006, SUBPARTITION pq2007, SUBPARTITION pq2008), PARTITION q3_2000 (SUBPARTITION c1 TABLESPACE tbs1, SUBPARTITION c2 TABLESPACE tbs2, SUBPARTITION c3 TABLESPACE tbs3, SUBPARTITION c4 TABLESPACE tbs4, SUBPARTITION c5 TABLESPACE tbs5), PARTITION q4_2000 (SUBPARTITION pq4001 TABLESPACE tbs6, SUBPARTITION pq4002 TABLESPACE tbs7, SUBPARTITION pq4003 TABLESPACE tbs8, SUBPARTITION pq4004 TABLESPACE tbs9) );
The following example creates a bitmap partitioned index on a table with four partitions:
CREATE BITMAP INDEX partno_ix
The next statement creates a bitmap join index on the demo table
sh.sales (a partitioned table) based on columns from the
CREATE BITMAP INDEX sales_products_ix ON sales (p.prod_category, p. prod_status) FROM sales s, products p WHERE s.prod_id = p.prod_id LOCAL;
sales is a partitioned table, the index must be a locally partitioned bitmap index.
In the following example,
uniq_proj_indx is created on storage table
nested_project_table. Including pseudocolumn
nested_table_id ensures distinct rows in nested table column
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);
You can build an index on attributes of the declared type of a substitutable column. In addition, you can reference the subtype attributes by using the appropriate
TREAT function. The following example uses the table
books, which is created in "Substitutable Table and Column Examples". The statement creates an index on the
salary attribute of all employee authors in the
The target type in the argument of the
TREAT function must be the type that added the attribute being referenced. In the example, the target of
employee_t, which is the type that added the
If this condition is not satisfied, Oracle interprets the
TREAT function as any functional expression and creates the index as a function-based index. For example, the following statement creates a function-based index on the
salary attribute of part-time employees, assigning nulls to instances of all other types in the type hierarchy.
You can also build an index on the type-discriminant column underlying a substitutable column by using the
The following statement creates a bitmap index on the typeid of the author column of the books table: