Then the solution is to prevent using FK_CUSTOMERID by simply using

from Orders inner join Customers on
Orders.CustomerId+0=Customers.CustomerId

as I suggested earlier. That leaves the optimizer with the choice between

PLAN JOIN (CUSTOMERS INDEX (IDX_ORDERS_DATE), CUSTOMERS INDEX
(RDB$PRIMARY8))

and

PLAN JOIN (CUSTOMERS NATURAL, ORDERS INDEX (IDX_ORDER_DATE))

(theoretically it also has the lousy choice of doing it all NATURAL)

In choosing between these two plans, I think Firebird will always choose
the first.

This should work,
Set

kokok_kokok wrote:

> The optimal plan for
> select sum(Orders.units) from Orders inner join Customers on
> Orders.CustomerId=Customers.CustomerId
> where Orders.OrderDate between `2007/1/1' and `2007/12/31'
> is:
>
> PLAN JOIN (CUSTOMERS INDEX (IDX_ORDERS_DATE), CUSTOMERS INDEX
> (RDB$PRIMARY8))
>
> The suboptimal plan for
> select sum(Orders.units) from Orders inner join Customers on
> Orders.CustomerId=Customers.CustomerId
> where Orders.OrderDate between `2007/1/1' and `2007/12/31'
> and Customers.EntryDate between `2006/1/1' and `2006/12/31'
> and Customers.CustomerType=1
> is:
>
> PLAN JOIN (CUSTOMERS NATURAL, ORDERS INDEX (FK_CUSTOMERID,
> IDX_ORDER_DATE))
>
> IDX_ORDER_DATE: Order date of Orders table
> FK_CUSTOMERID: CustomerID in Orders table.
>
> Thanks