Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: full-scan vs index for "small" tables

Re: full-scan vs index for "small" tables

From: Ghassan Salem <>
Date: Tue, 27 Jun 2006 15:45:38 +0200
Message-ID: <>

if you apply Nigel's "method" to Cary's statement, it's not wrong. He says "it NOT uniquely determined...". so I/O can have (and usually does) a big impact, but other factors can also have their share.


On 6/27/06, Mladen Gogala <> wrote:
> On 06/27/2006 09:03:33 AM, 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
> 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
> --

Received on Tue Jun 27 2006 - 08:45:38 CDT

Original text of this message