Advanced index compression works at the block level to provide the best compression for each block. The database uses the following technique:

• During index creation, as a leaf block becomes full, the database automatically compresses the block to the optimal level.
• When reorganizing an index block as a result of DML, if the database can create sufficient space for the incoming index entry, then a block split does not occur. During DML without advanced index compression, however, an index block split always occurs when the block becomes full.

Advanced Index Compression HIGH

managed it services

In releases previous to Oracle Database 12c Release 2 (12.2), the only form of advanced index compression was low compression (COMPRESS ADVANCED LOW). Now you can also specify high compression (COMPRESS ADVANCED HIGH), which is the default. Advanced index compression with the HIGH option offers the following advantages:

mobile app development

• Gives higher compression ratios in most cases, while also improving performance for queries that access the index
• Employs more complex compression algorithms than advanced low
  quote
• Stores data in a compression unit, which is a special on-disk format

Note

When you apply HIGH compression, all blocks have compression. When you apply LOW compression, the database may leave some blocks uncompressed. You can use statistics to determine how many blocks were left uncompressed.

Example 3-5 Creating an Index with Advanced High Compression

This example enables advanced index compression for an index on the hr.employees table:

CopyCREATE INDEX hr.emp_mndp_ix
  ON hr.employees(manager_id, department_id)
  COMPRESS ADVANCED;

The following query shows the type of compression:

CopySELECT COMPRESSION FROM DBA_INDEXES WHERE INDEX_NAME ='EMP_MNDP_IX';

COMPRESSION
-------------
ADVANCED HIGH

school management