db_icon_text

With release 12c Oracle has introduced partial indexing, the possibility to define partial local indexes on partitioned table. This is an interesting feature that allows to save some space getting rid of index segments for old partitions. Let’s quickly see how this works.
First of all, when creating a partitioned table, the INDEXING property can be specified to define if we want or not to create the indexes for that partition (default value is ON unless a different default is set at table level)

This property can also be changed in a second time issuing a simple alter command

The local indexes need to be created with the option INDEXING PARTIAL otherwise they will not be affected by the INDEXING ON/OFF property set at partition/subpartition level.
There is a restriction though: local unique indexes cannot be created as partial so it’s not possible to drop partitions of a unique index. Well, this is at least what Oracle says but let’s go on.
Our test table has INDEXING set to OFF on the first partition, let’s populate it with some data executing

Then let’s create two indexes

As you can see we specified INDEXING PARTIAL only on the non unique index. Just to finish our test case let’s grab some statistics

Now let’s check what’s the current status of our partitions

As you can see in the %_TAB_PARTITIONS and %_TAB_SUBPARTITIONS views of Oracle 12c there is a new column showing the INDEXING property.
When checking the index segments

we can see that, as expected, the index partition on P1 has not been created for the partial index MYTAB_IDX. We can also verify that there is no segment created or any space allocated for that index partition

But what if we want to get rid of old partitions for unique indexes? Is it really not possible?
If we drop and try to create a unique index with the INDEXING PARTIAL option we get an error with a clear description that leaves no doubts about it

In the first moment I thought “Well, it makes sense, how can Oracle grant uniqueness on a table if we remove a part of the unique index?”.
But wait! When I create a unique index, Oracle forces me to include in it all the columns used in the partitioning schema

This means that there is no way I can have duplicate values across different partitions. So why doesn’t Oracle allow partial indexing on a unique local index?
Let’s create our unique local index without the partial option

We are now back to the initial configuration where MYTAB_IDX has no partition for P1 while the one for the unique index MYTAB_IDX_UQ exists.
Now we can use a small trick: we know that any “alter table … move partition …” command make the indexes unusable for that partition. Yes, also the unique ones!
So let’s execute

Bingo! Because the move command affects all indexes on the partition, now also the unique index is unusable and it’s basically behaving like it was PARTIAL and not FULL.
Let’s verify if there is any space allocated for it

0 bytes for all indexes on partition P1! With this small workaround we managed to get rid of all index partitions on P1 regardless of the index being unique or not.
But what happens to the table in this condition? Well, not really anything to be worried about. Select statements on P1 still work without any problem and same updates

The only problem is that insert statements fail because the unique index is in “UNUSABLE” status

Assuming we only want to drop indexes for partitions that contain old historical data and that we are not going to modify anymore, this is not a big limitation at all.
An interesting aspect is that uniqueness is still guaranteed on the rest of the table as a proof that we are not breaking anything here

But now it’s time to check how the CBO deal with our indexes

This query accesses the column on which we created the non unique partial index. As you can see the CBO is smart enough to split the operation in two different chunks: one accesses the partitions that have INDEXING ON using the index, the other one executes a full table scan on the partition that has no index.
If we run a similar query but trying to use the unique index

we get the same result. This shows how the partial UNIQUE index is behaving exactly as the other non unique one and the CBO uses the index wherever this is usable.
Despite the limitation that Oracle sets, applying partial indexing to unique indexes is possible and doesn’t seem to cause any trouble so it might a viable solution to reduce the database footprint.
Only thing to take into consideration is that, as shown during this demo, unique index should be dropped only for old partitions where new rows are not likely to be inserted anymore.

Share this post on Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *