Tuesday, 31 October 2017

DBMS_COMPRESSION Enhancements in Oracle Database 12c

The DBMS_COMPRESSION package was introduced in Oracle 11gR2. Oracle Database 12c includes a number of enhancements to the DBMS_COMPRESSION package.

◉ Setup


The examples in this article use the table defined below.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id                NUMBER,
  code              VARCHAR2(20),
  description       VARCHAR2(50),
  clob_description  CLOB,
  created_date      DATE,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users);

CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;

INSERT INTO tab1
SELECT level,
       CASE
         WHEN MOD(level,2)=0 THEN 'CODE1'
         ELSE 'CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'Description for CODE1'
         ELSE 'Description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'CLOB description for CODE1'
         ELSE 'CLOB description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN TO_DATE('01/07/2015','DD/MM/YYYY')
         ELSE TO_DATE('01/07/2016','DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

◉ GET_COMPRESSION_RATIO


In addition to tables and partitions, the GET_COMPRESSION_RATIO procedure can now estimate the impact of different levels of compression on LOBs and indexes. It's also capable of estimating the impact of compression on the contents of the in-memory column store. The constants for the possible compression types, row sample sizes and object types are shown here.

The following example shows the effect of advanced compression on a table, using all rows in the table as a sample size.

SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST',
    objname         => 'TAB1',
    subobjname      => NULL,
    comptype        => DBMS_COMPRESSION.comp_advanced,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_allrows,
    objtype         => DBMS_COMPRESSION.objtype_table
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
Number of blocks used (compressed)       : 1325
Number of blocks used (uncompressed)     : 1753
Number of rows in a block (compressed)   : 74
Number of rows in a block (uncompressed) : 55
Compression ratio                        : 1.3
Compression type                         : "Compress Advanced"

PL/SQL procedure successfully completed.

SQL>

The following example shows the effect of low-level advanced compression on an index partition, using the minimum sample size.

SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST',
    objname         => 'TAB1_CODE_IDX',
    subobjname      => 'TAB1_PART_2015',
    comptype        => DBMS_COMPRESSION.comp_index_advanced_low,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_minrows,
    objtype         => DBMS_COMPRESSION.objtype_index
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
Number of blocks used (compressed)       : 78
Number of blocks used (uncompressed)     : 120
Number of rows in a block (compressed)   : 641
Number of rows in a block (uncompressed) : 417
Compression ratio                        : 1.5
Compression type                         : "Compress Advanced Low"

PL/SQL procedure successfully completed.

SQL>

It's also possible to test all indexes for a table in a single call. The procedure returns a collection of records holding the data for each index.

SET SERVEROUTPUT ON
DECLARE
  l_index_cr      DBMS_COMPRESSION.compreclist;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST',
    tabname         => 'TAB1',
    comptype        => DBMS_COMPRESSION.comp_index_advanced_low,
    index_cr        => l_index_cr,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_lob_maxrows
  );

  FOR i IN l_index_cr.FIRST .. l_index_cr.LAST LOOP
    DBMS_OUTPUT.put_line('----');
    DBMS_OUTPUT.put_line('ownname      : ' || l_index_cr(i).ownname);
    DBMS_OUTPUT.put_line('objname      : ' || l_index_cr(i).objname);
    DBMS_OUTPUT.put_line('blkcnt_cmp   : ' || l_index_cr(i).blkcnt_cmp);
    DBMS_OUTPUT.put_line('blkcnt_uncmp : ' || l_index_cr(i).blkcnt_uncmp);
    DBMS_OUTPUT.put_line('row_cmp      : ' || l_index_cr(i).row_cmp);
    DBMS_OUTPUT.put_line('row_uncmp    : ' || l_index_cr(i).row_uncmp);
    DBMS_OUTPUT.put_line('cmp_ratio    : ' || l_index_cr(i).cmp_ratio);
    DBMS_OUTPUT.put_line('objtype      : ' || l_index_cr(i).objtype);
  END LOOP;
END;
/
----
ownname      : TEST
objname      : TAB1_PK
blkcnt_cmp   : 223
blkcnt_uncmp : 223
row_cmp      : 448
row_uncmp    : 448
cmp_ratio    : 1
objtype      : 2
----
ownname      : TEST
objname      : TAB1_CODE_IDX
blkcnt_cmp   : 155
blkcnt_uncmp : 238
row_cmp      : 645
row_uncmp    : 420
cmp_ratio    : 1.5
objtype      : 2

PL/SQL procedure successfully completed.

SQL>

The following example shows the effect of compression on a CLOB in a table.

SET SERVEROUTPUT ON
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_lobcnt        PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    tabowner        => 'TEST',
    tabname         => 'TAB1',
    lobname         => 'CLOB_DESCRIPTION',
    partname        => NULL,
    comptype        => DBMS_COMPRESSION.comp_lob_high,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    lobcnt          => l_lobcnt,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_lob_maxrows
  );

  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_lobcnt);
  DBMS_OUTPUT.put_line('Number of lobs sampled                   : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
Number of blocks used (compressed)       : 67
Number of blocks used (uncompressed)     : 61
Number of rows in a block (compressed)   : 4927
Number of lobs sampled                   : .9
Compression type                         : "Compress High"

PL/SQL procedure successfully completed.

SQL>

◉ GET_COMPRESSION_TYPE


The GET_COMPRESSION_TYPE function now has an extra parameter to optionally specify a partition name to limit the scope of the function.

SELECT rowid,
       CASE DBMS_COMPRESSION.get_compression_type ('TEST', 'TAB1', rowid, 'TAB1_PART_2015')
         WHEN 1     THEN 'COMP_NOCOMPRESS'
         WHEN 2     THEN 'COMP_ADVANCED'
         WHEN 4     THEN 'COMP_QUERY_HIGH'
         WHEN 8     THEN 'COMP_QUERY_LOW'
         WHEN 16    THEN 'COMP_ARCHIVE_HIGH'
         WHEN 32    THEN 'COMP_ARCHIVE_LOW'
         WHEN 64    THEN 'COMP_BLOCK'
         WHEN 128   THEN 'COMP_LOB_HIGH'
         WHEN 256   THEN 'COMP_LOB_MEDIUM'
         WHEN 512   THEN 'COMP_LOB_LOW'
         WHEN 1024  THEN 'COMP_INDEX_ADVANCED_HIGH'
         WHEN 2048  THEN 'COMP_INDEX_ADVANCED_LOW'
         WHEN 1000  THEN 'COMP_RATIO_LOB_MINROWS'
         WHEN 4096  THEN 'COMP_BASIC'
         WHEN 5000  THEN 'COMP_RATIO_LOB_MAXROWS'
         WHEN 8192  THEN 'COMP_INMEMORY_NOCOMPRESS'
         WHEN 16384 THEN 'COMP_INMEMORY_DML'
         WHEN 32768 THEN 'COMP_INMEMORY_QUERY_LOW'
         WHEN 65536 THEN 'COMP_INMEMORY_QUERY_HIGH'
         WHEN 32768 THEN 'COMP_INMEMORY_CAPACITY_LOW'
         WHEN 65536 THEN 'COMP_INMEMORY_CAPACITY_HIGH'
       END AS compression_type
FROM   test.tab1 PARTITION (tab1_part_2015)
WHERE  rownum <= 5;

ROWID         COMPRESSION_TYPE
--------------  ---------------------
AAAX4aAAaAAABuSAAA COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAB COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAC COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAD COMP_NOCOMPRESS
AAAX4aAAaAAABuSAAE COMP_NOCOMPRESS

SQL>