Re: query for detect redundant index

----- Original Message -----

From: Fink, Dan

To: Multiple recipients of list ORACLE-L

Sent: Tuesday, September 03, 2002 4:53 PM

Subject: RE: query for detect redundant index

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: Adriano Freire
  INET: adriano.f_at_uol.com.br

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).