RE: query for detect redundant index

[Fink, Dan]

I presume 'redundant' means that a column in the leading position of an index is also in the leading position of another index.   Here is a quick and dirty SQL script to generate this information. Unfortunately, it repeats the information, but it does give you the information.     SQL> break on table_name on column_name SQL> l   1  select ic1.table_name,   2         ic1.column_name,   3         ic1.index_name,   4         ic1.column_position,   5         ic2.index_name,   6         ic2.column_position,   7         decode(ic1.column_position,   8                ic2.column_position, 'Redundant') redundancy   9  from user_ind_columns ic1,  10       user_ind_columns ic2  11  where ic1.table_name = ic2.table_name  12    and ic1.column_name = ic2.column_name  13*   and ic1.index_name != ic2.index_name SQL> /   TABLE_NAME COLUMN_NAME          INDEX_NAME      COLUMN_POSITION INDEX_NAME      COLUMN_POSITION REDUNDANC ---------- -------------------- --------------- --------------- --------------- --------------- ---- EMP        EMPNO                PK_EMP                        1 IX_EMP                        1 Redundant                                 IX_EMP                        1 PK_EMP                        1 Redundant            ENAME                IX_EMPNAME                    1 IX_EMP                        2                                 IX_EMP                        2 IX_EMPNAME                    1 -----Original Message----- From: Adriano Freire [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 03, 2002 11:15 AM To: Multiple recipients of list ORACLE-L Subject: query for detect redundant index HI all,   Do you've any pl/sql for detect redundant index?   thanks,   Adriano Freire