Domain Indexes

This section describes the domain index operations and how metadata associated with the domain index can be obtained.

Domain Index Operations

The following sections describe and demonstrate how to create, alter, truncate, and drop a domain index.

Creating a Domain Index

A domain index can be created on a column of a table, just like a B-tree index. However, an indextype must be explicitly specified. Example 8-4 shows how to specify an indextype on the MyEmployees table that was declared in Example 7-1.

Example 8-4 Creating a Domain Index

CREATE INDEX ResumeTextIndex ON MyEmployees(resume)
INDEXTYPE IS TextIndexType
PARAMETERS (':Language English :Ignore the a an');

The INDEXTYPE clause specifies the indextype to be used. The PARAMETERS clause identifies any parameters for the domain index, specified as a string. This string is passed uninterpreted to the ODCIIndexCreate() routine for creating the domain index. In the preceding example, the parameters string identifies the language of the text document (thus identifying the lexical analyzer to use) and the list of stop words which are to be ignored while creating the text index.

Altering a Domain Index

A domain index can be altered using the ALTER INDEX statement, as shown inExample 8-5:

Example 8-5 Changing a Domain Index

ALTER INDEX ResumeTextIndex PARAMETERS (':Ignore on');

The parameter string is passed uninterpreted to ODCIIndexAlter() routine, which takes appropriate actions to alter the domain index. This example specifies an additional stop word to ignore in the text index.

The ALTER statement can be used to rename a domain index, as shown in Example 8-6:

Example 8-6 Renaming a Domain Index

ALTER INDEX ResumeTextIndex RENAME TO ResumeTIdx;

A statement of this form causes Oracle to invoke the ODCIIndexAlter() method, which takes appropriate actions to rename the domain index.

In addition, the ALTER statement can be used to rebuild a domain index, as shown in Example 8-7:

Example 8-7 Rebuilding a Domain Index

ALTER INDEX ResumeTextIndex REBUILD PARAMETERS (':Ignore off');

The same ODCIIndexAlter() routine is called as before, but with additional information about the ALTER option.

When the end user executes an ALTER INDEX domain_index UPDATE BLOCK REFERENCES for a domain index on an index-organized table (IOT), ODCIIndexAlter() is called with the AlterIndexUpdBlockRefs bit set. This gives you the opportunity to update guesses as to the block locations of rows that are stored in the domain index in logical rowids.

Truncating a Domain Index

There is no explicit statement for truncating a domain index. However, when the corresponding base table is truncated, the underlying storage table for the domain indexes are also truncated. Additionally, ODCIIndexAlter() is invoked by the command in Example 8-8, and it truncates ResumeTextIndex because its alter_option is set to AlterIndexRebuild:

Example 8-8 Truncating a Domain Index

TRUNCATE TABLE MyEmployees;

Dropping a Domain Index

To drop an instance of a domain index, use the DROP INDEX statement, shown in Example 8-9:

Example 8-9 Dropping a Domain Index

DROP INDEX ResumeTextIndex;

This results in Oracle calling the ODCIIndexDrop() method, passing it information about the index.

Domain Indexes on Index-Organized Tables

This section discusses some issues you must consider if your indextype creates domain indexes on index-organized tables. You can use the IndexOnIOT bit of IndexInfoFlags in the ODCIIndexInfo structure to determine if the base table is an IOT.

Storing Rowids in a UROWID Column

When the base table of a domain index is an index-organized table, and you want to store rowids for the base table in a table of your own, you should store the rowids in a UROWID (universal rowid) column if you are testing rowids for equality.

If the rowids are stored in a VARCHAR column instead, comparisons for textual equality of a rowid from the base table and a rowid from your own table fail in some cases where the rowids pick out the same row. This is because index-organized tables use logical instead of physical rowids, and, unlike physical rowids, logical rowids for the same row can have different textual representations. Two logical rowids are equivalent when they have the same primary key, regardless of the guess data block addresses stored with them.

A UROWID column can contain both physical and logical rowids. Storing rowids for an IOT in a UROWID column ensures that the equality operator succeeds on two logical rowids that have the same primary key information but different guess DBAs.

If you create an index storage table with a rowid column by performing a CREATE TABLE AS SELECT from the IOT base table, then a UROWID column of the correct size is created for you in your index table. If you create a table with a rowid column, then you must explicitly declare your rowid column to be of type UROWID(x), where x is the size of the UROWID column. The size chosen should be large enough to hold any rowid from the base table; thus, it should be a function of the primary key from the base table. Use the query demonstrated by Example 8-10 to determine a suitable size for the UROWID column.

Example 8-10 Getting the Size of a UROWID Column

SELECT (SUM(column_length + 3) + 7) 
FROM user_ind_columns ic, user_indexes i 
WHERE ic.index_name = i.index_name 
AND i.index_type = 'IOT - TOP'
AND ic.table_ name = base_table;

Doing an ALTER INDEX REBUILD on index storage tables raises the same issues as doing a CREATE TABLE if you drop your storage tables and re-create them. If, on the other hand, you reuse your storage tables, no additional work should be necessary if your base table is an IOT.

DML on Index Storage Tables

