This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Advanced Index Compression is and how its used.
Note: Advanced Index Compression is part of the Advanced Compression option. Make sure you have the appropriate Oracle Database Enterprise Edition license with the Advanced Compression option before implementing this feature.
The Storage Challenge
Database indexes can be "storage hungry". Indexes can consume more space than the actual data tables. While you need indexes to improve performance, their storage overhead can really grow quickly. This is where Advanced Index Compression comes in.
What Makes Advanced Index Compression Special?
Advanced Index Compression is remarkably simple to use. It determines the best compression strategy for each index block, meaning you get great compression without the complexity.
The white paper says "Average storage savings can range from 2x to 5x depending on which compression level is implemented." You can check out the white paper for yourself here: Oracle Index Key Compression and Advanced Index Compression
Compression Levels
Advanced Index Compression offers two levels:
LOW: Automatically determines the best prefix compression for each block
CREATE INDEX sales_idx ON sales(region, date) COMPRESS ADVANCED LOW;Perfect for most use cases
Automatically optimizes compression for each block
Minimal performance impact
Typical space savings: 2x-3x
HIGH: Uses compression algorithms for the best compression:
CREATE INDEX sales_idx ON sales(region, date) COMPRESS ADVANCED HIGH;Maximum compression
Best for storage-critical environments
Still maintains good performance
Typical space savings: 3x-5x
Oracle Advanced Index Compression: A Real-World Example
Let's look at an example using a customer support database. We'll create a table that follows common patterns in support ticket data, with fields like status, priority, region, and categories. (This is also a good example of how real-world data can have repeating patterns which are great for compression).
Our example will:
Create a support tickets table with common support ticket fields
Populate it with realistic data patterns (like most tickets being 'Closed' and distributed across regions)
Create a typical index that support teams would use for searching tickets
Look at the potential compression savings
Apply different compression techniques to compare results
-- Create a support tickets table with common patterns
CREATE TABLE support_tickets (
ticket_id NUMBER,
status VARCHAR2(20),
priority VARCHAR2(10),
customer_region VARCHAR2(50),
category VARCHAR2(30),
subcategory VARCHAR2(30),
created_date DATE,
last_updated DATE
);
-- Populate with realistic data patterns
INSERT INTO support_tickets
WITH data AS (
SELECT
LEVEL as ticket_id,
-- Most tickets end up 'Closed'
CASE
WHEN MOD(LEVEL, 10) <= 7 THEN 'Closed'
WHEN MOD(LEVEL, 10) = 8 THEN 'Open'
ELSE 'In Progress'
END as status,
-- Typical priority distribution
CASE
WHEN MOD(LEVEL, 100) = 1 THEN 'Critical'
WHEN MOD(LEVEL, 10) = 1 THEN 'High'
WHEN MOD(LEVEL, 3) = 0 THEN 'Low'
ELSE 'Medium'
END as priority,
-- Common regions
CASE MOD(LEVEL, 5)
WHEN 0 THEN 'North America'
WHEN 1 THEN 'Europe'
WHEN 2 THEN 'Asia Pacific'
WHEN 3 THEN 'Latin America'
ELSE 'Middle East'
END as customer_region,
-- Common issue categories
CASE MOD(LEVEL, 3)
WHEN 0 THEN 'Software'
WHEN 1 THEN 'Hardware'
ELSE 'Network'
END as category,
-- Various subcategories
CASE MOD(LEVEL, 6)
WHEN 0 THEN 'Installation'
WHEN 1 THEN 'Configuration'
WHEN 2 THEN 'Performance'
WHEN 3 THEN 'Security'
WHEN 4 THEN 'Connectivity'
ELSE 'Other'
END as subcategory,
SYSDATE - (MOD(LEVEL, 730)) as created_date,
SYSDATE - (MOD(LEVEL, 730)/2) as last_updated
FROM dual
CONNECT BY LEVEL <= 1000000
)
SELECT * FROM data;
-- Create an index that support teams would commonly use
CREATE INDEX tickets_search_idx ON support_tickets(
status, priority, customer_region,
category, subcategory, created_date
);Let's check potential compression savings using Oracle's free Compression Advisor:
SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
scratchtbsname => 'USERS',
ownname => 'BOB',
objname => 'TICKETS_SEARCH_IDX',
subobjname => NULL,
comptype => DBMS_COMPRESSION.COMP_INDEX_ADVANCED_LOW,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
objtype => DBMS_COMPRESSION.OBJTYPE_INDEX
);
-- Output the analysis results
DBMS_OUTPUT.PUT_LINE('Compression Type: ' || l_comptype_str);
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
DBMS_OUTPUT.PUT_LINE('Estimated Compression Ratio: ' || l_cmp_ratio || 'x');
DBMS_OUTPUT.PUT_LINE('Estimated Space Savings: ' ||
ROUND((1 - (1/l_cmp_ratio)) * 100, 1) || '%');
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
DBMS_OUTPUT.PUT_LINE('Blocks used (compressed): ' || l_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blocks used (uncompressed): ' || l_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Rows per block (compressed): ' || l_row_cmp);
DBMS_OUTPUT.PUT_LINE('Rows per block (uncompressed): ' || l_row_uncmp);
END;
/
Let's understand what these Compression Advisor results mean:
The 'Compression Type' shows which compression method was analyzed
The 'Compression Ratio' (like 5.7x) means the data would take up about one-sixth of its original space
'Estimated Space Savings' shows the percentage reduction in storage - for example, 82.5% means you'd use 82.5% less space
The block counts show you exactly how many database blocks would be needed before and after compression
The rows per block metrics help you understand how many more rows can fit in each block after compression
Now let's create and compress our index:
-- Check current size
SELECT bytes/1024/1024 as size_mb
FROM user_segments
WHERE segment_name = 'TICKETS_SEARCH_IDX';
-- Try regular compression
ALTER INDEX tickets_search_idx REBUILD COMPRESS 3;
-- Check compressed size
SELECT bytes/1024/1024 as size_mb
FROM user_segments
WHERE segment_name = 'TICKETS_SEARCH_IDX';
-- Now try Advanced Index Compression
ALTER INDEX tickets_search_idx REBUILD COMPRESS ADVANCED LOW;
-- Check new size
SELECT bytes/1024/1024 as size_mb
FROM user_segments
WHERE segment_name = 'TICKETS_SEARCH_IDX';
Looks like the Compression Advisor crushed it
Limitations to Keep in Mind
While Advanced Index Compression is a nice feature, it's not meant fore every use case. Here are some scenarios where it can't be used:
Bitmap Indexes: Advanced Index Compression is not supported for bitmap indexes. If you're using bitmap indexes for data warehouse queries, you'll need to explore other options.
Functional Indexes: If you've created indexes on expressions or functions (like UPPER(last_name)), Advanced Index Compression cannot be applied to these functional indexes.
Single Column Unique Indexes: While technically supported, these typically won't benefit much from compression since there's no redundant data to compress.
High-Update Scenarios: While still supported, consider that extremely write-intensive indexes might see more overhead from compression operations. Use the Compression Advisor to look at the tradeoffs in your specific case.
Additional Information
Until next time...
-Killian
Comments