[20181127]12c Advanced Index Compression.txt

[20181127]12c Advanced Index Compression.txt --//12cR2 引入Advanced Index Compression,在11g之前,如果索引键值重复率很低,选择索引前缀压缩,反而适得起反, --//索引反而变大.而12c Advanced Index Compression能够很好控制改善这一状况,通过测试说明问题. --//测试参考链接,必要地方加入我的说明: https://richardfoote.wordpress.com/2014/10/02/index-compression-part-v-12c-advanced-index-compression-little-wonder/ 1.环境: SCOTT@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 SCOTT@test01p> create table bowie (id number, code number, name varchar2(30)); Table created. SCOTT@test01p> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000; 1000000 rows created. --//修改大部分数据导致出现重复值. SCOTT@test01p> update bowie set code = 42 where id between 250000 and 499999; 250000 rows updated. SCOTT@test01p> commit ; Commit complete. --//So I've fabricated the data such that the values in the CODE column are effectively unique within 75% of the table but --//the other 25% consists of repeated values. --//From an index compression perspective, this index really isn't a good candidate for normal compression as most of the --//CODE data contains unique data that doesn't compress. However, it's a shame that we can't easily just compress the 25% --//of the index that would benefit from compression (without using partitioning or some such). --//If we create a normal B-Tree index on the CODE column without compression: SCOTT@test01p> create index bowie_code_i on bowie(code); Index created. SCOTT@test01p> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE'; INDEX_NAME           LEAF_BLOCKS COMPRESSION -------------------- ----------- ------------- BOWIE_CODE_I                2157 DISABLED --//We notice the index consists of 2157 leaf blocks. --//If we now try to use normal compression on the index: SCOTT@test01p> alter index bowie_code_i rebuild compress; Index altered. SCOTT@test01p> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE'; INDEX_NAME           LEAF_BLOCKS COMPRESSION -------------------- ----------- ------------- BOWIE_CODE_I                2684 ENABLED --//你可以发现选择索引压缩反而索引占用空间增加. --//We notice that the compressed index rather than decrease in size has actually increased in size, up to 2684 leaf blocks. --//So the index has grown by some 25% due to the fact the index predominately contains unique values which don't compress --//at all and the resultant prefix section in the leaf blocks becomes nothing more than additional overhead. The 25% --//section of the index containing all the repeated values has indeed compressed effectively but these savings are more --//than offset by the increase in size associated with the other 75% of the index where the index entries had no --//duplication. --//However, if we use the new advanced index compression capability via the COMPRESS ADVANCED LOW clause: --//如果采用索引压缩呢? SCOTT@test01p> alter index bowie_code_i rebuild compress advanced low; Index altered. SCOTT@test01p> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE'; INDEX_NAME           LEAF_BLOCKS COMPRESSION -------------------- ----------- ------------- BOWIE_CODE_I                2054 ADVANCED LOW --//你可以发现选择Advanced Index Compression,可以获得好的索引大小. --//We notice the index has now indeed decreased in size from the original 2157 leaf blocks down to 2054. Oracle has --//effectively ignored all those leaf blocks where compression wasn't viable and compressed just the 25% of the index where --//compression was effective. Obviously, the larger the key values (remembering the rowids associated with the index --//entries can't be compressed) and the larger the percentage of repeated data, the larger the overall compression returns. --//With Advanced Index Compression, it's viable to simply set it on for all your B-Tree indexes and Oracle will uniquely --//compress automatically each individual index leaf block for each and every index as effectively as it can for the life --//of the index. --//补充测试看看: SCOTT@test01p> alter index bowie_code_i rebuild compress advanced high; Index altered. SCOTT@test01p> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE'; INDEX_NAME           LEAF_BLOCKS COMPRESSION -------------------- ----------- ------------- BOWIE_CODE_I                   0 ADVANCED HIGH --//奇怪什么显示LEAF_BLOCKS=0.选择compress advanced high;. SCOTT@test01p> select sum(bytes) from dba_extents where owner='SCOTT' and segment_name='BOWIE_CODE_I'; SUM(BYTES) ----------    7340032 SCOTT@test01p> select 7340032/8192 from dual ; 7340032/8192 ------------          896     --//压缩率更高.还原compress advanced low. SCOTT@test01p> alter index bowie_code_i rebuild compress advanced low; Index altered. SCOTT@test01p> select sum(bytes) from dba_extents where owner='SCOTT' and segment_name='BOWIE_CODE_I'; SUM(BYTES) ----------   17825792 SCOTT@test01p> select 17825792/8192 from dual ; 17825792/8192 -------------          2176 SCOTT@test01p> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE'; INDEX_NAME           LEAF_BLOCKS COMPRESSION -------------------- ----------- ------------- BOWIE_CODE_I                2054 ADVANCED LOW