Re: Why so many LIO's for select statement doesn't fetch any rows?

In article <erar41hhoal1n6rsockb0d83c8rhpbrt81_at_4ax.com>, Sybrand Bakker says...
>
>On 1 Apr 2005 12:08:31 -0800, "Carlos" <miotromailcarlos_at_netscape.net>
>wrote:
>
>>>>Tom Kyte also says: 'Test, test and test'
>>
>>You think 'Try /*+ FULL */ hint and see what happens.' is not another
>>way to say 'Test'?
>>
>>Cheers.
>
>I think the line 'Tom Kyte says: Full scans are not evil' is a
>dangerous generalization. It is the opposite of the generalization
>'full scans are to be avoided at all cost'
>Apart from that, I don't he said that in his seminar I attended, nor
>is it in the handout.
>

the complete quote would be:

1. because full scans are NOT EVIL
2. and indexes are NOT ALL GOODNESS
3. goto 1 until you believe it

the compulsive "find and remove all full scans" is bad. full scans are not always something to be removed. In some cases they are right. In some cases they are not so right.

Here though, when I was asked this question, responded: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6643159615303#37812317000504

This could be an index without enough columns (eg: 2 of the three columns where indexed and found about 125 possible rows per execution on average, when we went to 125 blocks to find the third column, it did not match the inputs. So we do 128 LIO's per execution to find zero rows).

That is my most probably "guess", without structures, hard to say.

There could be other causes (even an index with a massive whitespace issue, but i doubt that)

>
>--
>Sybrand Bakker, Senior Oracle DBA

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation