Hello,

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 ?

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 ?

Thanks,
Aldo