--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

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

No - The TRANS_VAREFRVSTRNR is an index on VAREFRVSTRNR alone.

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

I don't mind changing to join order.
Its only used in 1 place.

I just don't understand that FB1.5 does it it differently whether I I
use Inner Join first or last....

And God jul og godt nytår til dig også ;-)

Michael

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