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.
>
> No that can't be a reason, because:

Yes, maybe it isn't the exact reason. I just liked to inform Salaam about that feature.
Possibly this info put some idea into his head :)

> a) He's not using RBO, he's using CBO (on partitioned table, that's
> the only way).

Oracle manuals says:
"If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not add hints to your SQL statements, then your statements use the RBO." So we don't know for sure which optimizer mode used in our case. Besides, you can use RBO mode with partitioned table, you just will not gain any performance benefits from partitioning. But you can.

> b) In both of explains for that column is used INDEX RANGE SCAN, which
> doesn't seem to me like "disabling indexes".
>
> The change between two of them is that CBO decided to use NESTED LOOP
> using some partitions instead of HASH JOIN through all partitions.

Regards,

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