DBMS_COMPRESSION

The DBMS_COMPRESSION package provides an interface to facilitate choosing the correct compression level for an application.

The DBMS_COMPRESSSION package is defined with AUTHID CURRENT USER, so it executes with the privileges of the current user.

1. DBMS_COMPRESSION Constants

Table 34-1 DBMS_COMPRESSION Constants - Compression Types

Constant	Type	Value	Description
COMP_NOCOMPRESS	NUMBER	1	No compression
COMP_ADVANCED	NUMBER	2	Advanced row compression
COMP_QUERY_HIGH	NUMBER	4	High for query warehouse compression (Hybrid Columnar Compression)
COMP_QUERY_LOW	NUMBER	8	Low for query warehouse compression (Hybrid Columnar Compression)
COMP_ARCHIVE_HIGH	NUMBER	16	High archive compression (Hybrid Columnar Compression)
COMP_ARCHIVE_LOW	NUMBER	32	Low archive compression (Hybrid Columnar Compression)
COMP_BLOCK	NUMBER	64	Compressed block
COMP_LOB_HIGH	NUMBER	128	High compression level for LOB operations
COMP_LOB_MEDIUM	NUMBER	256	Medium compression level for LOB operations
COMP_LOB_LOW	NUMBER	512	Low compression level for LOB operations
COMP_INDEX_ADVANCED_HIGH	NUMBER	1024	High compression level for indexes
COMP_INDEX_ADVANCED_LOW	NUMBER	2048	Low compression level for indexes
COMP_RATIO_LOB_MINROWS	NUMBER	1000	Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated
COMP_BASIC	NUMBER	4096	Basic table compression
COMP_RATIO_LOB_MAXROWS	NUMBER	5000	Maximum number of LOBs used to compute the LOB compression ratio
COMP_INMEMORY_NOCOMPRESS	NUMBER	8192	In-Memory with no compression
COMP_INMEMORY_DML	NUMBER	16384	In-Memory compression level for DML
COMP_INMEMORY_QUERY_LOW	NUMBER	32768	In-Memory compression level optimized for query performance
COMP_INMEMORY_QUERY_HIGH	NUMBER	65536	In-Memory compression level optimized on query performance as well as space saving
COMP_INMEMORY_CAPACITY_LOW	NUMBER	131072	In-Memory low compression level optimizing for capacity
COMP_INMEMORY_CAPACITY_HIGH	NUMBER	262144	In-Memory high compression level optimizing for capacity
COMP_RATIO_MINROWS	NUMBER	1000000	Minimum required number of rows in the object for which HCC ratio is to be estimated
COMP_RATIO_ALLROWS	NUMBER	-1	To indicate the use of all the rows in the object to estimate HCC ratio
OBJTYPE_TABLE	PLS_INTEGER	1	Identifies the object whose compression ratio is estimated as of type table
OBJTYPE_INDEX	PLS_INTEGER	2	Identifies the object whose compression ratio is estimated as of type index

1. DBMS_COMPRESSION Data Structures

The DBMS_COMPRESSION package defines a RECORD type and a TABLE type.

2.1 COMPREC Record Type

The COMPREC record type is a record for calculating an individual index compression ratio on a table. Syntax：

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);

Field	Description
ownname	Schema of the object owner
objname	Name of the object
blkcnt_cmp	Number of blocks used by the compressed sample of the object
blkcnt_uncmp	Number of blocks used by the uncompressed sample of the object
row_cmp	Number of rows in a block in compressed sample of the object
row_uncmp	Number of rows in a block in uncompressed sample of the object
cmp_ratio	Compression ratio, blkcnt_uncmp divided by blkcnt_cmp
objtype	Type of the object

2.2 COMPRECLIST Table Type

COMPRECLIST is a table type of the COMPREC Record Type.

Syntax

TYPE compreclist IS TABLE OF comprec;

1. Summary of DBMS_COMPRESSION Subprograms

The DBMS_COMPRESSION package uses the GET_COMPRESSION_RATIO Procedure and GET_COMPRESSION_TYPE Function subprograms.

3.1 GET_COMPRESSION_RATIO Procedure

用于评估表或索引在压缩后的压缩比等信息，可以用于评估IN-MEMORY表压缩

This procedure analyzes the compression ratio of a table or an index, and gives information about compressibility of the object. Various parameters can be provided by the user to selectively analyze different compression types.

