Monday, November 23, 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

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments