Hi,

> The optimizer has a problem that if you put a left outer join in a
> view and then join it to another table it doesn't choose the correct
> plan. Arno mentioned this in one of his sessions at the firebird
> conference. I have had problems with this issue on a number of
> occasions and have generally removed the left outer join from the
> query. The other day I thought of a different way around the problem
> which is to put the left outer join table into the field selection but
> I am unsure if there is any down side to it. Below is simplified
> version of one of our views.

As long as you're LEFT JOIN relation returns maximum 1 record based on it's
relation and you only need 1 field from it. Then there shouldn't be any
performance issue, because for the sub-select the "same" index-lookup needs
to be done.

> CREATE VIEW "IDENTTIYLIST" (
> "INVESTORNAME",
> "ACCOUNTNAME"
> ) AS
> select
> InvestorRelate.InvestorName,
> coalesce((select AcctName.acctnames from acctname where
> acctname.invheadref = invhead.invheadref), '') as AccountName
> from
> InvestorRelate
> inner join invhead on
> InvestorRelate.InvestorIDRef = InvHead.IDRef

The only thing you should be aware of is that with COALESCE the sub-select
is used twice, but because they are executed directly behind each other the
performance decrease is probably very small. At least it is something to
keep in your mind.

> The original SQL was
>
> CREATE VIEW "IDENTTIYLIST" (
> "INVESTORNAME",
> "ACCOUNTNAME"
> ) AS
> select
> InvestorRelate.InvestorName,
> coalesce(AcctName.acctnames, '') as AccountName
> from
> InvestorRelate
> inner join invhead on
> InvestorRelate.InvestorIDRef = InvHead.IDRef
> left outer join AcctName on
> AcctName where acctname.invheadref = invhead.invheadref
>
> The AcctName table is related to the InvHead table in a 0..1
> relationship. I understand that if the relationship was a 0..N or
> 1..N this approach wouldn't work.

Indeed :)

> I notice that when I use the view, if I don't use the AccountName
> field it doesn't do the select on the acctname table which helps
> performance.

Yep, correct and that could be very helpful.

> On a more general issue I know that the optimizer doesn't take into
> account left outer joins but I wondered why it couldn't treat left
> outer joins as inner joins when working out the streams (I think that
> was the term Arno used) but then ignore any stream that would have a
> left outer join before an inner join.

With a X LEFT JOIN Y you tell explicit that that Y steam is depended on the
X stream and you'll always need everyting from X.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info