Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 122

Compressing indexes

In this recipe, we will see another option we can use during index creation or rebuild—the
COMPRESS parameter—and how it could affect the performance when using the index.

We will use the same table and index created in the previous recipe, Index Rebuilding .

How to do it…

If you have dropped the table, you have to recreate it as mentioned in the following steps:

1. Open a SQL*Plus session and connect to the SH schema:

CONNECT sh@TESTDB/sh

2. Create a table to test:

CREATE TABLE BIG_CUSTOMERS AS SELECT * FROM CUSTOMERS;

3. Insert more than 5 million records:

BEGIN
FOR j IN 1..100 LOOP
INSERT INTO BIG_CUSTOMERS SELECT * FROM CUSTOMERS;
END LOOP;
COMMIT;
END;

4. Create an index on the table:

CREATE INDEX IX1_BIG_CUSTOMERS
ON BIG_CUSTOMERS (CUST_LAST_NAME, CUST_FIRST_NAME);

5. Analyze the index to gather statistics:

ANALYZE INDEX IX1_BIG_CUSTOMERS VALIDATE STRUCTURE;

6. Inspect statistics on the index:

SELECT HEIGHT, BLOCKS, BTREE_SPACE, USED_SPACE,
OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE FROM INDEX_STATS
WHERE NAME = ‹IX1_BIG_CUSTOMERS›;

7. Rebuild the index with the COMPRESS option, following the optimal parameter from

the previous query ( OPT_CMPR_COUNT ):
ALTER INDEX IX1_BIG_CUSTOMERS REBUILD ONLINE COMPRESS 2;

8. Analyze the index to refresh statistics:

ANALYZE INDEX IX1_BIG_CUSTOMERS VALIDATE STRUCTURE;