Re: The 20% rule

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 24 Jul 2008 11:02:25 -0700
Message-ID: <1216922543.392125@bubbleator.drizzle.com>


aarklon_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 ?

The "rule" is pure unadulterated mythology. -- developed and run on a T43 ThinkPad with 4GB RAM and 11.1.0.6.

CREATE TABLE t (
acol NUMBER,
bcol NUMBER);

CREATE INDEX ix_t
ON t(acol);

INSERT INTO t VALUES (1,11);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (2,21);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (3,31);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (4,41);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (5,51);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (6,61);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (7,71);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (8,81);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO t VALUES (9,91);

EXPLAIN PLAN FOR
SELECT bcol
FROM t
WHERE testcol = 1;

SELECT * FROM TABLE(dbms_xplan.display);

Every explain plan looks like this:



Plan hash value: 2619160949

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01|
|*  1 |  INDEX RANGE SCAN| IX_T |     1 |    13 |     1   (0)| 00:00:01| 
------------------------------------------------------------------------

There is only one rule in this business ... it is the rule of testing.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jul 24 2008 - 13:02:25 CDT

Original text of this message