Re: Hint Ignored with new 9i JOIN syntax

mccmx_at_hotmail.com wrote:
> 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

I'd suggest going back to the ISO syntax. If it works better stick with it. Hardly anyone, in Oracle, uses ANSI.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)