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

Thanks in advance
Andrew Monteith