Subject Re: [firebird-support] Re: Subqueries and indexes
Author Daniel Rail

At August 10, 2006, 6:48 AM, yoav112003 wrote:

> Shouldn't the 'WHERE events.ROWID IN ...' clause be a hint to use the
> index on this field?

Because the optimizer would've most likely rewritten your query to
what Milan posted. Especially, since you said that both queries had
the same performance.

> As you can read in my reply to Milan, I tried to use a custom plan
> with this query which used this index, but I got an error message.

And, the error is valid, since the optimizer is using a query
structured like the one Milan posted, and an index on events.ROWID
can't be used.

> The query suggested by Milan and Helen have the same performance as
> the my query, my guess is because they too require a full table scan
> on the events table.

> It seems that the best solution is to use an INNER JOIN query if
> possible, but I am still curious to know the reason the ROWID index
> cannot be used.

If using INNER JOIN yields better performance, use it.

Best regards,
Daniel Rail
Senior Software Developer
ACCRA Consultants Inc. (
ACCRA Med Software Inc. (