Re: TABLE ACCESS FULL

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0310222139.7a781053_at_posting.google.com...

>
> Case 2. The exact opposite of case 1. Thus the index IS used.

Like he said: he's doing case 2 (30 rows out of 3 million) and the optimizer is NOT picking the index... In the chapter about indexes in Expert oneonone, TK goes at length under which conditions using an index is or is not better than doing a FTS. With examples. Worth a look.

> You have proved two things.
> a) Oracle CBO works as it should (and better than you thought)

Maybe not in his case?

> b) You could spend some more time with the Oracle manuals :-)

TK's book for one helps in these situations.

> IMO never ever enforce an index range scan (yes it is possible using
> hints). Index range scans can be a lot more painful than a FTS. When
> dealing with unknown index ranges, let the database (i.e. CBO)
> decides.

His words:

> > I'm trying to speed up my query which selects only 30 records out of
> > 3million records.

In anyone's book that is not an unknown index range... I agree though: WHEN dealing with *unknown* ranges, then it's better to let the CBO do its work. If it tends to err on the side of pessimism, it's always possible to nudge it in the right direction with the "optimizer_*" stuff. That is of course the general case.

OT: waddyareckon: Boks, AllBlacks or the Poms?

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam