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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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):
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 <span style="color: red;">P4</span> 10 INDEX PARTITION USERS T_IDX <span style="color: red;">P5</span> 10 INDEX PARTITION USERS T_IDX <span style="color: red;">PMAX</span> 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:
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @PartitionedIndexEfficiency Enter value for m_schemaname: c##mhouri table name : T - index name : T_IDX - Partition Name :<span style="color: red;">P4</span> - Partition Level: <span style="color: red;">TOP</span> --> Current partition index size (MB): 10 Expected Partition index size (MB): 4.63 table name : T - index name : T_IDX - Partition Name :<span style="color: red;">P5</span> - Partition Level: <span style="color: red;">TOP</span> --> Current partition index size (MB): 10 Expected Partition index size (MB): 4.63 table name : T - index name : T_IDX - Partition Name :<span style="color: red;">PMAX</span> - Partition Level: <span style="color: red;">TOP</span> --> 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 <span style="color: red;">P4</span> 4 –-> 10MB INDEX PARTITION USERS T_IDX <span style="color: red;">P5</span> 4 –-> 10MB INDEX PARTITION USERS T_IDX <span style="color: red;">PMAX</span> 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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 <span style="color: red;">SYS_SUBP13356</span> 10 INDEX SUBPARTITION USERS T_IDX <span style="color: red;">SYS_SUBP13357</span> 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:
|
1 2 3 4 5 6 7 8 9 |
SQL> @PartitionedIndexEfficiency Enter value for m_schemaname: c##mhouri table name : T - index name : T_IDX - Partition Name :<span style="color: red;">SYS_SUBP13356</span> -Partition Level: <span style="color: red;">SUB</span> --> Current partition index size (MB): 10 Expected Partition index size (MB): 4.63 table name : T - index name : T_IDX - Partition Name :<span style="color: red;">SYS_SUBP13357</span> -Partition Level: <span style="color: red;">SUB</span> --> 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 <span style="color: red;">SYS_SUBP13356</span> 4 –-> 10MB INDEX SUBPARTITION USERS T_IDX <span style="color: red;">SYS_SUBP13357</span> 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.
Load comments