Is there another way to state the update query so that it doesn't
perform a table scan for Foo?


-----Original Message-----
From: Arno Brinkman [mailto:firebird@...]
Sent: Wednesday, July 30, 2003 12:23 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Update with a Sub Select


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



Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.