Following up on my earlier post on 12.1.0.2 Advanced Index Compression, one of my readers asked what would be the difference if I reversed the order of columns in the chosen index.
My defined index was on (OWNER, OBJECT_TYPE, OBJECT_NAME) --- defined as being from the column with the fewest distinct values to the most. This ordering is best compressible with Index Key Compression (also known as Prefix Compression). If I reverse the order, Index Key Compression for the two leading columns wouldn't deliver the same level of compression. The question is whether Advanced Index Compression can intelligently handle the reversal.
Surprisingly, this index with Prefix 2 on (OBJECT_NAME, OBJECT_TYPE) is, at 3,091 leaf blocks, smaller than the previous index with Prefix 2 on (OWNER, OBJECT_TYPE) at 5,508 leaf blocks.
Continuing with Prefix 3
At 2,277 leaf blocks it is, as expected, the same size with Prefix 3 on (OWNER, OBJECT_TYPE, OBJECT_NAME). Since the entire index key is specified as the Prefix, both indexes would be the same size.
Going on to Advanced Index Compression
This is, again, as expected. Advanced Index Compression results in the same size irrespective of the ordering of the columns.
The advantage of Advanced Index Compression over Key or Prefix Compression is that the DBA does not need to determine the Prefix for compression. He does not have to spend time to analyze the data and compare the number of distinct values for each of the columns in the composite index.
.
.
.
My defined index was on (OWNER, OBJECT_TYPE, OBJECT_NAME) --- defined as being from the column with the fewest distinct values to the most. This ordering is best compressible with Index Key Compression (also known as Prefix Compression). If I reverse the order, Index Key Compression for the two leading columns wouldn't deliver the same level of compression. The question is whether Advanced Index Compression can intelligently handle the reversal.
SQL> create index target_data_ndx_3_comp on
2 target_data(object_name, object_type, owner) compress 2;
Index created.
SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_COMP');
PL/SQL procedure successfully completed.
SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_3_COMP'
4 /
LEAF_BLOCKS
-----------
3091
SQL>
Surprisingly, this index with Prefix 2 on (OBJECT_NAME, OBJECT_TYPE) is, at 3,091 leaf blocks, smaller than the previous index with Prefix 2 on (OWNER, OBJECT_TYPE) at 5,508 leaf blocks.
Continuing with Prefix 3
SQL> drop index target_data_ndx_3_comp;
Index dropped.
SQL> create index target_data_ndx_3_comp on
2 target_data(object_name, object_type, owner) compress 3;
Index created.
SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_COMP');
PL/SQL procedure successfully completed.
SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_3_COMP'
4 /
LEAF_BLOCKS
-----------
2277
SQL>
At 2,277 leaf blocks it is, as expected, the same size with Prefix 3 on (OWNER, OBJECT_TYPE, OBJECT_NAME). Since the entire index key is specified as the Prefix, both indexes would be the same size.
Going on to Advanced Index Compression
SQL> drop index target_data_ndx_3_comp;
Index dropped.
SQL> create index target_data_ndx_4_advcomp on
2 target_data(object_name, object_type, owner)
3 compress advanced low
4 /
Index created.
SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_4_ADVCOMP');
PL/SQL procedure successfully completed.
SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_4_ADVCOMP'
4 /
LEAF_BLOCKS
-----------
2277
SQL>
This is, again, as expected. Advanced Index Compression results in the same size irrespective of the ordering of the columns.
The advantage of Advanced Index Compression over Key or Prefix Compression is that the DBA does not need to determine the Prefix for compression. He does not have to spend time to analyze the data and compare the number of distinct values for each of the columns in the composite index.
.
.
.
2 comments:
Have some findings to share with you...
create table test (col_all_distinct number, col_50pct_distinct number, col_2_distinct char(1));
SQL> @genRows
SQL> commit;
SQL> @getDistinctCount
DIST_COL_ALL_DISTINCT DIST_COL_50PCT_DISTINCT DIST_COL_2_DISTINCT
--------------------- ----------------------- -------------------
1000000 500000 2
The above is the number of distinct values in the columns, starting from left to right.
T1
==
create index test_idx on test (col_all_distinct, col_50pct_distinct, col_2_distinct);
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 3201 DISABLED
T2
==
SQL> create index test_idx on test(col_all_distinct, col_50pct_distinct, col_2_distinct) compress;
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 4045 ENABLED <---------- Blocks are more. This is like compress 3.
Reversing the columns - ------------------
T3
==
SQL> create index test_idx on test(col_2_distinct,col_50pct_distinct,col_all_distinct);
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 3201 DISABLED <---------- without any compression, even reversing the keys will result in same size.
Enabling compression on the index with reversed columns ------- ------------
T4
==
SQL> create index test_idx on test(col_2_distinct,col_50pct_distinct,col_all_distinct) compress;
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 4041 ENABLED <---------- Blocks are more. Almost same as previous.
Cont...
compressing the original ordered index ------------ ---------------------
T5
==
SQL> create index test_idx on test(col_all_distinct, col_50pct_distinct, col_2_distinct) compress advanced low;
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 3201 ADVANCED LOW <---------- Index looks like not compressed. Exactly the size of uncompressed index
compressing the reversed column index ------------- --------------------
T6
==
SQL> create index test_idx on test(col_2_distinct,col_50pct_distinct,col_all_distinct) compress advanced low;
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 2925 ADVANCED LOW <---------- Index more compressed.
Advanced index compression will result in a smaller sized index, depending on how much it can compress the first column. But it will never result in a size bigger than the uncompressed form (as in normal compression, pre 12c) that I got terribly wrong. Atmost, the compressed index will be of the size of the uncompressed index, which is in T5 (similar to T1).
Now let me increase the number of distinct values of the middle column to exactly the same as the first column and check the size of the reversed index -
SQL> @getDistinctCount
DIST_COL_ALL_DISTINCT DIST_COL_50PCT_DISTINCT DIST_COL_2_DISTINCT
--------------------- ----------------------- -------------------
1000000 1000000 2
T7
==
SQL> create index test_idx on test(col_2_distinct,col_50pct_distinct,col_all_distinct);
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 3203 DISABLED <------------ Uncompressed index now takes 3203 blocks
T8
==
SQL> create index test_idx on test(col_2_distinct,col_50pct_distinct,col_all_distinct) compress advanced low;
SQL> @gatherIndexStats
SQL> @getIndBlkInfo
BLEVEL LEAF_BLOCKS COMPRESSION
---------- ----------- -------------
2 2926 ADVANCED LOW <---------- Index shows a growing trend. Previous was 2925.
Although the index shows a growing trend, but it will never grow beyond 3203 blocks, unless there is an increase in the number of rows in the table.
Post a Comment