Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E16760-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

36 DBMS_COMPRESSION

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

See Also:

This chapter contains the following topics:


Using DBMS_COMPRESSION


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


Note:

The constants COMP_FOR_QUERY_HIGH, COMP_FOR_QUERY_LOW, COMP_FOR_ARCHIVE_HIGH, and COMP_FOR_ARCHIVE_LOW are used only in the context of Exadata, specifically the feature, Exadata Hybrid Columnar Compression (EHCC) which offers higher compression ratios for direct path loaded data. For more information, see the Oracle Exadata Storage Server Online Documentation Library.

Views

The DBMS_DST 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:

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_perblk_cmp        OUT   PLS_INTEGER, 
   row_perblk_uncmp      OUT   PLS_INTEGER, 
   cmp_ratio             OUT   NUMBER, 
   comptype_str          OUT   varchar2);

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_perblk_cmp

Number of rows in a block in compressed sample of the table

row_perblk_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


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