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