Oracle Compression feature summary 2-effect of estimating table compression

Before using compression, we can estimate how much the effect of using compression could be.

11GR2 can use dbms_comp_advisor before, the specific code is already given 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. No longer described in detail.

Sql> set Serveroutput onsql> execdbms_comp_advisor.getratio (' SH ', ' SALES ', ten) 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 replace the dbms_comp_advisor to provide services.

[email protected]> 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 INP Rocedure incremental_compress ArgumentName Type in/out Default?            -------------------------------------------------------------------Ownname VARCHAR2 (30) In TabName VARCHAR2 (+) in PartName VARCHAR2 (+) in ColName VARCHAR2 in dump_on number in DE                FAULT autocompress_on number in DEFAULT where_clause VARCHAR2  In DEFAULT

Focus on Get_compression_ratio This stored procedure, which can estimate the compression scale of the table.
You can use the following anonymous block 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 (+): = ' &user '; TableName VARCHAR2 (+): = ' &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 effect:

/enter value for User:dexterold  8:   username varchar2 (+): = ' &user '; new  8:   username varchar2 (30): = ' DEXTER '; Enter value for Tb:accountold  9:   tablename VARCHAR2 (+): = ' &TB '; new  9:   tablename VARCHAR2 (30): = ' Account '; Enter value for Usedtbs:usersold one:   dbms_compression.get_compression_ratio (' &usedtbs ', New:  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, estimated compression ratio:1aboveindicates that there are no compression effects.
Advanced compression, based on intra-block compression. So if there is a duplicate value, but not in a block, then advanced compression will not work.

Here we highlight a parameter comptype, which has 6 options, namely

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;

Query High follows the content of HCC (Hybridcolumnar Compression) because it is related to exadata storage nodes, so it is not available in a non-exadata all-in-one machine environment. But interestingly, you can use Get_compression_ratio to estimate the proportions of compression in a normal environment.

11GR2 previously compression-advisor stored procedures:

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