Partial indexes came with 12c, representing nice option which enables partial indexing of the data. This allows for DBAs to index only active data and save on disk space (by excluding non-active data from indexing) and also gain on performance. For GLOBAL indexes only part of the data is indexed and for LOCAL indexes, non-indexed (excluded) partitions are set to UNUSABLE (but still defined as metadata).
Starting with 12c we can manually specify which partitions data we would like to be indexed, so that the optimizer has more information for the data for which it should generate an execution plan. Partial indexes work only for partitioned tables, we can’t use them for non-partitioned objects.
This previously existed from 11gR2 where we could set some of the local index partitions UNUSABLE and the CBO would perform Table Expansion Transformation in order to use indexes for only USABLE partitions. The key point with table expansion is that it splits the query in multiple query blocks where one part is using indexes to access the data and the other part is using full table access.
I.e. For local partitioned indexes we could get the same effect by setting some of the index partitions to UNUSABLE. But, with global indexes there was not a way to exclude portion of the data from indexing.
We can see that the optimizer performed table expansion transformation, TABLE ACCESS FULL for the second partition p2 and INDEX RANGE SCAN on the first partition which has corresponding LOCAL index.
Lets disable table expansion which is controlled by _optimizer_table_expansion and see what explain plan we would get.
Expected, CBO couldn’t use the index because not all index partitions where usable and there is no way to access rows for partition p2 through the index except with TABLE ACCESS FULL.
Just to prove that our index partition for partition 2 is unusable:
Will this always work ? We need to be careful because if we perform maintenance on table sub/partitions and use UPDATE INDEXES those index partitions which are unusable would be rebuild-ed and set back to USABLE (oracle doesn’t have information that we want those partitions to be UNUSABLE forever). This would result into more space consumption and the optimizer would again consider those partitions since their status will be USABLE.
Partial indexes, 12c:
As new feature in Oracle 12c, now we have the ability to specify (into the metadata) which partitions we want to be indexed and which to be skipped. It’s just another information for the CBO to be able to generate better plan. By default it’s set to ON which means that when we create an index all table sub/partitions are considered for indexing.
To enable partial indexing at table level we need to specify INDEXING ON|OFF within the DDL or with alter table modify_default_attributes clause for existing tables. Also, we can set INDEXING clause at sub/partition level, where we have option to exclude specific sub/partitions from indexing with INDEXING set to OFF.
I’ve created table T as range partitioned table, enabled partial indexing at table level (I could exclude INDEXING ON, because ON is default value) and excluded only partition p2 from indexing. This means that when I’ll create partial global or local index for table T, the data portion from partition p2 should not be included in the global index or if it’s local index, the index partition will be set as UNUSABLE.
We can see which partitions are enabled for indexing from DBA|ALL|USER_TAB_PARTITIONS dictionary view.
I’ve insert some data to populate the table partitions and created partial index on column x by adding INDEXING PARTIAL clause at index creation DDL.
We can see the index definition from DBA|ALL|USER_INDEXES. It has new column INDEXING which gives information whether the index is PARTIAL or FULL.
Lets see what kind of execution plan the optimizer will generate. Remember, second partition from table T has INDEXING = OFF setting which means that its data is not indexed with T_IX index.
Again, the optimizer performed table expansion transformation by separating partitions 1 and 3 into separate query blocks from partition 2.
Here, important to note is that if we use UPDATE INDEXES the partition still stays UNUSABLE since we’ve set it manually.
During testing and playing with partial indexes I discovered something strange as effect of TRUNCATE command (which according to me it’s a bug). If we execute TRUNCATE for the table those index partitions which are UNUSABLE will be rebuild-ed to USABLE. Also, if we truncate specific UNUSABLE partition it will be rebuild-ed to USABLE.
If we check the index partitions status in user_ind_partitions we would see that partition p2 is USABLE, meaning that partition data is indexed (ignoring the setting for table partition INDEXING=OFF).
Same will happen if we truncate only the table partition.
Now, because partition p2 is set to USABLE nothing stops the optimizer to take into consideration partition p2 for index access path.
As workaround/solution we can manually set the index partition to UNUSABLE (which will drop the partition segment) or, not so good solution, drop the index and re-create it again.