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!