Re: The 20% rule

On Jul 24, 10:08 am, Gints Plivna <gints.pli..._at_gmail.com> wrote:
> On 24 Jûl., 17:00, aark..._at_gmail.com wrote:
>
>
>
>
>
> > Hi all,
>
> >  I recently read the following in a book(oracle 9i for dummies by
> > Carol McCullough Dieter)
>
> > The fastest way to retrieve rows from a table is to access the row
> > with exact row id. An
> > index is the second fastest way, but it decreases in performance as
> > the proportion of the rows retrieved increases. if you are retrieving
> > approximately 20 % of the rows in a table, using a index is just as
> > fast. But beyond that magic 20 %, not using 20% is faster.
>
> > keep this rule in mind when you create indexes intended to help speed
> > up a query.
> > Queries vary in the rows that they select from a table. if you have a
> > query that you use often,
> > determine the number of rows that it selects from the table. if this
> > number is more than 20%
> > of the total no: of rows in the table  , an index on the table may not
> > improve the performance
> > of the query. you may just want to try both methods. if the number of
> > rows is less than 20%, an index will almost certainly help
> > performance.
>
> > my question is to what extent this rule is true ?
>
> See this artcile fom Hotsos to understand why this rule simply cannot
> be measured using %. Simply cannot.http://www.hotsos.com/e-library/abstract.php?id=5
>
> Free registration required.
>
> Gints Plivnahttp://www.gplivna.eu- Hide quoted text -
>
> - Show quoted text -

I agree with Gints. The basic concept is true in that that comes a point where a full table scan becomes a more efficient access path than an index and the other way around but there is no one fixed point (percentage of data) where this is true. The inflection point will vary from index to index and may change over time as the data changes.

HTH -- Mark D Powell -- Received on Thu Jul 24 2008 - 10:01:22 CDT