The 20% rule

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aarklon@gmail.com - 24 Jul 2008 15:01 GMT

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 ?

Reply to this Message

Ed Prochak - 24 Jul 2008 17:37 GMT

On Jul 24, 9:01 am, aark...@gmail.com wrote:

> Hi all,
>
[quoted text clipped - 21 lines]
>
> my question is to what extent this rule is true ?

As with all performance questions the answer is "it depends".
That is why there is EXPLAIN PLAN and other performance measures.

Here's the simple counter example for the 20% "rule"
SELECT * FROM sometable WHERE ColumnWith20Percent IS NULL;
since indices do not track NULLs.

You best bet is to remember that this is not a hard rule, but a
general guideline. The paragraph you quoted (or at least paraphrased)
hints at this since it says: "you may just want to try both methods".
to quote the raven: It's a guideline, nothing more.

HTH,
ed

Reply to this Message

Robert Klemme - 27 Jul 2008 13:27 GMT

> On Jul 24, 9:01 am, aark...@gmail.com wrote:
>> Hi all,
[quoted text clipped - 25 lines]
> As with all performance questions the answer is "it depends".
> That is why there is EXPLAIN PLAN and other performance measures.

Exactly.

> Here's the simple counter example for the 20% "rule"
> SELECT * FROM sometable WHERE ColumnWith20Percent IS NULL;
> since indices do not track NULLs.

Well, there *are* ways to make Oracle index NULL columns. :-) For
example, an index on multiple columns will contain NULL entries if there
are non NULL entries in other columns. See Richard's excellent blog for
more info, especially this entry

http://richardfoote.wordpress.com/2008/01/23/indexing-nulls-empty-spaces/

Using FBI's you can actually safe space by making use of the fact that
NULL's are not indexed. See also

http://richardfoote.wordpress.com/2008/01/28/index-only-values-of-interest-littl
e-wonder/

> You best bet is to remember that this is not a hard rule, but a
> general guideline. The paragraph you quoted (or at least paraphrased)
> hints at this since it says: "you may just want to try both methods".
> to quote the raven: It's a guideline, nothing more.

Absolutely agree!

Kind regards

robert

Reply to this Message

David Cressey - 24 Jul 2008 20:14 GMT

> Hi all,
>
[quoted text clipped - 21 lines]
>
> my question is to what extent this rule is true ?

The trouble with books written for dummies is that they often offer general
rules as a substitute for clear thinking.

Here's another exception to the 20% rule: if your query has an order by
clause.

select * from employees order by last_name, first_name;

If there is an index on last_name, first_name this query will run lots
faster using the index, because it saves the time spent sorting the results
from the table.

There are two things to consider when planning either indexing or queries:

First, if you are using the cost based optimizer (which most people do these
days) it becomes the optimizer's job to figure out whether to use the index
or scan the whole table. The optimizer often gets it right. You should pay
attention to strategy only when there's a reason to suspect the optimizer's
strategy.

Second, an index that pays off during a query also imposes a cost on
updates. You need to balance off performance gains during queiries against
performance losses during updates. You need to understand the traffic on
the database to get this right.

If you are actually a dummy, the best way to handle using indexes in Oracle
is to let somebody else do it. If you are not actually a dummy (hopefully
the case), you need to get beyond stuff written for dummies.

Reply to this Message