Oracle Partitioned Index Efficiency

PartitionedIndexEfficiency Although it is generally true that rebuilding an index represents a waste of time and resources, there nevertheless exist situations where degenerated indexes might benefit from reorganisation. When you think you are in such a situation, instead of shooting in the dark and rebuilding all indexes, you are better off finding and rebuilding only those indexes occupying more space

PartitionedIndexEfficiency

Although it is generally true that rebuilding an index represents a waste of time and resources, there nevertheless exist situations where degenerated indexes might benefit from reorganisation. When you think you are in such a situation, instead of shooting in the dark and rebuilding all indexes, you are better off finding and rebuilding only those indexes occupying more space than they theoretically ought to. Jonathan Lewis has helpfully anticipated this need by creating and sharing his Index Efficiency script, which I have been using extensively this last couple of years. This article aims to introduce an updated version of Jonathan’s script that locates degenerated partitioned and sub-partitioned indexes. The first part of the article outlines an example of how the original script finds exploded non-partitioned indexes and estimates their correct theoretical size. The second part examines the updated version of the script, with changes implemented to estimate the size of partitioned and sub-partitioned indexes.

1. Non Partitioned Indexes

Here’s an example showing how Jonathan Lewis’ script finds degenerated indexes with phenomenal precision (in Oracle 12.1.0.1):

This code creates a B-tree index, disorganises it and displays its actual size. Let’s now verify whether this index is occupying more space than it should (I have updated the script so that indexes with fewer than 10,000 leaf blocks can be considered):

The above output is indeed showing that there are 2255 leaf blocks that can be recovered and saved in the T_IDX index:

Let’s now rebuild this index and get its new size in order to evaluate the precision of the script:

As you can see, between the number of leaf blocks initially expected by the script (2195) and the actual number of leaf block following the index rebuild (2226) there a tiny difference of 31 leaf blocks. This is very high precision.

2. Locally-partitioned indexes

In the preceding section we examined how to evaluate the efficiency of non-partitioned indexes. Here we are going to use an updated version of the same script extended to locally-partitioned and sub-partitioned indexes. Below is the model I have created for this purpose:

And here’s the actual size of the newly-created partitioned index:

If you execute the original non-updated script to check whether the locally-partitioned index occupies more space than it should, you will realise that it will not detect this, as shown below:

Let’s check if the new ParitionedIndexEfficiency script will spot anything in the T_IDX partitioned index:

When the script displays Partition Level: TOP it is simply indicating that the current partition is not sub-partitioned. So, according the output of the new script, rebuilding P4,P5 and PMAX partitions will halve their actual size. Let’s see:

As you can see, the new script not only detects degenerated partitioned indexes but it is running with a very acceptable precision, oscillating between 81% and 86%.

3. Locally Sub-partitioned indexes

Along the same lines, we’ll now see how I have created and degenerated a locally sub-partitioned index (notice the granularity parameter value in the dbms_stats package):

And this is the actual size of this sub-partitioned index:

Finally it is time to check whether the new script is able to detect the presence of exploded sub-partitions in the above index:

When the script displays Partition Level: SUB then it is simply indicating that the current partition is a sub-partition of a TOP-level partition.

Let’s rebuild these two sub-partitions so that we can get an idea about the precision of the new script:

We went from an index size of 50MB to a new one of 38MB by rebuilding only two sub-partitions, recovering 6MB per sub-partition for a total gain of 12MB. Two important points can be emphasized here; first it is interesting to see that the script only locates partitions that are worth rebuilding, and secondly that the precision of the script is very acceptable.

4. Summary

There is one thing that does warrant mentioning again. Very often, you just don’t need to rebuild your indexes. In particular, you would not base your tuning and performance troubleshooting strategy on index rebuild operations. Indeed, you might be very unpleasantly surprised if you do so. However, if you really want to diagnose the efficiency of your indexes, looking for disk space reduction, or for an eventual performance enhancement which might become possible thanks to this index size reduction, then you can run the IndexEfficiency script for non-partitioned indexes and PartitionedIndexEfficiency script for locally-partitioned or sub-partitioned indexes.

You can download the script for this article here.