1. Get compression ratio for an object (table or index, default is table):

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (

   scratchtbsname        IN     VARCHAR2,

   ownname               IN     VARCHAR2,

   objname               IN     VARCHAR2,

   subobjname            IN     VARCHAR2,

   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);

Example:

set serveroutput on

declare

blkcnt_cmp NUMBER;

blkcnt_uncmp NUMBER;

row_cmp NUMBER;

row_uncmp NUMBER;

cmp_ratio NUMBER;

comptype_str VARCHAR2(32);

begin

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (

scratchtbsname=>'USERS',

ownname=>'SYS',  

objname=>'T1',

subobjname=>'T1_P1',

comptype=>2,

blkcnt_cmp=>blkcnt_cmp,

blkcnt_uncmp=>blkcnt_uncmp,

row_cmp=>row_cmp,

row_uncmp=>row_uncmp,

cmp_ratio=>cmp_ratio,

comptype_str=>comptype_str);

dbms_output.put_line('压缩类型: ' || comptype_str);

dbms_output.put_line('压缩后占用blocks: ' || blkcnt_cmp);

dbms_output.put_line('未压缩占用blocks: ' || blkcnt_uncmp);

dbms_output.put_line('压缩后单blocks包含行数: ' || row_cmp);

dbms_output.put_line('未压缩单blocks包含行数: ' || row_uncmp);

dbms_output.put_line('压缩率(压缩后占用blocks/未压缩占用blocks): ' || cmp_ratio);

end;

/

1. Get compression ratio for LOBs:

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);

1. Get compression ratio for all indexes on a table. The compression ratios are returned as a collection.

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (

  scratchtbsname        IN     VARCHAR2,

  ownname               IN     VARCHAR2,

  tabname               IN     VARCHAR2,

  comptype              IN     NUMBER,

  index_cr              OUT    DBMS_COMPRESSION.COMPRECLIST,

  comptype_str          OUT    VARCHAR2,

  subset_numrows        IN     NUMBER DEFAULT COMP_RATIO_INDEX_MINROWS);

Parameters

Parameter	Description
scratchtbsname	Temporary scratch tablespace that can be used for analysis 使用分析的表空间(非指临时空间)
ownname / tabowner	Schema of the table to analyze
tabname	Name of the table to analyze
objname	Name of the object
subobjname	Name of the partition or sub-partition of the object
comptype	Compression types for which analysis should be performed When the object is an index, only the following compression types are valid: COMP_INDEX_ADVANCED_HIGH (value 1024) and COMP_INDEX_ADVANCED_LOW (value 2048). Note: The following compression types cannot be specified in this parameter for any type of object: COMP_BLOCK (value 64) and COMP_BASIC (value 4096).
blkcnt_cmp	Number of blocks used by compressed sample of the table  压缩后表占用blocks
blkcnt_uncmp	Number of blocks used by uncompressed sample of the table 未压缩表占用blocks
row_cmp	Number of rows in a block in compressed sample of the table 压缩后一个block有多少行
row_uncmp	Number of rows in a block in uncompressed sample of the table 未压缩一个blocks有多少行
cmp_ratio	Compression ratio, blkcnt_uncmp divided by blkcnt_cmp
comptype_str	String describing the compression type
subset_numrows	Number of rows sampled to estimate compression ratio. 使用多少行作样本评估的
objtype	Type of the object, either OBJTYPE_TABLE or OBJTYPE_INDEX
lobname	Name of the LOB column
partname	In case of partitioned tables, the related partition name
lobcnt	Number of lobs actually sampled to estimate compression ratio
index_cr	List of indexes and their estimated compression ratios

Usage Notes:The procedure creates different tables in the scratch tablespace(用于评估的表空间) and runs analysis on these objects. It does not modify anything in the user-specified tables.

3.2 GET_COMPRESSION_TYPE Function

用于查看行是否被压缩及使用什么类型压缩

This function returns the compression type for a specified row. If the row is chained, the function returns the compression type of the head piece only, and does not examine the intermediate or the tail piece since head pieces can be differently compressed.

DBMS_COMPRESSION.GET_COMPRESSION_TYPE (

   ownname      IN    VARCHAR2,

   tabname      IN    VARCHAR2,

   row_id       IN    ROWID,

   subobjname   IN    VARCHAR2 DEFAULT NULL))

  RETURN NUMBER;

Return Values ：Flag to indicate the compression type (see Table 34-1).