* HOWTO: Index compression in Oracle database

Since Oracle 8i, index compression feature is provided by default. Suppose that you have a table like below:

GENDER CHAR(1)
BIRTHDATE DATE
NAME VARCHAR2(20)

And assume that you want to make a index on GENDER+BIRTHDATE. That being the case, it is manifest that there are lots of duplicating GENDER+BIRTHDATE. So, it is reasonable to save unique GENDER+BIRTHDATE only once, and save their locations upon the unique key.

To do this, execute index creation command as follows:

CREATE INDEX GENDER_BIRTHDATE_IDX ON TBL_NAME(GENDER, BIRTHDATE)
COMPRESS 2

COMPRESS 2 means that preceding two columns should be saved only once even if there are duplicting GENDER+BIRTHDATE.

COMPRESS option is useful for stroage saving. Enjoy this new feature.