Skip to Main Content

Database Software

Not getting expected compression ratios

rbalila-OracleApr 6 2011 — edited Apr 14 2011
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;



SQL> select count(*) from employees2;



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;



4. Compared space used for the 2 tables

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


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
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2011
Added on Apr 6 2011
1 comment