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’:
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.