Re: Indexes on very small tables

One consideration is if the table fits in 1 db block buffer (usually 10K in 5 two K buffers, but it varies by instance and platform). If it does, a table scan of one buffer is usually faster than an index range scan followed by a fetch by rowid.
Of course, if you need a unique index for referential integrity, this factor is irrelevant.

In article <19990709124923.09630.00008399_at_ng-fg1.aol.com>,   rniemic_at_aol.com (Rniemic) wrote:
> The index breakpoint seems to be in the area of 1000 rows but note
that is
> varies for SELECTs and UPDATES/DELETES (as far as the optimizer
choices). For
> example, the optimizer uses an index for a 1 million row table when
about 4% or
> less of the rows are being retrieved, but for a 1000 row table it
only uses the
> index when 1% or less is retrieved on a SELECT ... but ... that same
smaller
> table uses an index well into 4% for UPDATES/DELETES when the indexed
column is
> used. If you have the book, I cover the breakpoint on pages 386-389.
>
> Rich Niemiec
> Oracle Performance Tips and Techniques, Oracle Press
> ISBN: 0078824346
>
> The tuning scripts from the book are at:
> http://www.tusc.com/books/perf_tune.html
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 12 1999 - 15:59:46 CDT