Creating Local Domain Indexes

The CREATE INDEX routine implements the following steps:

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.

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.

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:

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.
To implement DMLs on a system-partitioned table, the cartridge code must include the syntax in Example 8-22. The DATAOBJ_TO_MAT_PARTITION() function is provided by the system.

Example 8-22 Calling DML Operations on System-Partitioned Tables

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

Querying Local Domain Indexes

Follow these steps to query local domain indexes:

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.
The index partition object identifier and the base table partition physical identifier are passed in as part of ODCIIndexInfo structure.
The index partition object identifier can be used to look up the metadata table, if necessary.
And the base table physical partition identifier can be used to query the corresponding partition of the system partitioned table.
The cartridge code must use the syntax in Example 8-23 and the provided function DATAOBJ_TO_MAT_PARTITION(), for querying the system partitioned table.

Example 8-23 Querying a System-Partitioned Table

SELECT FROM SP PARTITION
   (DATAOBJ_TO_MAT_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-partitioned, list-partitioned, hash-partitioned, and interval-partitioned tables
Local domain indexes can be created only for range-partitioned, list-partitioned, hash-partitioned, and interval-partitioned heap-organized tables. Local domain indexes cannot be built for REF-partitioned tables or IOTs.
Local domain indexes are not supported on composite partitioned tables.
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.

Modify metadata: issue an ALTER INDEXTYPE command to register the property of the indextype with the system. This disassociates the statistics type.
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.
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.

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.
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.