> In a select statement there are two tables joined by a single matching
> field, with search conditions in both of them. One of them has roughly
> 130000 records, while the other has 600.
>
> Inspecting the plan generated by the optimizer, I realized that it uses the
> shorter one as the controlling stream instead of the longer one (i.e. the
> shorter table is at the left side).
>
> Using a PLAN expression with the corresponding indexes to bypass the
> plan deviced by the optimizer in order to switch the order of the streams, the
> performance is boosted (nearly three times faster).
>
> What can cause the optimizer to pick a shorter table as the controlling
> stream ?

Old/invalid index statistics.

> Is there a workaround to induce the optimizer to select the streams in a
> fixed way or should I have to resort to a manually imposed PLAN ?

If you can provide the PLAN and SELECT (old and optimized) we can offer some suggestions.


Sean