( global_partitioned_index ::=, local_partitioned_index ::=, index_attributes::=, domain_index_clause ::=)ĭescription of the illustration index_attributes.gif ( local_partitioned_index ::=, index_attributes::=)ĭescription of the illustration index_expr.gifĭescription of the illustration index_properties.gif Also, you must 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.ĭescription of the illustration create_index.gifĭescription of the illustration cluster_index_clause.gifĭescription of the illustration table_index_clause.gifĭescription of the illustration bitmap_join_index_clause.gif To create a function-based 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. Before creating a domain index, you should first define the indextype. If you are creating a domain index in another user's schema, then the index owner also must have the EXECUTE object privilege on the indextype and its underlying implementation type. To create a domain index in your own schema, in addition to the prerequisites for creating a conventional index, you must also have the EXECUTE object privilege on the indextype. Also, the owner of the schema to contain the index must have either the UNLIMITED TABLESPACE system privilege or space quota on the tablespaces to contain the index or index partitions. To create an index in another schema, you must have the CREATE ANY INDEX system privilege. You must have the CREATE ANY INDEX system privilege.
You must have the INDEX object privilege on the table to be indexed.
The table or cluster to be indexed must be in your own schema. To create an index in your own schema, one of the following conditions must be true: Oracle Database Concepts for a discussion of indexes
They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.ĭomain indexes, which are instances of an application-specific index of type indextype Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the tableįunction-based indexes, which are based on expressions. (By default, Oracle Database creates B-tree indexes.)īitmap indexes, which store rowids associated with a key value as a bitmap Oracle Database supports several types of index: One or more scalar typed object attributes of a table or a clusterĪ nested table storage table for indexing a nested table columnĪn 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. One or more columns of a table, a partitioned table, an index-organized table, or a cluster Use the CREATE INDEX statement to create an index on: