8 Building Domain Indexes

Overview of Indextypes and Domain Indexes

A domain index is an index designed for a specialized domain, such as spatial or image processing. Users can build a domain index of a given type after the designer creates the indextype. The behavior of domain indexes is specific to an industry, a business function, or some other special purpose; you must specify it during cartridge development.

The system-managed approach to domain indexes, new in the Oracle Database 11g Release 1, requires less programmatic overhead and delivers better performance than the earlier user-managed domain indexes. It addresses the limitations of the user-managed approach, and has the following benefits:

• Because the kernel performs many more maintenance tasks on behalf of the user, there is no need for programmatic support for table and partition maintenance operations. These operations are implemented by taking actions in the server, thus requiring a very minimal set of user-defined interface routines to be coded by the user. The cartridge code can then be relatively unaware of partition issues.

• The number of objects that must be managed to support local partitioned domain indexes is identical to identical to those for non-partitioned domain indexes. For local partitioned indexes, the domain index storage tables are equipartitioned with respect to the base tables (using system-partitioned tables); therefore, the number of domain index storage tables does not increase with an increase in the number of partitions.

• A single set of query and DML statements can now access and manipulate the system-partitioned storage tables, facilitating cursor sharing and enhancing performance.

Oracle recommends that you develop new applications with system-managed domain indexes instead of user-managed domain indexes.

Indextypes encapsulate search and retrieval methods for complex domains such as text, spatial, and image processing. An indextype is similar to the indexes that are supplied with the Oracle Database. The difference is that you provide the application software that implements the indextype.

An indextype has two major components:

• The methods that implement the behavior of the indextype, such as creating and scanning the index

• The operators that the indextype supports, such as Contains() or Overlaps()

To create an indextype:

• Define the supported operators and create the functions that implement them

• Create the methods that implement the ODCIIndex interface, and define the type that encapsulates them, called the implementation type

• Create the indextype, specifying the implementation type and listing the operators with their bindings

In this context:

• Interface means a logical set of documented method specifications (not a separate schema object)

• ODCIIndex interface means a set of index definition, maintenance, and scan routine specifications

ODCIIndex Interface

The ODCIIndex interface specifies all the routines you must supply to implement an indextype. The routines must be implemented as type methods.

The ODCIIndex interface comprises the following method classes:

• Index definition methods

• Index maintenance methods

• Index scan methods

• Index metadata method

Creating, Dropping, and Commenting Indextypes

This section describes the SQL statements that manipulate indextypes.

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

DROP INDEXTYPE TextIndexType;

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

OWNER

VARCHAR2(30)

NOT NULL

INDEXTYPE_NAME

VARCHAR2(30)

NOT NULL

COMMENT

VARCHAR2(4000)

Domain Indexes

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

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

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

ALTER INDEX ResumeTextIndex RENAME TO ResumeTIdx;

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

TRUNCATE TABLE MyEmployees;

DROP INDEX ResumeTextIndex;

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;

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

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

Object Dependencies, Drop Semantics, and Validation

This section discusses issues that affect objects used in domain indexes.

Indextype, Domain Index, and Operator Privileges

• To create an operator and its bindings, you must have EXECUTE privilege on the function, operator, package, or the type referenced in addition to CREATE OPERATOR or CREATE ANY OPERATOR privilege.

• To create an indextype, you must have EXECUTE privilege on the type that implements the indextype in addition to CREATE INDEXTYPE or CREATE ANY INDEXTYPE privilege. Also, you must have EXECUTE privileges on the operators that the indextype supports.

• To alter an indextype in your own schema, you must have CREATE INDEXTYPE system privilege.

• To alter an indextype or operator in another user's schema, you must have the ALTER ANY INDEXTYPE or ALTER ANY OPERATOR system privilege.

• To create a domain index, you must have EXECUTE privilege on the indextype in addition to CREATE INDEX or CREATE ANY INDEX privileges.

• To alter a domain index, you must have EXECUTE privilege on the indextype.

• To use the operators in queries or DML statements, you must have EXECUTE privilege on the operator and the associated function, package, and indextype.

• To change the implementation type, you must have EXECUTE privilege on the new implementation type.

Partitioned Domain Indexes

A domain index can be built to have discrete index partitions that correspond to the partitions of a range- or list-partitioned table. Such an index is called a local domain index, as opposed to a global domain index, which has no index partitions. Local domain index refers to a partitioned index as a whole, not to the partitions that compose a local domain index.

