Infrastructure at your Service

Pierre Sicot

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; 
SQL> select segment_name, bytes/1024/1024 from user_segments
2 where segment_name = 'TEST1'; 
       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:

comp_ratio number;
comptype varchar2(300);
blockcount_comp number;
blockcount_nocomp number;
row_comp number;
row_nocomp number;
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('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%';
       TEST1                 27
  TEST1_COMP                  5

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



  • Randhir Kumar says:

    I tried to get compression ratio for indexes in 12c’s failing with the below error.

    2 blkcnt_cmp pls_integer;

    3 blkcnt_uncmp pls_integer;

    4 row_cmp pls_integer;

    5 row_uncmp pls_integer;

    6 cmp_ratio pls_integer;

    7 comptype_str varchar2(100);

    8 BEGIN


    10 (

    11 scratchtbsname => ‘TBSP_INDEX’,

    12 ownname => ‘SCOTT’,

    13 objname => ‘NAME_CMP1_IX’,

    14 subobjname => NULL,

    15 comptype => dbms_compression.COMP_INDEX_ADVANCED_LOW,

    16 blkcnt_cmp => blkcnt_cmp,

    17 blkcnt_uncmp => blkcnt_uncmp,

    18 row_cmp => row_cmp,

    19 row_uncmp => row_uncmp,

    20 cmp_ratio => cmp_ratio,

    21 comptype_str => comptype_str,

    22 subset_numrows => dbms_compression.COMP_RATIO_MINROWS,

    23 objtype => dbms_compression.OBJTYPE_INDEX

    24 );

    25 END ;

    26 /



    ERROR at line 1:

    ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

    ORA-06512: at “SYS.PRVT_COMPRESSION”, line 2199

    ORA-06512: at “SYS.PRVT_COMPRESSION”, line 243

    ORA-06512: at “SYS.DBMS_COMPRESSION”, line 218

    ORA-06512: at line 9

    Any suggestion

  • pierre sicot says:


    In oracle 12c the dbms_compression.get_compression_ratio has changed, you should use :
    scratchtbsname =>’USERS’, — tablespace
    ownname =>’PSI’, — owner
    objname =>’TEST1′, — table name
    subobjname =>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(‘Blocks Uncompressed:’||blockcount_nocomp);
    dbms_output.put_line(‘Rows Compressed :’||row_comp);
    dbms_output.put_line(‘Rowis Uncompressed:’||row_nocomp);

    The changed fields are tabname –> objname, and partname–> subobjname


    Pierre SICOT


Leave a Reply

four − 2 =

Pierre Sicot
Pierre Sicot

Senior Consultant