Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why Adding "+0" Make A Query Run Faster

Re: Why Adding "+0" Make A Query Run Faster

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 20 Jun 2002 16:45:41 +0100
Message-ID: <1024587891.27787.0.nnrp-12.9e984b29@news.demon.co.uk>

Which version of Oracle is this - to at least four digits. Could you do a full explain plan on it, this looks like an emasculated autotrace plan, and every detail helps when trying to figure out partitioned plans.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Salaam Yitbarek wrote in message
<77439c33.0206191141.5c91ea15_at_posting.google.com>...

>I have a query as follows:
>
>select count (distinct refdate)
>from datapoint
>where vector_id in
> (select vector_id
> from ciiw_array_vector
> where array_id = :a_id)
>and version = 1;
>
>The datapoint table is partitioned on the column vector_id. The explain
plan is:
>
>SELECT STATEMENT Hint=CHOOSE
> SORT GROUP BY
> HASH JOIN
> TABLE ACCESS BY INDEX ROWID CIIW_ARRAY_VECTOR
> INDEX RANGE SCAN PK_CIIW_ARRAY_VECTOR
> PARTITION RANGE ALL
> INDEX FAST FULL SCAN PK_DATAPOINT
>
>That is, it looks at all the partitions of the DATAPOINT table.
>
>Changing the query to:
>
>select count (distinct refdate)
>from datapoint
>where vector_id in
> (select vector_id+0
> from ciiw_array_vector
> where array_id = :a_id)
>and version = 1;
>
>changes the explain plan to:
>
>SELECT STATEMENT Hint=CHOOSE
> SORT GROUP BY
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID CIIW_ARRAY_VECTOR
> INDEX RANGE SCAN PK_CIIW_ARRAY_VECTOR
> PARTITION RANGE ITERATOR
> INDEX RANGE SCAN PK_DATAPOINT
>
>which, of course, is much more efficient.
>
>Why does the "+0" make all the difference?
>
>Salaam
Received on Thu Jun 20 2002 - 10:45:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US