Thanks for the response Dmitry


[Louis]

> 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.

[Dmitry]
True, when views contain outer joins.

[Louis]
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?




[Louis]

> It is really hard to tune queries if the results _/seem/_ unpredictable
> and there are no visibilty in how FB executes the query.

[Dmitry]
Plan shows how the query is being executed.

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



[Louis]

> Help would be appreciated

[Dmitry]
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_).

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

Thanks
Louis