Saturday, September 26, 2020
dbametrix
More
    Home Oracle Internals New 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

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments