What's New in oracle12c Index Compression COMPRESS ADVANCED LOW

Starting with oracle 12.1.0.2, index can be compressed through COMPRESS ADVANCED LOW when creating an index

Grammar

Create index index_name on table_name (col_name) COMPRESS ADVANCED LOW

Alter index index_name REBUILD COMPRESS ADVANCED LOW

Compressed space contrast

Create table ddeng as select * from dba_objects Create index idx_ddeng on ddeng (object_id,object_name,owner);-- temporary space size SQL > select segment_name,bytes/1024/1024 from dba_segments where segment_name = 'IDX_DDENG' SEGMENT_NAME BYTES/1024/1024--IDX_DDENG 80 muri-compressed SQL > alter index idx_ddeng REBUILD COMPRESS ADVANCED LOW;Index altered.SQL > select segment_name,bytes/1024/1024 from dba_segments where segment_name = 'IDX_DDENG' SEGMENT_NAME BYTES/1024/1024--IDX_DDENG 22

Enable COMPRESS ADVANCED LOW when the dba_indexes.compression value is displayed as Enable

Note:

Advanced index compression is not supported for bitmap indexes or index-organized tables.

Advanced index compression cannot be specified on a single column unique index.