Designing System-Managed Domain Indexes|best oracle training institute in chennai with placement

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.

Table 8-7 ODCIXXX() Methods for Non-Partitioned Domain Indexes

DDL	ODCI() Method Used in System-Managed Approach
CREATE INDEXTYPE	Specify the system-managed approach
CREATE INDEX	ODCIIndexCreate()
TRUNCATE TABLE	>ODCIIndexAlter() with the alter_option=AlterIndexRebuild
ALTER INDEX	ODCIIndexAlter()
GATHER_INDEX_STATS() in DBMS_STATS	ODCIStatsCollect()
DELETE_INDEX_STATS() in DBMS_STATS	ODCIStatsDelete()
DROP INDEX (Force)	ODCIIndexDrop() and ODCIStatsDelete()
INSERT	ODCIIndexInsert()
DELETE	ODCIIndexDelete()
UPDATE	ODCIIndexUpdate()
QUERY	ODCIIndexStart(), ODCIIndexFetch() and ODCIIndexClose()

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 ensure that they remain equipartitioned.
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.

Table 8-8 ODCIXXX() Methods for Local System-Managed Domain Indexes

DDL	ODCI XXX() Method Used in System-Managed Approach
CREATE INDEXTYPE	Specify the system-managed approach
CREATE INDEX	One call to ODCIIndexCreate(), one ODCIIndexAlter() call for each partition, with alter_option=AlterIndexRebuild, and then a final call to ODCIIndexCreate()
TRUNCATE TABLE	One call for each partition: ODCIIndexAlter(), with alter_option=AlterIndexRebuild
ALTER INDEX	ODCIIndexAlter()
GATHER_INDEX_STATS() in DBMS_STATS	One call to ODCIStatsCollect()
DELETE_INDEX_STATS() in DBMS_STATS	One call to ODCIStatsDelete()
DROP INDEX (Force)	ODCIIndexDrop(), and if user-defined statistics have been collected then ODCIStatsDelete()
ALTER TABLE ADD PARTITION	ODCIIndexUpdPartMetadata(),ODCIIndexAlter() with alter_option=AlterIndexRebuild
ALTER TABLE COALESCE PARTITION	ODCIIndexUpdPartMetadata(); ODCIStatsUpdPartStatistics() if statistics are collected
ALTER TABLE DROP PARTITION	ODCIIndexUpdPartMetadata(); ODCIStatsUpdPartStatistics() if statistics are collected
ALTER TABLE TRUNCATE PARTITION	ODCIIndexUpdPartMetadata(); ODCIIndexAlter() with alter_option=AlterIndexRebuild; ODCIStatsUpdPartStatistics() if a statistics type is associated with the indextype and if user-defined statistics have been collected
ALTER TABLE SPLIT PARTITION	ODCIIndexUpdPartMetadata(); ODCIIndexAlter() with alter_option=AlterIndexRebuild only if the result partitions are empty; ODCIStatsUpdPartStatistics() if a statistics type is associated with the indextype and if user-defined statistics have been collected
ALTER TABLE MERGE PARTITION	ODCIIndexUpdPartMetadata(); ODCIIndexAlter() with alter_option=AlterIndexRebuildonly if the result partitions are empty; ODCIStatsUpdPartStatistics() if a statistics type is associated with the indextype and if user-defined statistics have been collected
ALTER TABLE EXCHANGE PARTITION	ODCIIndexExchangePartition(); if a statistics type is associated with the indextype, and if user-defined statistics have been collected, also ODCIStatsExchangePartition()
ALTER TABLE MOVE PARTITION	ODCIIndexUpdPartMetadata() if a partitioned table has a valid system-managed local domain index that has been updated as part of a partition MOVE and rename operation. If a partition is moved without updating the system-managed indexes, the index partition is marked UNUSABLE.
GATHER_TABLE_STATS() in DBMS_STATS	One call to ODCIStatsCollect()
DELETE_TABLE_STATS() in DBMS_STATS	One call to ODCIStatsDelete(), if a statistics type is associated with the indextype, and if user-defined statistics have been collected
ALTER INDEX PARTITION	ODCIIndexAlter()
INSERT	ODCIIndexInsert()
DELETE	ODCIIndexDelete()
UPDATE	ODCIIndexUpdate()
QUERY	ODCIIndexStart(), ODCIIndexFetch() and ODCIIndexClose()