Re: EXPLAIN PLAN Examples and Questions

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:8vQ_h.294$mR2.233_at_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.
>
>

The full tablescan is NOT a "FULL" tablescan, it is a scan of a subset of the partitions. (I assume that you are copying text, and the

    partition range indicator
should really be

    partition range iterator

For all partitions above the one containing 4th May 2006, the query is asking for all the data, so a full scan of those partition probably is the only sensible option.

The switch to using the index is a little harder to explain, but only because of the absence of information about things like the number of days per partition, the number of distinct date values generated, and the number of partitions with high values above the critical value. A critical feature, though, is that the general calculation for handling

    column > (subquery)
is to assume a 5% selectivity which, combined with the parallelism against a partitioned table, may make the index option seem sensible to Oracle - even though you know that the result set is unchanged.

As a general guideline - use dbms_xplan to generate execution plans. Plans which don't identify filter and access predicate properly are often useless; parallel execution plans which don't show the distribution and table queues are hard to interpret.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html