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.
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.htmlReceived on Wed Jan 28 2009 - 02:09:01 CST