Re: The 20% rule
Date: Thu, 24 Jul 2008 16:45:37 -0700 (PDT)
On Jul 24, 7:00 am, 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
> my question is to what extent this rule is true ?
Also see http://richardfoote.wordpress.com/2008/05/12/index-scan-or-full-table-scan-the-magic-number-magic-dance/ and http://richardfoote.wordpress.com/2008/05/20/reading-100-of-data-via-an-index-we-are-hungry-men/ .
-- @home.com is bogus. "I, for one, welcome our new robot overlords." - Brad Templeton http://www.templetons.com/brad/robocars/Received on Thu Jul 24 2008 - 18:45:37 CDT