Hi,

> In a simple 2 table join I agree but in a 3 table case like below,
> where InvHead.InvHeadRef is a unique value it does a table scan on
> IdentityName (100,000 records). The InvHead.IDRef index has a
> selectivity of about 2. I know that if I switch the InvHead.IDRef
> index off by adding || '' it will then use InvHead first and join into
> identityname and account name as I would expect but it would be
> better if the optimizer was able to handle such cases. What I find
> interesting is that if I change the left outer join to an inner join
> it does choose the plan I want. This is why I though if you worked
> out all plans assuming all inner joins and then removed the invalid
> options that this would return you the correct plan.
>
> select identityname.idref
> from identityname
> inner join InvHead on identityname.idref = invhead.idref
> left join acctname on invhead.invheadref = acctname.invheadref
> where invhead.invheadref = 25809

I don't know if i understand you're problem exactly, but above PLAN can only
be :

PLAN JOIN(JOIN(identityname NATURAL, InvHead INDEX (...)), acctname INDEX
(...))
or
PLAN JOIN(JOIN(InvHead NATURAL, identityname INDEX (...)), acctname INDEX
(...))
or
PLAN JOIN(JOIN(InvHead INDEX (...), identityname INDEX (...)), acctname
INDEX (...))

Is the last PLAN a option, but the optimizer doesn't choose it?
The optimizer can decide the order between InvHead and identityname thus
that shouldn't be a problem. Are your statistics up-to-date?

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