Index compression

Here is a little script to assist in determining candidates for index compression. It simple takes each index and generates a list of SQL's that show the cardinality of each combination of index columns. For example, for table X with an index on (A,B,C), it would generate:

select count(distinct(A)),
       count(distinct(A||B)),
       count(distinct(A||B||C)),
       count(*)
from   X

Its pretty basic, so obviously its your own responsibility to set things like the date format mask etc to ensure that the concatenation is valid.

set serverout on size 999999
declare
  prev     varchar2(255) := '*';
  pt       varchar2(255) := '*';
  pto      varchar2(255) := '*';
  col_list varchar2(255);
begin
for i in (
   select ic.TABLE_NAME,ic.TABLE_OWNER,ic.INDEX_NAME, ic.COLUMN_NAME, 
          ic.INDEX_OWNER||ic.INDEX_NAME conc
   from   dba_ind_columns ic
   where (TABLE_NAME,TABLE_OWNER,INDEX_NAME) in
     ( select TABLE_NAME, TABLE_OWNER, INDEX_NAME
                from   dba_ind_columns
                where  INDEX_OWNER = '&OWNER'
                and    COLUMN_POSITION = 2
                and    TABLE_NAME not like 'DR$%'  )
   order by ic.TABLE_OWNER, ic.TABLE_NAME, ic.INDEX_NAME, ic.COLUMN_POSITION ) loop

  if prev = i.conc then
    col_list := col_list || '||' || i.COLUMN_NAME;
    dbms_output.put_line(', count(distinct('||col_list||')) x');
  else
     col_list := i.COLUMN_NAME;
     dbms_output.put_line(', count(*) from '||pto||'.'||pt||';');
     dbms_output.put_line('select ');
     dbms_output.put_line(' count(distinct('||col_list||')) x');
  end if;
  prev :=  i.conc; pt := i.TABLE_NAME; pto := i.TABLE_OWNER;
end loop;
dbms_output.put_line(' , count(*) from '||pto||'.'||pt||';');
end;
/