NDC is the primary key in each table. Given these queries, why would
the optimizer choose to walk all of TMP_GPI when only two records are
requested, and identified by the primary key? If I ask for only one,
then it uses the index. Changing the WHERE clause to search on
TMP_GPI.NDC uses the primary key. Moving the LEFT JOIN off
TMP_DRUGQUANTITY onto TMP_GPI has no effect.

SELECT
dq.NDC, dq.QTY,
g.GPI, g.TEE, g.UNIT_DOSE_USE, g.PKG_SZ, g.STATUS,
ln.LABELNAME
FROM
TMP_DRUGQUANTITY dq
JOIN TMP_GPI g
ON g.NDC = dq.NDC
LEFT JOIN TMP_LABELNAME ln
ON ln.NDC = dq.NDC
WHERE
dq.ndc=173069502 or dq.ndc=173069500

PLAN JOIN (JOIN (G NATURAL,DQ INDEX
(PK_DRUGQUANTITY,PK_DRUGQUANTITY,PK_DRUGQUANTITY)),LN INDEX
(PK_TMP_LABELNAME))

SELECT
dq.NDC, dq.QTY,
g.GPI, g.TEE, g.UNIT_DOSE_USE, g.PKG_SZ, g.STATUS,
ln.LABELNAME
FROM
TMP_DRUGQUANTITY dq
JOIN TMP_GPI g
ON g.NDC = dq.NDC
LEFT JOIN TMP_LABELNAME ln
ON ln.NDC = dq.NDC
WHERE
dq.ndc=173069502

PLAN JOIN (JOIN (G INDEX (PK_TMP_GPI),DQ INDEX (PK_DRUGQUANTITY)),LN
INDEX (PK_TMP_LABELNAME))

SELECT
dq.NDC, dq.QTY,
g.GPI, g.TEE, g.UNIT_DOSE_USE, g.PKG_SZ, g.STATUS,
ln.LABELNAME
FROM
TMP_DRUGQUANTITY dq
JOIN TMP_GPI g
ON dq.NDC = g.NDC
LEFT JOIN TMP_LABELNAME ln
ON ln.NDC = g.NDC
WHERE
g.ndc=173069502 or g.ndc=173069500 or g.ndc=173069600 or
g.ndc=173069700 or g.ndc=173069602

PLAN JOIN (JOIN (G INDEX
(PK_TMP_GPI,PK_TMP_GPI,PK_TMP_GPI,PK_TMP_GPI,PK_TMP_GPI),DQ INDEX
(PK_DRUGQUANTITY)),LN INDEX (PK_TMP_LABELNAME))