Oracle | DBMS_COMPRESSION

Oracle DBMS_COMPRESSION Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This package supports advanced compression features new as of 11gR2 and the Oracle-Sun Exadata Server

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Compression Level Constants
COMP_NOCOMPRESS	NUMBER	1
COMP_ADVANCED	NUMBER	2
COMP_QUERY_HIGH - Exadata Only	NUMBER	4
COMP_QUERY_LOW - Exadata Only	NUMBER	8
COMP_ARCHIVE_HIGH - Exadata Only	NUMBER	16
COMP_ARCHIVE_LOW - Exadata Only	NUMBER	32
COMP_BLOCK - Pillar Axiom & Sun ZFSSA Storage Only	NUMBER	64
COMP_LOB_HIGH	NUMBER	128
COMP_LOB_MEDIUM	NUMBER	256
COMP_LOB_LOW	NUMBER	512
COMP_INDEX_ADVANCED_HIGH	NUMBER	1024
COMP_INDEX_ADVANCED_LOW	NUMBER	2048
COMP_BASIC	NUMBER	4096
COMP_INMEMORY_NOCOMPRESS	NUMBER	8192
COMP_INMEMORY_DML	NUMBER	16384
COMP_INMEMORY_QUERY_LOW	NUMBER	32768
COMP_INMEMORY_QUERY_HIGH	NUMBER	65536
COMP_INMEMORY_CAPACITY_LOW	NUMBER	131072
COMP_INMEMORY_CAPACITY_HIGH	NUMBER	262144
Compression Ratio Constants
COMP_RATIO_ALLROWS	NUMBER	-1
COMP_RATIO_LOB_MINROWS	NUMBER	1000
COMP_RATIO_LOB_MAXROWS	NUMBER	5000
COMP_RATIO_INDEX_MINROWS	NUMBER	100000
COMP_RATIO_MINROWS	NUMBER	1000000
Object Type Constants
OBJTYPE_TABLE	NUMBER	1
OBJTYPE_INDEX	NUMBER	2
OBJTYPE_PART	NUMBER	3
OBJTYPE_SUBPART	NUMBER	4

Data Types --record for calculating an individual index cr on a table
TYPE compRec IS RECORD( ownname VARCHAR2(255), objname VARCHAR2(255), blkcnt_cmp PLS_INTEGER, blkcnt_uncmp PLS_INTEGER, row_cmp PLS_INTEGER, row_uncmp PLS_INTEGER, cmp_ratio NUMBER, objtype PLS_INTEGER); TYPE compRecList IS TABLE OF compRec;

Dependencies

ALL_ALL_TABLES	DBMS_ADVISOR	PLITBLM
ALL_OBJECTS	DBMS_ASSERT	PRVT_COMPRESS
ALL_TAB_PARTITIONS	DBMS_OUTPUT	PRVT_COMPRESSION
ALL_TAB_SUBPARTITIONS	DBMS_PRIV_CAPTURE	SEG$
DBA_OBJECTS	DBMS_ROWID	WRI$_ADV_COMPRESSION_T
DBA_TABLES	DBMS_STANDARD	WRI$_ADV_OBJSPACE_TREND_T

Documented Yes: Packages and Types Reference

First Available 11.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmscomp.sql

Subprograms

CLEAR_ANALYSIS	GET_COMPRESSION_RATIO	INCREMENTAL_COMPRESS
DUMP_COMPRESSION_MAP	GET_COMPRESSION_TYPE

CLEAR_ANALYSIS

Undocumented dbms_compression.clear_analysis( ownname IN VARCHAR2, tabname IN VARCHAR2, comptype IN NUMBER DEFAULT 0);

exec dbms_compression.clear_analysis('UWCLASS', 'COMP_TEST2', 0);

DUMP_COMPRESSION_MAP

Undocumented but appears to be Exadata specific based on the acceptable comptype constants dbms_compression.dump_compression_map( ownname IN VARCHAR2, tabname IN VARCHAR2, comptype IN NUMBER);

CREATE TABLE comptab AS

      SELECT /* APPEND */ * FROM source$;

      

      exec dbms_compression.dump_compression_map('SYS', 'COMPTAB', dbms_compression.comp_query_high);

GET_COMPRESSION_RATIO

Gives the possible compression ratio for an uncompressed table or materialized view and estimates the compression that can be achieved

Overload 1 dbms_compression.get_compression_ratio( scratchtbsname IN VARCHAR2, ownname IN VARCHAR2, objname IN VARCHAR2, subobjname IN VARCHAR2, -- partition name comptype IN NUMBER, blkcnt_cmp OUT PLS_INTEGER, blkcnt_uncmp OUT PLS_INTEGER, row_cmp OUT PLS_INTEGER, row_uncmp OUT PLS_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2, subset_numrows IN NUMBER DEFAULT COMP_RATIO_MINROWS, objtype IN PLS_INTEGER DEFAULT OBJTYPE_TABLE);

conn sys@pdbdev as sysdba

      

      set serveroutput on

      

      DECLARE

       blkcnt_cmp   PLS_INTEGER;

       blkcnt_uncmp PLS_INTEGER;

       row_comp     PLS_INTEGER;

       row_uncmp    PLS_INTEGER;

       cmp_ratio    NUMBER;

       comptype     VARCHAR2(30);

      BEGIN

        dbms_compression.get_compression_ratio('SYSTEM', 'SYS', 'SOURCE$', NULL, dbms_compression.comp_advanced, blkcnt_cmp, blkcnt_uncmp, row_comp, row_uncmp, cmp_ratio, comptype);

      

        dbms_output.put_line('Block Count Compressed:   ' || TO_CHAR(blkcnt_cmp));

        dbms_output.put_line('Block Count UnCompressed: ' || TO_CHAR(blkcnt_uncmp));

        dbms_output.put_line('Row Count Compressed:     ' || TO_CHAR(row_comp));

        dbms_output.put_line('Row Count UnCompressed:   ' || TO_CHAR(row_uncmp));

        dbms_output.put_line('Block Count Compressed:   ' || TO_CHAR(cmp_ratio));

        dbms_output.put_line('Compression Type:         ' || comptype);

      END;

      /

