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.

This was last published in March 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close