这里CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM是个大表, 有近600万行. 其他的表都不算大.
SELECT
CNR.TB_CNR011_DIM_PROD_FMLY.PROD_GRP_DISP_NAM,
CNR.TB_CNR021_FCT_CNCSSN.MNTH_IN_USE_NUM,
sum(CNR.TB_CNR021_FCT_CNCSSN.NET_CNCSSN_AMT),
CNR.TB_CNR011_DIM_PROD_FMLY.PROD_LN_CD,
CNR.TB_CNR011_DIM_PROD_FMLY.MFG_SITE_NAM,
CNR.TB_CNR011_DIM_PROD_FMLY.PROD_FMLY_NAM,
CNR.TB_CNR014_DIM_BRAND.BRAND_CD,
CNR.TB_CNR014_DIM_BRAND.BRAND_NAM,
CNR.TB_CNR013_DIM_WRNT_STS.WRNT_STS_CD,
CNR.TB_CNR015_DIM_CNCSSN_CTGY.CNCSSN_CTGY_CD,
CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW,
CNR.TB_CNR011_DIM_PROD_FMLY.PROD_GRP_NAM
FROM
CNR.TB_CNR011_DIM_PROD_FMLY,
CNR.TB_CNR021_FCT_CNCSSN,
CNR.TB_CNR014_DIM_BRAND,
CNR.TB_CNR013_DIM_WRNT_STS,
CNR.TB_CNR015_DIM_CNCSSN_CTGY,
CU.TB_COM02111_DIM_TIME
WHERE
( CNR.TB_CNR011_DIM_PROD_FMLY.PROD_FMLY_ID=CNR.TB_CNR021_FCT_CNCSSN.PROD_FMLY_ID)
AND( CNR.TB_CNR014_DIM_BRAND.BRAND_ID=CNR.TB_CNR021_FCT_CNCSSN.BRAND_ID)
AND( CNR.TB_CNR021_FCT_CNCSSN.WRNT_STS_ID=CNR.TB_CNR013_DIM_WRNT_STS.WRNT_STS_ID)
AND( CNR.TB_CNR021_FCT_CNCSSN.CNCSSN_CTGY_ID=CNR.TB_CNR015_DIM_CNCSSN_CTGY.CNCSSN_CTGY_ID)
AND( CNR.TB_CNR021_FCT_CNCSSN.RPT_ID = (select wkly_rpt_id from cnr.tb_cnr005_rep_ctrl))
AND( CNR.TB_CNR021_FCT_CNCSSN.CALENDAR_DT=CU.TB_COM02111_DIM_TIME.CALENDAR_DT)
AND( CNR.TB_CNR021_FCT_CNCSSN.CNCSSN_UNV_FLG='Y')
AND
CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW>200901AND
CU.TB_COM02111_DIM_TIME.FISCAL_YEAR_WEEK_YYYYWW=200901 AND
"TB_COM02111_DIM_TIME"."FISCAL_YEAR_WEEK_YYYYWW"<=200929)
11 - filter("TB_CNR021_FCT_CNCSSN"."CNCSSN_UNV_FLG"='Y' AND "TB_CNR021_FCT_CNCSSN"."RPT_ID"= (SELECT /*+ */
"TB_CNR005_REP_CTRL"."WKLY_RPT_ID" FROM "CNR"."TB_CNR005_REP_CTRL" "TB_CNR005_REP_CTRL"))
14 - access("TB_CNR021_FCT_CNCSSN"."WRNT_STS_ID"="TB_CNR013_DIM_WRNT_STS"."WRNT_STS_ID")
Note: cpu costing is off
36 rows selected.
|
