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

Mladen,
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.

rgds

On 6/27/06, Mladen Gogala <gogala_at_sbcglobal.net> wrote:
>
> On 06/27/2006 09:03:33 AM, oracle-l-bounce_at_freelists.org 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l