11g New Features: Advanced Compression Schema Level Storage Savings

-Richard

LajosV-Oracle

Thanks.
An example output e.g. run on the SH (Sales History demo) gives the following:

SH@dw23> select avg(cmp_ratio) "Avg Schema Compression Ratio" from compression_ratio_values
  2  /

Avg Schema Compression Ratio
----------------------------
                     1.85625

                     
SH@dw23> WITH
  2  X AS
  3  (
  4  select sum((seg.bytes/1024/1024)/comp.cmp_ratio) post
  5  from user_segments seg, compression_ratio_values comp
  6  where
  7  seg.segment_type = 'TABLE'
  8  and
  9  comp.table_name = seg.segment_name
 10  ),
 11  Y AS
 12  (
 13  select sum(seg.bytes)/1024/1024 MB from user_segments seg, compression_ratio_values comp
 14  where
 15  seg.segment_type = 'TABLE'
 16  and
 17  comp.table_name = seg.segment_name
 18  )
 19  SELECT Y.MB "Tables Before Compression(MB)", X.post "Tables After Compression(MB)"
 20  FROM X,Y
 21  /

Tables Before Compression(MB) Tables After Compression(MB)
----------------------------- ----------------------------
                       281.75                   115.673806


The column/expression alias on the select list of the last query appears to be too long: ""Schema Tables Before Compression(MB)"", so i reduced it to avoid error.

Thanks,
Lajos

Nip-Oracle

Thanks for sharing it. Much useful.