Hi,

Firebird 1.5 optimiser chooses wrong plan on following query. I can't
figure
out why it does so or how to correct it. The only sollution i found
was to
change the first inner join by a left join or to use an explicited
plan.
Could someone help me out, i prefer not to use explicited plans. Are
explicited plans safe to use with regard to backup/restore DBs (Risc
of
changed Index Names)?

Filip Moons,
Thx.

SELECT
A.LYDGBKJ,
A.LYDGCODE,
B.LYDCID,
B.LYDCBKJORG,
B.LYDCDGTYPE,
B.LYDCDOCNR,
B.LYDCDATIMP,
C.LYACID,
C.LYACDCID,
C.LYACDETAILCDE,
C.LYACSEQNR,
C.LYACREKTYP,
C.LYACREK
FROM
LYDGB A
INNER JOIN
LYDOC B
on
A.LYDGBKJ = B.LYDCBKJ AND
A.LYDGCODE = B.LYDCDGB
INNER JOIN
LYACT C
ON
C.LYACDCID = B.LYDCID
WHERE
A.LYDGBKJ = :ABKJ AND
A.LYDGTYPE = :ATYPE
ORDER BY
A.LYDGCODE,
B.LYDCDOCNR,
C.LYACDETAILCDE,
C.LYACSEQNR

LYDGB
CONSTRAINT RDB$UNIQUE_LYDGB_BKJ_CODE UNIQUE (LYDGBKJ, LYDGCODE);
CONSTRAINT RDB$PRIMARY_LYDGB_ID PRIMARY KEY (LYDGID);
INDEX RDB$INDEX_LYDGB_BKJ_TYPE ON LYDGB (LYDGBKJ, LYDGTYPE);

LYDOC
CONSTRAINT RDB$UNIQUE_LYDOC_BKJ_DOCNR UNIQUE (LYDCBKJ, LYDCDGB,
LYDCDOCNR,
LYDCBKJORG);
CONSTRAINT RDB$PRIMARY_LYDOC_ID PRIMARY KEY (LYDCID);
UNIQUE DESCENDING INDEX RDB$INDEX_LYDOC_BKJ_DOCNR_DSC ON LYDOC
(LYDCBKJ,
LYDCDGB, LYDCDOCNR, LYDCBKJORG);

LYACT
CONSTRAINT RDB$UNIQUE_LYACT_ID_CDE_SEQ UNIQUE (LYACDCID,
LYACDETAILCDE,
LYACSEQNR);
CONSTRAINT RDB$PRIMARY_LYACT_ID PRIMARY KEY (LYACID);
INDEX RDB$INDEX_LYACT_BKJ_TPE_REK ON LYACT (LYACBKJ, LYACREKTYP,
LYACREK);
INDEX RDB$INDEX_LYACT_DCID ON LYACT (LYACDCID);
UNIQUE DESCENDING INDEX RDB$INDEX_LYACT_ID_DETCDE_SEQNR ON LYACT
(LYACDCID,
LYACDETAILCDE, LYACSEQNR);


Wrong Plan chosen when using '...FROM LYDGB A INNER JOIN LYDOC
B...':
Plan
PLAN SORT (JOIN (B INDEX (RDB$UNIQUE_LYDOC_BKJ_DOCNR),A INDEX
(RDB$UNIQUE_LYDGB_BKJ_CODE),C INDEX (RDB$INDEX_LYACT_DCID)))

Adapted Plan
PLAN SORT (JOIN (B INDEX (RDB$UNIQUE_LYDOC_BKJ_DOCNR),A INDEX
(RDB$UNIQUE_LYDGB_BKJ_CODE),C INDEX (RDB$INDEX_LYACT_DCID)))

Right Plan using '...FROM LYDGB A LEFT JOIN LYDOC B...':
Plan
PLAN SORT (JOIN (JOIN (A INDEX (RDB$INDEX_LYDGB_BKJ_TYPE),B INDEX
(RDB$UNIQUE_LYDOC_BKJ_DOCNR)),C INDEX (RDB$INDEX_LYACT_DCID)))

Adapted Plan
PLAN SORT (JOIN (JOIN (A INDEX (RDB$INDEX_LYDGB_BKJ_TYPE),B INDEX
(RDB$UNIQUE_LYDOC_BKJ_DOCNR)),C INDEX (RDB$INDEX_LYACT_DCID)))