Unlike Table Compression that uses deduplication of column values, Index Compression is based on the keys. Key Compression is also called Prefix Compression.
This relies on repeated leading key values being eliminated. Thus, for example, if the leading column of the composite index has frequently repeated values and because an Index is always an organised (sorted) structure, we find the repeated values appearing as if "sequentially". Key Compression can eliminate the repeated values.
Thus, it becomes obvious that Index Key Compression is usable for
a. A Composite Index of 2 or more columns
b. Repeated appearances of values in the *leading* key columns
c. Compression defined for a maximum of n-1 columns (where n is the number of columns in the index). That is, the last column cannot be compressed.
Note that a Non-Unique Index automatically has the ROWID appended to it, so Key Compression can be applied to all the columns defined.
Let's look at a few examples.
Starting with creating a fairly large table (that is a multiplied copy of DBA_OBJECTS)
What composite index is a good candidate for Key Compression ?
*Not* an Index that begins with OBJECT_ID as that is a Unique value.
Let's compare two indexes (compressed and non-compressed) on (OWNER, OBJECT_TYPE, OBJECT_NAME).
Note the "compress 2" specification for the first index. That is an instruction to compress based on the leading 2 columns.
Thus, the compressed index is 5,629 blocks but the normal, non-compressed index is 7,608 blocks. We make a gain of 26% in the index size.
Why did I choose OWNER, OBJECT_TYPE as the leading columns ? Because I expected a high level of repetition on these column names.
Note : I have not explored Advanced Index Compression available in 12.1.0.2
Advanced Index Compression tested in 12.1.0.2
.
.
This relies on repeated leading key values being eliminated. Thus, for example, if the leading column of the composite index has frequently repeated values and because an Index is always an organised (sorted) structure, we find the repeated values appearing as if "sequentially". Key Compression can eliminate the repeated values.
Thus, it becomes obvious that Index Key Compression is usable for
a. A Composite Index of 2 or more columns
b. Repeated appearances of values in the *leading* key columns
c. Compression defined for a maximum of n-1 columns (where n is the number of columns in the index). That is, the last column cannot be compressed.
Note that a Non-Unique Index automatically has the ROWID appended to it, so Key Compression can be applied to all the columns defined.
Let's look at a few examples.
Starting with creating a fairly large table (that is a multiplied copy of DBA_OBJECTS)
PDB1@ORCL> create table target_data as select * from source_data where 1=2; Table created. PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data; 364496 rows created. PDB1@ORCL> commit; Commit complete. PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data; 364496 rows created. PDB1@ORCL> commit; Commit complete. PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data; 364496 rows created. PDB1@ORCL> commit; Commit complete. PDB1@ORCL> PDB1@ORCL> desc target_data Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(13) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) PDB1@ORCL>
What composite index is a good candidate for Key Compression ?
*Not* an Index that begins with OBJECT_ID as that is a Unique value.
Let's compare two indexes (compressed and non-compressed) on (OWNER, OBJECT_TYPE, OBJECT_NAME).
PDB1@ORCL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 2;
Index created.
PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');
PL/SQL procedure successfully completed.
PDB1@ORCL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /
LEAF_BLOCKS
-----------
5629
PDB1@ORCL>
PDB1@ORCL> drop index target_data_ndx_1_comp
2 /
Index dropped.
PDB1@ORCL> create index target_data_ndx_2_nocomp on
2 target_data (owner, object_type, object_name) ;
Index created.
PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_NOCOMP');
PL/SQL procedure successfully completed.
PDB1@ORCL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_2_NOCOMP'
4 /
LEAF_BLOCKS
-----------
7608
PDB1@ORCL>
Note the "compress 2" specification for the first index. That is an instruction to compress based on the leading 2 columns.
Thus, the compressed index is 5,629 blocks but the normal, non-compressed index is 7,608 blocks. We make a gain of 26% in the index size.
Why did I choose OWNER, OBJECT_TYPE as the leading columns ? Because I expected a high level of repetition on these column names.
Advanced Index Compression tested in 12.1.0.2
.
.