Problem solve Get help with specific problems with your technologies, process and projects.

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.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close