Subject RE: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?
Author Louis van Alphen
Thanks for the response Dmitry

> The same thing happens in many other queries where I use views and IMHO
> I find it non-sensical. I have come to the conclusion that FB does not
> choose great query plans when using views.

True, when views contain outer joins.

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 is really hard to tune queries if the results _/seem/_ unpredictable
> and there are no visibilty in how FB executes the query.

Plan shows how the query is being executed.

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

> Help would be appreciated

Something like:

select S.*
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

Not sure what you are doing here and what the where clause does. Are these tricks documented somewhere?