# RE: full-scan vs index for "small" tables

• From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
• Date: Tue, 27 Jun 2006 09:52:50 -0500

```Mladen,

I'm going to stand firm on the following statement:

Work first to reduce the biggest response time component of the

Everything else follows from that. But in order to know what your most
order to know what that task's biggest response time component is, you
have to measure your specific circumstance.

The objection I have with the world at large is when people guess
instead of measure. Most people guess "It's got to be I/O." But "it" is
I/O in fewer than 5% of cases I've witnessed since about 1995.

So I find the "It's got to be I/O" guess to be extremely dangerous,
first because it's the most common guess, second because it's erroneous
a surprisingly large proportion of the time.

Of course, if "it" is really I/O-and you know it to be so because you've
accurately measured-then by all means dive into the I/O problem with all
four feet.

But only if (that is, after) you've determined that I/O is the biggest

My point ends with, "Why guess... When you can know."

Cary Millsap

Hotsos Enterprises, Ltd.

http://www.hotsos.com

Nullius in verba

Hotsos Symposium 2007 / March 4-8 / Dallas

Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----
Sent: Tuesday, June 27, 2006 8:34 AM
To: Cary Millsap
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: full-scan vs index for "small" tables

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

>

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.

--

http://www.mgogala.com

```