During my present Exadata course in Munich, we have discussed Hybrid Columnar Compression (HCC) and how to estimate the size of tables after compression. In addition to my previous posting that introduced HCC, I have therefore prepared this little script that calls DBMS_COMPRESSION. Typically, the documentation also gives an example, but not in this case. Also my brief research in the web did not retrieve a (simple) example.
Please notice that you can use that script already on a non-Exadata Oracle Database (from 11.2 on) also to get an estimation about HCC, not only about BASIC and OLTP compression. The estimation is quite good but takes much space – about as much as the tables you estimate are in size. Therefore, it may be advisable to use a scratch tablespace only for that purpose and drop it afterwards.
SQL> set serveroutput on declare v_blkcnt_cmp pls_integer; v_blkcnt_uncmp pls_integer; v_row_cmp pls_integer; v_row_uncmp pls_integer; v_cmp_ratio number; v_comptype_str varchar2(60); begin dbms_compression.get_compression_ratio( scratchtbsname => upper('&ScratchTBS'), ownname => user, tabname => upper('&TableName'), partname => NULL, comptype => dbms_compression.comp_for_query_high, blkcnt_cmp => v_blkcnt_cmp, blkcnt_uncmp => v_blkcnt_uncmp, row_cmp => v_row_cmp, row_uncmp => v_row_uncmp, cmp_ratio => v_cmp_ratio, comptype_str => v_comptype_str); dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio)); dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp)); dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp)); end; /
Addendum: See Jonathan Lewis’ nice cheat to keep the HCC compressed table that gets created during the evaluation under the covers – even if you are NOT running DBMS_COMPRESSION on Exadata!
Second addendum: This posting was published in the current Oracle University Newsletter.