Cost-Based Optimizer

From: John Dennis <jdennis_at_netcom.com>
Date: Wed, 16 Mar 1994 20:31:02 GMT
Message-ID: <jdennisCMryBr.AtH_at_netcom.com>

 

I have a nasty query that worked fine on Oracle 6 (just made the switch) but is choking on Oracle 7. I "EXPLAINED" the query and it shows a large table (12,000+ rows) being used as "driving" table against a small table (14 rows). Yes, maybe sometimes Oracle has to do this for some reason unknown to me. But what? I've given it the "HINT" to use the "FUTURE_BKGS" table as the *inner* table but it still insists on using the "DEMOGROUP" as the inner table. (other hints work). Is there something about a nested loop?  

The small table is "DEMOGROUP" and the large table is "FUTURE_BKGS". The query and plan output is below.  

*Any* help would be appreciated!    

Thanks for your time,  

John Dennis    

SELECT	H1.Start_Time, H1.End_Time, H1.Station_Code, 
	H1.Rating_Svc, D1.Description, D1.Demo_Target, 
	D1.Sequence, SUM(H1.Spot_Value * H1.Spot_Count)
FROM	PROGRAM P1, FUTURE_BKGS H1, BDCTHEAD B1 ,
        RELATIVE_EFF R1, DEMOGROUP D1, SPOTTYPE SP

WHERE

B1.Station_Code = H1.Station_Code
and H1.Air_Date >='27-sep-93'
AND H1.Air_Date < '26-sep-94'
AND H1.Start_Time >= 400
AND H1.Start_Time < 2800
AND P1.DAY_PART = 'P'  AND H1.Rating_Svc = '1'  
AND B1.BAH_Number=H1.BAH_Number
AND B1.Contract_Year = H1.Contract_Year 
AND H1.Program_Code = P1.Program_Code 
AND H1.Demographic_Sold = D1.Demo_Target 
AND SP.Spot_Type = H1.Spot_Type 
AND R1.Station_Code(+) = H1.Station_Code 
AND R1.Program_Code(+) = H1.Program_Code 
AND R1.Rating_Svc(+) = H1.Rating_Svc 
AND H1.Air_Date >= R1.Start_Date(+) 

AND H1.Air_Date <= R1.End_Date(+)
AND H1.Demographic_Sold = R1.Target_Demo(+) Group By H1.Rating_Svc, D1.Sequence, H1.Station_Code,

	D1.Description, D1.Demo_Target, H1.Start_Time, 
	H1.End_Time, R1.Rel_Eff_GRP;
 
 

PLAN OUTPUT

---

 

Statement   Object       Object
  ID         Name         Type        Operation      Option      ID  PID  POS
--------- ---------- --------------- ------------ ------------ ---- ---- ----
advexp                               SELECT STATE                         163
                                     SORT         GROUP BY        1
                                     NESTED LOOPS                 2    1    1
                                     NESTED LOOPS                 3    2    1
                                     NESTED LOOPS OUTER           4    3    1
                                     NESTED LOOPS                 5    4    1
                                     NESTED LOOPS                 6    5    1
          DEMOGROUP                  TABLE ACCESS FULL            7    6    1
          FUTURE_BKG                 TABLE ACCESS BY ROWID        8    6    2
          YMS_N1_FUT NON-UNIQUE      INDEX        RANGE SCAN      9    8    1
          YMS_U_BDCT UNIQUE          INDEX        UNIQUE SCAN    10    5    2
          RELATIVE_E                 TABLE ACCESS BY ROWID       11    4    2
          YMS_U_RELA UNIQUE          INDEX        RANGE SCAN     12   11    1
          YMS_U_PROG UNIQUE          INDEX        UNIQUE SCAN    13    3    2
          YMS_U_SPOT UNIQUE          INDEX        UNIQUE SCAN    14    2    2
 
 

15 rows selected.     Received on Wed Mar 16 1994 - 21:31:02 CET