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… Continue Reading →

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):

create table t as select rownum n1, trunc((rownum-1)/5) n2, 'Fixed Text' v1
from dual connect by level <=1e6;

create index t_idx on t(n1);

begin
  delete from t;
  insert into t select 
                    rownum n1, trunc((rownum-1)/5) n2, 'Modif Text' v1
                from dual connect by level <=1e6;
  commit;
end;
/
exec dbms_stats.gather_table_stats(user, 't', cascade => true);

SQL> @sizeBySegNameMB
Enter value for segment_name: t_idx
Enter value for owner       : c##mhouri

SEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------- -------------------- --------------- ----------
INDEX              USERS      T_IDX                                        37
                                                                   ----------
Total Segment Size                                                         37

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):

SQL> @IndexEfficiency
Enter value for m_schemaname: c##mhouri

table name : T - index name : T_IDX
     Current Leaf blocks:        4,450         Target Leaf blocks:        2,195

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

SQL> select index_name, leaf_blocks from user_indexes where index_name ='T_IDX';

INDEX_NAME           LEAF_BLOCKS
-------------------- -----------
T_IDX                       4450

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

SQL> alter index t_idx rebuild;

SQL> select index_name, leaf_blocks from user_indexes where index_name ='T_IDX';

INDEX_NAME           LEAF_BLOCKS
-------------------- -----------
T_IDX                       2226


SQL> @sizeBySegNameMB
Enter value for segment_name: t_idx
Enter value for owner       : c##mhouri

SEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------- -------------------- --------------- ----------
INDEX              USERS      T_IDX                                        18
                                                                   ----------
Total Segment Size                                                         18

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:

create table t (n1 number, n2 number, v1 varchar2(30))
partition by range(n1)
(partition p1 values less than (10000)
,partition p2 values less than (50000)
,partition p3 values less than (100000)
,partition p4 values less than (300000)
,partition p5 values less than (500000)
,partition pmax values less than(maxvalue)
);
create index t_idx on t(n1) local;

insert into t select rownum , trunc((rownum-1)/5), 'Fixed Text' from dual connect by level <=1e6;
commit;

begin
  delete from t;
  insert into t select 
                    rownum n1, trunc((rownum-1)/5) n2, 'Modif Text' v1
                from dual connect by level <=1e6;
  commit;
end;
/

exec dbms_stats.gather_table_stats(user, 't', cascade => true);

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

SQL> @sizeBySegNameMB
Enter value for segment_name: t_idx
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------- -------------------- --------------- ----------
INDEX PARTITION    USERS      T_IDX                P1                      .5
INDEX PARTITION    USERS      T_IDX                P2                       2
INDEX PARTITION    USERS      T_IDX                P3                       3
INDEX PARTITION    USERS      T_IDX                P4                      10
INDEX PARTITION    USERS      T_IDX                P5                      10
INDEX PARTITION    USERS      T_IDX                PMAX                    24
                                                                   ----------
Total Segment Size                                                       49.5

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:

SQL> @IndexEfficiency
Enter value for m_schemaname: c##mhouri

PL/SQL procedure successfully completed.

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

SQL> @PartitionedIndexEfficiency
Enter value for m_schemaname: c##mhouri

table name : T - index name : T_IDX - Partition Name :P4 - Partition Level: TOP
--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63


table name : T - index name : T_IDX - Partition Name :P5 - Partition Level: TOP
--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63


table name : T - index name : T_IDX - Partition Name :PMAX - Partition Level: TOP
--> Current partition index size (MB): 24    Expected Partition index size (MB): 11.11

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:

SQL> alter index t_idx rebuild partition p4;
SQL> alter index t_idx rebuild partition p5;
SQL> alter index t_idx rebuild partition pmax;



SQL> @sizeBySegNameMB
Enter value for segment_name: t_idx
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------- -------------------- --------------- ----------
INDEX PARTITION    USERS      T_IDX                P1                      .5
INDEX PARTITION    USERS      T_IDX                P2                       2
INDEX PARTITION    USERS      T_IDX                P3                       3
INDEX PARTITION    USERS      T_IDX                P4                       4 –-> 10MB
INDEX PARTITION    USERS      T_IDX                P5                       4 –-> 10MB
INDEX PARTITION    USERS      T_IDX                PMAX                     9 –-> 24MB
                                                                   ----------
Total Segment Size                                                       22.5

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):

create table t (n1 number, n2 number, v1 varchar2(30))
partition by range(n1)
subpartition by hash(n2)
(partition p1 values less than (10000)
,partition p2 values less than (50000)
,partition p3 values less than (100000)
,partition p4 values less than (300000)
,partition p5 values less than (500000)
,partition pmax values less than(maxvalue)
 subpartitions 8
);

create index t_idx on t(n1) local;

insert into t select rownum , trunc((rownum-1)/5) , 'Fixed Text' from dual connect by level <=1e6;

commit;

begin
 delete from t;
 insert into t select 
 rownum n1, trunc((rownum-1)/5) n2, 'Modif Text' v1
 from dual connect by level <=1e6;
 commit;
end;
/

exec dbms_stats.gather_table_stats(user, 't', granularity => 'ALL', cascade => true);

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

SQL> @sizeBySegNameMB
Enter value for segment_name: t_idx
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------- -------------------- --------------- ----------
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13353           .5
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13354            2
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13355            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13356           10
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13357           10
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13358            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13359            4
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13360            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13361            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13362            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13363            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13364            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13365            3
                                                                   ----------
Total Segment Size                                                       50.5

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

SQL> @PartitionedIndexEfficiency
Enter value for m_schemaname: c##mhouri

table name : T - index name : T_IDX - Partition Name :SYS_SUBP13356 -Partition Level: SUB
--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63


table name : T - index name : T_IDX - Partition Name :SYS_SUBP13357 -Partition Level: SUB
--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63

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:

SQL> alter index t_idx rebuild subpartition SYS_SUBP13356;
SQL> alter index t_idx rebuild subpartition SYS_SUBP13357;

SQL> @sizeBySegNameMB
Enter value for segment_name: t_idx
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------- -------------------- --------------- ----------
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13353           .5
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13354            2
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13355            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13356            4 –-> 10MB
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13357            4 –-> 10MB
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13358            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13359            4
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13360            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13361            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13362            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13363            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13364            3
INDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13365            3
                                                                   ----------
Total Segment Size                                                       38.5

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.