How does Oracle enhance index compression

This article mainly introduces how Oracle enhances index compression. In daily operations, I believe that many people have doubts about how Oracle enhances index compression. The editor consulted various materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer your doubts about how Oracle can enhance index compression! Next, please follow the editor to learn together!

Advanced index compression works well with all supported indexes. Creating indexes with Advanced Index Compression reduces the size of all unique and non-unique indexes and dramatically improves compression ratios, taking up less space while still efficiently accessing the indexes.

For partitioned indexes, the compression type can be specified on a partition basis. You can also specify advanced index compression on an index partition, even if the parent index is not compressed.

Advanced index compression works at the block level, providing better compression for each block.

1 specific example

SQL>conn oe/oracle@orclpdb; Connected. SQL> create table test_a(id number,name varchar2(20)); Table created.

1.1 Create low-level advanced index compression

SQL> create index pk_id on test_a(id) compress advanced low; Index created.

1.2 View index compression level

SQL> col index_name for a20 SQL> select INDEX_NAME, COMPRESSION from dba_indexes where index_name='PK_ID'; INDEX_NAME COMPRESSION -------------------- -------------------------- PK_ID ADVANCED LOW

1.3 Enable high-level advanced index compression by rebuilding the index

SQL> alter index pk_id rebuild compress advanced high; Index altered. 

 SQL> select INDEX_NAME, COMPRESSION from dba_indexes where index_name='PK_ID'; INDEX_NAME COMPRESSION -------------------- -------------------------- PK_ID ADVANCED HIGH

1.4 Explanation

• Low Level Advanced Index Compression: This level provides lower compression ratios with minimal CPU overhead. The database must be at compatibility level 12.1.0 or higher before COMPRESS ADVANCED LOW is enabled.

• High Level Advanced Index Compression: This level, by default, provides higher compression ratios at some CPU heavy overhead. The database must be at compatibility level 12.2.0 or higher before COMPRESS ADVANCED HIGH is enabled.

Note:

• Advanced index compression does not support bitmap indexes or index-organized tables
  

• When low-level advanced index compression is enabled, advanced cannot be specified on a single column unique index Index compression. This limitation does not apply when the high level of advanced index compression is enabled.