Working on 11g, release 1 (11.1.0.7), managers wanted to know what kind of compression factor we achieved in the datawarehouse.

Amount of (compressed) data: just about 1,5 TB, excluding indexes.

In all my naivity I thought Oracle delivered the package ‘DBMS_COMPRESSION’ which will show me the rate of achieved compression per table. But  in 11gr1 there was no package like ‘DBMS_COMPRESSION’, this is only delivered with release 2.

So I downloaded the DBMS_COMP_ADVISOR from :

http://www.oracle.com/technology/products/database/compression/download.html

Second disappointment appears after reading the readme-file:

DBMS_COMP_ADVISOR:

This package can be used on Oracle Database 9i Release 2 through 11g Release 1 to estimate the compression ratio for a table and to determine the space saving that can be potentially realized using the Oracle Database 11g OLTP Table Compression feature (part of the Advanced Compression option). Compression ratio is defined as the ratio of table size before and after compression.

–> Discoverd that’s not what I want, not before the compression, but afterwards!

The following example made it clear. This is the ratio what Oracle expect to achieve, and run it on an uncompressed table:
SQL> set serveroutput on
SQL> exec dbms_compression.getratio(‘SH’,'SALES’,'OLTP’,10)
Sampling table: SH.SALES
Sampling percentage: 10%
Compression Type: OLTP
Expected Compression ratio with Advanced Compression Option: 2.96
PL/SQL procedure successfully completed.

O.k., then get it the old-fashioned way, comparing a compressed and uncompressed table.

Used an uncompressed tablespace, created an uncompressed table, imported some data.
Then determine how much space it used:

select sum(bytes)/1024/1024/1024 “Size in GB”
from dba_segments
where owner = ‘JOB_DWH’
and segment_name=’DWH_TABLE’
/
Size in GB
———-
14.1601563

Just to check how many rows:
SQL> select count(*) from job_dwh.dwh_table;
COUNT(*)
———-
60596597

Then compressing the table:
SQL> alter table job_dwh.dwh_table move compress;

select sum(bytes)/1024/1024/1024 “Size in GB”
from dba_segments
where owner = ‘JOB_DWH’
and segment_name=’DWH_TABLE’
/
Size in GB
———-
4.8828125

Compression-factor of 2,9 .