Re: Oracle picks the wrong driving table?

NetComrade wrote:
> Can somebody help me understand why oracle picks the wrong driving
> table here? I've been inserting too many ORDERED and RULE hints over
> the past few days.

<snip examples>
> optimizer_index_caching integer 90
> optimizer_index_cost_adj integer 10
<snip>

What analysis was done in deciding to change OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ from the default settings (0 and 100 respectively)? You've told Oracle that index blocks are going to be almost completely in cache and accessing indexes is way cheaper than accessing the table. Doesn't seem particularly surprising that it tried it's best to find a way to use nothing but indexes to answer your query.   I'd guess if you set OPTIMIZER_INDEX_COST_ADJ back to the default of 100, the plan would change. Just try it in a single session to see what happens ...

alter session set optimizer_index_cost_adj = 100;

WARNING!!! I'm not suggesting you actually change this setting for your whole instance. That should involve a detailed examination of the effects on your whole system. However, if you're really having to hint "too many" queries then it's likely these aren't the right settings.

--
Richard Kuhler