Similarly as discussed in this post explaining how to enable partial unique indexes on Oracle 12c, it is possible to use partial indexes on Oracle 11g (tested on as well! As you know this feature has been introduced with the Oracle release 12c but using the same method explained in the previous post we can apply it to 11g.
Let’s create our usual test table

Here we can’t specify any INDEXING option because there is no such feature on 11g. Let’s also populate the table

and the local unique index

Before proceeding let’s also gather stats

The index has been created on all partitions with USABLE status and it’s segments occupy some space

Now, let’s try to apply the same method we used for unique indexes on Oracle 12c and issue a “move partition” then check what happens

We are not surprised anymore to see that the index partition has been dropped with its segment freeing some space.
This sounds interesting but how does the CBO deals with this situation? On 12c, considering that the feature has been officially introduced by Oracle, the CBO is smart enough to “split” scans on different partition using indexes wherever they are available otherwise choosing a full scan. Here on 11g the behaviour is a bit different.
Let’s explain a first query that accesses only a partition on which the index exists

On the selected partition the index exists so Oracle uses it. Same happens if we access more partitions all with existing index

What happens if we remove the condition on the date and force the CBO to look into all the partitions? Let’s check

Well, surprise for me, also on 11g the CBO is smart enough to split the operation and use indexes wherever they exist!
Conclusion of this short demo is that on Oracle release 11g it is possible to remove old indexes partitions in order to reduce the database footprint. From the tests I’ve been running it seems there are no drawbacks in using this method but let me know if you come up with something.

Share this post on Share on Facebook
Tweet about this on Twitter
Share on LinkedIn
Email this to someone

Leave a Reply

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