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;
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.