Re: The 20% rule
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 24 Jul 2008 17:23:47 -0700
Message-ID: <1216945423.53114@bubbleator.drizzle.com>
>> 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 -
Date: Thu, 24 Jul 2008 17:23:47 -0700
Message-ID: <1216945423.53114@bubbleator.drizzle.com>
fitzjarrell_at_cox.net wrote:
> 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
Sorry about that. I ran several versions to verify I reproduce the behavior at will.
-- 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.orgReceived on Thu Jul 24 2008 - 19:23:47 CDT