Oracle Index Key Compression

Description

Oracle introduced Index Key Compression in 8i. Though it has been around since 8i more recent versions appear to have improved it and it has become are more noted feature to use to improve space utilization as well as performance.

Index Key Compression applies to B-Tree Indexes and index organized tables (IOT), however Bitmap Indexes have a form of compression by default. Index Key Compression works a lot like table/data compression where duplicated values are removed from index entries. Due to the way indexes are structure as compared to tables it is a little more complicated and has some more restrictions and considerations than table compression. Index compression works at the column level and compresses repeating groups at the column level where table does something similar but done at the block level.

Compression only happens with new data being loading, therefore it is possible for an object to have compressed and uncompressed data at the same time. Therefore to maintain compression you must perform maintenance activities over time.

Index compression can be turned on and off for an index at anytime with a rebuild of the index.
Index Key compression can be used:
A non-unique index single and multi-column
You have a unique multi-column index.
Generally the larger the block size the better the compression, example 16k block size will give better compression than an 8k block size.

In non-unique indexes Index Key Compression all index columns are stored compressed, however in unique indexes at least one column of the index has to be stored uncompressed. The Index Key, with a unique index, compression creates a grouping piece, a grouping piece is the column or columns that form a leading subset of the columns in the index the second piece is the suffix or also called the unique piece. Then the grouping piece is stored in a way that it is shared by multiple suffix or unique pieces. The cardinality of the columns compressed determines the level of compression that can occur. For example, if a unique index consists of four columns and provides uniqueness mostly by the last two columns, it is best to choose the two leading columns to be stored compressed. If you choose to compress three columns, the repetitiveness will be very low, and the compression will not be as good.



Using Index Compression

There are 3 ways to compress and index.

1. Compress the index when it is created with the compress keyword
a. create index scott.dept_dname on scott.dept (dname) compress ;
2. Rebuild the index compressed with the alter index rebuild and the compress keyword.
a. alter index scott.dept_dname on scott.dept (dname) rebuild compress ;
3. Rebuild the index compressed online with the alter index rebuild online compress keyword (Tested 10g and 11g)
a. alter index scott.dept_dname on scott.dept (dname) rebuild online compress ;

To rebuild a compressed index to an uncompressed index we can just use the same 3 way just not compressed.

1. Un-Compress the index by dropping and recreating the index
a. drop index scott.dept_dname ;
b. create index scott.dept_dname on scott.dept (dname) ;
2. Rebuild the index compressed with the alter index rebuild and the nocompress keyword.
a. alter index scott.dept (dname) rebuild nocompress ;
b. Rebuild the index compressed online with the alter index rebuild online and the nocompress keyword
c. alter index scott.dept (dname) rebuild online nocompress ;

The compress keyword of the create index or alter index will accept an integer parameter. The integer parameter tells oracle how many columns make up the grouping piece. This can allow us to control the number of columns in an index that make up the grouping piece. The group piece default is all but the last column.



Benefits

1. Increase in performance by reducing the # of blocks the index is stored in. Therefore reducing the number of blocks that have to be read when reading from the index.
2. Index uses less space therefore saving valuable disk space.
3. The implementation of Index Key Compression is transparent to all applications as the compressed indexes are used just like all other B-Tree indexes.



Limitations

1. Performance may suffer during index scans as the burden on the CPU is increased in order to rebuild the key values.
2. Index compression cannot be used on a unique index that has only one attribute as a single column unique index by its very nature will not have a repeating group for the grouping piece.

Example:

SQL> alter index scott.pk_dept rebuild compress ;
alter index scott.pk_dept rebuild compress
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key


SQL>



Summary

Like with any feature of Oracle you must test it in your environment and evaluate for yourself the features value. Based on the small test performed it appears that index compression does indeed shrink the size of the index. The small test showed out compressed index size as 2048 bytes and the uncompressed index size at 4096. This would indicate significant reduction in index size. The reduction in index size helped reduce I/O which can help improve performance of queries that use the compressed index. The small test does seem to indicate that the query runtime can be faster as a query using the compressed index ran in .01 seconds where as the same index columns with the index uncompressed, same query, took .06 seconds.

The test is too small to draw conclusive results, but it certainly showed that the feature is worth exploring further with much larger data sets. Future test should be performed on very large tables with very large indexes and would like to examine 10046 traces with before compression and after compression results to give a best comparison to the full impact of Index Key Compression.

** Note: For specifics of tests and the results step by step see Specific Test Results Section



Specific Test Results

Built a small test using index compression against an uncompressed table. Though the table is not very large we can see that we can make some preliminary conclusions from the test.

