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...