Re: Optimizer not pushing filters down into view

From: <mccmx_at_hotmail.com>
Date: 23 Aug 2006 00:49:13 -0700
Message-ID: <1156319353.115655.69740@p79g2000cwp.googlegroups.com>

View definitions:

I've trimmed out the very long select list to help readability....

PS_TM_PEFF_V_SHDAY: SELECT /*+ ORDERED USE_HASH(D) PUSH_PRED(D) */ .....

FROM PS_TM_PEFF_V_COEF E


, PS_TM_PEFF_CONT_DT C

, PS_TM_PEFF_TWTCAL A

, PS_TM_PEFF_TCJR B

, PS_TM_PEFF_V_CCEH D
, PS_TM_PEFF_MFCAL F

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT

AND d.business_unit = f.business_unit
AND d.tm_date = f.tm_date
AND A.CMS_LABOR_TYPE = 'A'
AND A.CMS_LABOR_TYPE = B.TM_LABOR_CLASS
AND B.TM_EFFECT_DT = (

SELECT MAX(TM_EFFECT_DT)
FROM PS_TM_PEFF_TCJR B1
WHERE B1.BUSINESS_UNIT = B.BUSINESS_UNIT AND B1.TM_LABOR_CLASS = B.TM_LABOR_CLASS AND B1.TM_EFFECT_DT <= A.TWT_DATE)
AND C.BUSINESS_UNIT = A.BUSINESS_UNIT

AND C.REP_COST_CENTER_CD = A.REP_COST_CENTER_CD
AND (C.TM_EFF_START_DATE <= A.TWT_DATE
AND (C.TM_EFF_STOP_DATE >= A.TWT_DATE

OR C.TM_EFF_STOP_DATE IS NULL))

AND D.BUSINESS_UNIT = A.BUSINESS_UNIT
AND D.REP_COST_CENTER_CD = A.REP_COST_CENTER_CD
AND D.TM_DATE = A.TWT_DATE
AND D.DEPTID = A.COST_CENTER_CD
AND D.CMS_LABOR_TYPE = A.CMS_LABOR_TYPE
AND E.BUSINESS_UNIT = D.BUSINESS_UNIT
AND E.TM_SHOP_CODE = C.TM_SHOP_CODE

AND (E.TM_EFF_START_DATE = (
SELECT MAX(E1.TM_EFF_START_DATE)
FROM PS_TM_PEFF_COEF E1
WHERE E.BUSINESS_UNIT = E1.BUSINESS_UNIT AND E.TM_SHOP_CODE = E1.TM_SHOP_CODE
AND E1.TM_EFF_START_DATE <= D.TM_DATE)
OR E.TM_EFF_START_DATE IS NULL)
UNION
SELECT .......

FROM PS_TM_PEFF_TWTCAL A


, PS_TM_PEFF_MFCAL D

, PS_TM_PEFF_CONT_DT B
, PS_TM_PEFF_TCJR C

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT

AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.TWT_DATE = D.TM_DATE
AND A.REP_COST_CENTER_CD = B.REP_COST_CENTER_CD

AND (B.TM_EFF_START_DATE <= A.TWT_DATE
AND (B.TM_EFF_STOP_DATE >= A.TWT_DATE
OR B.TM_EFF_STOP_DATE IS NULL))

AND A.CMS_LABOR_TYPE NOT IN ('B2','A')
AND A.TWT_TYPE = 'DA'
AND C.BUSINESS_UNIT = A.BUSINESS_UNIT
AND C.TM_LABOR_CLASS = 'B'
AND C.TM_EFFECT_DT = (

SELECT MAX(TM_EFFECT_DT)
FROM PS_TM_PEFF_TCJR C1
WHERE C1.BUSINESS_UNIT = C.BUSINESS_UNIT AND C1.TM_LABOR_CLASS = C.TM_LABOR_CLASS AND C1.TM_EFFECT_DT <= A.TWT_DATE);

PS_TM_PEFF_V_CCEH: SELECT /*+ NO_MERGE */ ......
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; PS_TM_PEFF_V_COEF: SELECT .....
FROM
ps_tm_peff_shpmstr a,
ps_tm_peff_coef b
WHERE a.business_unit = b.business_unit (+) AND a.tm_shop_code = b.tm_shop_code (+); Received on Wed Aug 23 2006 - 02:49:13 CDT