Database Software

All,

I'm using 11.2.0.1 (11g R2) and have a series of large partitioned tables which I want to compare non-compressed sizes vs compressed sizes.

I first determined the sizes of all of the tables in non-compressed form.

I then created new tablespaces with clause "DEFAULT COMPRESS FOR OLTP;" included as in the following example:

CREATE SMALLFILE
TABLESPACE "EIA_SPIDERCLKSTRM_INDEXES_COMP"
LOGGING
DATAFILE '/u03/app/oracle/oradata/ORADB1/EIA_SPIDERCLKSTRM_INDEXES_COMP_01.dbf' SIZE 300M AUTOEXTEND
ON NEXT 50M MAXSIZE 500M EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP;


I then altered each partition of the non-compressed table to move it to the new COMPRESS FOR OLTP tablespace.

I've now re-calculated the compression ratio and I'm seeing roughly 15-20% reduction in space usage.

I'm wondering though, if I look in the DBA_TABLES view, the COMPRESS_FOR column is still NULL. This kind of makes sense to me since I never compressed the table but I'm wondering if there is some other way I can tell that the table partitions are compressed properly (beyond comparing sizes before and after). Or is what I have done the best way to check that the table partitions are compressed properly?

Also, I'm wondering if 15-20% is average compression results for folks or if I should expect a higher compression ratio?

I'm just trying to verify that I'm doing everything right.

Thanks,

John