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]
