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.
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:
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: