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.
The DDL for the tables on both versions of Firebird are:
CREATE TABLE msttbl (
col01Int DM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT
NULL CHECK (value > 0) */,
col02SmInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT
NULL CHECK (value > 0) */,
col03SMInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT
NULL CHECK (value > 0) */,
col04VarCh45 DM_NOMESNNULL /* DM_NOMESNNULL = VARCHAR(45) NOT NULL */
);
ALTER TABLE msttbl ADD CONSTRAINT PK_msttbl PRIMARY KEY (col01Int, col02Int,
col03Int);
CREATE INDEX IXmsttbl_col04VarCh45 ON msttbl (col04VarCh45);
CREATE TABLE dettbl (
col01Int DM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT
NULL CHECK (value > 0) */,
col02SmInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT
NULL CHECK (value > 0) */,
col03SMInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT
NULL CHECK (value > 0) */,
col04Int DM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT
NULL CHECK (value > 0) */,
ColDetSmIntFlag DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT
NULL CHECK (value > 0) */
);
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?
TIA
Andrew
