Partitioned Domain Indexes

A domain index can be built to have discrete index partitions that correspond to the partitions of a range-, list-, hash-, or interval-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.

Example 8-12 Using Local Domain Index Methods Within an Indextype

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.

Example 8-13 Creating and Partitioning an Index

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:

Example 8-14 Creating a Local Domain Index

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

Dropping a Local Domain Index

A specified index partition cannot be dropped explicitly. To drop a local index partition, you must drop the entire local domain index:

Example 8-15 Dropping a Local Index Partition

DROP INDEX ResumeIndex;

Altering a Local Domain Index

Use the ALTER INDEX statement to perform the following operations on a local domain index:

• Rename the top level index.
• Modify the default parameter string for all the index partitions.
• Modify the parameter string associated with a specific partition.
• Rename an index partition.
• Rebuild an index partition.

The ALTER INDEXTYPE statement lets you change properties and the implementation type of an indextype without having to drop and re-create the indextype, then rebuild all dependent indexes.

Table 8-4 Summary of Index States

DML Operations with Local Domain Indexes

DML operations cannot be performed on the underlying table if an index partition of a local domain index is in any of these states: IN_PROGRESS, FAILED, or UNUSABLE. However, if the index is marked UNUSABLE, and SKIP_UNUSABLE_INDEXES = true, then index maintenance is not performed.

Table Operations that Affect Indexes

The tables in this section list operations that can be performed on the underlying table of an index and describe the effect, if any, on the index. Table 8-5 lists TABLE operations, while Table 8-6 lists ALTER TABLE operations.

Table 8-5 Summary of Table Operations

Table 8-6 Summary of ALTER TABLE Operations with Partition Maintenance

ODCIIndex Interfaces for Partitioning Domain Indexes

To support local domain indexes, you must implement the standard ODCIIndex methods, plus two additional methods that are specific to local domain indexes:

• ODCIIndexExchangePartition()
• ODCIIndexUpdPartMetadata()

Domain Indexes and SQL*Loader

SQL*Loader conventional path loads and direct path loads are supported for tables on which domain indexes are defined, with this limitation: The table must be heap-organized.

To do a direct path load on a domain index defined on an IOT or on a LOB column, perform these tasks:

• Drop the domain index
• Do the direct path load in SQL*Loader.
• Re-create the domain indexes.

< <Object Dependencies, Drop Semantics, and Validation

Using System Partitioning >>