> Is ther any index you'd recommend me to try to get F2 to use and index
> plan rather than natural scan? The SQL is generated from an OO
> framework, so reconstructing the query is not an option.
>
> I will change the system to do this in a completely different way later.
> The index I'm after is just a "quick fix" in the meantime.
>
> select F2.ECO_ID
> from F2
> where exists (
> select F1.ECO_ID
> from F1
> where F1.ECO_ID = 3504
> and F2.TELNR = F1.TELNR
> )
>
> PLAN (F1 INDEX (IX_PK_Företag))
> PLAN (F2 NATURAL)
>
> Btw, index on TELNR doesn't help.
>
> Regards,
> Kjell

Sorry Kjell, but there's no way to use an index for F2 with your query (there's no fixed value to look up). What I'd recommend if you use a Fb version that support CTEs, is to change the query to

WITH MYTELNR AS
(SELECT DISTINCT TELNR
From F WHERE ECOID = 3504)
SELECT F1.ECO_ID
FROM F F1
JOIN MYTELNR MTN on F1.TELNR = MTN.TELNR

The result should be identical to your original query, but both indexes on ECO_ID and TELNR can be utilized.

HTH,
Set