Subject Why optimizer uses A.NATURAL
Author Roland Turcan
Hello IB-Support!

I have this SQL statement:

SELECT A.KURZ_LIST,A.KOMBINOVAT
FROM KOMBINACIE A
LEFT JOIN TIKETY2 B ON (B.KURZ_LIST=A.KURZ_LIST)
WHERE (A.KURZ_LIST=19954 OR B.TIKET=9251001)
;

Plan:
PLAN JOIN (A NATURAL,B INDEX (IDX_TIKETY2_KURZ_LIST))

DDLs:

CREATE TABLE TIKETY2 (JEDIN INTEGER,KURZ_LIST INTEGER,TIKET INTEGER, ...);
ALTER TABLE TIKETY2 ADD CONSTRAINT PK_TIKETY2 PRIMARY KEY (JEDIN);
CREATE INDEX IDX_TIKETY2_KURZ_LIST ON TIKETY2 (KURZ_LIST);
CREATE UNIQUE INDEX TIKETY2_STAVKA ON TIKETY2 (TIKET, KURZ_LIST);

CREATE TABLE KOMBINACIE (JEDIN INTEGER NOT NULL,KOMBINOVAT INTEGER,KURZ_LIST INTEGER);
ALTER TABLE KOMBINACIE ADD CONSTRAINT PK_KOMBINACIE PRIMARY KEY (JEDIN);
CREATE INDEX IDX_KOMBINACIE_KOMBINOVAT ON KOMBINACIE (KOMBINOVAT);
CREATE INDEX IDX_KOMBINACIE_KURZ_LIST ON KOMBINACIE (KURZ_LIST);

Record count: TIKETY2>90000 and KOMBINACIE>9000

But execute time is too slow, but if I use (this statement not return
right result set):

SELECT A.KURZ_LIST,A.KOMBINOVAT
FROM KOMBINACIE A
LEFT JOIN TIKETY2 B ON (B.KURZ_LIST=A.KURZ_LIST)
WHERE (A.KURZ_LIST=19954 AND B.TIKET=9251001)
;

is plan PLAN JOIN (A INDEX (IDX_KOMBINACIE_KURZ_LIST),B INDEX (IDX_TIKETY2_KURZ_LIST))

and result set is returned immediately.

How to optimize this ?
--
Best regards, TRoland
http://www.rotursoft.sk