SQL>
SQL> ---------------------------------------------
SQL> -- Working with the uncompressed table.
SQL> ---------------------------------------------
SQL>
SQL> -----------------------------------------------------------------
SQL> -- set our timing on so we get timings on index build
SQL> set timing on
SQL>
SQL> -- Create new compressed index on our uncompressed table
SQL> create index test_index_compressed
2 on TEST_COMPRESSION_UNCOMPRESSED (OWNER, TABLE_NAME)
3 compress ;

Index created.

Elapsed: 00:00:00.20
SQL>
SQL> -- turn the timing off as we are in just a query for size
SQL> set timing off
SQL>
SQL> -- Showsize of the new compressed index
SQL> select sum(bytes)/1024 from dba_extents where segment_name = 'TEST_INDEX_COMPRESSED' ;

SUM(BYTES)/1024
---------------
2048

SQL>
SQL> -- set autotrace on so we se I/O stats
SQL> -- when we query using the index
SQL> set autotrace traceonly
SQL>
SQL> -- set timing on to test time it takes to complete the query using the index
SQL> set timing on
SQL>
SQL> -- Check our access times, plan and resources with the new compressed index
SQL> select owner, table_name, tablespace_name, num_rows
2 from TEST_COMPRESSION_UNCOMPRESSED
3 where owner = 'MRMESSIN'
4 and table_name = 'TEST_COMPRESSION_UNCOMPRESSED' ;

31 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 461570243

-------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 31 1984 34 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST_COMPRESSION_UNCOMPRESSED 31 1984 34 (0) 00:00:01
* 2 INDEX RANGE SCAN TEST_INDEX_COMPRESSED 31 1 (0) 00:00:01
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='MRMESSIN' AND "TABLE_NAME"='TEST_COMPRESSION_UNCOMPRESSED')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
61 recursive calls
0 db block gets
122 consistent gets
1 physical reads
0 redo size
1038 bytes sent via SQL*Net to client
403 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31 rows processed

SQL>
SQL> -- Turn the autotrace back off
SQL> set autotrace off
SQL>
SQL> -- Turn timing off again as we are just dropping the index
SQL> set timing off
SQL>
SQL> -- drop the index so we can create a compressed one using the exact same columns
SQL> drop index test_index_compressed ;

Index dropped.

SQL>
SQL> -----------------------------------------------------------------
SQL> -- set timing on to get timing on index build
SQL> set timing on
SQL>
SQL> -- create a new uncompressed index just like the compressed one from before
SQL> create index test_index_uncompressed
2 on TEST_COMPRESSION_UNCOMPRESSED (OWNER, TABLE_NAME) ;

Index created.

Elapsed: 00:00:00.51
SQL>
SQL> -- set timing off as we are just checking size
SQL> set timing off
SQL>
SQL> -- Show size of the new uncompressed index
SQL> select sum(bytes)/1024 from dba_extents where segment_name = 'TEST_INDEX_UNCOMPRESSED' ;

SUM(BYTES)/1024
---------------
4096

SQL>
SQL> -- set autotrace on so we se I/O stats
SQL> -- when we query using the index
SQL> set autotrace traceonly
SQL>
SQL> -- set timing on to check for time for query to complete
SQL> -- so we can compare compressed and uncompressed at a high level
SQL> set timing on
SQL>
SQL> -- Check our access times, plan and resources with the new uncompressed index
SQL> select owner, table_name, tablespace_name, num_rows
2 from TEST_COMPRESSION_UNCOMPRESSED
3 where owner = 'MRMESSIN'
4 and table_name = 'TEST_COMPRESSION_UNCOMPRESSED' ;

31 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2607798795

-------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 31 1984 36 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST_COMPRESSION_UNCOMPRESSED 31 1984 36 (0) 00:00:01
* 2 INDEX RANGE SCAN TEST_INDEX_UNCOMPRESSED 31 3 (0) 00:00:01
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='MRMESSIN' AND "TABLE_NAME"='TEST_COMPRESSION_UNCOMPRESSED')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
61 recursive calls
0 db block gets
124 consistent gets
2 physical reads
0 redo size
1038 bytes sent via SQL*Net to client
403 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
31 rows processed

SQL>
SQL> -- Turn the autotrace back off
SQL> set autotrace off
SQL>
SQL> -- Can we rebuild the uncompressed index to a compressed index using alter index rebuild online?
SQL> alter index test_index_uncompressed rebuild online compress ;

Index altered.

Elapsed: 00:00:00.89
SQL>
SQL> -- set timing off only checking size of index
SQL> set timing off
SQL>
SQL> -- Show size of the new uncompressed index
SQL> select sum(bytes)/1024 from dba_extents where segment_name = 'TEST_INDEX_UNCOMPRESSED' ;

SUM(BYTES)/1024
---------------
2048

SQL> -- Drop the newly rebuilt compressed index
SQL> drop index test_index_uncompressed ;

Index dropped.

SQL>
SQL> -- turn the spool to log file off
SQL> spool off