--- In firebird-support@yahoogroups.com, "donjules2k" wrote:

> Hi,

Hi Guilio!

> I have a query where I use an IN clause, and in the plan I notice it
> does an index lookup for each item in the IN clause. Why does it do
> this rather than a single lookup and pick out all items listed?

I also think of plans and indexes in this overly simplified manner (it
makes things easier to understand). But beneath I know that things are
much more complex, e.g. the indexes are not a sequential ordering of
records, but a tree that you climb up (I guess down is the more common
way of visualising this). IN is transated to OR and if the optimizers
says that it is better to jump down and start climbing anew rather
than go a bit back and then further up, then I simply choose to
believe them.

> Also, I notice It does a natural join on one of the tables yet the
> fields being used are indexed. What it the best way to analyse this
> and see how to build a better index?

If this is the first table in the plan, then this is normally no big
deal. It basically says that fields in the where clause for this table
are not indexed, or that the selectivity is so rotten that the
optimizer thinks it can get the return set quicker without using any
index.

In general, you get better answers by asking specific questions, i.e.
provide the query, plan and some information about tables and indexes.

Set