Re: EXPLAIN Plan

From: <fitzjarrell_at_cox.net>
Date: 2 Oct 2006 15:09:10 -0700
Message-ID: <1159826950.177153.178120@e3g2000cwe.googlegroups.com>

Dereck L. Dietz wrote:
> Earlier today my manager sent an EXPLAIN PLAN to a number of us for help
> with a query. In the EXPLAN PLAN there was an INDEX RANGE SCAN which the
> off-site DBA said was caused by the table not being analyzed.
>
> The INDEX RANGE SCAN was using an index - maybe not the one I'd expect but
> nonetheless was using an index on the table being EXPLAINED.
>
> Am I missing something or does what the DBA not really make sense?

Your DBA doesn't make sense:

SQL> create table mytest as select * from all_objects where rownum < 50001;

Table created.

SQL> create index mytest_ctd on mytest(created);

Index created.

SQL> set autotrace on
SQL> select * from mytest where created between sysdate - 40 and sysdate:

[lots of data here]

Execution Plan

---

Plan hash value: 2298431287

---

| Id  | Operation                    | Name       | Rows  | Bytes |
Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    24 |  3072 |
3   (0)| 00:00:01 |
|*  1 |  FILTER                      |            |       |       |
       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYTEST     |    24 |  3072 |
3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | MYTEST_CTD |    24 |       |

2 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   1 - filter(SYSDATE@!-40<=SYSDATE@!)
   3 - access("CREATED">=SYSDATE@!-40 AND "CREATED"<=SYSDATE@!)

Note

---

• dynamic sampling used for this statement

Statistics

---

          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       2749  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed

SQL> exec dbms_stats.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SQL> select * from mytest where created between sysdate - 40 and sysdate:

[lots of data here]

Execution Plan

---

Plan hash value: 2298431287

---

| Id  | Operation                    | Name       | Rows  | Bytes |
Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  5873 |   539K|
101   (0)| 00:00:02 |
|*  1 |  FILTER                      |            |       |       |
       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYTEST     |  5873 |   539K|
101   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | MYTEST_CTD |  5873 |       |

17 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   1 - filter(SYSDATE@!-40<=SYSDATE@!)
   3 - access("CREATED">=SYSDATE@!-40 AND "CREATED"<=SYSDATE@!)

Statistics

---

          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       2749  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed

SQL> The same plan is returned whether or not statistics were gathered (this is for 10.2.0.2) as dynamic sampling was employed when no statistics existed. The index range scan was employed because the WHERE condition included a range on an indexed value.

I'd ask him to guess again.

David Fitzjarrell Received on Mon Oct 02 2006 - 17:09:10 CDT