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

Jonathan Lewis wrote:

> I've just tried emulating the problem (with insufficient
> detail, unfortunately) and get the same NESTED LOOP
> plan in both cases.
>
> I had to create the index on DATAPOINT in the order
> (vector_id, refdate, version) to get the fast full scan
> though.
>
> The oddity, to me, is that you get a
> > PARTITION RANGE ITERATOR
> I would have expected a PARTITION (SINGLE) as
> the second step of a nested loop, as there is clearly
> only one partition that could possibly supply answers.
>
> It has to be said that later versions of Oracle can do
> some clever partition elimination in cases where
> a) it has a nested loop into the partition table and
> b) the partition key is not visible as a constant.
> When it switches to a hash, or merge, join, all the
> partition elimination disappears. Unfortunately in
> some versions of Oracle the arithmetic goes wrong
> in this area.
>
> It may simply be in your case that adding zero
> to the vector_id has caused Oracle to use a
> slightly different calculation for the range of
> vector_ids requested, which just happens to have
> pushed it from a hash path to a nested loop path,
> which just happens to be much better than expected.
>
> --
> 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

Which once again would reinforce the fact that people need to explain plan everything to understand what it is doing. Just relying on mythology and habit can create unpleasant surprises.

Daniel Morgan Received on Fri Jun 21 2002 - 10:19:56 CDT