Hi,

> Maybe someone can explain this to me.
>
> When I do an UPDATE with a sub SELECT, the Query Plan shows a table
> scan.
>
> However, if I change the IN clause to use explicit values (say some
> comma delimited integers), it uses my index.
>
> Something like:
>
> UPDATE Foo
> SET val=0
> WHERE id IN ( 1,2,3,4 );

> This shows the query using the index on Foo.id.

Correct, A index that has Foo.id (as first or only field) is being used.

> UPDATE Foo
> SET val=0
> WHERE id IN ( SELECT Bar.id FROM Bar );
>
> This shows the query performing a table scan on Foo.

Correct, see it as : that it internally is converted to :
EXISTS(SELECT Bar.id FROM Bar WHERE Bar.id = Foo.id)

This means Foo will be NATURAL and for Bar a index is used.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81