Hint Ignored with new 9i JOIN syntax

From: <mccmx_at_hotmail.com>
Date: 19 Jul 2005 01:42:00 -0700
Message-ID: <1121762520.559385.313760@g44g2000cwa.googlegroups.com>

Oracle 9.2.0.4 EE on HP-UX 11.11

I have rewritten a query to use the new 9i JOIN syntax and the optimizer has now chosen a different plan. This has caused the execution time to double. In order to improve the performance of the new (slow) query I want to add a hint to the underlying view in order to infuence the optimizer to chose the old (fast) plan. However it choses to ignore the hint.

As far as I am aware outer joins dictate the join order of an SQL statement, but not the join method. However, if I hint the second
(new) query with a USE_HASH hint the optimizer choses to ignore it.

Here are the 2 queries and resultant plans:

select count(*) from ps_tm_peff_v_cceh where business_unit = 'TMUK' and tm_date between '01-JUN-05' and '08-JUN-05'

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2313 Card=1 Bytes=4)    1 0 SORT (AGGREGATE)
   2 1 VIEW OF 'PS_TM_PEFF_V_CCEH' (Cost=2313 Card=13679 Bytes=54716)

   3    2       SORT (GROUP BY) (Cost=2313 Card=13679 Bytes=1217431)
   4    3         FILTER
   5    4           FILTER
   6    5             HASH JOIN (OUTER)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF
'PS_TM_PEFF_GPQCAL' (Cost=2032 Card=13679 Bytes=588197)
   8    7                 INDEX (RANGE SCAN) OF 'PS_TM_PEFF_GPQCAL'


(UNIQUE) (Cost=125 Card=13679)

   9    6               INDEX (FAST FULL SCAN) OF

'PSATM_PEFF_BNCHMRK'(UNIQUE) (Cost=62 Card=6972 Bytes=320712)

select count(*) from MATT where business_unit = 'TMUK' and tm_date between '01-JUN-05' and '08-JUN-05'

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43181 Card=1 Bytes=2)    1 0 SORT (AGGREGATE)

   2    1     VIEW (Cost=43181 Card=13679 Bytes=27358)
   3    2       SORT (GROUP BY) (Cost=43181 Card=13679 Bytes=588197)
   4    3         NESTED LOOPS (OUTER) (Cost=43069 Card=13679
Bytes=588197)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF
'PS_TM_PEFF_GPQCAL' (Cost=2032 Card=13679 Bytes=588197)
   6    5             INDEX (RANGE SCAN) OF 'PS_TM_PEFF_GPQCAL'


(UNIQUE) (Cost=125 Card=13679)

   7    4           VIEW (Cost=3 Card=1)
   8    7             FILTER
   9    8               INDEX (RANGE SCAN) OF 'PSATM_PEFF_BNCHMRK'

(UNIQUE) (Cost=3 Card=1 Bytes=46)

Here are the view definitions for the 2 queries (I have changed the join syntax from the 'old' 8i method to the new 9i method).

8i Syntax - PS_TM_PEFF_V_CCEH:

SELECT B.BUSINESS_UNIT ,B.REP_COST_CENTER_CD ,B.DEPTID ,B.TM_DATE , NVL(Round(SUM((B.PART_COUNT * A.BM_HOURS) /1000) ,1) ,0), B.CMS_LABOR_TYPE ,B.CMS_SHIFT
FROM

	PS_TM_PEFF_BNCHMRK A ,
	PS_TM_PEFF_GPQCAL B
WHERE
	A.BUSINESS_UNIT (+) = B.BUSINESS_UNIT
AND 	A.REP_COST_CENTER_CD (+) = B.REP_COST_CENTER_CD
AND 	A.SRG_CODE (+) = B.SRG_CODE
AND 	A.PART_NUM (+) = B.PART_NUM
AND 	A.PART_SFX_CD (+) = B.PART_SFX_CD
AND 	A.PROCESS_CODE (+) = B.PROCESS_CODE
AND 	(
	 (B.TM_DATE >= A.TM_EFFECT_DT
AND 	  (B.TM_DATE <= A.EFF_STOP_DATE OR A.EFF_STOP_DATE IS NULL)
	 )
OR 	A.TM_EFFECT_DT IS NULL
	)

GROUP BY
B.BUSINESS_UNIT,B.REP_COST_CENTER_CD,B.DEPTID,B.TM_DATE,B.CMS_LABOR_TYPE,B.CMS_SHIFT 9i Syntax - MATT:

SELECT B.BUSINESS_UNIT ,B.REP_COST_CENTER_CD ,B.DEPTID , B.TM_DATE ,NVL(Round(SUM((B.PART_COUNT * A.BM_HOURS) /1000) ,1) ,0) ,B.CMS_LABOR_TYPE ,B.CMS_SHIFT
FROM

        PS_TM_PEFF_BNCHMRK A right outer join
        PS_TM_PEFF_GPQCAL B
on
        A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND     A.REP_COST_CENTER_CD = B.REP_COST_CENTER_CD
AND     A.SRG_CODE = B.SRG_CODE
AND     A.PART_NUM = B.PART_NUM
AND     A.PART_SFX_CD = B.PART_SFX_CD
AND     A.PROCESS_CODE = B.PROCESS_CODE
AND     (
         (B.TM_DATE >= A.TM_EFFECT_DT
AND       (B.TM_DATE <= A.EFF_STOP_DATE OR A.EFF_STOP_DATE IS NULL)
         )
OR      A.TM_EFFECT_DT IS NULL
        )

GROUP BY B.BUSINESS_UNIT,B.REP_COST_CENTER_CD,B.DEPTID,B.TM_DATE, B.CMS_LABOR_TYPE,B.CMS_SHIFT Thanks in advance...

Matt Received on Tue Jul 19 2005 - 03:42:00 CDT