Re: indexing

• Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: "Tim Gorman" <tim_at_evdbt.com>; "Zelli, Brian" <Brian.Zelli_at_RoswellPark.org> Cc: <oracle-l_at_freelists.org> Sent: Tuesday, February 19, 2013 7:32 PM Subject: Re: indexing

|However, an index may be used by the optimizer during hard parsing even if it does not get used in the plan eventually. I am sure it was Jonathan Lewis who demonstrated this. An index may be used to get a more accurate cardinality estimate for a row source but not any further. This does not get recorded in v$object_usage but if you take that index away - because it is "unused" - the cardinality estimate changes and with it potentially the access path.

Correct, but fortunately this "sanity check" currently applies (for no obvious reason I can think of) only to unique indexes, and unique indexes are less likely to be dropped casually.
Other logical problems with index monitoring, though:

1. Oracle may not be using an index it ought to be using
2. Oracle may be using an index it ought not to be using

I think Mark Farnhan has already pointed out the two opposite extremes of the timing problem.
Basically index monitoring was (in my opinion) a pointless exercise coded up as an easy add-on in response to popular demand.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

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