Re: The 20% rule
Date: Thu, 24 Jul 2008 11:38:02 -0700 (PDT)
Message-ID: <6b85656e-007b-46ff-8024-0232f0f778bf@y22g2000prd.googlegroups.com>
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>
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