How to find duplicate indexes ... source incl.

From: Daryl Erwin <derwin_at_mach2.wlu.ca>
Date: Tue, 29 Jun 1993 14:01:09 GMT
Message-ID: <C9Dyxy.HHz_at_mach1.wlu.ca>

Here is a script that I put together to find indexes that are redundant. I hope this is of some value to others.....

---dupli_index.sql----

SET ECHO OFF

rem ******************************************************************
rem          DBA REPORTS  
rem ******************************************************************
REM
REM	Program:	dupli_index.SQL
REM	Author:		Daryl Erwin
REM	Date:		June 28th, 1993
REM	Funtion:	To print out all redundant indexes. That is all 
REM             elements of the index can be found in that of
REM             another index (beginning from element 1).
REM ******************************************************************
REM
REM			CHANGE HISTORY
REM
REM DATE  	    WHO	Reason for Change 
REM 28-Jun-93   DWE	New Prog
REM ******************************************************************

prompt Print the name of indexes where another index contains the same prompt elements as that of the first.
set newpage 0
ttitle center 'Duplicate Index Report' - right 'Page:' format 999 sql.pno skip skip col nline newline
set verify off
spool dupli_ind.lst
set feedback 6
set termout off
set heading off
set space 0
select 'Index: ' || rtrim(t2.index_name) || ' is similar to ' || t3.index_name from user_ind_columns T3, user_ind_columns T2

where t2.index_name != t3.index_name
  and t2.table_name = t3.table_name
  and t2.column_name = t3.column_name
  and t2.column_position = t3.column_position

group by t2.index_name, t3.index_name
having count(*) = (select count(*)
from user_ind_columns t1
where t1.index_name = t2.index_name)
/
spool off
set termout on
prompt End of Report
spool off
ttitle off
clear breaks
clear columns
clear computes
set verify on
quit;

-- 
Daryl Erwin                                     Net Address: derwin_at_mach2.WLU.CA
Information Systems                             Surface Mail: P4-1, Main Campus 
Wilfrid Laurier University, Waterloo, Ontario 
Bus:(519) 884-1970 x2910                                !edisni deppart m'I pleH

Received on Tue Jun 29 1993 - 16:01:09 CEST