Oracle9i New Feature Series: Block Level Data Compression

Block level compression is a new feature in Oracle9i Release 2, which is very useful in many data warehouses and read only large tables. Table compression can reduce storage space requirement (drastically some times) and may make the queries run faster (need to read only fewer blocks of data).

You may enable compression on a table at the time of table creation or by altering the table. Remember the existing data in the table is not compressed on uncompressed when you do the “alter”.

CREATE TABLE MYTABLE (

COL1 VARCHAR2 (20),

COL2 DATE)

TABLESPACE MYTABLESPACE

NOLOGGING

COMPRESS

PCTFREE 0;

ALTER TABLE MYTABLE COMPRESS;

The data compression is transparent to the user. You run queries against the table the same way you use to do before. Oracle compresses data blocks only when the data is loaded in direct path. The statements could be

Rows loaded using conventional insert statements are not compressed.

Compression is suitable for large tables, where the updates/deletes are close to none. If there are updates/deletes, you may end up using more space – to update, Oracle has to uncompress the row, and insert it again; row deleted will free up some space which may not be sufficient for the next inserted row, because conventional inserts are not compressed, direct load inserts always load above the HWM.

You can either compress the table, or selectively on partitions. It may be a good idea to compress the older data on a partitioned table. To do this, you have to perform a

ALTER TABLE MYPARTTABLE MOVE PARTITION JAN04 TABLESPACE COMP_DATA COMPRESS PCTFREE 0;

After the partition move, you may also have to do:

ALTER TABLE MYPARTTABLE 

MODIFY PARTITION JAN04 REBUILD UNUSABLE LOCAL INDEXES;

Another place to use compression is when you create materialized views, because most of the MVs are read only. If the MV already exist, you may do

ALTER MATERIALIZED VIEW MYMV COMPRESS;

The data will be compressed when the materialized view is refreshed.

Restrictions:

The dictionary views DBA_TABLES, DBA_TAB_PARTITIONS have a column named COMPRESSION, which will be either DISABLED or ENABLED.