Creating, Dropping, and Commenting Indextypes

This section describes the SQL statements that manipulate indextypes.

Creating Indextypes

When you have implemented the ODCIIndex interface and defined the implementation type, you can create a new indextype by specifying the list of operators supported by the indextype and referring to the type that implements the index interface.

Using the information retrieval example, the DDL statement for defining the new indextype TextIndexType, which supports the Contains operator and whose implementation is provided by the type TextIndexMethods, as demonstrated by Example 8-1.

Example 8-1 Creating an Indextype

CREATE INDEXTYPE TextIndexType
FOR Contains (VARCHAR2, VARCHAR2)
USING TextIndexMethods
WITH SYSTEM MANAGED STORAGE TABLES;

In addition to the ODCIIndex interface routines, the implementation type must implement the ODCIGetInterfaces() routine. This routine returns the version of the interface implemented by the implementation type. Oracle invokes the ODCIGetInterfaces() routine when CREATE INDEXTYPE is executed.

Dropping Indextypes

To remove the definition of an indextype, use the DROP statement, as in Example 8-2:

Example 8-2 Dropping an IndexType

DROP INDEXTYPE TextIndexType;

The default DROP behavior is DROP RESTRICT semantics, that is, if one or more domain indexes exist that uses the indextype then the DROP operation is disallowed. Users can override the default behavior with the FORCE option, which drops the indextype and marks any dependent domain indexes invalid.

Commenting Indextypes

Use the COMMENT statement to supply information about an indextype or operator, as shown in Example 8-3.

Example 8-3 Commenting an INDEXTYPE

COMMENT ON INDEXTYPE
TextIndexType IS 'implemented by the type TextIndexMethods to support the Contains operator';

Comments on indextypes can be viewed in these data dictionary views:

• ALL_INDEXTYPE_COMMENTS displays comments for the user-defined indextypes accessible to the current user.
• DBA_INDEXTYPE_COMMENTS displays comments for all user-defined indextypes in the database.
• USER_INDEXTYPE_COMMENTS displays comments for the user-defined indextypes owned by the current user.

Table 8-1 Views *_INDEXTYPE_COMMENTS

OWNER

VARCHAR2(30)

NOT NULL

INDEXTYPE_NAME

VARCHAR2(30)

NOT NULL

COMMENT

VARCHAR2(4000)

To place a comment on an indextype, the indextype must be in your own schema or you must have the COMMENT ANY INDEXTYPE privilege.

< < ODCIIndex Interface

Domain Indexes >>