I think the answer is that it's faster for interbase to access the records
in physical order and then sort the results than to access the records in
emp_no order, which may involve jumping all over the place as each record is
fetched.

Dan

-----Original Message-----
From: Dmitry Kuzmenko [mailto:dima@...]
Sent: Wednesday, September 19, 2001 3:09 AM
To: ib-architect@yahoogroups.com
Subject: [IB-Architect] order by not using index


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.

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/