Re: query for detect redundant index

2002-09-05 Thread Adriano Freire 



Fink,

Thanks a lot


  - 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.
  
  
  SQLbreak on table_name on column_nameSQL 
  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 ic211 where ic1.table_name = 
  ic2.table_name12 and ic1.column_name = 
  ic2.column_name13* and ic1.index_name != 
  ic2.index_nameSQL /
  
  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 AMTo: Multiple recipients of list 
ORACLE-LSubject: query for detect redundant 
index
HI all,

Do you've any pl/sql for detect redundant 
index?

thanks,

Adriano Freire

RE: query for detect redundant index

2002-09-03 Thread 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.


SQLbreak on table_name on column_nameSQL 
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 ic211 where ic1.table_name = 
ic2.table_name12 and ic1.column_name = 
ic2.column_name13* and ic1.index_name != 
ic2.index_nameSQL /

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 AMTo: Multiple recipients of list ORACLE-LSubject: 
  query for detect redundant index
  HI all,
  
  Do you've any pl/sql for detect redundant 
  index?
  
  thanks,
  
  Adriano Freire