21.08.2015 10:26, 'Louis van Alphen' wrote:

> Dmitry, if I then understand you correctly, if a view contains an outer join, then FB will table scan? Is this documented somewhere so that I can read up?

It depends on a number of factors, but generally your conclusion is
correct. The problem is that the join order always starts with a view
with a joined table coming afterwards.

This is a known issue, but unfortunately hard to fix.

> Sure, I look at the plan, but the plan is after the fact. It does not show you why?

Right, you cannot see why the optimizer does this or that choice, you
see only the resulting plan.

> Something like:
>
> select S.*
> from COLLECTION_ COLL
> left join SKIN S on S.ID = COLL.SKIN_ID
> where S.ID is not null
>
> i.e. fake the left join to get the correct join order
> (COLLECTION_->SKIN_->COLOUR_).
>
> Not sure what you are doing here and what the where clause does. Are these tricks documented somewhere?

This is a quite common trick. For inner joins, possible join orders are
either {COLL->SKIN} or {SKIN->COLL}. We want the join order to be
{COLL->SKIN} but the optimizer decides differently and chooses
{SKIN->COLL}. For outer joins, however, the join order is always
predefined and dictated by the join syntax. So we replace inner join
with left join to guarantee the desired join order {COLL->SKIN}. But we
need to exclude the "false" rows produced by the outer join (records
from COLL having no matches in SKIN), so we add a WHERE clause -- S.ID
is not null -- to remove those unnecessary rows (I assume S.ID is a
primary key and thus it should never be NULL unless produced by the left
join).


Dmitry