Re: slow query - index usage question

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Wed, 19 Mar 2003 10:40:34 -0000
Message-ID: <COXda.3504$pK2.3897@news.indigo.ie>

Beware of quoting other people like that (TK) without posting a reference....

I'm not sure if you can use SQLT (the new explainer) on 806,but please post as much detail as you can from the EXPLAIN

Of course it can't use an index... You're trying to do a NOT IN ! indexes don't store nulls so therefore it must scan the whole table.

Also, the inner distinct looks like it could be removed to me. if it both VINs were not null and the distinct hauled out

we might be looking at a HASH_ANTI + INDEX_FFS (or a range scan depending on what slice VIN_NBR > x takes out although if my brain serves me

(goes off and looks at manuals (can't find 806 manuals but 9202 says this for bind variable predicates

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops .htm#45076 and this kind of thing shouldn't have changed too much in the interim.

HTH "Holger Baer" <holger.baer_at_science-computing.de> wrote in message news:3E782CEE.2020206_at_science-computing.de...
> Ed Stevens wrote:
> > On Mon, 17 Mar 2003 16:18:00 +0000, Rob Cowell <rjc4687_at_hotmail.com>
> > wrote:
> >
> >
> >>
> >>Ed Stevens wrote:
> >>
> >>>Platform - Ora 8.0.6 on NT4
> >>>
> >>>User presented me with this query:
> >>>
> >>>SELECT /* + INDEX(t_warranty_claim, T_WCLM_FK_WVIN) Use T_WCLM_FK_WVIN
> >>>*/
> >>> DISTINCT VIN_NBR
> >>> FROM T_WARRANTY_CLAIM
> >>> WHERE VIN_NBR NOT IN (SELECT DISTINCT VIN_NBR FROM
> >>>S_WARRANTY_VIN)
> >>> AND VIN_NBR > :v1
> >>> ORDER BY VIN_NBR
> >>>
> >>
> >>Do you mean a RANGE SCAN or FAST FULL SCAN? There are no WHERE
> >>predicates in the sub select, how could it RANGE SCAN ?
> >>The optimizer is deciding to evaluate both DISTINCTs and an ORDER BY
> >>with a single SORT UNIQUE, sounds quite efficient in theory.
> >
> >
> > Yes, I should have said a FAST FULL SCAN. In any event, we don't
> > understand why the inner query was using a full table scan when the
> > criteria could have been satisfied from the high-order column of one
> > of the indexes.
> >
> >

>

> Hello Ed,

>

> it would be nice to have the table declaration, also how recent
> statistics are, data size and distribution etc. (You're really
> long enough on this group to know that the more detailed the
> information, the better the answer). ;-)

>

> But since I did a few tests, here's what I found (and what I expected
> from the very beginning): If the column vin_nbr isn't declared as not
> null, then an index cannot be used. Besides, if it's not only a typo
> from posting, then the hint won't be effective because there's a
> space between '/*' and '+'.

>

> Furthermore: it has been said often (by such knowledgeable people as
> T.Kyte) that a hint is a hint not an order. A hint adjusts factors
> that are taken into the calculation of the costs. So depending on the
> information the optimizer has, it still can (and will) decide that it's
> more cheaper to use a full table scan compared to an index fast full scan.

>

> If that's the case, then I'd suggest you start looking for a document
> called something like 'Search for intelligence in cbo', which explains
> some of the parameters that influence cbo's decision when to use and
> index.

>

> HTH

>

> Holger

> Received on Wed Mar 19 2003 - 04:40:34 CST