From: fuocor@novachem.com
Subject: Re: Select with view join takes too long.
Date: 1997/04/04
Message-ID: <3344465c.3012746@news.htw-zittau.de>#1/1
References: <5heb03$6fq@info.csufresno.edu> <3343D8C4.1F49@hk.super.net>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server



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@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