A local domain index is equipartitioned with the underlying table: all keys in a local domain index refer to rows stored in its corresponding table partition; none refer to rows in other partitions.

You provide for using local domain indexes in the indextype, with the CREATE INDEXTYPE statement, as demonstrated in Example 8-12.

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

This statement specifies that the implementation type TextIndexMethods is capable of creating and maintaining local domain indexes.

The CREATE INDEX statement creates and partitions the index, as demonstrated by Example 8-13.

CREATE INDEX [schema.]index 
  ON [schema.]table [t.alias] (indexed_column)
  INDEXTYPE IS indextype
  [LOCAL [PARTITION [partition [PARAMETERS ('string')]]] [...] ]
  [PARALLEL parallel_degree]
  [PARAMETERS ('string')];

The LOCAL [PARTITION] clause indicates that the index is a local index on a partitioned table. You can specify partition names or allow Oracle to generate them.

The PARALLEL clause specifies that the index partitions are to be created in parallel. The ODCIIndexAlter() routines, which correspond to index partition create, rebuild, or populate, are called in parallel.

In the 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 LOCAL [PARTITION] clause, you override any default parameters with parameters for the individual partition. The LOCAL [PARTITION] clause can specify multiple partitions.

When 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 DROP INDEX and (for non-local indexes) REBUILD INDEX. Example 8-14 creates a local domain index ResumeIndex:

CREATE INDEX ResumeIndex ON MyEmployees(Resume)
  INDEXTYPE IS TextIndexType LOCAL;

DROP INDEX ResumeIndex;

Using System Partitioning

System Partitioning enables you to create a single table consisting of multiple physical partitions. System partitioning does not use partitioning keys. Instead, it creates the number of partitions specified. Therefore, the resulting partitions have no bounds (range), values (list), or a partitioning method.

Because there are no partitioning keys, you must explicitly map the distributed table rows to the destination partition. When inserting a row, for example, you must use the partition extended syntax to specify the partition to which a row must be mapped.

CREATE TABLE SystemPartitionedTable (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
  PARTITION p1 TABLESPACE tbs_1,
  PARTITION p2 TABLESPACE tbs_2,
  PARTITION p3 TABLESPACE tbs_3,
  PARTITION p4 TABLESPACE tbs_4
);

INSERT INTO SystemPartitionedTable PARTITION (p1) VALUES (4,5);

INSERT INTO SystemPartitionedTable PARTITION 
  (DATAOBJ_TO_PARTITION (base_table, :physical_partid))
  VALUES (...);

Using System-Managed Domain Indexes

This section describes how system-managed domain indexes work, how to collect and store statistics for them, and lists restrictions for use.

Let us examine how system-managed domain indexes work.

Figure 8-1 illustrates the initial setup of a base table T1. T1 has the following elements:

• three partitions

• a local domain index on one of its columns, IT1

• a table of corresponding metadata objects, MT1, which is the optional metadata table created by the indextype to store information specific to each partition of the local domain index

• a system-partitioned table, SPT1, created by the indextype to store index data

The structures shown in these tables (table T1, index IT1 and the system partitioned table SPT1) have the same number of partitions, in a one-to-one relationship. The metadata table MT1 has as many rows as the number of partitions in these tables.

Figure 8-1 Three-Partition Table with a Local Domain Index, and Associated Structures

Description of "Figure 8-1 Three-Partition Table with a Local Domain Index, and Associated Structures"

Figure 8-2 illustrates what happens to T1 and its related structures after splitting one of its partitions with the operation in Example 8-20:

ALTER TABLE T1 SPLIT PARTITION P2 INTO P21, P22

• the partition P2 in the base table T1 splits into P21 and P22

• in the local domain index, partition IP2 is dropped and two new partitions, IP21 and IP22, are created

• the indextype invokes the ODCIIndexUpdPartMetadata() method that makes the necessary updates to the metadata table MT1

• in the system partitioned table SPT1, the partition that corresponds to partition IP2 is dropped and two new partitions are created

• index partitions are marked UNUSABLE as a result of the split operation; they must be rebuilt to make them USABLE

Figure 8-2 A Three-Partition Table after ALTER TABLE SPLIT PARTITION

Description of "Figure 8-2 A Three-Partition Table after ALTER TABLE SPLIT PARTITION "

Designing System-Managed Domain Indexes

