Re: Strange performance related question

From: <syakobson_at_erols.com>
Date: 1997/10/28
Message-ID: <878095048.10838@dejanews.com>#1/1

Did you check explain plan for both cases? I believe you have Merge Join versus Nested Loop issue plus possible ORDERED issue. For some reason (table statistics, or stock market crash) optimizer decided to join worker and department via Merge Join for View1 and via Nested Loop for View2. Merge join will first produce a little monster with 20,000 x 300 = 6,000,000 rows and only then apply WHERE department.id = worker.dept. Nested Loop will take one worker row at a time and join it with department rows with department.id = worker.dept. The difference in performance is obvious. Try to get rid of statistics and use hints (I assume you have no index on worker.dept):

CREATE VIEW depwork AS
SELECT /*+ ORDERED USE_NL(department worker) */

                worker.*,
                department.*
   FROM  worker,
                department

   WHERE department.id = worker.dept;

Note that hint ORDERED is very important when using USE_NL hint. Especially when there is no index on worker.dept (which was my assumption). I do not recall which way optimizer processes tables in FROM clause - left to right or right to left (and you should not rely on it. Oracle can change it in the future). By using ORDERED we force optimizer to process tables the same order they are listed in FROM clause. Optimizer will take one worker row at a time and join it to department table, which has an unique index on id. Therefore it will result in 20,000 unique index scans + 20,000 table scans by rowid (we are selecting all the columns and index has only column id) of department table + full scan of worker table . If we switch table order it will result in 300 full scans of worker table (again based on my assumption that worker.dept is not indexed) and full scan of department table. As you can see performance will be not too good. If worker.dept is indexed, switching table order will result in 300 range index scans (such index would be non UNIQUE) and 300 scans by rowid of worker table and a full scan of department table and give us comparable or even better performance.

Solomon Yakobson.

In article <01bce3b8$db4dfc20$0201dede_at_mum>,   "Michael G. Schneider" <mgs_software_at_compuserve.com> 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

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Tue Oct 28 1997 - 00:00:00 CST