Script to find out space saving with HCC

The following scrips loops through a list of tables and displays estimated compression ratio for all the tables for all types of HCC compression :


set serveroutput on
spool all.out
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);
type Str_Array is VARRAY(10) of VARCHAR2(50);
v_array Str_Array ;
begin
v_array:= Str_Array('lineitem','nation');
for i in v_array.first .. v_array.last loop
        dbms_output.put_line('hello ' || v_array(i));
end loop;
for i in v_array.first .. v_array.last
loop
for j in 1..5
loop
dbms_compression.get_compression_ratio(
scratchtbsname => upper('scratch1'),
ownname => upper('tpch'),
tabname => upper(v_array(i)),
partname => NULL,
comptype => power(2,j),
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,
subset_numrows => -1) ;
dbms_output.put_line('OUTPUT FOR ' || v_comptype_str ||' FOR TABLE ' || v_array(i)) ;
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 LOOP;
end LOOP;
end;
/
;