When a top-level DDL that affects a non-partitioned domain index is called, the system invokes user-implemented ODCIIndexXXX() and ODCIStatsXXX() methods. Table 8-7 shows these methods.

CREATE INDEXTYPE

CREATE INDEX

TRUNCATE TABLE

ALTER INDEX

GATHER_INDEX_STATS()

DELETE_INDEX_STATS()

DROP INDEX

INSERT

DELETE

UPDATE

QUERY

When a top-level DDL that affects a local system managed domain index is called, the system invokes user-implemented ODCIIndexXXX() and ODCIStatsXXX() methods. Table 8-8 shows these methods. In summary, the following rules apply:

• For ODCIIndexXXX () DMLs and queries, both the index partition object identifier (ODCIIndexInfo.IndexPartitionIden) and a base table partition physical identifier (ODIIndexInfo.IndexCols(1).TablePartitionIden) are required. For ODCIIndexXXX () DDL routines, both the index partition object identifier and the index partition name are supplied.

• The CREATE INDEX routine uses two calls to ODCIIndexCreate() (one at the beginning and one at the end), and as many calls to ODCIIndexAlter() with alter_option=AlterIndexRebuild as there are partitions.

• The TRUNCATE TABLE routine uses as many calls to ODCIIndexAlter() with alter_option=AlterIndexRebuild as there are partitions.

• All partition maintenance operations invoke ODCIIndexUpdPartMetadata() so that the indextype correctly updates its partition metadata table. The list of index partitions is specified by the index partition name and the index partition object identifier, and is supplied with information regarding addition or dropping of the partition. No DDLs are allowed in these calls.With each partition maintenance operation, the system implicitly transforms the system-partitioned storage tables that were created using domain indexes. The names of the newly generated partitions correspond to the index partition names.

• If the system-partitioned tables are used to store partition-level statistics, then the tables and indexes created by ODCIStatsCollect() and dropped by ODCIStatsDelete() are tracked by the system to maintain equipartitioning.

• If the application implements user-defined partition-level statistics, the system invokes ODCIStatsUpdPartStatistics() with every partition maintenance operation. This ensure that the statistics type updates its partition-level statistics, and (optionally) its aggregate global statistics. No DDLs are allowed in these calls. If ODCIStatsUpdPartStatistics() is not implemented, the system does not raise an exception but proceeds to the next programmatic step.

CREATE INDEXTYPE

CREATE INDEX

TRUNCATE TABLE

ALTER INDEX

GATHER_INDEX_STATS()

DELETE_INDEX_STATS()

DROP INDEX

ALTER TABLE ADD PARTITION

ALTER TABLE DROP PARTITION

ALTER TABLE TRUNCATE PARTITION

ALTER TABLE SPLIT PARTITION

ALTER TABLE MERGE PARTITION

ALTER TABLE EXCHANGE PARTITION

ALTER TABLE MOVE PARTITION

GATHER_TABLE_STATS()

DELETE_TABLE_STATS()

ALTER INDEX PARTITION

INSERT

DELETE

UPDATE

QUERY

Creating Local Domain Indexes

The CREATE INDEX routine implements the following steps:

1. To create system-partitioned storage tables, the system calls ODCIIndexCreate() with index information. The number of partitions is supplied in the ODCIIndexInfo.IndexPartitionTotal attribute. Note that all the partitioned storage tables should be system-partitioned.

   The object-level CREATE routine passes in only the object-level parameter string. To construct the storage attributes for all partitions, the indextype needs partition-level parameter strings. To obtain these, the cartridge must programmatically query the XXX_IND_PARTITIONS views on the dictionary tables.

   Oracle recommends that the indextype assign names to the storage tables and its partitions using the index partition name. Note that you must also obtain index partition names programmatically, from the XXX_IND_PARTITIONS views.

2. For each partition, the system calls the ODCIIndexAlter() method with alter_option=AlterIndexRebuild.

   You can verify if this ODCIIndexAlter() call has been made as part of a CREATE INDEX call by checking whether the ODICEnv.IntermediateCall bit was set.

   Programatically select the index column values for each partition from the base table partition, transform them, and store the transformed data in the corresponding system-partitioned table.

   During DML or query operations, if the indextype must refer to the metadata table, it should be programmed to insert the index partition object identifier into the corresponding row of the metadata table.

   To store values in non-partitioned tables, you can program the cartridge either at the level of the initial ODCIIndexCreate() call, or at the level of the ODCIIndexAlter() calls made for each partition.

