Tuesday, October 6, 2009

Identifying low cardinality indexes

#Candidate b-tree indexes to drop, or replace with bitmap indexes.
#Selects indexes with num_rows < 200 or distinct_keys < 200 or selectivity < 0.1
#Excludes indexes with multiple columns, or used to support constraints

select table_owner||'^'||table_name||'^'||index_name||'^'||index_type||'^'||num_rows||'^'||distinct_keys
from dba_indexes di
where di.owner in (select username from dba_users where profile in ('BI_PROFILE','APPLICATION_PROFILE'))
and num_rows > 0
and index_type = 'NORMAL'
and (num_rows < 200 or distinct_keys < 200 or distinct_keys/num_rows < 0.1)
and index_name in (select index_name from dba_ind_columns group by index_name having count(*) = 1)
and not exists (select 1 from dba_constraints dc where dc.index_name = di.index_name);