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;
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.