Re: Query plan and sub-queries

Steve Heaven wrote:
>
> When the WHERE clause includes a sub query the query plan seems to ignore
> indexes.

This is a FAQ:

4.23) Why are my subqueries using IN so slow?

Currently, we join subqueries to outer queries by sequential
scanning the result of the subquery for each row of the outer
query. A workaround is to replace IN with EXISTS:

        SELECT *
        FROM tab
        WHERE col1 IN (SELECT col2 FROM TAB2)

to:

        SELECT *
        FROM tab
        WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)

We hope to fix this limitation in a future release.

Hope that helps,

Mike Mascari