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

> Everytime I issue a query that has 3 or more table in it, and each
> table is joined on index fields with high selectivity (mainly
> primary key fields), the first table in the plan ALWAYS does a table
> scan ('NATURAL' in PLAN lingo).
>
> And when I try to create my own plan, and just add the index where
> it is doing a NATURAL, I get an error stating that the 'index
> cannot be used in the specified plan'.
>
> this plan works:
> PLAN JOIN (A NATURAL,B INDEX (PRODS_VSTYLE),C INDEX (AI_SKU));
>
> this also works:
> PLAN JOIN (B NATURAL, A INDEX(PKX_PRODUCT),C INDEX (AI_SKU));
>
> but this won't work:
> PLAN JOIN (B INDEX (PRODS_VSTYLE), A INDEX(PKX_PRODUCT),C INDEX
> (AI_SKU));
>
> this won't work either:
> PLAN JOIN (A INDEX(PKX_PRODUCT), B INDEX (PRODS_VSTYLE) ,C INDEX
> (AI_SKU));
>
> Is this another bug in the optimizer?

No, it's a bug in the programmer ;oO

I say it is a bug in the programmer, because you're rarely interested
in all records - one of the things you'll hopefully learn when you get
more used to client/server databases is the benefits of using a WHERE
clause to limit the number of returned rows as much as possible.

The fastest way to access an entire table, is to do a natural scan,
and if you do not have any WHERE clause, there is nothing faster for
the first table (A) in the plan than to go natural. For the other
tables, it is only interested in one or a few records in B for each
row in A, and normally benefit from indexes that is defined in the
JOIN clause.

HTH,
Set