Hello, All!

Just a question:

employee.gdb

select p.*, e.* from
employee_project p
left join employee e
on p.emp_no = e.emp_no
order by p.emp_no

PLAN SORT (JOIN (P NATURAL,E INDEX (RDB$PRIMARY7)))

Why optimizer can't use index on p.emp_no to order records?
It is a left join, and all employee_project records will be in
result.

More tests shows that no index used to order records for
implicit or explicit join:

select p.*, e.* from
employee_project p
join employee e
on p.emp_no = e.emp_no
order by p.emp_no
PLAN SORT (JOIN (E NATURAL,P INDEX (RDB$PRIMARY14)))

select p.*, e.* from
employee_project p, employee e
where p.emp_no = e.emp_no
order by p.emp_no
PLAN SORT (JOIN (E NATURAL,P INDEX (RDB$PRIMARY14)))

Firebird 1.0.0.338 for Windows.

--
Dmitry Kuzmenko, Epsylon Technologies.