>Hi all,
>
>I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries that aren't planning the way they should.
>
>I have this query:
>
>Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45
>from dettbl b
> Inner Join msttbl a on (a.col01Int = i.col01Int and
> a.col02SmInt = i.col02SmInt and
> a.col03SmInt = i.col03SmInt)
>where
> b.ColDetSmIntFlag = 1
>Order by a.col04VarCh45
>
>on v1.56 I get this plan:
>PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl))))
>
>and on v2.54 I get
>PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl))))
>
>that is killing my app. The tables in question have 450K+ and 800K+ rows.
>
>ALTER TABLE msttbl ADD CONSTRAINT PK_msttbl PRIMARY KEY (col01Int, col02Int, col03Int);
>CREATE INDEX IXmsttbl_col04VarCh45 ON msttbl (col04VarCh45);
>ALTER TABLE dettbl ADD CONSTRAINT PK_dettbl PRIMARY KEY col01Int, col02Int, col03Int, col04Int);
>ALTER TABLE dettbl ADD CONSTRAINT FK_dettbl FOREIGN KEY (col01Int, col02Int, col03Int) REFERENCES msttbl (col01Int, col02Int, col03Int);
>CREATE INDEX IXColDetSmIntFlag ON dettbl (ColDetSmIntFlag, col04Int);
>
>I've been changing the query and trying to change indexes to no success.
>Is there a way to force FB 2.54 to chose the right indexes?

Hi Andrew!

Forcing a correct plan is not always benefitial or possible, but it is normally quite easy to prevent a bad plan from being used. I'd recommend you to try:

Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45
from dettbl b
Inner Join msttbl a on (a.col01Int = b.col01Int+0 and
a.col02SmInt = b.col02SmInt and
a.col03SmInt = b.col03SmInt)
where
b.ColDetSmIntFlag = 1
Order by a.col04VarCh45

The additional +0 should prevent FK_dettbl from being used, but there's no guarantee that another poor plan cannot be chosen, so this kind of modification sometimes needs a couple of attempts before you reach a good result.

HTH,
Set