Sunday, October 24, 2021
dbametrix
More
    HomeOracle InternalsNew feature partial indexes

    New feature partial indexes

    Detail explanation of Oracle 12c new feature of Partial index of partition tables.

    Oracle 12c: Partial index on partitioned tables

    You don’t always need to index all partitions. In a table that keeps all the history, we may need to index only the current partition, accessed transactionally. The old partitions still have the data, for reporting purposes, but without having to store all the indexes useful for the current partition.

    Before 12c, we could do this with local indexes by making them unusable (and skip_unusable_indexes = true). But no solution for global indexes – if not archive the old rows in another table and make a UNION ALL view.

    - Advertisement -
    dbametrix

    In 12c, we can have partial indexes:

    Commands are following…

    CREATE INDEX ... GLOBAL INDEXING PARTIAL
    CREATE INDEX ... LOCAL INDEXING PARTIAL

    And we specify at the table level the partitions that will not be indexed by the Partial Indexes:

    - Advertisement -
    dbametrix

    ALTER TABLE ... MODIFY PARTITION ... INDEXING OFF

    The Final result:

    The Partial Local Indexes will have their partitions in “indexing off” with the UNUSABLE status (so keep the default value skip_unusable_indexes = true)
    Partial Global Indexes will not have an entry for lines in indexing off ’partitions. If there were any before, they will become orphans

    The reverse operation will have to rebuild the local partitions, and reindex the global indexes for these partitions.

    It’s transparent: SELECTs that use the index will do a UNION ALL to fetch data indexed via index, and the others via full scan partition.
    For example, when partition 1 is ‘indexing off’, partition 2 is ‘indexing on’, and the global index is ‘indexing partial’:

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Recent Comments