Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: query for detect redundant index
----- Original Message -----From: Fink, DanSent: Tuesday, September 03, 2002 4:53 PMSubject: RE: query for detect redundant indexI 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 indexHI 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).Received on Thu Sep 05 2002 - 11:38:29 CDT