36 DBMS_COMPRESSION

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

Using DBMS_COMPRESSION

Overview
Security Model
Constants
Views

Overview

The DBMS_COMPRESSION package gathers compression-related information within a database environment. This includes tools for estimating compressibility of a table for both partitioned and non-partitioned tables, and gathering row-level compression information on previously compressed tables. This gives the user with adequate information to make compression-related decision.

Security Model

The DBMS_COMPRESSSION package is owned by user SYS, and must be created by SYS. The execution privilege is granted to PUBLIC. Subprograms in this package are executed using the privileges of the current user.

Constants

The DBMS_COMPRESSION package uses the constants shown in Table 36-1, "DBMS_COMPRESSION Constants - Compression Types"e:

Table 36-1 DBMS_COMPRESSION Constants - Compression Types

Constant	Type	Value	Description
`COMP_NOCOMPRESS`	`NUMBER`	`1`	No compression
`COMP_FOR_OLTP`	`NUMBER`	`2`	OLTP compression
`COMP_FOR_QUERY_HIGH`	`NUMBER`	`4`	High compression level for query operations
`COMP_FOR_QUERY_LOW`	`NUMBER`	`8`	Low compression level for query operations
`COMP_FOR_ARCHIVE_HIGH`	`NUMBER`	`16`	High compression level for archive operations
`COMP_FOR_ARCHIVE_LOW`	`NUMBER`	`32`	Low compression level for archive operations
`COMP_BLOCK`	`NUMBER`	`64`	Compression
`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

Note:

Hybrid columnar compression is a feature of certain Oracle storage systems. See Oracle Database Concepts for more information.

Views

The DBMS_COMPRESSION package uses views described in the Oracle Database Reference. The twenty catalog views that contain a COMPRESS_FOR or DEF_COMPRESS_FOR will have a list of valid displayed values to be one of the following:

BASIC
OLTP
QUERY LOW
QUERY HIGH
ARCHIVE LOW
ARCHIVE HIGH

The affected views are:

Summary of DBMS_COMPRESSION Subprograms

Table 36-2 DBMS_COMPRESSION Package Subprograms

Subprogram	Description
GET_COMPRESSION_RATIO Procedure	Analyzes the compression ratio of a table, and gives information about compressibility of a table
GET_COMPRESSION_TYPE Function	Returns the compression type for a specified row

GET_COMPRESSION_RATIO Procedure

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

Syntax

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
   scratchtbsname        IN     VARCHAR2,
   ownname               IN     VARCHAR2,
   tabname               IN     VARCHAR2,
   partname              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);

Parameters

Table 36-3 GET_COMPRESSION_RATIO Procedure Parameters

Parameter	Description
`scratchtbsname`	Temporary scratch tablespace that can be used for analysis
`ownname`	Schema of the table to analyze
`tabname`	Name of the table to analyze
`partname`	In case of partitioned tables, the related partition name
`comptype`	Compression types for which analysis should be performed
`blkcnt_cmp`	Number of blocks used by compressed sample of the table
`blkcnt_uncmp`	Number of blocks used by uncompressed sample of the table
`row_cmp`	Number of rows in a block in compressed sample of the table
`row_uncmp`	Number of rows in a block in uncompressed sample of the table
`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.

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.

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.

Syntax

DBMS_COMPRESSION.GET_COMPRESSION_TYPE (
   ownname    IN    VARCHAR2, 
   tabname    IN    VARCHAR2, 
   row_id     IN    ROWID)
  RETURN NUMBER;

Parameters

Table 36-4 GET_COMPRESSION_TYPE Function Parameters

Parameter	Description
`ownname`	Schema name of the table
`tabname`	Name of table
`rowid`	Rowid of the row

Return Values

Flag to indicate the compression type (see DBMS_COMPRESSION Constants - Compression Types).