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 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 
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 
physical I/O requests. Spinning in memory is relatively rare and can be 
Connor McDonald's sinister "hit ratio adjusting tool" but I didn't see to many 
it in real life. Tuning response time is an extremely sound methodology which 
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), 
down on the number of physical I/O requests will usually have an extremely 
impact. It's a common wisdom which served me well, throughout my career.

Mladen Gogala


Other related posts: