RE: query for detect redundant index

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Tue, 03 Sep 2002 11:53:29 -0800
Message-ID: <F001.004C65E1.20020903115329@fatcity.com>

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:adriano.f@uol.com.br]
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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Sep 03 2002 - 14:53:29 CDT