Re: Select with view join takes too long.

since you are using cost base optimization, the join does a merge join. Try using a hint in your sql to force rulebase. This will probably make the statement use a nested loop. This might be faster because indexes will be used on the driving table or driven tables resulting in less I/O

Regards,
Richard Fuoco

On Fri, 04 Apr 1997 00:20:20 +0800, Michael Ho <infoage_at_hk.super.net> wrote:

>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