Re: Select with view join takes too long

This is a neat illustration of a question raised very recently on the efficiencu of views.
Using the view joined to the table, the predicate MT.TERM = '19962' can only be applied to MASTER_TBL and all rows defined by the view must be returned before the join can take place. (The transitivity of predicates from one table to a joined table does not apply to complex views)

However, there appears to be an additional problem here. The plan of the expanded query shows an index (I_COURSE) being used which is not used in the plan of the view-join query. I would guess that this index is on COURSE(ID). If this is the case, then an index on COURSE(ID,TERM) and a predicate of V1.TERM = '19962' (not MT.TERM = '19962') would enable the view to be evaluated for the single required group before the join to MASTER_TBL is executed.

Hope this helps.

Chrysalis.

Steve Cosner wrote:
>
> 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