Subject Re: Optimizer selects non-optimal plan
Author jstahl80
> Why all these parenthesis, Joja? You have specifically told it to
> left join ch_daten to autrags_daten_run before joining to ch_daten
> and leaves the optimizer with less choices.

Isn't it the optimizers tasks to switch the things around to get the
best performing plan whose output conforms to what I initially
requested in terms of i.e. relational logic? I do not want to
specify how the engine should process my request but what the result
shall be. This is theory of course, each optimizer is not optimal. I
only wanted to help make it better...

> So, I recommend you to change to
> select distinct c.*, a.*
> from ch_daten c
> INNER JOIN cmd cmdm ON c.objid=cmdm.to_objid
> INNER JOIN ch_daten chdm ON cmdm.objid=chdm.objid and
> LEFT JOIN auftrags_daten_run a ON c.objid = a.objid
> where
> chdm.dch_nr=70106234

This is not execatly but near what my first query looks like. The
resulting plan is identical to my first query excluding the missing

> (I also removed the two left joined tables that you're not
selecting from since they do not contribute to your answer in any

In this case they are not needed. But the where condition is
adjusted depending on users selection of conditions in a form. Then
where parts may be added that refer the other tables.

Thank, Joja