[20181127]12c Advanced Index Compression.txt

[20181127]12c Advanced Index Compression.txt

--//12cR2 引入Advanced Index Compression,在11g之前,如果索引鍵值重複率很低,選擇索引字首壓縮,反而適得起反,

--//索引反而變大.而12c Advanced Index Compression能夠很好控制改善這一狀況,透過測試說明問題.

--//測試參考連結,必要地方加入我的說明:

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