Strange performance related question

There are two tables 'worker' and 'department'. The 'worker' table has a field 'dept' referencing a 'department'. The 'department' table has a UNIQUE constraint on the 'id' field. There are about 20000 workers and 300 departments.

Now, I want to have all workers with their department, so I write a view

CREATE VIEW depwork AS
SELECT
   worker.*,
   department.*
FROM
   worker,
   department
WHERE
   department.id = worker.dept;

That's no big thing, and it works. However, it runs very slowly. Opening a cursor and walking through all rows takes about 300 seconds.

But by trial and error, I changed the view as follows:

CREATE VIEW depwork AS
SELECT
   worker.*,
   department.*
FROM
   worker,
   department
WHERE
   department.id = (SELECT worker.dept FROM DUAL);

Obviously it does the same, it just looks strange and more difficult. But believe it or not, this one is about 10 times faster.

Can anybody explain this situation and maybe even give a rule about how to write a view and what to avoid. Until now I thought, Oracle would have converted the above two views to the same internal representation. But obviously this is not so.

Michael G. Schneider

mgs_software_at_compuserve.com Received on Tue Oct 28 1997 - 00:00:00 CST