index key compression 索引压缩对性能的影响

使用体验：

Oracle允许指定压缩列，全压 或者advanced compress。使用压缩的目的是减少IO的消耗。所以在明确创建索引列， 已经索引顺序后可以比较不同压缩方式被检索的数据块数。 从而找到最优的索引。索引列中如果很少有重复值，就没有必要使用压缩

例子：

-- when table has hug rows , by using parallel option is better to create index in short time.

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,key_4) PARALLEL 24     TABLESPACE  ts_INDX ;

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,key_4) PARALLEL 24   compress 1  TABLESPACE  ts_INDX ;

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,Key_4) PARALLEL 24   compress 2 TABLESPACE  ts_INDX ;

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,key_4) PARALLEL 24   compress  3 TABLESPACE  ts_INDX ;

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,key_4) PARALLEL 24   compress  TABLESPACE  ts_INDX ;

exec dbms_stats.gather_index_stats(ownname => 'USER_NAME',indname => 'index_name' ,estimate_percent => 100 ,degree => 24) ; 
ALTER INDEX user_name.index_name NOPARALLEL; 

根据具体需求比较那种压缩更少的COST and execution time

select BYTES/1024/1024 mb, t.* from  DBA_segments  t where segment_name='index_name'  ;

select index_name, index_type, leaf_blocks, compression, t.* from dba_indexes t where index_name in('index_name')
and OWNER='user_name';
 

12c new feature: advanced high takes more times when creating index, but it has higher compression, which means hige compression make the index has smaller size and less leavef blocks. IO cost will be the loest one. the defects are maitaining time longer if the table doesn't have frequent DML, it will be good. 

compress advanced low: oracle use low cpu cost to get  the compression rate.

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,key_4) PARALLEL 24   compress advanced low  TABLESPACE  ts_INDX ;

CREATE INDEX index_name ON table_name (key_1,key_2,key_3,key_4) PARALLEL 24   compress advanced high  TABLESPACE  ts_INDX ;