Find unnecessary duplicate indexes on a table

This script will generate a drop index script which can be used to drop redundant indexes on all tables except those owned by user SYS and SYSTEM. For example, if table PERSON has two indexes -- the first one is person_id,ssn and the second one is person_id -- this script will identify the second one as duplicate index. This was tested on 8.1.7 and 9.2 versions.

set serverout on size 1000000;
set echo off
set feed off
set veri off
set head off
spool drop_idx.sql
declare
wk_owner            varchar2(30);
wk_table_name       varchar2(30);
wk_index_name       varchar2(30);
wk_index_name2      varchar2(30);
wk_numcol           number(3);
wk_numint           number(3);
cursor cur1 is
select index_owner,table_name,index_name,count(*)
from sys.dba_ind_columns
where index_owner not in ('SYS','SYSTEM') 
and  (index_owner,table_name) in (select index_owner,table_name
                                  from dba_ind_columns
                                  where column_position = 1
                                    and index_owner not in
('SYS','SYSTEM') 
                                  group by
index_owner,table_name,column_name
                                  having count(*) > 1)
group by index_owner,table_name,index_name
order by index_owner,table_name,index_name;
cursor cur2 is
select index_name
from sys.dba_indexes
where owner = wk_owner
  and table_name = wk_table_name
  and index_name <> wk_index_name;
begin
 open cur1;
 loop
  fetch cur1 into wk_owner,wk_table_name,wk_index_name,wk_numcol;
  exit when cur1%notfound;
  open cur2;
  loop
   fetch cur2 into wk_index_name2;
   exit when cur2%notfound;
   select count(*) into wk_numint from 
   ( select column_name,column_position from dba_ind_columns
     where index_owner = wk_owner
       and index_name  = wk_index_name
     intersect         
     select column_name,column_position from dba_ind_columns
     where index_owner = wk_owner
       and index_name  = wk_index_name2);
     if wk_numcol = wk_numint
        then        
        dbms_output.put_line('drop index
'||wk_owner||'.'||wk_index_name||';');
       exit;
     end if;
   end loop;  
   close cur2; 
  end loop;  
end;
/
spool off;
set echo on
set feed on;
set veri on;
set head on;

Reader Feedback

Micha T. writes: About this tip, be careful when your application is still running under RBO because there your single-column indexes could be vital for index joins.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.