Dmitry thanks for the insight. This actual puts FB in quite a different light. I have used FB since IB 5 and is my DB of choice. However this issue may prove quite problematic. Let me explain:



I have a large ERP class system ( currently ~400+ tables). An architectural design choice in the beginning was to abstract tables with views. So if I need to store a Customer object, I will have table CUSTOMER_ and then on top a view called CUSTOMER. Doing this, I have several benefits:

- This allows me to control access better than granting access to table level. At least that is my thinking. People accessing the DB cannot modify data if they don’t have table access, but can access ‘rich’ views to extract data

- It allows the ‘flattening out’ of an entity when selecting it from the DB. E.g. the CUSTOMER view will incluce the currency code from the CURRENCY_ table etc. So when my services layer returns the Customer object to the client, the CurrencyCode field is populated and is ready for presentation. I don’t need multiple calls to the DB to fetch the lookup values to populate the Customer object. One of the big tables have 115 columns with about 50 of them lookups.

- Views also allow me to easily implement multi-tenancy on a DB level. Therefore the DB will partition the various tenants’ data.



This problem now causes, in some queries, very inefficient retrieval of data where I did not expect it to happen. So I have 2 options: change to another DB or have a major architectural refactor.



Regards

Louis





From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 21 August 2015 10:19 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?





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





[Non-text portions of this message have been removed]