Hi Rick!

First of all, I am a bit confused so what I write may or may not be
correct.

Do you have any foreign keys or additional indexes defined on NDC in
either of the tables? I know duplicate indexes can confuse Firebird.

What happens in your first plan, is that Firebird does what you tell
it to do. An alternative plan would probably be:

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

but the number of records in each table may make it believe that going
NATURAL is a better choice (is G much smaller that DQ?).

(By changing to "JOIN TMP_GPI g ON g.NDC = dq.NDC+0" you would
probably get the plan above?)

Without the OR, Firebird sees that it can replace
"WHERE dq.ndc=173069502" with "WHERE g.ndc=173069502". I have no idea
why it understands this when failing to see it can do the same thing
when an OR intervenes (experts like Arno will know, maybe the required
analysis would be too time consuming in more complex cases).

But as I say, I'm confused and have never experienced something
similar. Maybe because G is smaller than DQ, and I would normally use
G.NDC rather than DQ.NDC in similar cases? If G is bigger than DQ, I
have no idea why Firebird chooses the plan it chooses.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
> 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))