Select with view join takes too long

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/03/28
Message-ID: <859567729.14264@dejanews.com>#1/1

I have created a view which sums units for a few rows within a large table. This view was created especially to use with selects for reports. However, we have found that when the view is used, the selects take much longer than expected.

When a select is written that creates its own sum rather than joining with the view, it runs many times faster.

If you have any ideas as to why, or even better, can show how to use the view efficiently, then please reply.

Here is the view:
  CREATE VIEW V1 as
    SELECT ID, TERM, SUM (UA) SUM_UA
      FROM COURSE GROUP BY ID, TERM; Note that Course has a non-unique index on ID and TERM.

Here is the select using the view:

  SELECT  MT.ID, V1.SUM_UA  FROM  V1, MASTER_TBL MT
    WHERE V1.ID   = MT.ID
      AND V1.TERM = MT.TERM
      AND MT.TERM = '19962';

Explain plan shows:
Query Plan

SELECT STATEMENT Cost = 1014
MERGE JOIN

      SORT JOIN
        VIEW  V1
          SORT GROUP BY
            TABLE ACCESS FULL COURSE  <--Note the full table scan
      SORT JOIN
        TABLE ACCESS FULL MASTER_TBL

Now, bypassing the view, and coding everything in the select:

  SELECT MT.ID, SUM(C.UA) SUM_UA FROM  MASTER_TBL MT, COURSE C
   WHERE C.ID    = MT.ID
     AND C.TERM  = MT.TERM
     AND MT.TERM = '19962'

GROUP BY MT.ID; Explain plan shows:
Query Plan

SELECT STATEMENT Cost = 188
SORT GROUP BY
NESTED LOOPS

      TABLE ACCESS FULL MASTER_TBL
      TABLE ACCESS BY ROWID COURSE
        INDEX RANGE SCAN I_COURSE

I am not familiar with reading "Explain Plan" output, but I have the feeling that the explanation can be found by comparing the two. I have reduced the complexity in the examples above to simplify things, but they still illustrate the problem. I do hope someone can help with this!

Thanks in advance,
Steve Cosner

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

Received on Fri Mar 28 1997 - 00:00:00 CST