--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<firebird@a...> wrote:

> 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

For this SQL it uses the first of the 3 you supplied above.

Bernard

>
> ?
>
> 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