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

Hi,

adding 0 to column means disabling indexes on that column in RBO environment.
That's why you got different explain plans.

Regards,

--
Roman Mirzaitov
Brainbench MVP for Oracle Administration
www.brainbench.com

"Salaam Yitbarek" <yitbsal_at_yahoo.com> wrote in message
news: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