Re: Outer Join reads all rows from underlying tables

From: Matt <mccmx_at_hotmail.com>
Date: 13 May 2004 02:25:29 -0700
Message-ID: <cfee5bcf.0405130125.7ff44486@posting.google.com>

Notes in-line

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0405112308.b0b96f3_at_posting.google.com>...
> Matt,
> Throw away is the difference between the number of rows scanned (using
> either a table or index scan) and the number of rows selected (after
> applying all the predicates). So large throwaway means that you had
> unnecessarily read many rows which failed to meet the criteria.
> Throwaway may be caused by full scans or unselective index range
> scans, missing indexes, missing predicate pushing, wrong join order ..
> You can see a lot of throw away in the view PS_TM_PEFF_V_CCEH.
>

Yeah, thats what got me thinking about the outer join in the first place... i.e. the huge number of rows selected from this view (CCEH) when only a small number of rows was needed.

> It would have been better if you had included the view definitions of
> PS_TM_PEFF_V_CCEH, ps_tm_peff_gpqtwt, ps_tm_peff_v_cc_vw &
> PS_TM_PEFF_BMRKVW2.
See view definitions below...

> Also what are the values for these parameters in your instance.
> _complex_view_merging, _push_join_predicate, _push_join_union_view,
> _pre_rewrite_push_pred.
>

All these values are set as default (i.e. FALSE according to X$KSPPSV) except
"_pre_rewrite_push_pred" which is probably 9i only.

> There are several rules for View Merging. View Merging doesn't happen
> by default if the view contains GROUP BY or DISTINCT. Also the CBO
> cant merge views that are on the deficient side of the outer join if
> they have multiple base tables (which is your case for the view
> PS_TM_PEFF_V_CCEH). If you can give the view definitions, we can see
> why the predicates are not getting pushed.

OK, see view definition below...

PS_TM_PEFF_V_CCEH: SELECT A.BUSINESS_UNIT ,A.DEPTID ,B.PRODUCTION_DT ,'A' ,SUM((B.PART_COUNT * A.BM_HOURS) /1000)
FROM
PS_TM_PEFF_BMRKVW2 A ,
PS_TM_PEFF_GPQCAL B
WHERE
A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND (A.DEPTID = B.DEPTID OR B.DEPTID = ' ') AND (A.SRG_CODE = B.SRG_CODE OR B.SRG_CODE = ' ')

AND (A.PART_NUM = B.PART_NUM OR B.PART_NUM= ' ') 
AND (A.PART_SFX_CD = B.PART_SFX_CD OR B.PART_SFX_CD = ' ') 
AND (A.PROCESS_CODE = B.PROCESS_CODE OR B.PROCESS_CODE = ' ') 
AND ((B.PRODUCTION_DT >= A.TM_EFFECT_DT 
AND (B.PRODUCTION_DT <= A.EFF_STOP_DATE OR A.EFF_STOP_DATE IS NULL)) 

OR B.PRODUCTION_DT IS NULL)
GROUP BY A.BUSINESS_UNIT,A.DEPTID,B.PRODUCTION_DT ps_tm_peff_gpqtwt is a table

ps_tm_peff_v_cc_vw:

SELECT DISTINCT A.BUSINESS_UNIT , E.TM_SHOP_CODE ,

E.REP_COST_CENTER_CD ,
D.DEPTID , C.CMS_LABOR_TYPE , D.CMS_SHIFT , E.TM_EFF_START_DATE ,
E.TM_EFF_STOP_DATE ,
E.TM_CONTRIB_TYPE , E.TM_CONTRIBUTION 
FROM 
	PS_TM_PEFF_BU_TBL A , 
	PS_CMS_DPTSTRU B , 
	PS_CMS_DPTDPTS C , 
	PS_CMS_DPTDPTS D , 
	PS_TM_PEFF_CONTRIB E 

WHERE A.BUSINESS_UNIT = B.SETID AND B.TREE_NAME = 'CMS_DPTDPTS' AND B.LEVELNUM = 4 AND B.EFFDT =

		( SELECT MAX(B1.EFFDT) FROM PS_CMS_DPTSTRU B1 
		WHERE B.SETID = B1.SETID AND B.TREE_NAME = B1.TREE_NAME 
		AND B.LEVELNUM = B1.LEVELNUM AND B.TREE_NODE = B1.TREE_NODE 
		AND B.DEPTID = B1.DEPTID AND B1.EFFDT <= SYSDATE
		) 

AND B.EFF_STATUS = 'A' ANDB.SETID = C.SETID AND B.TREE_NODE = C.DEPTID AND C.EFFDT = ( SELECT MAX(C1.EFFDT)

		FROM PS_CMS_DPTDPTS C1 WHERE C.SETID = C1.SETID 
		AND C.DEPTID = C1.DEPTID AND C1.EFFDT <= SYSDATE
		) 

AND C.CMS_LABOR_TYPE IN ('A', 'B1', 'B2', 'C1', 'C2') AND C.CMS_DEPT_TYPE = 'RP'
AND B.SETID = D.SETID AND B.DEPTID = D.DEPTID AND D.EFFDT =

		( SELECT MAX(D1.EFFDT) FROM PS_CMS_DPTDPTS D1 
		WHERE D.SETID = D1.SETID AND D.DEPTID = D1.DEPTID 
		AND D1.EFFDT <= SYSDATE
		) 
AND D.CMS_SHIFT IN ('1','2','3') AND E.BUSINESS_UNIT = A.BUSINESS_UNIT

AND E.TM_SHOP_CODE <> ' ' AND E.TM_CONTRIBUTION <> 0 AND E.REP_COST_CENTER_CD = C.DEPTID PS_TM_PEFF_BMRKVW2: SELECT DISTINCT A.DEPTID , C.BUSINESS_UNIT , C.SRG_CODE , C.PART_NUM , C.PART_SFX_CD , C.REP_COST_CENTER_CD , C.PROCESS_CODE , C.TM_EFFECT_DT ,
C.EFF_STOP_DATE, C.BM_HOURS
FROM
PS_CMS_DPTSTRU A ,
PS_TM_PEFF_BNCHMRK C
WHERE A.SETID = C.BUSINESS_UNIT AND A.EFFDT =

		( SELECT MAX(A_ED.EFFDT) FROM PS_CMS_DPTSTRU A_ED 
		WHERE A.SETID = A_ED.SETID 
		AND A.TREE_NAME = A_ED.TREE_NAME 
		AND A.LEVELNUM = A_ED.LEVELNUM 
		AND A.TREE_NODE = A_ED.TREE_NODE 
		AND A.DEPTID = A_ED.DEPTID 
		AND A_ED.EFFDT <= SYSDATE
		) 

AND A.LEVELNUM = 4 AND C.REP_COST_CENTER_CD = A.TREE_NODE AND C.TM_EFFECT_DT <= SYSDATE

Thanks for your help....

Matt Received on Thu May 13 2004 - 04:25:29 CDT