Hi Rick!

I don't remember which Firebird version you use, but if you're on
Firebird 2.1, have you tried something like:

select
*
from
V_CLAIM_REPLENISHGRP crg
join mddb_drug d
on d.productid = crg.productid
join crm_pharm p
on p.NABPNUM = crg.PHARMACY
left join (select * from
RPL_CLM_POBILL_ITM cpbi
join RPL_POBILL_ITM pbi
on pbi.id = cpbi.POBILL_ITM
join mddb_drug d2
on d2.productid = pbi.ndc) cbpi2 on cpbi2.CLAIM_ID = crg.CLAIM_ID
where
crg.GRANTEE = ? and
d.LABELER = ? and
d.gpi = ?

This may or may not be a quick solution, try it if you use an
appropriate Firebird version.

HTH,
Set

Rick Debay wrote:

> I would expect the first query to be the best, as within the parenthesis
> I explicitly state that the three tables can use an inner join.
> Instead, the looser second query where all items use a left join
> performs much better, and the plan reflects this by only having one
> NATURAL scan. The first query has a NATURAL scan for each of the two
> table groupings.
> Does the optimizer know how to handle parenthesized groupings, or is it
> not a special case?
>
> select
> *
> from
> V_CLAIM_REPLENISHGRP crg
> join mddb_drug d
> on d.productid = crg.productid
> join crm_pharm p
> on p.NABPNUM = crg.PHARMACY
> left join (
> RPL_CLM_POBILL_ITM cpbi
> join RPL_POBILL_ITM pbi
> on pbi.id = cpbi.POBILL_ITM
> join mddb_drug d2
> on d2.productid = pbi.ndc
> ) on cpbi.CLAIM_ID = crg.CLAIM_ID
> where
> crg.GRANTEE = ? and
> d.LABELER = ? and
> d.gpi = ?
>
> PLAN JOIN (JOIN (P NATURAL,CRG PHGR INDEX (FK_PHARMRPLGRP_PHARM),CRG
> RPLGRP INDEX (PK_CRM_RPL_GRP),CRG CHC INDEX (FK_CRMCHC_CRMGRANTEE),CRG
> PBM INDEX (FK_PBMCLAIM_ACTCHCPHARMSUBACCT),D INDEX (PK_MDDB_DRUG)),JOIN
> (PBI NATURAL,D2 INDEX (PK_MDDB_DRUG),CPBI INDEX (U_RPLCLMPOBILLITM_PK)))
>
> select
> *
> from
> V_CLAIM_REPLENISHGRP crg
> join mddb_drug d
> on d.productid = crg.productid
> join crm_pharm p
> on p.NABPNUM = crg.PHARMACY
> left join RPL_CLM_POBILL_ITM cpbi
> on cpbi.CLAIM_ID = crg.CLAIM_ID
> left join RPL_POBILL_ITM pbi
> on pbi.id = cpbi.POBILL_ITM
> left join rpl_pobill pb
> on pb.id = pbi.invoice
> left join mddb_drug d2
> on d2.productid = pbi.ndc
> where
> crg.GRANTEE = ? and
> d.LABELER = ? and
> d.gpi = ?
>
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (P NATURAL,CRG PHGR INDEX
> (FK_PHARMRPLGRP_PHARM),CRG RPLGRP INDEX (PK_CRM_RPL_GRP),CRG CHC INDEX
> (FK_CRMCHC_CRMGRANTEE),CRG PBM INDEX (FK_PBMCLAIM_ACTCHCPHARMSUBACCT),D
> INDEX (PK_MDDB_DRUG)),CPBI INDEX (FK_RPLCLMPOBILLITM_CLAIM)),PBI INDEX
> (PK_RPL_POBILL_ITM)),PB INDEX (PK_RPL_POBILL)),D2 INDEX (PK_MDDB_DRUG))