8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_COMPRESSION Enhancements in Oracle Database 12c Release 1 (12.1.0.2)
The DBMS_COMPRESSION
package was introduced in Oracle 11gR2, as described here. Oracle Database 12c includes a number of enhancements to the DBMS_COMPRESSION
package.
Related articles.
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>
For more information see:
Hope this helps. Regards Tim...