Re: EXPLAIN PLAN Examples and Questions

Dereck L. Dietz wrote:
> 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.

Run a real Explain Plan using dbms_xplan.

If you don't know how to do it go to Morgan's Library at www.psoug.org and click on Explain Plan and DBMS_XPLAN.

The fact that Oracle is not using an index has been covered in agonizing detail by everyone from Tom Kyte to Jonathan Lewis to, well, me. Post the actual Explain Plan and we'll help you read it.

You might also post the plan using the INDEX hint to force index usage for comparison and consider applying the patches Oracle has put on metalink for 10.2.0.1.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org