EXPLAIN PLAN Examples and Questions

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Fri, 4 May 2007 20:48:17 -0500
Message-ID: <8vQ_h.294$mR2.233@newssvr22.news.prodigy.net>

Oracle 10g 10 2 0 1

Quad Dual Core AMDx64
8 Gig RAM
Windows 2003

I have an 80 million row table I'm examining. It's partitioned into 10 partitions
on the SRV_DT_FROM column and also indexed by that column. I have no control over how
it's partitioned or indexed

Queries against it normally take the first form below. Even though it has an index
it doesn't seem to be using the index and is doing a full table scan instead

SELECT DISTINCT person_id

           FROM claim v_claim
          WHERE srv_dt_from >= '04-MAY-2006'

Description
Cost    Cardinality     Bytes
------------------------------------------------        ------  ----------- 
    -------
SELECT STATEMENT,GOAL=ALL_ROWS       293162  564376          7901264
  HASH UNIQUE                                                   293162
564376          7901264
    PARTITION RANGE INDICATOR                  280416  6082022
85148308
      TABLE ACCESS FULL                                   280416  6082022

85148308

The query below it looks like it is finally using an index and only scanning the partitions
it needs to instead of the entire table.

SELECT DISTINCT person_id

           FROM claim v_claim
          WHERE srv_dt_from >= ( SELECT DISTINCT srv_dt_from
                                            FROM claim v_claim
                                           WHERE srv_dt_from =

'04-MAY-2006' ) Description

Cost       Cardinality        Bytes
------------------------------------------------                         ---
---      -----------     -------
SELECT STATEMENT, GOAL=ALL_ROWS                         340551  564015

7896210
  PX COORDINATOR
    PX SEND QC (RANDOM)

340551   564015           7896210
      HASH UNIQUE
340551   564015           7896210
        PX RECEIVE
331338   4033826         56473564
          PX SEND HASH
331338   4033826         56473564
            PX PARTITION RANGE INTERATOR                        331338
4033826         56473564
              TABLE ACCESS BY LOCAL INDEX ROWID         331338    4033826
56473564
                INDEX RANGE SCAN
2027        726089
                  SORT UNIQUE NOSORT
160          1                     8
                    PARTITION RANGE SINGLE                              157
46195             369560
                      INDEX RANGE SCAN
157         46195            369560

My two questions are:

1 In order to determine the actual efficiency or cost of a query using the explain plans
above do I use the TOPMOST cost value or the BOTTOM cost value?

2 Why does Oracle decide to use an index with the second query and not the first?

Thanks. Received on Fri May 04 2007 - 20:48:17 CDT