Oracle compression function Summary 2-estimate the effect of table Compression

Before using compression, we can estimate the effect of compression.

Dbms_comp_advisor can be used before 11gr2. The specific code is provided in the attachment. You only need to execute two files, dbmscomp. SQL and prvtcomp. plb, and then use the DBMS_COMP_ADVISOR.getratio stored procedure. It is not described in detail.

SQL> set serveroutput onSQL> execdbms_comp_advisor.getratio('SH','SALES',10)Sampling table: SH.SALESSampling percentage: 10%Estimated compression ratio for the advancedcompression option is : 2.96

After 11gr2, the system will bring a dbms_compression package to provide services in place of dbms_comp_advisor.

_sys@FAKE> desc dbms_compressionPROCEDURE GET_COMPRESSION_RATIO ArgumentName                  Type                    In/Out Default? ----------------------------------------------------- ------ -------- SCRATCHTBSNAME                 VARCHAR2                IN OWNNAME                        VARCHAR2                IN TABNAME                        VARCHAR2                IN PARTNAME                       VARCHAR2                IN COMPTYPE                       NUMBER                  IN BLKCNT_CMP                     BINARY_INTEGER          OUT BLKCNT_UNCMP                   BINARY_INTEGER          OUT ROW_CMP                        BINARY_INTEGER          OUT ROW_UNCMP                      BINARY_INTEGER          OUT CMP_RATIO                      NUMBER                  OUT COMPTYPE_STR                   VARCHAR2                OUT SUBSET_NUMROWS                 NUMBER                  IN     DEFAULTFUNCTION GET_COMPRESSION_TYPE RETURNS NUMBER ArgumentName                  Type                    In/Out Default? ----------------------------------------------------- ------ -------- OWNNAME                        VARCHAR2                IN TABNAME                        VARCHAR2                IN ROW_ID                         ROWID                   INPROCEDURE INCREMENTAL_COMPRESS ArgumentName                  Type                    In/Out Default? ----------------------------------------------------- ------ -------- OWNNAME                        VARCHAR2(30)            IN TABNAME                        VARCHAR2(128)           IN PARTNAME                       VARCHAR2(30)            IN COLNAME                        VARCHAR2                IN DUMP_ON                        NUMBER                  IN     DEFAULT AUTOCOMPRESS_ON                NUMBER                  IN     DEFAULT WHERE_CLAUSE                   VARCHAR2                IN     DEFAULT 

Focus on the Stored Procedure GET_COMPRESSION_RATIO, which can estimate the compression ratio of the table.
You can use the following anonymous blocks for execution.

DECLARE blkcnt_comp PLS_INTEGER; blkcnt_uncm PLS_INTEGER; row_comp    PLS_INTEGER; row_uncm    PLS_INTEGER; comp_ratio  number; comp_type   VARCHAR2(30); username varchar2(30) := '&USER'; tablename varchar2(30) := '&TB' ;BEGIN dbms_compression.get_compression_ratio('&Usedtbs',                                         username,                                        tablename,                                         NULL,                                        dbms_compression.COMP_FOR_OLTP,                                        blkcnt_comp,                                         blkcnt_uncm,                                        row_comp,                                        row_uncm,                                        comp_ratio,                                        comp_type); dbms_output.put_line('Sampling table: '||username||'.'||tablename); dbms_output.put_line('Estimated compression ratio: ' ||TO_CHAR(comp_ratio)); dbms_output.put_line('Compression Type: ' || comp_type);END;/

Execution result:

 /Enter value for user: DEXTERold  8:   username varchar2(30) :='&USER';new  8:   username varchar2(30) :='DEXTER';Enter value for tb: ACCOUNTold  9:   tablename varchar2(30) :='&TB' ;new  9:   tablename varchar2(30) :='ACCOUNT' ;Enter value for usedtbs: USERSold 11:   dbms_compression.get_compression_ratio('&Usedtbs',new 11:  dbms_compression.get_compression_ratio('USERS',Sampling table: DEXTER.ACCOUNTEstimated compression ratio: 1Compression Type: "Compress For OLTP" PL/SQL procedure successfully completed.

Because there are very few duplicate values in the table,In the previous article, Estimated compression ratio: 1, indicating that there is no compression effect.
Advanced compression, based on intra-block compression. So even if there is a duplicate value but it is not in a block, the advanced compression still cannot work.

Here we will focus on a parameter COMPTYPE, which has six options:

COMP_NOCOMPRESS               CONSTANT NUMBER := 1;COMP_FOR_OLTP                 CONSTANT NUMBER := 2;COMP_FOR_QUERY_HIGH           CONSTANT NUMBER := 4;COMP_FOR_QUERY_LOW            CONSTANT NUMBER := 8;COMP_FOR_ARCHIVE_HIGH         CONSTANT NUMBER := 16;COMP_FOR_ARCHIVE_LOW          CONSTANT NUMBER := 32;

The following are the contents of HybridColumnar Compression in Query high. Because it is related to the storage node of Exadata, it cannot be used in a non-Exadata all-in-one environment. However, you can use get_compression_ratio in a common environment to estimate the compression ratio.

Compression-advisor stored procedure before 11gr2:

Http://download.csdn.net/detail/renfengjun/7514723