I gave it a try, but then realized that 1.5x doesn't support this.
We'll be upgrading Firebird in a couple of months, though :)

________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Thursday, June 12, 2008 5:03 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Left join plans



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





Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.