For some reason, FB 2.0 does not find the optimal plan for the typical
Order – Customer relation.

For example:

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'

FB selects the optimal plan for the above statement, using OrderDate
as index.


Now I do:
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

In the above case, FB get a suboptimal plan, using a natural plan
instead of the OrderDate index. The execution takes 20 times more than
the first plan, being over 2 minutes in my scenario, totally
unaccepted by the user.

Why FB use the natural plan? how can I solve it?


I do not want to use the PLAN clause, the above it is only the summary
of a consult with 14 tables integrated in a view.

Thanks