Hi!

Try this:

with ids as (select '123' id from RDB$DATABASE )
select a.*
from AnyTable a
inner join ids i on i.id = a.id

András


From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Friday, March 1, 2019 1:11 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query optimization when using sub query with in operator



The following problem applies to Firebird 3.0. Cant test right now on
older versions.

Suppose we have a table called AnyTable with column ID as primary key.

The query

select * from AnyTable where ID = '123'

produce the following plan...

PLAN (AnyTable INDEX (PK_AnyTable))

Now a second query, semantically identical to the first...

select * from AnyTable where ID in ( '123' )

will produce the same plan:

PLAN (AnyTable INDEX (PK_AnyTable))

However that third query, also semantically identical to the first...

select * from AnyTable where ID in ( select '123' from RDB$DATABASE )

will produce a really bad plan:

PLAN (RDB$DATABASE NATURAL)
PLAN (AnyTable NATURAL)

If I try to force the primary key index, an error occurs:

SQL Message : -284
index cannot be used in the specified plan

Are there any way to force index usage on a situation like that?

Ivan Cruz.



__________ Information from ESET Mail Security, version of virus signature database 18952 (20190228) __________

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]