> On May 15, 2015, at 9:02 AM, brucedickin...@wp.pl [firebird-support] > <firebird-support@yahoogroups.com> wrote:
> SELECT * FROM > TABLE_2 T2 > INNER JOIN > TABLE_1 T1 > ON > T2.TABLE_1_ID = T1.ID > > After executing this query I am getting such plan: > > PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) > > > Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am > getting error when i try to enforce it with: > PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) > > > There's nothing to look up in the primary key index of T1 - indexes are used to find records withh specific values in the key fields. If you added another condition like T1.ID between 5 and 10, you'd get the plan you expect. Good luck, Ann > here is full example: > > > CREATE TABLE TABLE_1 > ( > ID INTEGER NOT NULL, > NAME VARCHAR(32), > CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) > ); > > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON TABLE_1 TO SYSDBA WITH GRANT OPTION; > > CREATE TABLE TABLE_2 > ( > ID INTEGER NOT NULL, > TABLE_1_ID INTEGER > ); > > ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 > FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON TABLE_2 TO SYSDBA WITH GRANT OPTION; > > > INSERT INTO TABLE_1 VALUES(1, 'V_1'); > INSERT INTO TABLE_1 VALUES(2, 'V_2'); > INSERT INTO TABLE_1 VALUES(3, 'V_3'); > INSERT INTO TABLE_1 VALUES(4, 'V_4'); > INSERT INTO TABLE_1 VALUES(5, 'V_5'); > > INSERT INTO TABLE_2 VALUES(1, 1); > INSERT INTO TABLE_2 VALUES(2, 1); > INSERT INTO TABLE_2 VALUES(3, 2); > INSERT INTO TABLE_2 VALUES(4, 2); > INSERT INTO TABLE_2 VALUES(5, 3); > > SELECT * FROM > > TABLE_2 T2 > INNER JOIN > TABLE_1 T1 > ON > T2.TABLE_1_ID = T1.ID > > After executing this query I am getting such plan: > > > > PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) > > > > Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am > getting error when i try to enforce it with: > PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) > > > > Thanks for any clues. > > > > > >
