Index Column Order – Impact On Index Branch Blocks Part II (The Weeping Song)

In Part I, I discussed how the order of columns in an index makes no real difference to the effectiveness of the index if all columns are referenced via equality predicates.

If the leading column has a high number of distinct columns, it might result in less necessary data within index branches as less data is required to determine the unique path down to the leaf block containing the first index entry of interest. This might save a moderate number of index branch blocks. The number of branch blocks though has a trivial impact on index performance, if as in the vast majority of cases, the index height remains the same.

However, if one can potentially significantly reduce the number of required leaf blocks within an index, this might not only also significantly reduce the number of associated index branch blocks, but obviously the overall size of the index. This is possible with Basic Index Compression, but such compression is only possible if the leading column(s) has relatively few distinct values.

So going back to the demo in Part I, when the index was created with the ID column leading (which had many distinct values):

SQL> create index ziggy_id_code_i ON ziggy(id, code);

Index created.

SQL> analyze index ziggy_id_code_i validate structure;

Index analyzed.

SQL> select height, lf_blks, br_blks, br_rows_len, btree_space, used_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BR_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ---------- ---------- ----------- ----------- ----------
         3      14135         23      176612   113264736  101146313

We note the size of the index, with 14135 leaf blocks and 23 branch blocks.

If we now attempt to compress this index with basic index compression:

SQL> alter index ziggy_id_code_i rebuild compress;

Index altered.

SQL> analyze index ziggy_id_code_i validate structure;

Index analyzed.

SQL> select height, lf_blks, br_blks, br_rows_len, btree_space, used_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BR_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ---------- ---------- ----------- ----------- ----------
         3      15795         26      197435   126505652  113167136

We notice basic index compression has been totally ineffective. In fact, the index has increased in size with there now being 15795 leaf blocks and 26 branch blocks. The number of compressed index columns makes no difference, as it’s the leading column with high distinct values that is the problem here.

That’s because the de-duplication at the leaf block level necessary for effective basic index compression is impossible with the ID column leading as there are little to no replicated column values. Basic index compression must have high numbers of replicated column values in at least the leading column(s) to be effective.

If we look at the index with the replicated CODE column as the leading column:

SQL> create index ziggy_code_id_i on ziggy(code,id);

Index created.

SQL> analyze index ziggy_code_id_i validate structure;

Index analyzed.

SQL> select height, lf_blks, br_blks, br_rows_len, btree_space, used_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BR_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ---------- ---------- ----------- ----------- ----------
         3      14125         83      656341   113666656  101626042

We notice although the number of leaf blocks are similar to the previous non-compressed index at 14125 leaf blocks, at 83 there are more branch blocks (previous index had just 23). As discussed in Part I, this is because the relatively large sized CODE column must be stored in the branch blocks.

However, this index is compressible with the leading CODE column having duplicate values. Therefore, if we compress the index by compressing just the CODE column:

SQL> alter index ziggy_code_id_i rebuild compress 1;

Index altered.

SQL> analyze index ziggy_code_id_i validate structure;

Index analyzed.

SQL> select height, lf_blks, br_blks, br_rows_len, btree_space, used_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BR_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ---------- ---------- ----------- ----------- ----------
         3       4620         28      214696    37166416   33369357

We notice not only has the number of branch blocks reduced (28 down from 83), but more importantly, we have significantly reduced the number of overall leaf blocks (4620 down from 14125).

So if reducing the size of the resultant index is the aim, you will generally get a much better result by using basic index compression and ensuring the columns with the few distinct values are the leading columns, than by potentially moderately reducing branch blocks with the leading column more distinct.

The other advantage to placing the columns with fewer distinct values as the leading columns of an index is that it makes an Index Skip Scan a viable execution path if the leading column(s) is not referenced in a predicate. This is not possible if the leading column is too distinct. I’ve discussed Index Skip Scans previously in this blog.

Note basic index compression is free (you don’t need the Advanced Compression Option), but you do need to be on Enterprise Edition to use this feature.