| Subject | RE: [firebird-support] Re: Plan question |
|---|---|
| Author | Rick Debay |
| Post date | 2005-08-03T14:21:18Z |
Normally the two tables have the same number of rows. In this case, as I didn't keep my development system up to date, g had more rows.
Since these are temporary tables until I integrate the Medispan database properly into our system, there are no foreign keys, just the primary key on NDC in each table.
Using g.NDC = dq.NDC+0, the plan is
PLAN JOIN (JOIN (DQ INDEX (PK_DRUGQUANTITY,PK_DRUGQUANTITY),G INDEX (PK_TMP_GPI)),LN INDEX (PK_TMP_LABELNAME))
If it had to use NATURAL for some reason, dq has smaller records so more would fit in a page, making it a better choice to step through (more of the table would remain in cache). I doubt that is one of the rules the optimzer uses, though.
This query is the basis for a view. I need the view in order to create an interface to the Medispan database, so I can recreate it properly without impacting the rest of the system. However, a query that takes over 200 times longer than it should has an impact of a different sort.
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Wednesday, August 03, 2005 3:46 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Plan question
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
Since these are temporary tables until I integrate the Medispan database properly into our system, there are no foreign keys, just the primary key on NDC in each table.
Using g.NDC = dq.NDC+0, the plan is
PLAN JOIN (JOIN (DQ INDEX (PK_DRUGQUANTITY,PK_DRUGQUANTITY),G INDEX (PK_TMP_GPI)),LN INDEX (PK_TMP_LABELNAME))
If it had to use NATURAL for some reason, dq has smaller records so more would fit in a page, making it a better choice to step through (more of the table would remain in cache). I doubt that is one of the rules the optimzer uses, though.
This query is the basis for a view. I need the view in order to create an interface to the Medispan database, so I can recreate it properly without impacting the rest of the system. However, a query that takes over 200 times longer than it should has an impact of a different sort.
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Wednesday, August 03, 2005 3:46 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Plan question
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