About Oracle 11gR2 Advanced Compression

Oracle 11g introduces a new feature called Advanced Compression that offers organizations the promise of tables that take up less space, therefore, equaling a smaller database. A smaller database taking up less disk space also equals a lower cost for disk storage for databases. With database sizes continuing to grow at an alarming rate, the ability to increase the amount of data stored per gigabyte is exciting. There is also the potential performance benefits from large read operations like full table scans, where Oracle would need to read fewer physical blocks to complete a full table scan, as well as the potential buffer cache memory savings by allowing more data to be stored in the SGA with the compressed blocks.

Oracle first introduced compression in 8i with index key compression, and then in 9i Oracle added compression for tables. Oracle 9i table compression was limited as compression could be used only upon creation via operations like CREATE TABLE AS SELECT, direct loads, or INSERT with APPEND. This compression was well suited for initial loads of data, but over time the table had to be reorganized to recompress, which required maintenance and downtime to maintain compression over time. With pressure to increase availability of database table, compression was not well suited for normal OLTP systems since most data was not direct loaded. Oracle’s introduction of Advanced Compression changes that and allows a table to maintain compression as data is updated and inserted into a table, as shown in the CREATE TABLE:

 

 

 

 

 

 

 

Consider the following Advanced Compression settings:

• NOCOMPRESS The table or partition is not compressed. This is the default action.
• COMPRESS Suitable for data warehouses. Compression enabled during direct-path inserts only.
• COMPRESS FOR DIRECT_LOAD OPERATIONS Same effect as the simple COMPRESS.
• COMPRESS FOR ALL OPERATIONS Suitable for OLTP systems. Compression for all operations, including regular DML statements. Requires COMPATIBLE to be set to 11.1.0 or higher.
• COMPRESS FOR OLTP Suitable for OLTP systems. Enables compression for OLTP operations, including regular DML statements. Requires COMPATIBLE to be set to 11.1.0 or higher and, in 11.2, replaces the COMPRESS FOR ALL OPERATIONS syntax, but COMPRESS FOR ALL OPERATIONS syntax still exists and is still valid.