Subject | Why optimizer uses A.NATURAL |
---|---|
Author | Roland Turcan |
Post date | 2003-04-11T14:06:48Z |
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
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