3. The system makes a final call to the ODCIIndexCreate() method so that the indextype can create any necessary indexes on the storage tables.

   The CREATE routine may use temporary storage tables for intermediate data. As an option, you can programmatically instruct your application to use external files; it is then the application's responsibility to manage these files.

   After this ODCIIndexCreate() call completes, all partitioned tables created and not dropped by this call are managed by the system.

Note that creation of global indexes of any type on a system-partitioned index storage table is flagged as an error.

Maintaining Local Domain Indexes with INSERT, DELETE, and UPDATE

DML operations should be implemented in the following manner:

1. One of ODCIIndexInsert(), ODCIIndexDelete(), or ODCIIndexUpdate() is invoked. Both the index partition object identifier (for accessing the metadata table) and the base table partition physical identifier (for performing DMLs in the corresponding partition) are supplied as part of the ODICIndexInfo structure.

2. To implement DMLs on a system-partitioned table, the cartridge code must include the syntax in Example 8-21. The DATAOBJ_TO_PARTITION() function is provided by the system.

   Example 8-21 How to Issue DML Operations on System-Partitioned Tables

   INSERT INTO SP PARTITION
      (DATAOBJ_TO_PARTITION(base_table, :physical_partid)) VALUES(...)
   

Querying Local Domain Indexes

Follow these steps to query local domain indexes:

1. When the optimizer receives a query that has a user-defined operator, if it determines to use a domain index scan for evaluation, ODCIIndexStart(), ODCIIndexFetch(), or ODCIIndexClose() is invoked.

2. The index partition object identifier and the base table partition physical identifier are passed in as part of ODCIIndexInfo structure.

3. The index partition object identifier can be used to look up the metadata table, if necessary.

4. And the base table physical partition identifier can be used to query the corresponding partition of the system partitioned table.

5. The cartridge code must use the syntax in Example 8-22 and the provided function DATAOBJ_TO_PARTITION(), for querying the system partitioned table.

   Example 8-22 How to Query a System-Partitioned Table

   SELECT FROM SP PARTITION
      (DATAOBJ_TO_PARTITION(base_table, :physical_partid)) WHERE <..>;
   

Restrictions of System-Managed Domain Indexing

The system-managed domain indexing approach supports the following structures:

• Non-partitioned system managed domain indexes

• Local system managed domain indexes on range- and list-partitioned tables

• Local domain indexes can be created only for range- and list-partitioned heap-organized tables. Local domain indexes cannot be built for hash-partitioned, ref-partitioned, or interval-partitioned tables or IOTs.

• A system-managed domain index can index only a single column.

• You cannot specify a bitmap or unique domain index

Migrating Non-Partitioned Indexes

The following steps show how to migrate non-partitioned user-managed domain indexes into system-managed domain indexes.

1. Modify metadata: issue an ALTER INDEXTYPE command to register the property of the indextype with the system. This disassociates the statistics type.

2. The index is marked as INVALID. You must implicitly issue an ALTER INDEX ... COMPILE command to validate the index again. This calls the ODCIIndexAlter() method with alter_option=AlterIndexMigrate.

3. Issue an ASSOCIATE STATISTICS command to associate a system-managed statistics type with the system-managed indextype.

Migrating Local Partitioned Indexes

The following steps show how to migrate local partitioned user-managed domain indexes into system-managed equi-partitioned domain indexes.

1. Modify metadata: issue an ALTER INDEXTYPE command to register the new index routines and the property of the indextype so it can be managed by the system. All indexes of this indextype are marked INVALID, and cannot be used until after the completion of the next step. This disassociates the statistics type and erases the old statistics.

2. Modify index data: invoke the ALTER INDEX ... COMPILE command for the new indextype of each index. This calls the ODCIIndexAlter() method with alter_option=AlterIndexMigrate. You must implement this method to transform groups on non-partitioned tables into system-partitioned tables. For each set of n tables that represent a partitioned table, the cartridge code should perform the following actions. Note that the migration does not require re-generation of index data, but involves only exchange operations.

   • Create a system-partitioned table.

   • For each of the n non-partitioned tables, call the ALTER TABLE EXCHANGE PARTITION [INCLUDING INDEXES] routine to exchange a non-partitioned table for a partition of the system-partitioned table.

   • Drop all n non-partitioned tables.

3. Issue an ASSOCIATE STATISTICS command to associate a system-managed statistics type with the system-managed indextype.