11g New Features: Advanced Compression Schema Level Storage Savings
-Richard
Posts: 1 Newbie
Comments
-
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
0 -
Thanks for sharing it. Much useful.0