Re: TABLE ACCESS FULL

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0310231105.702529b9_at_posting.google.com...
> "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. :-)

One of the scenarios I suspect is that the data is seriously skewed and there are no histogram stats. (Or alternatively bind variables are being used in a skewed case). 30 Rows out of 3m have a status of 'UNKNOWN' but as the table only has 3 distinct values for status 'PROCESSED','UNPROCESSED' and 'UNKNOWN' the optimizer thinks that it will pick a 3rd of the table with the condition AND STATUS='UNKNOWN'.

<snip on topic stuff>

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

yeah that's what I saw in SA all the SA guys I met standing shoulder to shoulder with the Aussies :( FWIW I see England winning the competition in a tough fight with NZ in the final. And then collapsing for the next 10 years.

And another thing I fly 6000 miles to SA and there's no bloody southern cross or any constellation at all to see in the night sky. Talk about a let down. sheesh.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************