Re: indexing
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>, "Tim Gorman" <tim@xxxxxxxxx>, "Zelli, Brian" <Brian.Zelli@xxxxxxxxxxxxxxx>
- Date: Tue, 19 Feb 2013 21:19:17 -0000
----- Original Message -----
From: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
To: "Tim Gorman" <tim@xxxxxxxxx>; "Zelli, Brian"
<Brian.Zelli@xxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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:
a) Oracle may not be using an index it ought to be using
b) 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
