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:

CREATE INDEX hr.emp_mndp_ix ON hr.employees(manager_id, department_id)

   COMPRESS ADVANCED LOW;

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:

ALTER INDEX hr.emp_manager_ix REBUILD COMPRESS ADVANCED LOW;

测试案例：

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);

2、  ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;

3、  ALTER INDEX emp_name REBUILD;

4、  ALTER INDEX emp_name REBUILD ONLINE;

5、  ALTER INDEX emp_email_uk UNUSABLE;

6、  ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;

7、  ALTER INDEX index INVISIBLE;

8、  ALTER INDEX index VISIBLE;

9、  ALTER INDEX index_name RENAME TO new_name;

10、ALTER INDEX index MONITORING USAGE;

11、ALTER INDEX index NOMONITORING USAGE;

12、DROP INDEX emp_ename;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28612416/viewspace-1448066/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28612416/viewspace-1448066/