Why Adding "+0" Make A Query Run Faster

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 Wed Jun 19 2002 - 14:41:18 CDT