Hi,

> > 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?
> >
>
> The last plan is an option and is used if acctname is an inner join
> but not if it is an outer join. I always thought that this shouldn't
> matter to how it chooses between the first two tables but it seems to.
> Statistics are completely up to date. I also did a restore last
> night so the indexes should be as balanced as possible. I am using
> firebird 1.5 on a linux box. Our index fields are mostly 10 character
> strings not intergers due to historical reasons if that makes and
> difference.

Shouldn't make a difference.

> The database is about 4gig. The following sqls do choose
> the last plan.
>
> select identityname.idref
> from identityname
> inner join InvHead on identityname.idref = invhead.idref
> inner join acctname on invhead.invheadref = acctname.invheadref
> where invhead.invheadref = 25809
>
> 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

What PLAN does the engine use with :

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

?

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://80.126.130.81