This script will generate a drop index script which can be used to drop redundant indexes on all tables except...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.