Hi Andrew!

Firebird occationally choose the wrong plan, but I don't think it matters
whether it is an inner or outer join. By using an outer join, you are
limiting Firebird in what indexes it can use. Changing queries to force
Firebird choose another plan can be quite useful whenever the chosen plan
is stupid, but I've never changed an inner join to an outer join to achieve
this.

You do not say anything about the tables and index selectivity, nor about
the time taken to retrieve the selected records - so it is impossible for
us to say which plan is the most efficient. If the table PurchRequisition
contained 100 records whereas the other two contained millions of records,
then the first plan would probably be the most efficient. I guess Firebirds
reasoning behind the plan is in some way associated with the table
PurchRequisition being smaller than PurchReqTags. Still, it normally isn't
very wise to choose no index for one table and two indexes for another
table rather than one index for both tables and we ought to try to discover
why Firebird is doing this. Please give us information about

Table definitions
Approximate number of records within each table
Index definitions (all of them for these three tables, not only the chosen
indexes)
Index selectivity

I see that Helen just answered your question pretty thoroughly, but I'll
still send off this reply in case there is something useful here that she
forgot.

Set

At 12:05 05.03.2003 +0000, you wrote:

>Hi
>
>Why does Firebird handle inner joins so inefficiently? Here is a
>example.
>
>select prt.Inc_Reqn,
> pr.PartNo, pr.PartParam, pr.Operation,
> pr.FulfilUnitsQty, pr.RequesterNo, jm.Division,
> pr.FulfilInc_Cost, pr.RequiredDte, pr.Destination
>from PurchReqTags prt
>inner join PurchRequisition pr on ( prt.Inc_Reqn = pr.Inc_Reqn )
>left join JobMast jm on ( jm .JobNo = pr.RequesterNo )
>where ( prt.Inc_ReqnB = :AiInc_ReqnB )
>and ( pr.Actioned = 'N' )
>and ( pr.FulFilSuppNo = :sSuppNo )
>
>Adapted plan:
>PLAN JOIN (JOIN (PR NATURAL,PRT INDEX
>(PURCHREQTAGS_BYREQNB,PURCHREQTAGS_BYREQN)),JM INDEX
>(JOBMAST_BYJOBNO))
>
>changed inner to left and get the following plan
>
>
>select prt.Inc_Reqn,
> pr.PartNo, pr.PartParam, pr.Operation,
> pr.FulfilUnitsQty, pr.RequesterNo, jm.Division,
> pr.FulfilInc_Cost, pr.RequiredDte, pr.Destination
>from PurchReqTags prt
>left join PurchRequisition pr on ( prt.Inc_Reqn = pr.Inc_Reqn )
>left join JobMast jm on ( jm .JobNo = pr.RequesterNo )
>where ( prt.Inc_ReqnB = :AiInc_ReqnB )
>and ( pr.Actioned = 'N' )
>and ( pr.FulFilSuppNo = :sSuppNo )
>
>PLAN JOIN (JOIN (PRT INDEX (PURCHREQTAGS_BYREQNB),PR INDEX
>(INTEG_117)),JM INDEX (JOBMAST_BYJOBNO))
>
>Why is the inner join not using the index. In my mind it should, or
>am I missing something???