SQL scripts for index compression in Oracle, along with sample outputs

advertisements

_____________________________________________________________________________________________________________________

1. Identify Indexes Using Index Key Compression 

This script identifies indexes using index key compression. It queries the all_indexes data dictionary view to find indexes that have compression enabled (compression = 'ENABLED'). The compress_for column provides information about the compression level, such as "QUERY HIGH," "QUERY LOW," or "ARCHIVE HIGH."

-- Query to identify indexes using index key compression
SELECT index_name, table_name, compression, compress_for
FROM all_indexes
WHERE compression = 'ENABLED';

INDEX_NAME      TABLE_NAME      COMPRESSION   COMPRESS_FOR
-------------   -------------   ------------  --------------
EMPLOYEES_IDX   EMPLOYEES       ENABLED       QUERY HIGH
ORDERS_IDX      ORDERS          ENABLED       QUERY LOW

advertisements

 

2. Estimate the Compression Ratio and Space Savings for an Index 

This script estimates the compression ratio and space savings for indexes using index key compression. It queries the all_ind_compression data dictionary view, which contains statistics about the compression of index segments.

-- Query to estimate the compression ratio and space savings for an index
SELECT index_name, table_name,
       (1 - (AVG_LEAF_BLOCKS / AVG_LEAF_BLOCKS_COMPRESSED)) * 100 AS compression_ratio,
       (AVG_SPACE / AVG_SPACE_COMPRESSED) * 100 AS space_savings
FROM all_ind_compression;

INDEX_NAME      TABLE_NAME      COMPRESSION_RATIO  SPACE_SAVINGS
-------------   -------------   -----------------  -------------
EMPLOYEES_IDX   EMPLOYEES       40.23              61.85
ORDERS_IDX      ORDERS          25.67              82.14

3. Alter an Index and Enable or Disable Index Key Compression 

 These scripts alter an index to enable or disable index key compression. Replace 'YOUR_INDEX_NAME' with the name of the index you want to modify. Sample Output (No output is generated for these statements): The ALTER INDEX ... ENABLE COMPRESSION statement enables index key compression on the specified index. The ALTER INDEX ... DISABLE COMPRESSION statement disables index key compression on the specified index.

 

-- Query to alter an index and enable index key compression
ALTER INDEX YOUR_INDEX_NAME ENABLE COMPRESSION;

-- Query to alter an index and disable index key compression
ALTER INDEX YOUR_INDEX_NAME DISABLE COMPRESSION;

_____________________________________________________________________________________________________________________

Website Stats