Subject Re: [firebird-support] Why index is not used in this query?
Author Tim Ward
Post date 2015-05-15T13:10:12Z
How about something along the lines of:

A table that small is going to fit into a single disk page. So a table scan involves reading one disk page.

Using the index would involve reading the index as well, which is a second disk page, so twice as slow.

?

(Other RDBMS which have a "covering index" concept can, for appropriate queries, use the index without reading the table at all, and might come to a different conclusion in such cases.)

On 15/05/2015 14:02, brucedickinson@... [firebird-support] wrote:
 

Hello,


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.




-- 
Tim Ward