Not getting expected compression ratios

rbalila-Oracle

Hi All,

I did a simple experiment ...

1. Created a new table based on the hr.employees table.

SQL> create table employees2 as select * from hr.employees;

2. Inserted the same data multiple times (24 times), so I should have a table where the same rows are repeated 24 times.

SQL> insert into employees2 select * from hr.employees

SQL> select count(*) from hr.employees;

COUNT(*)

107

SQL> select count(*) from employees2;

COUNT(*)

1068288


3. Created the compressed table (11gR1)

SQL> create table employees2_ac compress for all operations as select * from employees2;

SQL> select count(*) from employees2_ac;

COUNT(*)

1068288


4. Compared space used for the 2 tables

SQL> select segment_name, bytes
2 from dba_segments
3 where segment_name like 'EMPLOYEES2%'
4 /

SEGMENT_NAME BYTES

EMPLOYEES2 92274688
EMPLOYEES2_AC 75497472

5. That amounts to 1.2:1 compression ratio. I thought it would be orders of magnitude more than that.

Does this make sense?

I went further and proceeded to use the Oracle Advanced Compression Advisor (on both compressed and uncompressed tables) and got the following puzzling results.

Uncompressed Table

SQL> exec dbms_comp_advisor.getratio('SYS','EMPLOYEES2','OLTP',10)
Sampling table: SYS.EMPLOYEES2
Sampling percentage: 50%
Compression Type: OLTP
Estimated Compression Ratio: *1.65*

Compressed Table

SQL> exec dbms_comp_advisor.getratio('SYS','EMPLOYEES2_AC','OLTP',10)
Sampling table: SYS.EMPLOYEES2_AC
Sampling percentage: 50%
Compression Type: OLTP
Estimated Compression Ratio: *1.65*

So first the compression ratio don't match the actual actual numbers and second why is it that you can get a compression ratio for a compressed table?

Edited by: rbalila on Apr 6, 2011 1:00 PM

Edited by: rbalila on Apr 6, 2011 1:17 PM

GokhanAtil

So first the compression ratio don't match the actual actual numbers

As I see, it used half of the table (sample pct=50%). Although I aggress that it should give a better estimation, after all it's just an estimation and you can't get the estimated ratio all the time.

second why is it that you can get a compression ratio for a compressed table?

I think it just evaluates the compression ratio based on the raw data. It doesn't care if it's already compressed. It reads sample data (uncompresses them while reading), then tries to compress them and calculate the ratio.