oracle index compression and JDE

Some indexes compress well and some do not, that is my position on index compression – which is about the same for tables.

There are two types of index compression:

• Creating an Index Using Prefix Compression
  Creating an index using prefix compression (also known as key compression) eliminates repeated occurrences of key column prefix values. Prefix compression is most useful for non-unique indexes with a large number of duplicates on the leading columns.
• Creating an Index Using Advanced Index Compression (only available in 12c)
  Creating an index using advanced index compression reduces the size of all supported unique and non-unique indexes. Advanced index compression improves the compression ratios significantly while still providing efficient access to the indexes. Therefore, advanced index compression works well on all supported indexes, including those indexes that are not good candidates for prefix compression.

Take a look at the F0911 data below:

select t1.segment_name, t1.owner, t1.segment_type, t1.tablespace_name, t1.bytes, t2.COMPRESSION
from dba_segments t1, all_indexes t2
where t1.owner in ('TESTDTA', 'ORADTA', 'CRPDTA')
and t2.index_name = t1.segment_name
and t2.owner = t1.owner
and t1.segment_name like 'F0911%'
order by t1.segment_name, t1.owner;

Provides:

Note that in my example CRPDTA and TESTDTA are both the same data and compressed.  ORADTA is an older copy and not compressed.

SEGMENT_NAME     OWNER        SEGMENT_TYPE       TABLESPACE_NAME    BYTES COMPRESS

F0911_10    CRPDTA    INDEX    CRPDTAI    9964158976    ENABLED
F0911_10    ORADTA    INDEX    ORADTAI    34437660672    DISABLED
F0911_10    TESTDTA    INDEX    SSDMAXI    9963700224    ENABLED

F0911_11    CRPDTA    INDEX    CRPDTAI    4324261888    ENABLED
F0911_11    ORADTA    INDEX    ORADTAI    15610150912    DISABLED
F0911_11    TESTDTA    INDEX    SSDMAXI    4312596480    ENABLED

F0911_12    CRPDTA    INDEX    CRPDTAI    4467720192    ENABLED
F0911_12    ORADTA    INDEX    ORADTAI    13323927552    DISABLED
F0911_12    TESTDTA    INDEX    SSDMAXI    4467130368    ENABLED

F0911_13    CRPDTA    INDEX    CRPDTAI    4342087680    ENABLED
F0911_13    ORADTA    INDEX    ORADTAI    13638959104    DISABLED
F0911_13    TESTDTA    INDEX    SSDMAXI    4298702848    ENABLED

F0911_15    CRPDTA    INDEX    CRPDTAI    17101357056    ENABLED
F0911_15    ORADTA    INDEX    ORADTAI    14177140736    DISABLED
F0911_15    TESTDTA    INDEX    SSDMAXI    17108828160    ENABLED

List of all F0911 indexes is below:

Interesting hey, some are really good and some are pretty junk at compression.

_10 reduces by about 350%, _11 by 350% – but look at 15 and the compressed version is actually larger (that is a little bit of a problem with my dodgy data).

Why is this so?

Looking at the definitions of the indexes:

F0911_10 = GLPOST, GLAID, GLLT, GLCTRY

This is made up of the following columns

CRPDTA    F0911_10    CRPDTA    F0911    GLPOST    1    2    1    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLAID    2    16    8    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLLT    3    4    2    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLCTRY    4    22    0    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLFY    5    22    0    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLPN    6    22    0    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLSBL    7    16    8    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLSBLT    8    2    1    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLDGJ    9    22    0    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLASID    10    50    25    ASC
CRPDTA    F0911_10    CRPDTA    F0911    GLBRE    11    2    1    ASC

I feel that this compresses well because of the similar prefix columns in other indexes…

Index F0911_15 is rubbish:

CRPDTA    F0911_15    CRPDTA    F0911    GLDCT    1    4    2    ASC
CRPDTA    F0911_15    CRPDTA    F0911    GLDOC    2    22    0    ASC
CRPDTA    F0911_15    CRPDTA    F0911    GLKCO    3    10    5    ASC
CRPDTA    F0911_15    CRPDTA    F0911    GLDGJ    4    22    0    ASC
CRPDTA    F0911_15    CRPDTA    F0911    GLLT    5    4    2    ASC
CRPDTA    F0911_15    CRPDTA    F0911    GLEXTL    6    4    2    ASC
CRPDTA    F0911_15    CRPDTA    F0911    SYS_NC00142$    7    34    0    DESC

So, because I’m using standard index compression, the database is unable to find another index with similar prefix’s as index 15, which means that it cannot get any benefits out of the prefix compression.  Okay that is good to know.