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, brucedickin...@wp.pl [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
