WHERE ID IN ( <constants> ) is very different from WHERE ID IN (
<subselect> ). In your simple case the subselect is not correlated and only
needs to be executed once, but correlated queries are more complex. What I
mean by correlated can be seen in this example:
SELECT ...
FROM A
WHERE A.ID IN ( SELECT B.A_ID FROM B WHERE B.AField = A.AField )
In the above query, the subselect has to be executed once for every
possible row in A and hence, no index can be useful for A. Sure, it would
be good if the optimizer to a greater extent had understood the difference
between correlated and not correlated subqueries, but it doesn't.
Normally, it is easy to fix IN ( <subselect> ) queries by changing to
constants or EXISTS (sometimes even a simple JOIN), though I do admit that
I sometimes find it a bit annoying when I find the following query to be
time consuming (when A contains many millions of rows and B only a handful)
UPDATE A SET A.Field = ( SELECT B.Field FROM B WHERE A.ID = B.A_ID )
WHERE EXISTS( SELECT * FROM B WHERE A.ID = B.A_ID )
and have to rewrite it to
EXECUTE BLOCK AS
DECLARE VARIABLE ID Integer;
DECLARE VARIABLE MyField Varchar( 200 );
BEGIN
FOR SELECT A_ID, FIELD
FROM B
INTO :ID, :MyField DO
BEGIN
UPDATE A
SET FIELD = :MyField
WHERE ID = :ID;
END
END
Note that I'm still on Firebird 2.5 and don't know if 3.0 is better with
such queries or not.
HTH,
Set
Den fre. 1. mar. 2019 kl. 01:11 skrev Ivan C Cruz [email protected]
[firebird-support] <[email protected]>:
> 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.
>
>
> ------------------------------------
> Posted by: Ivan C Cruz <[email protected]>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
