The 20% rule

From: <aarklon_at_gmail.com>
Date: Thu, 24 Jul 2008 07:00:14 -0700 (PDT)
Message-ID: <a3ae9096-5f4d-44dc-8059-943b5f40dbc6@o40g2000prn.googlegroups.com>


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 ? Received on Thu Jul 24 2008 - 09:00:14 CDT

Original text of this message