Charles Hooper wrote:
> DA Morgan wrote:
> > Charles Hooper wrote:
> > > I recall reading various write-ups by Tom Kyte, Jonathan Lewis, or
> > > others that detail why the index cannot be used in such a such a case,
> > > but cannot locate a reference to one of the write-ups.  As a general
> > > rule, if more than 20% to 25% of a table's rows are expected to be
> > > read, it is usually less expensive to perform a full table scan, rather
> > > than a scan of the index and then an access by ROWID to read the data
> > > from the table.  Note that using a % or _ wildcard for the initial
> > > character, Oracle would be required to access every block in the index
> > > and then potentially every row in the table by ROWID.

> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.

> > Jonathan and other, oh how I hate to try paraphrasing others accurately,
> > have been generally dismissive of throwing out that 20-25% number. I
> > have run tests in my lab where I have been able to show wildly different
> > numbers for the optimizer depending on many factors including version,
> > caching related parameters, etc. But as a general rule it is certainly a
> > good thing to keep in mind that >50% is probably not a winning
> > percentage for index usage.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor...@x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org

> Paraphrasing from "Expert Oracle Database Architecture" by Tom Kyte:
> B*Tree indexes should be used only when retrieving a small portion of
> the rows in a table - less than 20% of the rows.

> I have seen similar comments from other respectable sources.  The 20%
> to 25% is a rough guideline.  A read through "Cost-Based Oracle
> Fundamentals" leaves the impression that nothing is that simple.  If
> the database has an 8KB block size, and the average row length is 20
> bytes, does the 20% to 25% rough guideline hold true?  If the database
> has an 8KB block size, and the average row length is 8000 bytes, does
> the 20% to 25% rough guideline hold true?  What if the last 80% of the
> rows added to the table are deleted causing a high water mark that is
> far beyond the last used block, does the 20% to 25% rough guideline
> hold true?  Yes, No, and Maybe are all possibly correct answers.

> Maybe Gaja Vaidyanatha put it best in "Oracle Performance Tuning 101"
> (page 76), when referring to an article written by Cary Millsap in
> 1993:
> "It is pretty clear that the use of an index for a query should not be
> determined by some arbitrary percentage of the rows processed or
> selected from the table, instead it should be determined by the number
> of blocks visited to retrieve the data.  If the number of blocks
> visited for an index is lower than a full table scan, then the index
> will be useful.  Otherwise, a full tablescan will provide much better
> performance."

> But then, you must also consider the number of index and table blocks
> that may already be in the buffer cache, the multiblock read count, the
> time required to retrieve a single block compared with the time to
> retrieve multiple blocks, etc.

> I am not disagreeing with your comments on this topic.  However, if
> someone asks me why Oracle is not using my index, it is very easy to
> ask, does Oracle expect to retrieve more than 20% to 25% of the rows by
> index?  If the answer is Yes or Maybe, then that is why Oracle is not
> using your index.