Re: Strange performance related question

Hi Michael,

Try explain plan. It seems that department.id and worker.dept both have indexes and Oracle choose wrong one in the first case. You can choose the
right one simply placing equivalent expression instead of column. This expression disables index usage for the column. Your select ... from dual
is one of the possible expressions. Another one is:

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

It performs better because of full scan on worker table and totally cached in memory access to department table. Your first definition may use full index scan on worker. It needs random disk access which is slower then serial one.

                                      Andrew Protasov
                                      protasov_at_percombank.kiev.ua

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 Wed Oct 29 1997 - 00:00:00 CST