Re: Joining two tables with sort

Not sure I'm following you but aren't you equating employee.refnr = department.refnr
in the Where clause? Otherwise, you're just getting the Cartesian Product, with meaningless rows. It might be quicker to put the employee table first in the From clause, so that in a Nested Loop for each employee you do an exact match via the primary key index for the matching department row (assuming there are a heck of a lot more employees than departments), then sorting with an Order By. But you'll have to test it and see if this is better than for each department doing a range scan for employees. From what you show below, it looks like a sort is still needed because you don't have an index on (refnr, seq). You might want to make this a unique key for the employee table. And use a unique key constraint! It's better than simply making a unique index.
Run an Explain Plan to see what you're really getting. If you have a nested loop with a full table scan inside, and that table has a lot of rows, you'll have a s l o w query.
-Dan Clamage

Rune Frøysa <runefr_at_gyda.ifi.uio.no> wrote in article <w6890vl941a.fsf_at_levding.ifi.uio.no>...
> I have some problems extracting sorted data from two tables. The
> tables looks somthing like this:
> department: refnr INT PRIMARY KEY, location VARCHAR(2000)
> employee:
> refnr INT REFERENCES department(refnr), seq INT KEY, name VARCHAR(2000)
> I want to extract a list of all departments, and all their employees.
> The departments should be ordered by "seq, name" for the employees,
> and the employees within a given department should be ordered by seq
> (thus, if two departments have a person named Doe with seq=1, we need
> to look at the person with seq=2 to determine which department to
> print first).
> Apparently the correct solution is not to join the two tables with a
> "SELECT ... FROM department d, emplyee e", as this seems to take an
> awfull lot of time (the tables are huge), and location would be
> returned multiple times. If required, modification of the database
> model is an alternative.
Received on Thu Oct 23 1997 - 00:00:00 CDT