Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select with view join takes too long.
Steve Cosner wrote:
> 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:
> 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
The concept need effort to understand :
CASE 1:
SELECT A, SUM(B)
FROM T
WHERE A=1
GROUP BY A
CASE 2:
SELECT A, SUM(B)
FROM T
GROUP BY A
HAVING A=1
Same output, different execution plan :
CASE 1:
Table T is filtered (index possible) then grouped, so faster.
CASE 2:
Table T is grouped (Full-Access) Then filtered, so slower
Implies :
SELECT * FROM (SELECT A, SUM(B) FROM T GROUP BY A) WHERE A=1
Equivalent to (slower) :
SELECT A,SUM(B) FROM T GROUP BY A HAVING A=1
Solution : review your needs for Views, used with planning and
precautions.
Regards,
Michael
Received on Fri Apr 04 1997 - 00:00:00 CST