I think my laptop has FB 1.5.1; here's the version when querying: WI-V6.3.1.4481 Firebird 1.5

TMP_DRUGQUANTITY (184)
Primary pointer page: 364, Index root page: 365
Data pages: 820, data page slots: 1631, average fill: 59%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 798
60 - 79% = 21
80 - 99% = 0

Index PK_DRUGQUANTITY (0)
Depth: 2, leaf buckets: 303, nodes: 115596
Average data length: 4.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 302
60 - 79% = 1
80 - 99% = 0

TMP_GPI (185)
Primary pointer page: 366, Index root page: 367
Data pages: 1099, data page slots: 2163, average fill: 70%
Fill distribution:
0 - 19% = 2
20 - 39% = 1
40 - 59% = 1
60 - 79% = 1095
80 - 99% = 0

Index I_TMP_GPI_RPL (2)
Depth: 2, leaf buckets: 127, nodes: 115596
Average data length: 2.00, total dup: 71051, max dup: 790
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 3
60 - 79% = 0
80 - 99% = 124

Index PK_TMP_GPI (0)
Depth: 2, leaf buckets: 305, nodes: 115596
Average data length: 4.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 2
40 - 59% = 303
60 - 79% = 0
80 - 99% = 0

TMP_LABELNAME (186)
Primary pointer page: 368, Index root page: 369
Data pages: 1044, data page slots: 1044, average fill: 68%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1044
80 - 99% = 0

Index PK_TMP_LABELNAME (0)
Depth: 2, leaf buckets: 239, nodes: 116075
Average data length: 4.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 124
60 - 79% = 77
80 - 99% = 38

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Thursday, August 04, 2005 3:53 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Plan question

OK, then I have no idea why it chose the plan it did (other than possibly duplicate indexes). Tell us which version of Firebird (or
InterBase) you use and then hopefully Arno, Ann or someone else can give an explanation. I for one would be interested in knowing why your simple query

SELECT *
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

resulted in this plan

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

rather than

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

Set

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




------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12hpdkap1/M=362131.6882499.7825260.1510227/D=groups/S=1705115386:TM/Y=YAHOO/EXP=1123149204/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</a>.</font> --------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links