Database Software

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