| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Select with view join takes too long
On Fri, 28 Mar 1997 10:51:42 -0600, stevec_at_zimmer.csufresno.edu (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.
>
>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';
>
>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;
>
>
These are two very different queries actually. The first one cannot just be ARBITRARILY unrolled into a join (the one using the view). The view MUST be materialized first and then joined to the Master table. The reason is the SUM() that is in the view. I can tell by your naming convention that This is a MASTER/DETAIL (each course record has exactly one master record) but the optimizer cannot. Therefore, it has to assume that more then one master record may appear for each COURSE. Therefore, it must do the SUM first and materialize the view. If it did not, and there was more then one Master/Course, we would get the wrong answer.
Since the predicates on the view are only expressed in terms of a JOIN, the view will be materialized AND THEN the selection against MASTER will happen, then the JOIN against the materialized view will take place.
Its really important to understand that:
> 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';
>
AND
>
> 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;
>
are not the same. The first one says
"Take the sum(ua) by id/term from the course table and join it to the master_tbl"
the second one says
"Take the join of the course table and join it to the master table, after that sum the UA column by ID"
Consider the following example (i know, in your case only ONE master / course but in general that doesn't have to be true)
SQL> desc c
Name Null? Type ------------------------------- -------- ---- ID NUMBER TERM NUMBER UA NUMBER
SQL> insert into c values ( 1, 1, 1 );
1 row created.
SQL> desc m
Name Null? Type ------------------------------- -------- ---- ID NUMBER TERM NUMBER
SQL> insert into m values ( 1, 1 );
1 row created.
SQL> insert into m values ( 1, 1 );
1 row created.
SQL> SELECT MT.ID, V1.SUM_UA FROM V1, M MT
2 WHERE V1.ID = MT.ID
3 and v1.term = mt.term
4 and mt.term = 1;
ID SUM_UA
---------- ----------
1 1
1 1
SQL> SELECT MT.ID, SUM(C.UA) SUM_UA FROM M mt, C
2 WHERE C.ID = MT.ID
3 and c.term = mt.term
4 and mt.term = 1
5* group by mt.id;
ID SUM_UA
---------- ----------
1 2
SQL> As you can see, joining the SUM(UA) to the master CAN BE different then joining to the master and summing...
Hope this helps...
>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.
>
[snip]
>
>Thanks in advance,
>Steve Cosner
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |