Re: How many is too many

When stating an index audit, monitoring was what I was referring to, but in 10g, you have to get a lock on the object to turn on the monitoring...This can be the biggest hurdle, more so than the overhead of the index or monitoring for usage.
Kellyn



________________________________
From: "Walker, Jed S" <Jed_Walker_at_cable.comcast.com>
To: "veeeraman_at_gmail.com" <veeeraman_at_gmail.com>; ORACLE-L <oracle-l_at_freelists.org>
Sent: Friday, August 12, 2011 1:42 PM
Subject: RE: How many is too many


 
I don’t think it is so much a number as “is the index used”. You can use the “alter index [no]monitoring” command to turn on monitoring and then watch v$object_usage to see which are used. Make sure you observe over a good time period to ensure you don’t miss any reports that are not run too often.
 
From:oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
Sent: Thursday, August 11, 2011 2:45 PM
To: ORACLE-L
Subject: How many is too many
 
Listers,
   
  I am looking at a table in our system and it has 12 indexes, we are planning on adding another one. I am aware of the effects of having too many indexes, but in this case adding an extra index helps a certain query that runs slow. Other queries and most other operations against the table are acceptable too. I see a few tables like this; is there a number above which is considered a no-no when it comes to adding more indexes.  
 
PS. The tables and queries are structured in a way that seem to require several indexes - it is a third party product. 
     
TIA,
Ram.

--
http://www.freelists.org/webpage/oracle-l