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:
- Open a SQL*Plus session and connect to the SH schema:
CONNECT sh@TESTDB/sh
- Create a table to test:
CREATE TABLE BIG_CUSTOMERS AS SELECT * FROM CUSTOMERS;
- Insert more than 5 million records:
BEGIN FOR j IN 1..100 LOOP INSERT INTO BIG_CUSTOMERS SELECT * FROM CUSTOMERS; END LOOP; COMMIT; END;
- Create an index on the table:
CREATE INDEX IX1_BIG_CUSTOMERS ON BIG_CUSTOMERS ...
Get Oracle Database 11gR2 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.