Data Loads into Oracle Compressed Tables

Oracle introduced table compression feature in Oracle 9i to help reduce the disk space utilization in databases. Datawarehousing and decision support systems have been evolving over years and have become the biggest hogs of disk space. Managing disk capacity has become a challenge for the dba as well as the infrastructure team. In todays world terabyte systems have become very common and require large volumes of data to be stored in various large tables. The compression of data is transparent to users and applications and the access to data continues the same way it has already been developed. Recap of how Oracle Table Compression Works The compression algorithm of oracle works wonders by eliminating the duplicate occurences of the data in an individual data block. Space savings are achieved by eliminating the duplicate content within the block and using a symbol to abbreviate the data being stored. When compression is enabled on a table oracle creates a "symbol table" in each block. These symbols are used to compress the data that is being inserted.  When data is inserted, oracle stores a pointer from the symbol table instead of the actual data that is being inserted.  You can create a new compressed table using the compress keyword at the end of the create table statement or you can also convert an existing table to a compressed format by issuing "Alter table <table_name> compress;" command. If you are creating a new database, its best to set this attribute at the tablespace level. ALTER TABLE ODT_ORDER_LINES COMPRESS; CREATE TABLE TEST ( ACOL VARCHAR2(10)) COMPRESS; Data Load into Oracle Compressed Tables When data is loaded into oracle tables that are compressed, the dba or developer must use specific techniques to load or append the data. Otherwise the data will be loaded in uncompressed form. I have identified the following 2 methods to achieve compression during data loads. 1. Direct Path SQL*Loader : This method is most preferred by the database administrators for data loads from flat files. SQL*Loader has built in transformation and error handling processes making it the most reliable data loading tool for oracle. To perform a direct load in sql loader, the command should include the direct=true statement. Please check oracle documentation on SQL*Loader to understand the restriction on the direct load. 2. INSERT with APPEND hint or PARALLEL INSERT : If the data is already loaded into temporary or staging areas, using the insert command with an append hint or running the insert in parallel with parallel dml enabled would accomplish this job for you Compressing an Existing Oracle Table Issuing an "Alter table <table_name> compress" only enables the new data being inserted to be compressed.  To compress an existing table, the dba can take 2 different approaches. 1. ALTER TABLE MOVE COMPRESS : This method is most preferred since there is no intermediate copy that has to be managed by the dba. All constraints, triggers or other dependent objects stay the same but the indexes are marked as unusable. To mark the indexes as usable, the dba would have to rebuild the indexes 2. CREATE TABLE AS SELECT : This method allows for creation of a new compressed table without the indexes or constraints and the dba will have to apply the constraints indexes and other code to the newly compressed table. If you don't use the correct loading or INSERT method, the data in the table will remain uncompressed, even if the table is defined using COMPRESS. For example, if you use conventional path SQL*Loader or regular INSERT statements, data will not be compressed.