Re: Strange performance related question

Hallo Michael,

check the sql statement "explain plan" for help. It lists an execution plan for a given sql statement like the base select of your view. I suppose you may find some full table scan actions with your first statement causing the large cost. As far as I remember, the lack of a non-unique index on a foreign key may result in full table scans (Check the Application Developer's Guide for more information). So I suggest you try an index on worker.dept (if you didn't do so already).

Hope this helps.
Stefan.

Michael G. Schneider wrote:
>
> 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