Re: full-scan vs index for "small" tables
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: cary.millsap@xxxxxxxxxx
- Date: Tue, 27 Jun 2006 09:33:37 -0400
On 06/27/2006 09:03:33 AM, oracle-l-bounce@xxxxxxxxxxxxx wrote:
> On a slightly modified topic, for a long time I've had a problem with this
> section 13.5.1.2.3 of the Oracle documentation. It's one of those sections
> that was written 25 years ago and apparently never subjected to scientific
> scrutiny.
>
> The part about "...which can be read in a single I/O call..." is one of those
> myths that makes sense when you hear it, but it's just not true. An index
> scan of a 1-row, 1-block table is more efficient than a full table scan of
> that table. Try it.
>
> Performance of an Oracle database is NOT uniquely determined by how many OS
> read calls your application causes it to make.
>
Cary, here I have to disagree with you. Performance is not uniquely determined,
but, apart from sleeping and waiting for an event, physical I/O is the slowest
thing that an application can do. If the pathological cases of endlessly
waiting
for locks are eliminated, the performance of an application will have, at least
according to my own experience, a direct correlation with the number of
performed
physical I/O requests. Spinning in memory is relatively rare and can be
constructed
Connor McDonald's sinister "hit ratio adjusting tool" but I didn't see to many
of
it in real life. Tuning response time is an extremely sound methodology which
essentially
dictates going after the part of application where the application spends most
of the
time, but in "real life" in the computer room (contradiction in terms, I know),
cutting
down on the number of physical I/O requests will usually have an extremely
beneficial
impact. It's a common wisdom which served me well, throughout my career.
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
