{"id":73076,"date":"2017-02-09T13:39:49","date_gmt":"2017-02-09T13:39:49","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-partitioned-index-efficiency\/"},"modified":"2021-07-14T13:06:56","modified_gmt":"2021-07-14T13:06:56","slug":"oracle-partitioned-index-efficiency","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-partitioned-index-efficiency\/","title":{"rendered":"Oracle Partitioned Index Efficiency"},"content":{"rendered":"

PartitionedIndexEfficiency<\/a><\/p>\n

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<\/a> 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.<\/p>\n

1. Non Partitioned Indexes<\/strong><\/h2>\n

Here’s an example showing how Jonathan Lewis’ script finds degenerated indexes with phenomenal precision (in Oracle 12.1.0.1):<\/p>\n

create table t as select rownum n1, trunc((rownum-1)\/5) n2, 'Fixed Text' v1\r\nfrom dual connect by level <=1e6;\r\n\r\ncreate index t_idx on t(n1);\r\n\r\nbegin\r\n  delete from t;\r\n  insert into t select \r\n                    rownum n1, trunc((rownum-1)\/5) n2, 'Modif Text' v1\r\n                from dual connect by level <=1e6;\r\n  commit;\r\nend;\r\n\/\r\nexec dbms_stats.gather_table_stats(user, 't', cascade => true);\r\n\r\nSQL> @sizeBySegNameMB\r\nEnter value for segment_name: t_idx\r\nEnter value for owner       : c##mhouri\r\n\r\nSEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB\r\n------------------ ---------- -------------------- --------------- ----------\r\nINDEX              USERS      T_IDX                                        37\r\n                                                                   ----------\r\nTotal Segment Size                                                         37<\/pre>\n
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):<\/p>\n
SQL> @IndexEfficiency\r\nEnter value for m_schemaname: c##mhouri\r\n\r\ntable name : T - index name : T_IDX\r\n     Current Leaf blocks:        4,450         Target Leaf blocks:        2,195<\/pre>\n
The above output is indeed showing that there are 2255 leaf blocks that can be recovered and saved in the T_IDX index:<\/p>\n
SQL> select index_name, leaf_blocks from user_indexes where index_name ='T_IDX';\r\n\r\nINDEX_NAME           LEAF_BLOCKS\r\n-------------------- -----------\r\nT_IDX                       4450<\/pre>\n
Let’s now rebuild this index and get its new size in order to evaluate the precision of the script:<\/p>\n
SQL> alter index t_idx rebuild;\r\n\r\nSQL> select index_name, leaf_blocks from user_indexes where index_name ='T_IDX';\r\n\r\nINDEX_NAME           LEAF_BLOCKS\r\n-------------------- -----------\r\nT_IDX                       2226\r\n\r\n\r\nSQL> @sizeBySegNameMB\r\nEnter value for segment_name: t_idx\r\nEnter value for owner       : c##mhouri\r\n\r\nSEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB\r\n------------------ ---------- -------------------- --------------- ----------\r\nINDEX              USERS      T_IDX                                        18\r\n                                                                   ----------\r\nTotal Segment Size                                                         18<\/pre>\n
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.<\/p>\n
2. Locally-partitioned indexes<\/strong><\/h2>\n
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:<\/p>\n
create table t (n1 number, n2 number, v1 varchar2(30))\r\npartition by range(n1)\r\n(partition p1 values less than (10000)\r\n,partition p2 values less than (50000)\r\n,partition p3 values less than (100000)\r\n,partition p4 values less than (300000)\r\n,partition p5 values less than (500000)\r\n,partition pmax values less than(maxvalue)\r\n);\r\ncreate index t_idx on t(n1) local;\r\n\r\ninsert into t select rownum , trunc((rownum-1)\/5), 'Fixed Text' from dual connect by level <=1e6;\r\ncommit;\r\n\r\nbegin\r\n  delete from t;\r\n  insert into t select \r\n                    rownum n1, trunc((rownum-1)\/5) n2, 'Modif Text' v1\r\n                from dual connect by level <=1e6;\r\n  commit;\r\nend;\r\n\/\r\n\r\nexec dbms_stats.gather_table_stats(user, 't', cascade => true);\r\n<\/pre>\n
And here’s the actual size of the newly-created partitioned index:<\/p>\n
SQL> @sizeBySegNameMB\r\nEnter value for segment_name: t_idx\r\nEnter value for owner: c##mhouri\r\n\r\nSEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB\r\n------------------ ---------- -------------------- --------------- ----------\r\nINDEX PARTITION    USERS      T_IDX                P1                      .5\r\nINDEX PARTITION    USERS      T_IDX                P2                       2\r\nINDEX PARTITION    USERS      T_IDX                P3                       3\r\nINDEX PARTITION    USERS      T_IDX                P4<\/span>                      10\r\nINDEX PARTITION    USERS      T_IDX                P5<\/span>                      10\r\nINDEX PARTITION    USERS      T_IDX                PMAX<\/span>                    24\r\n                                                                   ----------\r\nTotal Segment Size                                                       49.5<\/pre>\n
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:<\/p>\n
SQL> @IndexEfficiency\r\nEnter value for m_schemaname: c##mhouri\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\n
Let’s check if the new ParitionedIndexEfficiency<\/strong> script will spot anything in the T_IDX<\/code> partitioned index:<\/p>\n
SQL> @PartitionedIndexEfficiency\r\nEnter value for m_schemaname: c##mhouri\r\n\r\ntable name : T - index name : T_IDX - Partition Name :P4<\/span> - Partition Level: TOP<\/span>\r\n--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63\r\n\r\n\r\ntable name : T - index name : T_IDX - Partition Name :P5<\/span> - Partition Level: TOP<\/span>\r\n--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63\r\n\r\n\r\ntable name : T - index name : T_IDX - Partition Name :PMAX<\/span> - Partition Level: TOP<\/span>\r\n--> Current partition index size (MB): 24    Expected Partition index size (MB): 11.11<\/pre>\n
When the script displays Partition Level: TOP<\/code> it is simply indicating that the current partition is not sub-partitioned. So, according the output of the new script, rebuilding P4<\/code>,P5<\/code> and PMAX<\/code> partitions will halve their actual size. Let’s see:<\/p>\n
SQL> alter index t_idx rebuild partition p4;\r\nSQL> alter index t_idx rebuild partition p5;\r\nSQL> alter index t_idx rebuild partition pmax;\r\n\r\n\r\n\r\nSQL> @sizeBySegNameMB\r\nEnter value for segment_name: t_idx\r\nEnter value for owner: c##mhouri\r\n\r\nSEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB\r\n------------------ ---------- -------------------- --------------- ----------\r\nINDEX PARTITION    USERS      T_IDX                P1                      .5\r\nINDEX PARTITION    USERS      T_IDX                P2                       2\r\nINDEX PARTITION    USERS      T_IDX                P3                       3\r\nINDEX PARTITION    USERS      T_IDX                P4<\/span>                       4 \u2013-> 10MB\r\nINDEX PARTITION    USERS      T_IDX                P5<\/span>                       4 \u2013-> 10MB\r\nINDEX PARTITION    USERS      T_IDX                PMAX<\/span>                     9 \u2013-> 24MB\r\n                                                                   ----------\r\nTotal Segment Size                                                       22.5\r\n<\/pre>\n
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%.<\/p>\n
3. Locally Sub-partitioned indexes<\/strong><\/h2>\n
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):<\/p>\n
create table t (n1 number, n2 number, v1 varchar2(30))\r\npartition by range(n1)\r\nsubpartition by hash(n2)\r\n(partition p1 values less than (10000)\r\n,partition p2 values less than (50000)\r\n,partition p3 values less than (100000)\r\n,partition p4 values less than (300000)\r\n,partition p5 values less than (500000)\r\n,partition pmax values less than(maxvalue)\r\n subpartitions 8\r\n);\r\n\r\ncreate index t_idx on t(n1) local;\r\n\r\ninsert into t select rownum , trunc((rownum-1)\/5) , 'Fixed Text' from dual connect by level <=1e6;\r\n\r\ncommit;\r\n\r\nbegin\r\n delete from t;\r\n insert into t select \r\n rownum n1, trunc((rownum-1)\/5) n2, 'Modif Text' v1\r\n from dual connect by level <=1e6;\r\n commit;\r\nend;\r\n\/\r\n\r\nexec dbms_stats.gather_table_stats(user, 't', granularity => 'ALL', cascade => true);<\/pre>\n
And this is the actual size of this sub-partitioned index:<\/p>\n
SQL> @sizeBySegNameMB\r\nEnter value for segment_name: t_idx\r\nEnter value for owner: c##mhouri\r\n\r\nSEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB\r\n------------------ ---------- -------------------- --------------- ----------\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13353           .5\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13354            2\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13355            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13356<\/span>           10\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13357<\/span>           10\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13358            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13359            4\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13360            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13361            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13362            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13363            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13364            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13365            3\r\n                                                                   ----------\r\nTotal Segment Size                                                       50.5\r\n<\/pre>\n
Finally it is time to check whether the new script is able to detect the presence of exploded sub-partitions in the above index:<\/p>\n
SQL> @PartitionedIndexEfficiency\r\nEnter value for m_schemaname: c##mhouri\r\n\r\ntable name : T - index name : T_IDX - Partition Name :SYS_SUBP13356<\/span> -Partition Level: SUB<\/span>\r\n--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63\r\n\r\n\r\ntable name : T - index name : T_IDX - Partition Name :SYS_SUBP13357<\/span> -Partition Level: SUB<\/span>\r\n--> Current partition index size (MB): 10    Expected Partition index size (MB): 4.63\r\n<\/pre>\n
When the script displays Partition Level: SUB<\/span><\/code> then it is simply indicating that the current partition is a sub-partition of a TOP<\/code>-level partition.<\/p>\n
Let’s rebuild these two sub-partitions so that we can get an idea about the precision of the new script:<\/p>\n
SQL> alter index t_idx rebuild subpartition SYS_SUBP13356;\r\nSQL> alter index t_idx rebuild subpartition SYS_SUBP13357;\r\n\r\nSQL> @sizeBySegNameMB\r\nEnter value for segment_name: t_idx\r\nEnter value for owner: c##mhouri\r\n\r\nSEGMENT_TYPE       TABLESPACE SEGMENT_NAME         PARTITION_NAME          MB\r\n------------------ ---------- -------------------- --------------- ----------\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13353           .5\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13354            2\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13355            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13356<\/span>            4 \u2013-> 10MB\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13357<\/span>            4 \u2013-> 10MB\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13358            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13359            4\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13360            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13361            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13362            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13363            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13364            3\r\nINDEX SUBPARTITION USERS      T_IDX                SYS_SUBP13365            3\r\n                                                                   ----------\r\nTotal Segment Size                                                       38.5<\/pre>\n
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.<\/p>\n
4. Summary<\/strong><\/h1>\n
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<\/strong> 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.<\/p>\n
You can download the script for this article here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 spac…<\/p>\n","protected":false},"author":305852,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48470,5700,5934,124952],"coauthors":[48641],"class_list":["post-73076","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-partioned-indexes","tag-partitioning","tag-performance-tuning","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/305852"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73076"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73076\/revisions"}],"predecessor-version":[{"id":88951,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73076\/revisions\/88951"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73076"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}