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

I've had this exact situation a while back, where a 1-block lookup table with a few rows didn't have an index and was getting hammered by a trigger (I think -- it was "a while back"). Sure enough, after applying an index (probably based on a previous Cary post on this topic), elapsed time was significantly better.

One thing I've been wanting to test is to compare performance of a small non-indexed table in LM/fixed tablespaces with extents of 128K, 4M, and 128M. I have my theories, but no time to test!

Rich

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]

Sent: Tuesday, June 27, 2006 8:04 AM
To: oracle-l_at_freelists.org
Subject: full-scan vs index for "small" tables

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 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-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Ogun.Heper_at_turkcell.com.tr
Sent: Tuesday, June 27, 2006 4:07 AM
To: sjaffarhussain_at_gmail.com; oracle-l_at_freelists.org Subject: RE: db file scattered reads

Hi,

First of all, the p3 parameter doesn't have to be equal to DB_FILE_MULTIBLOCK_READ_COUNT. The only explanation about this value in the documentation is;

P3 - The number of blocks (should be greater than 1)

Furthermore, documentation states that;

13.5.1.2.3 Small Table

If a table contains less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, which can be read in a single I/O call, then a full table scan might be cheaper than an index range scan, regardless of the fraction of tables being accessed or indexes present.

[snip]

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