WHAT??? That's not a normal choice for Firebird, I'm at least used to
Firebird being convinced when adding +0. However, it is still possible
to be even more persuasive:

select sum(Orders.units) from Orders left join Customers on
Orders.CustomerId=Customers.CustomerId
where Orders.OrderDate between `2007/1/1' and `2007/12/31'

+0 is a strong hint to put ORDERS first in the plan, using LEFT JOIN
leaves the optimizer without any choice at all. If there may be orders
without any customer, you may have to add 'and customer.customerid is
not null' to your WHERE clause.

This works,
Set

kokok_kokok wrote:

> I have tried the Orders.CustomerId+0=Customers.CustomerId proposal but
> regrettably I get the same natural plan: PLAN JOIN (CUSTOMERS NATURAL,
> ORDERS INDEX (IDX_ORDER_DATE)). in fact, it is worst because now I
> never get the optimal plan, including the initial statement:
>
> 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'
>
> returns a optimal plan, but
>
>
> select sum(Orders.units) from Orders inner join Customers on
> Orders.CustomerId+0=Customers.CustomerId
> where Orders.OrderDate between `2007/1/1' and `2007/12/31'