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.