Advanced Index Compression

轉載請註明出處..........http://blog.itpub.net/28612416/viewspace-1448066/

1.1 Creating an Index Using Advanced Index Compression

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.

高階索引壓縮可以有效的降低唯一性和非唯一性索引所佔用的空間。高階索引壓縮技術提供了索引的高壓縮率同時增強了索引的訪問效率。因此，高階索引壓縮在支援的索引上能夠很好的工作。

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

For a partitioned index, you can specify the compression type on a partition by partition basis. You can also specify advanced index compression on index partitions even when the parent index is not compressed.

對於分割槽索引，透過分割槽基礎可以指定壓縮型別在一個分割槽上。可以指定高階索引壓縮在分割槽索引上甚至在父索引未壓縮的情況下。

Advanced index compression works at the block level to provide the best compression for each block. When a CREATE INDEX DDL statement is executed, a block is filled with rows. When it is full, it is compressed with advanced index compression if enough space is saved to insert the next row. When DML statements or other types of DDL statements are executed, and a block becomes full and is about to be split, the block might be recompressed using advanced index compression to avoid the split if enough space is saved to insert the incoming key.

高階索引壓縮提供了塊級別的最好壓縮。當執行建立索引語句時，塊透過行來填充。當塊填滿後，若還有足夠的空間用於儲存下一行的資料insert時，就使用高階索引壓縮。當在執行dml或者ddl時一個塊被填滿並且將要分離時，若空間足夠儲存下一個insert的鍵位的話，這個塊會透過高階索引壓縮再次壓縮，從而避免了split。

Before enabling advanced index compression, the database must be at 12.1.0 or higher compatibility level. You enable advanced index compression using the COMPRESS ADVANCED LOW clause. For example, the following statement enables advanced index compression during the creation of thehr.emp_mndp_ix index:

You can also specify the COMPRESS ADVANCED LOW clause during an index rebuild. For example, during rebuild, you can enable advanced index compression for the hr.emp_manager_ix index as follows:

測試案例：

Create user pdba identified by pdba;

Grant dba to pdba;

Create table test_idx as select * from dba_objects;

Insert into test_idx select * from test_idx;

SQL> select count(*) from test_idx;

  COUNT(*)

----------

  23516416

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> set autot trace exp

SQL> set timing on

create index OBJECT_ID_idx on test_idx(OBJECT_ID);

SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 FROM USER_SEGMENTS;

SEGMENT_NAME                    SEGMENT_TYPE      BYTES/1024/1024

------------------------------ ---------------------------------------------------------------------------------------

TEST_IDX                             TABLE                               3138

OBJECT_ID_IDX                       INDEX                              415

CREATE INDEX OBJECT_ID_IDX ON TEST_IDX(OBJECT_ID) COMPRESS ADVANCED LOW;

SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 FROM USER_SEGMENTS;

SEGMENT_NAME                    SEGMENT_TYPE    BYTES/1024/1024

------------------------------ ------------------ ----------------------------------------------------------

TEST_IDX                              TABLE                             3138

OBJECT_ID_IDX                       INDEX                              288

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.02

SQL> set autot trace exp

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K

NEXT 20k) INVISIBLE;

配合invisible索引技術，在同一個欄位上可以建立多個索引，只有visible的索引才能被執行計劃使用，同一個欄位一次只能有一個索引處於visible狀態。

目前根據索引狀態共有三種索引情況：

1、  visible狀態

2、  invisible狀態

3、  unusable狀態

常見索引修改語句：

1、  ALTER INDEX emp_ename STORAGE (NEXT 40);

3、  ALTER INDEX emp_name REBUILD;