>Hi All
>
>I want to hear that the "IDX_TEST1" is not used.
>
>IDX_TEST1 seems to be not used for Query3 and Query4, though both of IDX_TEST1
>and Index in SUBQUERYTABLE (RDB$PRIMARY14) are used in MS SQL Server.
>Doesn't work IDX_TEST1 in queries like Query3 and Query4 in Firebird?
>
>[Table Definition]
>CREATE TABLE TEST
>(
>ID Bigint NOT NULL,
>ID2 Bigint NOT NULL,
>PRIMARY KEY (ID)

<);

>CREATE INDEX IDX_TEST1 ON TEST (ID2);
>CREATE TABLE SUBQUERYTABLE
>(ID2 Bigint NOT NULL,
>PRIMARY KEY (ID2));
>
>[Query3]
>select id from TEST T where exists(select * from SUBQUERYTABLE sq where sq.id2 = t.id2)
>[Query plan] PLAN (SQ INDEX (RDB$PRIMARY14)) PLAN (T NATURAL) Script execution finished.
>
>[Query4]
>select t.ID,t.ID2 from TEST T
>where t.ID2 in (select id2 from SUBQUERYTABLE sb where t.id2 = sb.ID2)
>[Query plan] PLAN (SB INDEX (RDB$PRIMARY14)) PLAN (T NATURAL) Script execution finished.

If every record of a table has to be examined, NATURAL is the quickest way to do this. If it somehow can be limited, then using an index may be far better. The only criteria in your WHERE clauses are subselects (both Query3 and Query4). In my understanding (far simpler than the way Firebird does things), the only sensible ways to reach your results are to either go through every record of TEST and see if those are in SUBQUERYTABLE or go through every record of SUBQUERYTABLE and see if those are in TEST. Hence, NATURAL is needed either for TEST or SUBQUERYTABLE.

If your query had been something like:

select id from TEST T
where T.id2 between 15 and 30
and exists(select * from SUBQUERYTABLE sq where sq.id2 = t.id2)

then an index for both tables could be used, but as long as the subselect is the only part of your WHERE clause, there has to be at least one NATURAL (unless you want to slow down the query or add ORDER BY t.ID2; I've no clue how MS SQL utilizes both indexes).

Set