Wrong Result With Parallel & Star_transformation_enabled = Temp_disable (Doc ID 472084.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

After setting:

alter session set star_transformation_enabled=temp_disable;

The following statement produces wrong results:

SELECT
DIM_TIME.MONTH_CODE,
sum(F_INV_ORD_MOTO.INV_QTY)
FROM
F_ORDXSL_MOTO KPI_ORD,
DIM_TIME,
F_INV_ORD_MOTO,
DIM_SALES_ORG,
DIM_DISTRIBUTION_CHANNEL,
X_DIM_CUSTOMER,
X_DIM_MATERIAL,
DIM_TIME ORDER_TIME,
DIM_TIME TIME_FATT
WHERE
( DIM_DISTRIBUTION_CHANNEL.DC_ID_DISTR_CHANNEL=F_INV_ORD_MOTO.DC_ID_DISTR_CHANNEL )
AND ( F_INV_ORD_MOTO.CS_ID_CUSTOMER=X_DIM_CUSTOMER.CS_ID_CUSTOMER )
AND ( DIM_SALES_ORG.SO_ID_SALES_ORG=F_INV_ORD_MOTO.SO_ID_SALES_ORG )
AND ( F_INV_ORD_MOTO.DATE_ID=TIME_FATT.DATE_ID )
AND ( F_INV_ORD_MOTO.MAT_ID_MATERIAL=X_DIM_MATERIAL.MAT_ID_MATERIAL )
AND ( F_INV_ORD_MOTO.CS_ID_CUSTOMER=KPI_ORD.CS_ID_CUSTOMER and
F_INV_ORD_MOTO.DC_ID_DISTR_CHANNEL=KPI_ORD.DC_ID_DISTR_CHANNEL and
F_INV_ORD_MOTO.MANDT=KPI_ORD.MANDT and
F_INV_ORD_MOTO.MCS_ID_MAT_CUST_SALES=KPI_ORD.MCS_ID_MAT_CUST_SALES and
F_INV_ORD_MOTO.SO_ID_SALES_ORG=KPI_ORD.SO_ID_SALES_ORG and
F_INV_ORD_MOTO.VBELN=KPI_ORD.VBELN and
F_INV_ORD_MOTO.MAT_ID_MATERIAL=KPI_ORD.MAT_ID_MATERIAL )
AND ( KPI_ORD.DATE_ID_ORDER=ORDER_TIME.DATE_ID )
AND ( KPI_ORD.DATE_ID_RIF=DIM_TIME.DATE_ID )
AND
(
DIM_SALES_ORG.SO_DW_DESCRIPTION = '0582-USA'
AND
DIM_DISTRIBUTION_CHANNEL.DC_COD_DISTR_CHANNEL = '01'
AND
X_DIM_CUSTOMER.CS_CUSTOMER_CODE In (
'0007008831','0007008832','0007008833','0007008834','0007008835','0007008841' )
AND
NVL(X_DIM_MATERIAL.MF_COD_MATFAM,'ZZ') = 'C7'
AND
( ( ORDER_TIME.DATE_CODE ) <= add_months(to_date('2007/0110','YYYY/MMDD'),-1) )
AND
( ( DIM_TIME.MONTH_CODE ) = '2007/01' )
AND
( ( TIME_FATT.MONTH_CODE )= '2007/01' )
)
GROUP BY
DIM_TIME.MONTH_CODE;

(no records are returned).

However when star_transformation_enabled is not set to temp_disable, 44 records are returned.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms