CONNECT BY NOCYCLE PRIOR 10G Optimiser Mode

Question

question for today; if the RBO is enabled in 10.2.0.3 and one attempts to use a hierarchical approach; CONNECT BY PRIOR for example, does the optimiser get switched to CBO for execution? I have a large RBO 10GR2 (Don't ask!!), I know the stats are out of date and the query runs like a dog using CONNECT BY.

In v$sqlarea the OPTIMIZER_MODE is RULE. I know using LEFT OUTERS will force RULE to COST.

Any thoughts?

Answer 1

When my Memory is correct, you should be able to force the RBO with:

/*+ RULE */

as optimzier hint.

Answer 2

I managed to figure out that it was not the CONNECT BY forcing the CBO, there was a RANK() over Partition in the SELECT clause causing it!