Oracle Index Compression

Oracle has an index compression feature.
It reduces I/O read but increases CPU consumption. If you have bottleneck in I/O (aka I/O bound) and plenty CPU power to spare, this feature might help you.
It only works on Regular Index, not Bitmap.

It looks like this

CREATE INDEX test_obj_n1
ON test_obj(owner, object_type, status, object_name)
COMPRESS 3;

The COMPRESS 3 indicates which column you want to compress. In this case is status.

Oracle also provides an internal tool to specify which column has the most optimal compression. Analyze the index with VALIDATE STRUCTURE and get the result from index_stats built-in view.

SQL> ANALYZE INDEX test_obj_n1 VALIDATE STRUCTURE;
SQL> SELECT height, lf_blks, br_blks, btree_space
  2       , opt_cmpr_count, opt_cmpr_pctsave
  3 FROM index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- ----------- -------------- ----------------
         2         78          1      631716              3               39

This result tells us the most optimal column to compress is 3 (status), with 39% savings in space.

Here is the demo script:

SQL> CREATE TABLE test_obj AS
  2  SELECT * FROM all_objects;

Table created

SQL> CREATE index test_obj_n1
  2  ON test_obj(owner, object_type, status, object_name);

Index created

SQL> ANALYZE INDEX test_obj_n1 VALIDATE STRUCTURE;

Index analyzed

SQL> 
SQL> SELECT height, lf_blks, br_blks, btree_space
  2       , opt_cmpr_count, opt_cmpr_pctsave
  3  FROM index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- ----------- -------------- ----------------
         2         78          1      631716              3               39

SQL> DROP INDEX test_obj_n1;

Index dropped

SQL> CREATE INDEX test_obj_n1
  2   ON test_obj(owner, object_type, status, object_name)
  3  COMPRESS 3;

Index created

SQL> ANALYZE INDEX test_obj_n1 VALIDATE STRUCTURE;

Index analyzed

SQL> SELECT height, lf_blks, br_blks, btree_space
  2       , opt_cmpr_count, opt_cmpr_pctsave
  3  FROM index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- ----------- -------------- ----------------
         2         47          1      383652              3                0

About these ads