Re: indexing

From: Jonathan Lewis <>
Date: Tue, 19 Feb 2013 21:19:17 -0000
Message-ID: <A92ACAE74922431BBFEC0FBEAFBDDDD5_at_Primary>

  • Original Message ----- From: "Wolfgang Breitling" <> To: "Tim Gorman" <>; "Zelli, Brian" <> Cc: <> 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.


Jonathan Lewis

Author: Oracle Core (Apress 2011)

Received on Tue Feb 19 2013 - 22:19:17 CET

Original text of this message