http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_data.html
It looks suitable for report database /ODS.
According to the article,
The benefits are:
1. Half space saved
2. Faster speed as less block accessed
The overhead is double time required during data loading.
Details of testing list below
CodeListing 3: Comparing blocks in uncompressed and compressed tables
ANALYZE TABLE SALES_HISTORY COMPUTE STATISTICS;
ANALYZE TABLE SALES_HISTORY_COMP COMPUTE STATISTICS;
SELECT TABLE_NAME, BLOCKS, NUM_ROWS, COMPRESSION
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'SALES_HIST%';
TABLE_NAME BLOCKS NUM_ROWS COMPRESSION
------------------ ------ -------- -----------
SALES_HISTORY 12137 1000000 DISABLED
SALES_HISTORY_COMP 6188 1000000 ENABLED
codeLISTING 4: Comparing queries on uncompressed and compressed tables
TKPROF results of the query on the uncompressed table:
SELECT SALE_DATE, COUNT(*) FROM SALES_HISTORY GROUP BY SALE_DATE;
call count cpu elapsed disk query current rows
------- ------ ---- ------- ----- ---------- ---------- -----
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.22 13.76 10560 12148 0 1
------- ------ ---- ------- ----- ---------- ---------- -----
total 4 5.22 13.78 10560 12148 0 1
TKPROF results of the query on the compressed table:
SELECT SALE_DATE, COUNT(*) FROM SALES_HISTORY_COMP GROUP BY SALE_DATE;
call count cpu elapsed disk query current rows
------- ------ ---- ------- ----- ---------- ---------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.27 7.20 6082 6091 0 1
------- ------ ---- ------- ----- ---------- ---------- -----
total 4 5.27 7.20 6082 6091 0 1
