Re: Unexpected Tkprof Row Count

From: Matt <mccmx_at_hotmail.com>
Date: 1 Sep 2004 01:55:53 -0700
Message-ID: <cfee5bcf.0409010055.72f239b5@posting.google.com>

> 
> May we see the query in question?
>

I wanted to avoid posting the whole query because it makes use of 3 layers of nested views and OLAP queries. It would take a long time to plough through...!

I just wanted to get some feedback about possible causes of this row count anomoly...

However here is view definition of the part of the query which is responsible for the section of the Tkprof output in question (PS_TM_PEFF_V_CCEH)... SELECT B.BUSINESS_UNIT ,B.REP_COST_CENTER_CD ,B.DEPTID ,B.TM_DATE ,'A'
,

NVL(SUM((B.PART_COUNT * A.BM_HOURS) /1000) ,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 and here is the view definition of PS_TM_PEFF_V_SHDAY:

SELECT A.BUSINESS_UNIT
,C.TM_SHOP_CODE
,1
,D.DEPTID
,D.TM_DATE
,D.CMS_LABOR_TYPE
,DECODE(((C.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME) *
B.TM_CORE_JBHR_RATIO
,0
,0
,((C.EH_CONTRIBUTION/100)*D.TM_EARNED_HOURS)/(((C.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME)
* B.TM_CORE_JBHR_RATIO)) AS PEFF
,0
,0
,0
,0
,0
,(C.EH_CONTRIBUTION/100)*D.TM_EARNED_HOURS AS EH
,(C.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME AS TWT
,0
,(C.TWT_CONTRIBUTION/100)*A.STRT_COREJOB_TWT AS STRT_TWT
,(C.TWT_CONTRIBUTION/100)*A.OT_COREJOB_TWT AS OT_TWT
,(C.TWT_CONTRIBUTION/100)*A.OVER_TIME AS OVER_TIME
,DECODE(A.HEAD_COUNT
,0
,0
,(((C.TWT_CONTRIBUTION/100)*A.WORK_AVG)/((C.TWT_CONTRIBUTION/100)*A.HEAD_COUNT))*100)
AS ATTEND_PCT
,(C.TWT_CONTRIBUTION/100)*A.HEAD_COUNT AS HC
,(C.TWT_CONTRIBUTION/100)*A.WORK_AVG AS ATTEND_AVG
,DECODE(A.WORK_AVG
,0
,0
,(((C.TWT_CONTRIBUTION/100)*A.OVER_TIME)/((C.TWT_CONTRIBUTION/100)*A.WORK_AVG)))
AS OT_EMP_DAY
,DECODE((C.TWT_CONTRIBUTION/100)*A.TM_ACT_TWT
,0
,0
,((C.EH_CONTRIBUTION/100)*D.TM_EARNED_HOURS)/((C.TWT_CONTRIBUTION/100)*A.TM_ACT_TWT))
AS DIRPEFF
,DECODE((C.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME
,0
,0
,((C.TWT_CONTRIBUTION/100)*A.TM_ACT_TWT)/((C.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME))
AS ACTCJHR
,B.TM_CORE_JBHR_RATIO
,(C.TWT_CONTRIBUTION/100)*A.BORROWED_TIME AS BORR_HRS
,(C.TWT_CONTRIBUTION/100)*A.LOANED_TIME AS LOAN_HRS
,((C.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME) * B.TM_CORE_JBHR_RATIO
AS TWT_BY_CJHR
,(C.TWT_CONTRIBUTION/100)*A.TM_ACT_TWT AS ACT_TWT
,((C.EH_CONTRIBUTION/100)*D.TM_EARNED_HOURS)*E.TM_BC_COEFFICIENT AS
BCEH

FROM PS_TM_PEFF_TWTCAL A 


, PS_TM_PEFF_TCJR B 

, PS_TM_PEFF_T_CONTR C

, PS_TM_PEFF_V_CCEH D
, PS_TM_PEFF_V_COEF E

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT

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 (A.TWT_DATE >= C.TM_EFF_START_DATE
AND C.TM_EFF_START_DATE = (
SELECT MAX(TM_EFF_START_DATE)
FROM PS_TM_PEFF_T_CONTR V
WHERE V.BUSINESS_UNIT = C.BUSINESS_UNIT

AND V.REP_COST_CENTER_CD = C.REP_COST_CENTER_CD 
AND V.TM_EFF_START_DATE <= A.TWT_DATE)) 
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 A.BUSINESS_UNIT
,B.TM_SHOP_CODE
,1
,A.COST_CENTER_CD
,A.TWT_DATE
,A.CMS_LABOR_TYPE
,0
,0
,0
,0
,0
,0
,0
,(B.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME
,DECODE(A.CMS_LABOR_TYPE
,'B2'
,(B.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME
,'B1'
,0
,'C1'
,0
,'C2'
,0) * C.TM_CORE_JBHR_RATIO AS TWT_PILOT
,(B.TWT_CONTRIBUTION/100)*A.STRT_COREJOB_TWT
,(B.TWT_CONTRIBUTION/100)*A.OT_COREJOB_TWT
,(B.TWT_CONTRIBUTION/100)*A.OVER_TIME
,DECODE(A.HEAD_COUNT
,0
,0
,(((B.TWT_CONTRIBUTION/100)*A.WORK_AVG)/((B.TWT_CONTRIBUTION/100)*A.HEAD_COUNT))*100)
,(B.TWT_CONTRIBUTION/100)*A.HEAD_COUNT
,(B.TWT_CONTRIBUTION/100)*A.WORK_AVG
,DECODE(A.WORK_AVG
,0
,0
,(((B.TWT_CONTRIBUTION/100)*A.OVER_TIME)/((B.TWT_CONTRIBUTION/100)*A.WORK_AVG)))
,0
,DECODE((B.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME
,0
,0
,(((B.TWT_CONTRIBUTION/100)*A.TM_ACT_TWT)/((B.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME)))
AS ACT_CJHR
, C.TM_CORE_JBHR_RATIO
,(B.TWT_CONTRIBUTION/100)*A.BORROWED_TIME
,(B.TWT_CONTRIBUTION/100)*A.LOANED_TIME
,((B.TWT_CONTRIBUTION/100)*A.TOTAL_WORK_TIME) * C.TM_CORE_JBHR_RATIO
,(B.TWT_CONTRIBUTION/100)*A.TM_ACT_TWT
,0

FROM PS_TM_PEFF_TWTCAL A

, PS_TM_PEFF_T_CONTR B
, PS_TM_PEFF_TCJR C

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REP_COST_CENTER_CD = B.REP_COST_CENTER_CD AND (A.TWT_DATE >= B.TM_EFF_START_DATE
AND B.TM_EFF_START_DATE = (
SELECT MAX(TM_EFF_START_DATE)
FROM PS_TM_PEFF_CONTRIB V
WHERE V.BUSINESS_UNIT = B.BUSINESS_UNIT

AND V.REP_COST_CENTER_CD = B.REP_COST_CENTER_CD 
AND V.TM_EFF_START_DATE <= A.TWT_DATE)) 
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
/ Received on Wed Sep 01 2004 - 03:55:53 CDT