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
