Re: TABLE ACCESS FULL

"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote

> Like he said: he's doing case 2 (30 rows out of 3 million)
> and the optimizer is NOT picking the index..

Yeah, I did see that "30 rows being returned/selected", but I was thinking group by was neglected to be mentioned.

Well either that, or the CBO is seriously screwy when deciding FTS to do only 30 rows. The latter I will only believe when hard evidence is given. :-)

> 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.

Yep. Total coincidence. The Book is right here next to the keyboard. :-)

Just a pity that he did not add to the myths that FTSs are not evil. I've seen a lot of frustration and tuning by people who sees a FTS in an execution plan and immediately go "holy crap!" and then start swinging away at the problem with index hints. Instead the warning bells should go as a FTS implies that you want to hit big volumes with your SQL.

> 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.

Agree. That is IF the statement on 30 rows being selected ALSO implies that these are also *only* 30 rows (of the millions) being processed. And in the absense of more detail, I cannot see that to be the case when the CBO decides to do a FTS.

> OT: waddyareckon: Boks, AllBlacks or the Poms?

Tough one. Of course I'm hoping for the Boks. The Poms are damn good, but the longer you're on the top, the more difficult it becomes to stay there. I do not think that they can keep up that level of play consistently for such a long time. They have everything to lose and little to gain. Not forgetting the Kiwis either. They have a good team. And then there's the mightly AllBlacks... So the heart says Bokke but the brain says mebbe the AllBlacks.

Besides, us southern hemisphere okes have to stand together. :-)

--
Billy