Re: New OTN tuning article available

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:Deyra.10979$pK2.14391_at_news.indigo.ie...
>

http://technet.oracle.com/oramag/webcolumns/2003/techarticles/burleson_wait.
> html.
>
>
> Given the interesting threads that Don's writing tend to generate I'm
just
> posting the link

It looks like a pretty good article to me on a first reading, though I did pick up on

"Remember, one characteristic of suboptimal SQL execution is an unnecessary large-table full-table scan. For example, if you query only returns 10 rows, it would not be optimal to be performing a full-table scan on a 100 block table. "

Which may not necessarily be true. Consider

select customer_name from customers
where customer_state in ('NY','CA','FL');

with an index on the state column. This may well return our ten rows from ten different blocks in the table. it isn't clear at all to me that a traditional visit the index a couple of times to determine each rowid followed by a read of each of the ten blocks involved would necessarily outperform a FTS, consider that with a 2k block size you might have a multiblock read count as high as 32 or 64 depending on the capabilities of the hardware. So you might be able to FTS this table with just two or three physical reads.

This is clearly mostly a question of numbers though - 10 rows from a million block table is unlikely to have the same issue :( My big gripe is really the suggestion that FTS are always bad, they aren't. I do recognize that the first sentence quoted can be read not to say that FTS are always bad, but it clearly can also be read to say that they are.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK