Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E17118-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE INDEXTYPE

Purpose

Use the CREATE INDEXTYPE statement to create an indextype, which is an object that specifies the routines that manage a domain (application-specific) index. Indextypes reside in the same namespace as tables, views, and other schema objects. This statement binds the indextype name to an implementation type, which in turn specifies and refers to user-defined index functions and procedures that implement the indextype.

See Also:

Oracle Database Data Cartridge Developer's Guide for more information on implementing indextypes

Prerequisites

To create an indextype in your own schema, you must have the CREATE INDEXTYPE system privilege. To create an indextype in another schema, you must have the CREATE ANY INDEXTYPE system privilege. In either case, you must have the EXECUTE object privilege on the implementation type and the supported operators.

An indextype supports one or more operators, so before creating an indextype, you must first design the operator or operators to be supported and provide functional implementation for those operators.

See Also:

CREATE OPERATOR

Syntax

create_indextype::=

Description of create_indextype.gif follows
Description of the illustration create_indextype.gif

using_type_clause::=

Description of using_type_clause.gif follows
Description of the illustration using_type_clause.gif

array_DML_clause ::=

Description of array_dml_clause.gif follows
Description of the illustration array_dml_clause.gif

storage_table_clause::=

Description of storage_table_clause.gif follows
Description of the illustration storage_table_clause.gif

Semantics

schema

Specify the name of the schema in which the indextype resides. If you omit schema, then Oracle Database creates the indextype in your own schema.

indextype

Specify the name of the indextype to be created.

FOR Clause

Use the FOR clause to specify the list of operators supported by the indextype.

using_type_clause

The USING clause lets you specify the type that provides the implementation for the new indextype.

For implementation_type, specify the name of the type that implements the appropriate Oracle Data Cartridge Interface (ODCI).

See Also:

Oracle Database Data Cartridge Developer's Guide for additional information on this interface

WITH LOCAL PARTITION

Use this clause to indicate that the indextype can be used to create local domain indexes on range- and list-partitioned tables. You use this clause in combination with the storage_table in several ways (see storage_table_clause).

If you omit this clause entirely, then you cannot subsequently use this indextype to create a local domain index on a range- or list-partitioned table.

storage_table_clause

Use this clause to specify how storage tables and partition maintenance operations for indexes built on this indextype are managed:

See Also:

Oracle Database Data Cartridge Developer's Guide for more information about storage tables for domain indexes

array_DML_clause

Use this clause to let the indextype support the array interface for the ODCIIndexInsert method.

type and varray_type If the data type of the column to be indexed is a user-defined object type, then you must specify this clause to identify the varray varray_type that Oracle should use to hold column values of type. If the indextype supports a list of types, then you can specify a corresponding list of varray types. If you omit schema for either type or varray_type, then Oracle assumes the type is in your own schema.

If the data type of the column to be indexed is a built-in system type, then any varray type specified for the indextype takes precedence over the ODCI types defined by the system.

See Also:

Oracle Database Data Cartridge Developer's Guide for more information on the ODCI array interface

Example

Creating an Indextype: Example The following statement creates an indextype named position_indextype and specifies the position_between operator that is supported by the indextype and the position_im type that implements the index interface. Refer to "Using Extensible Indexing" for an extensible indexing scenario that uses this indextype:

CREATE INDEXTYPE position_indextype
   FOR position_between(NUMBER, NUMBER, NUMBER)
   USING position_im;