DBMS compression: determining a table’s compression ratio

According to the Oracle documentation, the dbms_compression procedure allows to determine the compression ratio for a table without creating it. Let’s have a look at it.

We choose a table with a lot of records and an estimated size of 27 Mo:

SQL> select count(*) from test1; 
161068
 
SQL> select segment_name, bytes/1024/1024 from user_segments
2 where segment_name = 'TEST1'; 
SEGMENT_NAME BYTES/1024/1024 
       TEST1              27

Let’s see if the dbms_compression.get_ratio is correct for the Oracle procedure. We use a script named get_ratio.sql which calculates the compression ratio:

declare
comp_ratio number;
comptype varchar2(300);
blockcount_comp number;
blockcount_nocomp number;
row_comp number;
row_nocomp number;
begin
dbms_compression.get_compression_ratio(
scratchtbsname =>'USERS', -- tablespace
ownname =>'PSI', -- owner
tabname =>'TEST1', -- table name
partname =>null, -- partition name
comptype =>2, -- compression type 2 = OLTP
blkcnt_cmp =>blockcount_comp, -- number of blocks compressed
blkcnt_uncmp =>blockcount_nocomp, -- number of blocks non compressed
row_cmp =>row_comp, -- number of rows compressed
row_uncmp =>row_nocomp, -- number of rows non compressed
cmp_ratio =>comp_ratio, -- compression ratio
comptype_str =>comptype); -- compression type
dbms_output.put_line('Compression Ratio :'||comp_ratio);
dbms_output.put_line('Block Count :'||blockcount_comp);
dbms_output.put_line('Compression Type :'||comptype);
dbms_output.put_line('Blocks Compressed :'||blockcount_comp);
dbms_output.put_line('BlocksUncompressed:'||blockcount_nocomp);
dbms_output.put_line('Rows Compressed :'||row_comp);
dbms_output.put_line('Rowis Uncompressed:'||row_nocomp);end;
/

SQL> @get_ratio
Compression Ratio :5.3
Block Count :361
Compression Type :"Compress For OLTP"
Blocks Compressed :361
Blocks Uncompressed:1922
Rows Compressed :263
Rowis Uncompressed:49

When we analyze the results, this is what we get: Blocks Compressed 361, Blocks Uncompressed 1922. This means we should have a gain of 80 % (361 /1922 = 0,18).

We create a compressed tablespace:

SQL> create tablespace users_compress datafile '/u01/database/DB112/users_compress.dbf'size 100M reuse AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO default compress for all operations;

We create the TEST1_COMPRESS table in the compressed tablespace:

SQL> create table test1_comp tablespace users_compress2 as select * from test1;

We now calculate the effective sizes of the TEST1% tables:

SQL> select segment_name,bytes/1024/1024 from user_segments
2 where segment_name like 'TEST1%';
SEGMENT_NAME    BYTES/1024/1024
       TEST1                 27
  TEST1_COMP                  5

Obviously, we have the same ratio, this procedure seems to work perfectly!