Understanding Index Compression In Oracle Database 19c

From Oracle 9i onward both non-unique and unique indexes can be compressed to save disk space.

The COMPRESS [N] clause indicates compression is required, with “N” optionally specifying the prefix, or number of keys (with similar data) to compress. The default for index creation is NOCOMPRESS.

Bitmap indexes can’t be compressed.

SQL> CREATE TABLE t1 (
  2    col_1 VARCHAR2(50),
  3    col_2 VARCHAR2(50),
  4    col_3 VARCHAR2(50),
  5    col_4 VARCHAR2(50),
  6    col_5 VARCHAR2(50),
  7    col_6 VARCHAR2(50)
  8  );

Table created.

--The first five columns contain the same data in each row, but the last column contains a unique value in each row.
SQL> INSERT /*+ APPEND */ INTO t1
  2  SELECT RPAD('X',50, 'X'),
  3         RPAD('X',50, 'X'),
  4         RPAD('X',50, 'X'),
  5         RPAD('X',50, 'X'),
  6         RPAD('X',50, 'X'),
  7         RPAD(TO_CHAR(level),50, 'X')
  8  FROM   dual
  9  CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

PL/SQL procedure successfully completed.

SQL> SELECT bytes FROM user_segments WHERE segment_name = 'T1_IDX';

     BYTES
----------
   4194304

SQL> DROP INDEX t1_idx;

Index dropped.

SQL> CREATE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6) COMPRESS 5;

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

PL/SQL procedure successfully completed.

SQL> SELECT bytes FROM user_segments WHERE segment_name = 'T1_IDX';

     BYTES
----------
    786432

--Even though the index is compressed, the NON - UNIQUE index HIT will take place as shown below :-

SQL> SELECT col_1 FROM t1 WHERE col_1 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' AND ROWNUM <= 1;

COL_1
--------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Execution Plan
----------------------------------------------------------
Plan hash value: 3679858050

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    51 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T1_IDX |     1 |    51 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   2 - access("COL_1"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
              X')

SQL> DROP INDEX t1_idx;

Index dropped.

SQL> CREATE UNIQUE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

PL/SQL procedure successfully completed.

SQL> SELECT bytes FROM user_segments WHERE segment_name = 'T1_IDX';

     BYTES
----------
   4194304

SQL> DROP INDEX t1_idx;

Index dropped.

SQL> CREATE UNIQUE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6) COMPRESS 5;

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

PL/SQL procedure successfully completed.

SQL> SELECT bytes FROM user_segments WHERE segment_name = 'T1_IDX';

     BYTES
----------
    786432	
	
--Even though the index is compressed, the UNIQUE index HIT will take place as shown below :-

SQL> SELECT col_1 FROM t1 WHERE col_1 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' AND ROWNUM <= 1;

COL_1
--------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Execution Plan
----------------------------------------------------------
Plan hash value: 3679858050

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    51 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T1_IDX |     1 |    51 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   2 - access("COL_1"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
              X')