# Re: The 20% rule

From: <fitzjarrell_at_cox.net>
Date: Thu, 24 Jul 2008 11:38:02 -0700 (PDT)

On Jul 24, 1:02 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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 ?
>
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Not with the posted code; every plan looks like this:

SQL> EXPLAIN PLAN FOR
2 SELECT bcol
3 FROM t
4 WHERE testcol = 1;
WHERE testcol = 1

*
ERROR at line 4:
ORA-00904: "TESTCOL": invalid identifier

```SQL>
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

```

PLAN_TABLE_OUTPUT

Error: cannot fetch last explain plan from PLAN_TABLE SQL> Modifying the code slightly and running this on 10.2.0.3 tells a different tale:

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 2153619298
```| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    26 |     3   (0)|
```
00:00:01 |

Predicate Information (identified by operation id):

1 - filter("ACOL"=1)

Note

• dynamic sampling used for this statement SQL>
Computing statistics for the given table and index changes the picture somewhat:

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1110118096
```| Id  | Operation                   | Name | Rows  | Bytes | Cost
(%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    26 |     1
(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    26 |     1
(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_T |     1 |       |     1
```
(0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access("ACOL"=1)
SQL> Of course I haven't 11.1.0.6 as a playground so I can't speak to the behaviour reported by Daniel.

David Fitzjarrell Received on Thu Jul 24 2008 - 13:38:02 CDT

Original text of this message