Index compression

From 8i onwards, you can now have index compression which you can read about here. Whilst in almost all circumstances, index compression yields better performance, determining the optimal index compression is non-trivial. The following utility takes a brute force approach by taking each index and applying the various compression levels and reporting the compression effectiveness of each.

Needless to say, this will grind away for some time and care should be taken when it is used.

Notes for use:

1. p_owner = the owner of indexes to be tested
2. p_leaf_threshold = only indexes with at least this many leaf blocks will be processed
3. p_skip_analyze = whether to skip to initial analyze command to determine the size of indexes
4. p_percent = sample size for analyze commands used throughout execution
5. p_skip_suspected_synthetic = whether to skip testing for indexes which are suspected to be synthetic keys (namely single column unique indexes with high ratio of distinct keys)
6. p_sort_size = whether to alter the session's sort size before starting
7. Set a large serveroutput before running
8. At the completion of the assessment, indexes tested will be left in a compressed state
9. You need to appropriate ANALYZE and ALTER privileges to run the tool

create or replace
package COMPRESSION_TEST is

  procedure assessment(p_owner varchar2, 
                       p_leaf_threshold number default 1000, 
					   p_skip_analyze varchar2 default 'N',
					   p_percent number default null,
					   p_skip_suspected_synthetic varchar2 default 'Y',
					   p_sort_size number default null) ;
end;
/

create or replace
package body COMPRESSION_TEST is

procedure premlinary_analyze(p_owner varchar2, p_percent number default null) is
begin
  for i in ( select 'analyze table '||owner||'.'||table_name||' estimate statistics'||
                    decode(p_percent,null,null,' sample '||p_percent||' percent')||' for table' ddl 
             from dba_tables 
             where owner = upper(p_owner)) loop
    execute immediate i.ddl;
  end loop;

  for i in ( select 'analyze index '||p_owner||'.'||index_name||' estimate statistics'||
                    decode(p_percent,null,null,' sample '||p_percent||' percent') ddl 
             from dba_indexes
             where owner = upper(p_owner)) loop
    execute immediate i.ddl;
  end loop;
end;

procedure assessment(p_owner varchar2, 
                       p_leaf_threshold number default 1000, 
					   p_skip_analyze varchar2 default 'N',
					   p_percent number default null,
					   p_skip_suspected_synthetic varchar2 default 'Y',
					   p_sort_size number default null) is
  cursor index_list is					   
    select di.index_name, max(dic.column_position)-decode(di.uniqueness,'UNIQUE',1,0) col_pos, 
	       dt.num_rows, di.distinct_keys, di.leaf_blocks 
    from   dba_indexes di,
           dba_ind_columns dic,
   	       dba_tables dt
    where  di.owner = upper(p_owner)
    and    di.leaf_blocks > p_leaf_threshold
    and    di.compression = 'DISABLED'
    and    dic.index_owner = di.owner
    and    dic.index_name = di.index_name
    and    dt.owner = di.table_owner
    and    dt.table_name = di.table_name
    group by di.index_name, di.uniqueness, dt.num_rows, di.distinct_keys, di.leaf_blocks
	having (     upper(p_skip_suspected_synthetic) = 'Y' 
	         and (   max(dic.column_position) > 1
			      or di.uniqueness != 'UNIQUE'
			      or abs(dt.num_rows / di.distinct_keys -1 ) > 0.1 )
		   ) 
		   or upper(p_skip_suspected_synthetic) != 'Y';
		   
  v_analyze_clause varchar2(100) := ' estimate statistics ';
  v_leaf_blocks    number;  
begin
	
  if p_sort_size is not null then
    execute immediate 'alter session set sort_area_size = '||p_sort_size;
    execute immediate 'alter session set sort_area_retained_size = '||p_sort_size;
  end if;		
	
  if p_skip_analyze not in ('N','n') then
     premlinary_analyze(p_owner,p_percent);
  end if;

  if p_percent is not null then
     v_analyze_clause := v_analyze_clause || 'sample '||p_percent||' percent';
  end if;
  
  for i in index_list loop
    dbms_output.put_line('INDEX: '||i.index_name);
    dbms_output.put_line('- Initial       : '||i.leaf_blocks);
    for j in 1 .. i.col_pos loop
	   execute immediate 'alter index '||p_owner||'.'||i.index_name||' rebuild compress '||j;
	   execute immediate 'analyze index '||p_owner||'.'||i.index_name||v_analyze_clause;
	   select leaf_blocks
	   into   v_leaf_blocks
	   from   dba_indexes
	   where  owner = upper(p_owner)
	   and    index_name = i.index_name;
       dbms_output.put_line('- Compression '||j||': '||v_leaf_blocks);
	end loop;
  end loop;  
  
end;					 

end;
/