On Sun, 13 Feb 2000, Marin Komadina wrote:

> Hi, Ari. 
> 
> Short  question. Is there any script which can be used to see redundant
> indexes. I meant , to find out single columns indexes which are duplicated
> in some database schema. 
> 
> 
> Thanks,  for all effort since now.
> 
> Marin. 

Marin,

To do what you want:

SELECT * FROM ALL_IND_COLUMNS;

This will give you all columns that the indexes are based on. I don't know
how to do a compare of indexes on different tables for indexes with more
than one column. You were asking about single-column indexes...

SELECT OWNER, COLUMN_NAME, TABLE_NAME from ALL_IND_COLUMNS;

This gives all index columns. I think you can do the following, although I
am just doing this in my head and not trying it for real:

SELECT a.OWNER, b.OWNER, a.COLUMN_NAME, a.TABLE_NAME, a.INDEX_NAME
FROM ALL_IND_COLUMNS a, ALL_IND_COLUMNS b
WHERE a.OWNER = b.OWNER AND
      a.COLUMN_NAME = b.COLUMN_NAME AND
      a.TABLE_NAME = b.TABLE_NAME AND
      a.INDEX_NAME != b.INDEX_NAME

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 350+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->


Back to Ari Kaplan's Home Page