36 DBMS_COMPRESSION

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:

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:

• BASIC

• OLTP

• QUERY LOW

• QUERY HIGH

• ARCHIVE LOW

• ARCHIVE LOW

The affected views are:

• ALL_ALL_TABLES

• ALL_OBJECT_TABLES

• ALL_PART_TABLES

• ALL_TABLES

• ALL_TAB_PARTITIONS

• ALL_TAB_SUBPARTITIONS

• DBA_ALL_TABLES

• DBA_OBJECT_TABLES

• DBA_PART_TABLES

• DBA_TABLES

• DBA_TAB_PARTITIONS

• DBA_TAB_SUBPARTITIONS

• DBA_TABLESPACES

• USER_OBJECT_TABLES

• USER_PART_TABLES

• USER_TABLES

• USER_TAB_PARTITIONS

• USER_TABLESPACES

Summary of DBMS_COMPRESSION Subprograms

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.

The overload takes an index name and an optional partition name as IN arguments, and for every possible type of compression algorithm (currently Auto-Prefix or OLTP), it return the compression ratio as an OUT argument. In addition, it returns a count of the compressed blocks and uncompressed blocks which can be used to determine absolute space consumption of the index (partition) with or without compression.

Syntax

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

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
   scratchtbsname        IN    VARCHAR2,
   indexname             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,
   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_LOB_MAXROWS);

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
   scratchtbsname        IN    VARCHAR2,
   indexname             IN    VARCHAR2,
   tabowner              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

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

Return Values

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