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”.
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
After the partition move, you may also have to do:
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
The data will be compressed when the materialized view is refreshed.
The dictionary views DBA_TABLES, DBA_TAB_PARTITIONS have a column named COMPRESSION, which will be either DISABLED or ENABLED.