Re: Yet another "why is my index not used" question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 28 Jan 2009 08:09:01 -0000
Message-ID: <1dWdneBEILUIjx3UnZ2dnUVZ8oKdnZ2d_at_bt.com>



"Michael Austin" <maustin_at_firstdbasource.com> wrote in message news:lBKfl.16314$c45.1300_at_nlpi065.nbdc.sbc.com...
> Jonathan, Based on his WHERE Clause - no amount of fiddling is going to
> fix his problem until he gives the optimizer something other than "is not
> null" to work with- it is still going to do a FTS.

Michael,

I think you're looking at the query that Dimitre used to generate an alternative value for the clustering_factor. This uses "is not null" clauses against every column in the index he is analyzing to ensure that Oracle can legal use the index-only access path that's needed.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Wed Jan 28 2009 - 02:09:01 CST

Original text of this message