Franz J Fortuny wrote:

> --- In firebird-support@yahoogroups.com, Dmitry Yemanov <dimitr@...>
> wrote:
>
>> What is the reported plan? Do you have an index for tor_equiv.idsub1?
>>
>
> The reported plan uses indexes in every single relation, EXCEPT where
> it should be using the primary key of table SUBARTIS, which is a very
> huge table (126,000 rows or more).
>
> TOR_EQUIV.IDSUB1 AND ...IDSUB2 are BOTH columns that reference the
> primary key of SUBARTIS.IDSUBARTI.
>
> It scans 6 times all 126,000 rows in order to generate the 6 rows from
> SUBARTIS that the IDSUB2 column of TOR_EQUIV indicates. IDSUB2 is a
> column that has as foreign key the primary key of SUBARTIS.
>
> The index it is NOT USING is the one corresponding to table SUBARTIS
> (as reported by the PLAN). If I try to force it to use the index that
> it should use, it responds that IT CAN NOT USE THAT INDEX IN THAT
> QUERY. I don't see why not!
>
> FJFortuny
>

If you had read carefully what others have said, you would notice that
the query with IN (sub-select) is transformed internally to a correlated
EXISTS where the first part would use NATURAL.

Try the suggestion that Set gave to you, I am sure it will be a lot faster.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br