"Carlos H. Cantu" wrote:

>
> Hi !
>
> Ok, everyone knows that the IB Optimizer has some flaws, but it is supposed
> to work well with simple stataments, so why this ??? :
>
> select a.*, f.forn_nome, f.forn_end
> from AQUISI a
> join forn f on f.cod_forn = a.cod_forn
>
> generated plan :
>
> PLAN JOIN (F NATURAL,A INDEX (RDB$FOREIGN57))
>
> f.cod_forn is the primary key of FORN. Why the optimizer isn't using the
> index in this simple join ????

You have written the equivalent of the following:

select e.*, department.department
from employee e
join department on department.dept_no = e.dept_no

which generates the following plan:

PLAN JOIN (DEPARTMENT NATURAL,E INDEX (RDB$FOREIGN8))

and requires 208 fetches to return 42 rows.

By adding this line:

where dept_no>=0

we force the use of the index in the plan

PLAN JOIN (DEPARTMENT INDEX (RDB$PRIMARY5),E INDEX (RDB$FOREIGN8))

at the cost of 239 fetches.

Common sense sometimes just isn't clever enough. In this case InterBase knows it
has to look at every row in DEPARTMENT, so why waste time using an index to do
it?


Paul
--

Paul Reeves
http://www.ibphoenix.com
taking InterBase further