Re: EXPLAIN PLAN Examples and Questions

Dereck L. Dietz a écrit :
> 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.
>
>

Hi,
You should use "to_date('04-MAY-2006','dd-mon-yyyy')" instead of a single character string on which oracle has to do an implicit conversion. Regards,
Franck.

-- 
Franck PACHOT - DBA - Oracle 10g Certified - France