Index Compression

On static Tables used for reporting, when an Index properly ordered, we can take advantage of Index Compression which will reduce Disc IO. To test this feature, I created a “regular” Index and then an Index with “COMPRESS 2” on FISCAL_YEAR and FISCAL_PERIOD. Results show a much smaller index (467MB -> 273MB) and even faster execution times. 

The number after the COMPRESS keyword denotes how many columns to compress. The default is all columns in a Non-Unique index and all columns except the last column in a Unique index.

With ‘Regular’ Index:

select count(*) from ODSMGR.OPERATING_LEDGER_MV

where fiscal_year='2012' and fiscal_period = '01'

--------------------------------------------------------------------------------------------

| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                        |     1 |   256 |    75   (4)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |   256 |            |          |

|*  2 |   INDEX RANGE SCAN| OPERATING_LEDGER_MV_03 | 85275 |    20M|    75   (4)| 00:00:01 |

--------------------------------------------------------------------------------------------

With Index Compress = 2:

--------------------------------------------------------------------------------------------

| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                        |     1 |   256 |    46   (7)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |   256 |            |          |

|*  2 |   INDEX RANGE SCAN| OPERATING_LEDGER_MV_03 | 85275 |    20M|    46   (7)| 00:00:01 |

--------------------------------------------------------------------------------------------

As W. Breitling recently pointed out on ORACLE-L, Index Compression is part of the basic database license - even SE - and is (unfortunately) a badly understood and rarely used feature. Test, test, test...