If you maintain a UROWID column in the index storage table, then you must change the type of the rowid bind variable in DML INSERT, UPDATE, and DELETE statements so that it works for all kinds of rowids. Converting the rowid argument passed in to a text string and then binding it as a text string works well for both physical and universal rowids. This strategy may help you to code your indextype to work with both regular tables and IOTs.

Start, Fetch, and Close Operations on Index Storage Tables

If you use an index scan-context structure to pass context between Start, Fetch, and Close, you must alter this structure. In particular, if you store the rowid define variable for the query in a buffer in this structure, then you must allocate the maximum size for a UROWID in this buffer (3800 bytes for universal rowids in byte format, 5072 for universal rowids in character format) unless you know the size of the primary key of the base table in advance or wish to determine it at run time. You also must store a bit in the context to indicate if the base table is an IOT, since ODCIIndexInfo is not available in Fetch.

As with DML operations, setting up the define variable as a text string works well for both physical and universal rowids. When physical rowids are fetched from the index table, you can be sure that their length is 18 characters. Universal rowids, however, may be up to 5072 characters long, so a string length function must be used to determine the actual length of a fetched universal rowid.

Indexes on Non-Unique Columns

All values of a primary key column must be unique, so a domain index defined upon a non-unique column of a table cannot use this column as the primary key of an underlying IOT used to store the index. To work around this, you can add a column in the IOT, holding the index data, to hold a unique sequence number. When a column value is inserted in the table, generate a unique sequence number to go with it; you can then use the indexed column with the sequence number as the primary key of the IOT. (Note that the sequence-number column cannot be a UROWID because UROWID columns cannot be part of a primary key for an IOT.) This approach also preserves the fast access to primary key column values that is a major benefit of IOTs.

Domain Index Metadata

For B-tree indexes, users can query the USER_INDEXES view to get index information. To provide similar support for domain indexes, you can provide domain-specific metadata in the following manner:

• Define one or more tables that contain this meta information. The key column of this table must be a unique identifier for the index. This unique key could be the index name (schema.index). The remainder of the columns can contain your metadata.
• Create views that join the system-defined metadata tables with the index meta tables to provide a comprehensive set of information for each instance of a domain index. It is your responsibility as the indextype designer to provide the view definitions.

Moving Domain Indexes Using Export/Import

Like B-tree and bitmap indexes, domain indexes are exported and subsequently imported when their base tables are exported. However, domain indexes can have implementation-specific metadata associated with them that is not stored in the system catalogs. For example, a text domain index can have associated policy information, a list of irrelevant words, and so on. The export/import mechanism moves this metadata from the source platform to the target platform.

To move the domain index metadata, the indextype must implement the ODCIIndexGetMetadata() interface method. When a domain index is being exported, this method is invoked and passes the domain index information. It can return any number of anonymous PL/SQL blocks that are written into the dump file and executed on import. If present, these anonymous PL/SQL blocks are executed immediately before the creation of the associated domain index.

By default, secondary objects of the domain are not imported or exported. However, if the interfaces ODCIIndexUtilGetTableNames() and ODCIIndexUtilCleanup() are present, the system invokes them to determine if the secondary objects associated with the domain indexes are part of the export/import operation.

Moving data using transportable tablespaces can be much faster than performing either an export and import, or unload and load of the data because transporting a tablespace only requires copying datafiles and integrating tablespace structural information. Also, you do not have to rebuild the index afterward as you do when loading or importing. You can check for the presence of the TransTblspc flag in ODCIIndexInfo to determine whether the ODCIIndexCreate() call is the result of an imported domain index.

To use transportable tablespace for the secondary tables of a domain index, you must provide two additional ODCI interfaces, ODCIIndexUtilGetTableNames() and ODCIIndexUtilCleanup(), in the implementation type.

• ALL_SECONDARY_OBJECTS provide information about secondary objects associated with domain indexes accessible to the user.
• DBA_SECONDARY_OBJECTS provides information about all secondary objects that are associated with domain indexes in the database.
• USER_SECONDARY_OBJECTS provides information about secondary objects associated with domain indexes owned by the current user.

Table 8-2 Views *_SECONDARY_OBJECTS

INDEX_OWNER

VARCHAR2(30)

NOT NULL

INDEX_NAME

VARCHAR2(30)

NOT NULL

SECONDARY_INDEX_OWNER

VARCHAR2(30)

NOT NULL

SECONDARY_INDEX_NAME

VARCHAR2(30)

NOT NULL

SECONDARY_OBJDATA_TYPE

VARCHAR2(20)

NOT NULL

Example 8-11 demonstrates how the USER_SECONDARY_OBJECTS view may be used to obtain information on the ResumeTextIndex that was created in Example 8-4.

Example 8-11 Using *_SECONDARY_OBJECTS Views

SELECT SECONDARY_OBJECT_OWNER, SECONDARY_OBJECT_NAME 
  FROM USER_SECONDARY_OBJECTS 
  WHERE INDEX_OWNER = USER and INDEX_NAME = 'ResumeTextIndex' 

< < Creating, Dropping, and Commenting Indextypes

Object Dependencies, Drop Semantics, and Validation >>