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

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

1. it has a nested loop into the partition table and
2. 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