Overload 2 dbms_compression.get_compression_ratio( scratchtbsname IN VARCHAR2, tabowner IN VARCHAR2, tabname IN VARCHAR2, lobname IN VARCHAR2, partname IN VARCHAR2, comptype IN NUMBER, blkcnt_cmp OUT PLS_INTEGER, blkcnt_uncmp OUT PLS_INTEGER, lobcnt OUT PLS_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2, subset_numrows IN number DEFAULT COMP_RATIO_LOB_MAXROWS);

TBD

Overload 3 dbms_compression.get_compression_ratio( scratchtbsname IN VARCHAR2, ownname IN VARCHAR2, tabname IN VARCHAR2, comptype IN NUMBER, index_cr OUT compRecList, comptype_str OUT VARCHAR2, subset_numrows IN NUMBER DEFAULT COMP_RATIO_MINROWS);

TBD

GET_COMPRESSION_TYPE

Inspects data and reports what compression type is in use for a specific row dbms_compression.get_compression_type( ownname IN VARCHAR2, tabname IN VARCHAR2, row_id IN ROWID, subobjname IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;

conn sys@pdbdev as sysdba

      

      CREATE TABLE  uwclass.comp_test2

      COMPRESS FOR OLTP AS

      SELECT * FROM dba_objects_ae;

      

      DECLARE

       rid ROWID;

       n   NUMBER;

      BEGIN

        SELECT MAX(rowid)

        INTO rid

        FROM uwclass.comp_test2;

      

        n := dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST2', rid);

        dbms_output.put_line(TO_CHAR(n));

      END;

      /

INCREMENTAL_COMPRESS

Allows turning compression of on and off and compressing uncompressed data offline, in the background)

This undocumented proc must be run as SYS and appears to have a bug.

Dump is an optional parameter that dumps out the space saved in each block into the trace files. It is turned OFF by default (set to 0). It is advised not to turn this feature on for large tables or partitions because of excessive logging. dbms_compression.incremental_compress( ownname IN dba_objects.owner%TYPE, tabname IN dba_objects.object_name%TYPE, tabpart IN dba_objects.subobject_name%TYPE, colname IN VARCHAR2, dump_on IN NUMBER DEFAULT 0, autocompress_on IN NUMBER DEFAULT 0, where_clause IN VARCHAR2 DEFAULT '');

conn sys@pdbdev as sysdba

      

      UPDATE uwclass.comp_test1

      SET namespace = 100;

      

      exec dbms_compression.incremental_compress('UWCLASS', 'COMP_TEST1', NULL, 'OBJECT_NAME', 1, 0, '''WHERE namespace=200''');

      

      INSERT INTO uwclass.comp_test1

      SELECT * FROM dba_objects_ae;

      

      UPDATE uwclass.comp_test1

      SET namespace = 200

      WHERE namespace <> 100;

      

      SELECT MAX(rowid) FROM uwclass.comp_test1 WHERE namespace = 100;

      SELECT MAX(rowid) FROM uwclass.comp_test1 WHERE namespace = 200;

      

      DECLARE

       rid100 ROWID;

       rid200 ROWID;

       n      NUMBER;

      BEGIN

        SELECT MAX(rowid)

        INTO rid100

        FROM uwclass.comp_test1

        WHERE namespace = 100;

      

        n := dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST1', rid100);

        dbms_output.put_line(n);

      

        SELECT MAX(rowid)

        INTO rid200

        FROM uwclass.comp_test1

        WHERE namespace = 200;

      

        n := dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST1', rid200);

        dbms_output.put_line(n);

      END;

      /

Related Queries

Identifying Compression Type conn sys@pdbdev as sysdba CREATE TABLE uwclass.comp_test2 COMPRESS FOR OLTP AS SELECT * FROM dba_objects_ae; SELECT COUNT(*) FROM comp_test2; COUNT(*) ---------- 92471 SELECT CASE comp_type WHEN 1 THEN 'No Compression' WHEN 2 THEN 'Advanced compression level' WHEN 4 THEN 'Hybrid Columnar Compression for Query High' WHEN 8 THEN 'Hybrid Columnar Compression for Query Low' WHEN 16 THEN 'Hybrid Columnar Compression for Archive High' WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low' WHEN 64 THEN 'Compressed row' WHEN 128 THEN 'High compression level for LOB operations' WHEN 256 THEN 'Medium compression level for LOB operations' WHEN 512 THEN 'Low compression level for LOB operations' WHEN 1000 THEN 'Minimum req. number of LOBs in the object for which LOB compression ratio is to be estimated' WHEN 4096 THEN 'Basic compression level' WHEN 8192 THEN 'Inmemory NoCompress' WHEN 16384 THEN 'Inmemory DML Compression' WHEN 32768 THEN 'Inmemory Query Low' WHEN 65536 THEN 'Inmemory Query High' WHEN 131072 THEN 'Inmemory Capacity Low' WHEN 262144 THEN 'Inmemory Capacity High' ELSE 'Unknown Compression Type' END AS comp_type, num_rows FROM ( SELECT dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST2', rid) AS comp_type, COUNT(*) NUM_ROWS FROM ( SELECT rowid AS rid FROM uwclass.comp_test2 WHERE rownum < 10001) GROUP BY dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST2', rid));