Hmm, I'm confused Michael. Does the TRANS_VAREFRVSTRNR index contain
both VAREFRVSTRNR and AFDELING_ID? The reason for my suspicion is that
the TRANSAFD1 index isn't used in your first query, but present in the
second.

Your original query indicates that Transaktioner is the table that
should be considered first in your plan, then Varer and finally
Leverandoerer, but that the optimizer somehow fails to see this. I'd
start by doing just a small change:

FROM Transaktioner
Inner Join Varer on (Varer.PLU_NR=Transaktioner.VAREFRVSTRNR+0)
Left Outer Join Leverandoerer jTabel on

to tell the optimizer that using the index on PLU_NR is better than
trying to use any index on VAREFRVSTRNR. Hopefully, this will be enough
for Fb 1.5 to give you a decent plan without changing the join order.

HTH og God jul (merry Christmas),
Set

Michael Vilhelmsen wrote:

> Hi
>
> We have a FB 1.5.3 running on a Win32 machine.
>
> When doing this query:
>
> SELECT ...
> FROM Transaktioner
> Inner Join Varer on (Varer.PLU_NR=Transaktioner.VAREFRVSTRNR)
> Left Outer Join Leverandoerer jTabel on
> (jTabel.NAVN=Transaktioner.LEVNAVN)
> WHERE
> (Transaktioner.ART=0 OR Transaktioner.ART=1) AND
> (Transaktioner.DATO>:PDATO) AND
> (Transaktioner.DATO<:PDATOTIL) AND
> (Transaktioner.AFDELING_ID=:PAfdeling)
> ORDER BY
> jTabel.V509INDEX,
> Transaktioner.Dato,
> Transaktioner.BONNR;
>
> Gives this PLAN:
>
> PLAN SORT (JOIN (JOIN (VARER NATURAL,TRANSAKTIONER INDEX
> (TRANS_VAREFRVSTRNR,TRANS_DATO,TRANS_ART,TRANS_ART)),JTABEL INDEX
> (RDB$PRIMARY23)))
>
> Sinse our tabel VARER can contain quite some records and the table
> TRANSAKTIONER even more this query isn't running so good.
>
> But changing the join order like this (doing the left outer join first)
> it returns a diff. PLAN.
>
> SELECT ...
> FROM Transaktioner
> Left Outer Join Leverandoerer jTabel on
> (jTabel.NAVN=Transaktioner.LEVNAVN)
> Inner Join Varer on (Varer.PLU_NR=Transaktioner.VAREFRVSTRNR)
> WHERE
> (Transaktioner.ART=0 OR Transaktioner.ART=1) AND
> (Transaktioner.DATO>:PDATO) AND
> (Transaktioner.DATO<:PDATOTIL) AND
> (Transaktioner.AFDELING_ID=:PAfdeling)
> ORDER BY
> jTabel.V509INDEX,
> Transaktioner.Dato,
> Transaktioner.BONNR;
>
> PLAN:
> PLAN SORT (JOIN (JOIN (TRANSAKTIONER INDEX
> (TRANSAFD1,TRANS_DATO,TRANS_ART,TRANS_ART),JTABEL INDEX
> (RDB$PRIMARY23)),VARER INDEX (RDB$PRIMARY26)))
>
> Now FB uses the Primary Key.
>
> The problem isn't present in FB 2.0.
>
> What I really don't get is, that its the INNER JOIN that suddenly uses
> no primary key.
> Not the left outer join - which I would have presummed..
>
> Michael