环境： EXADATA V2 , 版本 11.2.0.2.0
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
我的PM抱怨对我说，APEX上面有个SQL 要跑很久，最开始它跑3-5分钟，过了段时间，随着业务的变换，数据的增长，它要跑20分钟，随着数据的不断增长到现在它要跑1小时。PM说，这个SQL执行非常频繁，这里你想到了什么呢？因为是11g，我们可以利用result cache去优化它，但是即使用了result cache,我们依然要面对一个事实--------第一次执行的时候它很慢，所以这个SQL必须优化。下面是我给老外做的技术分析，拿出来分享一下：
Hi All
I think I may find the way to optimize the SQL.
SELECT COUNT(*) FROM ADWGQ_CRS.CRS_RPT_ERR_DUP_VW WHERE AGG_NAME = 'CNTRY_IT';
The source code for View CRS_RPT_ERR_DUP_VW as below
SELECT
--------------------------------------------------------------------------------------
-- View : CRS_RPT_ERR_DUP_VW
-- Purpose : View to find duplicates after TP/SF data merge
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- Change History --
-- Date Programmer Description --
-- ---------- ----------------- ----------------------------------------------- --
-- 2011-09-01 Grzegorz Zapert Initial
-- 2011-09-23 Grzegorz Zapert Changed '' to 'A' for TP/SF combined contracts.
-- 2011-09-27 Marcin Maslon Rename to CRS_RPT
-- 2011-10-04 Grzegorz Zapert Optimizations
-- 2011-10-10 Maciej Nowak Added cols: EXCHG_RATE, CVRG_FACTR_THSND_LC_RATE,
-- CVRG_FACTR_THSND_SU_RATE, CVRG_YA_FACTR_THSND_LC_RATE, CVRG_YA_FACTR_THSND_SU_RATE
-- 2011-10-11 Grzegorz Zapert Changed to use crs_rpt_basic_nomerge_check_vw
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
/*CS*/ AGG_NAME
,CVRG_FACTR_THSND_LC_RATE
,CVRG_FACTR_THSND_SU_RATE
,CVRG_YA_FACTR_THSND_LC_RATE
,CVRG_YA_FACTR_THSND_SU_RATE
,END_DATE -- AS END_DATE
,EXCHG_RATE
,FISC_QTR_NAME
,FISC_YR_NAME
,count(*) AS RCD_CNT
,RPT_AREA_LVL
,RPT_AREA_NAME
,RPT_ATTR_01_NAME
,RPT_ATTR_02_NAME
,RPT_ATTR_03_NAME
,RPT_ATTR_04_NAME
,RPT_ATTR_05_NAME
,RPT_ATTR_06_NAME
,RPT_ATTR_07_NAME
,RPT_ATTR_08_NAME
,RPT_ATTR_09_NAME
,RPT_ATTR_10_NAME
,RPT_ATTR_11_NAME
,RPT_ATTR_12_NAME
,RPT_ATTR_13_NAME
,RPT_ATTR_14_NAME
,RPT_BRAND_FRNCH_NAME
,RPT_BRAND_NAME
,RPT_CATEG_NAME
,RPT_CNTRY_CODE
,RPT_CNTRY_NAME
,RPT_CO_NAME
,RPT_CUST_TYPE_NAME
,RPT_HIGST_PRMTN_LVL
,RPT_HUB_NAME
,RPT_LOWST_LVL
,RPT_MRBU_NAME
,PROD_ID
,RPT_PROD_ID
,RPT_PROD_LVL
,RPT_RBU_NAME
,RPT_REGN_NAME
,sum(decode(substr(CNTRT_ID,7,1),'S',1,0)) AS SF_RCD_CNT
,TIME_NAME
,TIME_PERD_TYPE_CODE
,sum(decode(substr(CNTRT_ID,7,1),'T',1,0)) AS TRDPN_RCD_CNT
,sum(decode(substr(CNTRT_ID,7,1),'A',1,0)) AS TRDPN_SF_RCD_CNT
/*CE*/
FROM crs_rpt_basic_check_vw
GROUP BY AGG_NAME
,CVRG_FACTR_THSND_LC_RATE
,CVRG_FACTR_THSND_SU_RATE
,CVRG_YA_FACTR_THSND_LC_RATE
,CVRG_YA_FACTR_THSND_SU_RATE
,END_DATE -- AS END_DATE
,EXCHG_RATE
,FISC_QTR_NAME
,FISC_YR_NAME
,RPT_AREA_LVL
,RPT_AREA_NAME
,RPT_ATTR_01_NAME
,RPT_ATTR_02_NAME
,RPT_ATTR_03_NAME
,RPT_ATTR_04_NAME
,RPT_ATTR_05_NAME
,RPT_ATTR_06_NAME
,RPT_ATTR_07_NAME
,RPT_ATTR_08_NAME
,RPT_ATTR_09_NAME
,RPT_ATTR_10_NAME
,RPT_ATTR_11_NAME
,RPT_ATTR_12_NAME
,RPT_ATTR_13_NAME
,RPT_ATTR_14_NAME
,RPT_BRAND_FRNCH_NAME
,RPT_BRAND_NAME
,RPT_CATEG_NAME
,RPT_CNTRY_CODE
,RPT_CNTRY_NAME
,RPT_CO_NAME
,RPT_CUST_TYPE_NAME
,RPT_HIGST_PRMTN_LVL
,RPT_HUB_NAME
,RPT_LOWST_LVL
,RPT_MRBU_NAME
,PROD_ID
,RPT_PROD_ID
,RPT_PROD_LVL
,RPT_RBU_NAME
,RPT_REGN_NAME
,TIME_NAME
,TIME_PERD_TYPE_CODE
having
-- for contracts with separate TP/SF data
(count(*) > 1
and (sum(decode(substr(CNTRT_ID,7,1),'T',1,0)) > 1
OR sum(decode(substr(CNTRT_ID,7,1),'S',1,0)) > 1
)
)
-- for contracts with combined TP/SF data
OR (count(*) > 1
AND sum(decode(substr(CNTRT_ID,7,1),'A',1,0)) > 1
)
if we want to optimize the SQL, we need to optimize view crs_rpt_basic_check_vw
the source code for view crs_rpt_basic_check_vw as below
WITH cvrg_factr AS (
--------------------------------------------------------------------------------------
-- View : CRS_RPT_BASIC_CHECK_VW
-- Purpose : Basic fact view with product, geo, time dimensions and mapped attributes
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- Change History --
-- Date Programmer Description --
-- ---------- ----------------- ----------------------------------------------- --
-- 2011-09-01 Grzegorz Zapert Initial
-- 2011-09-19 Grzegorz Zapert Added updated RPT_BRAND_FRNCH_NAME
-- 2011-09-23 Grzegorz Zapert Added TIME_PERD_TYPE_CODE.
-- 2011-09-27 Grzegorz Zapert Fixed PXM logic to include P12M
-- 2011-09-27 Marcin Maslon Rename to CRS_RPT
-- 2011-10-07 Grzegorz Zapert Changed long_name to name for TIME_NAME
-- Using Coverage Factor YA for measures YA
-- Added FY, FQ dynamically from crs_time_perd_fdim
-- Changed cols: exchg_rate to EXCHG_RATE, cvrg_factr_val_mlc to CVRG_FACTR_THSND_LC_RATE, cvrg_factr_vol_msu to CVRG_FACTR_THSND_SU_RATE
-- Added cols: CVRG_YA_FACTR_THSND_LC_RATE, CVRG_YA_FACTR_THSND_SU_RATE
-- 2011-10-10 Grzegorz Zapert Changed from crs_rpt_prod_dim_vw to crs_rpt_prod_dim and crs_rpt_geo_dim_vw to crs_rpt_geo_dim
-- Optimized time period (FY, QR) joins
-- 2011-10-12 Grzegorz Zapert Filtered out OB/EB TIME_PERD_TYPE_CODE
-- 2011-11-10 Marcin Maslon Added new measures and attributes
-- 2011-12-07 Marcin Maslon Added join on area_lvl with coverage factor
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
SELECT /*+ MATERIALIZE */
cf1.cntry_id,
rc.rpt_categ_name,
cf1.area_lvl_name,
cf1.rpt_area_name,
cf1.time_perd_id,
t.end_date,
nvl(cf2.cvrg_factr_thsnd_su_rate, cf1.cvrg_factr_thsnd_su_rate) AS cvrg_factr_thsnd_su_rate,
nvl(cf2.cvrg_factr_thsnd_lc_rate, cf1.cvrg_factr_thsnd_lc_rate) AS cvrg_factr_thsnd_lc_rate
FROM crs_cvrg_factr_uprc cf1
JOIN crs_rpt_categ_uprc rc
ON cf1.wwmd_seg_name = rc.wwmd_seg_name
JOIN crs_time_perd_fdim t
ON cf1.time_perd_id = t.time_perd_id
LEFT JOIN crs_cvrg_factr_uprc cf2
ON cf1.wwmd_seg_name = cf2.wwmd_seg_name
AND cf1.time_perd_id = cf2.time_perd_id
AND cf1.rpt_area_name = cf2.rpt_area_name
AND cf1.area_lvl_name = cf2.area_lvl_name
AND cf1.cntry_id = cf2.cntry_id
AND cf2.srce_name = 'MANUAL'
WHERE cf1.srce_name != 'MANUAL'
)
,time_perd_fy_qr AS (
SELECT /*+ MATERIALIZE */
t.TIME_PERD_ID
,t.name
,t.end_date
,tfy.name AS tfy_name
,tfy.end_date AS tfy_end_date
,tqr.name AS tqr_name
FROM crs_time_perd_fdim t
LEFT JOIN crs_time_perd_fdim tfy
ON tfy.time_perd_type_code = 'FY'
AND t.end_date BETWEEN tfy.start_date AND tfy.end_date
LEFT JOIN crs_time_perd_fdim tqr
ON tqr.time_perd_type_code = 'QR'
AND t.end_date BETWEEN tqr.start_date AND tqr.end_date
WHERE t.time_perd_type_code = 'MH'
),
exchange_rate_cutover AS (
SELECT /*+ MATERIALIZE */ srce_iso_crncy_code, MAX(exchg_rate) AS exchg_rate
FROM crs_exchg_rate_lkp
WHERE (last_day(end_date) = last_day((SELECT to_date(parm_val,'YYYY-MM-DD')
FROM crs_cnfg_uprc
WHERE parm_name = 'WWMD_CUT_OVER_DATE'))) OR (srce_iso_crncy_code = 'USD')
GROUP BY srce_iso_crncy_code
)
SELECT /*+ ORDERED PARALLEL(f 8) USE_HASH(accc f g p plbf t tfyqr cf cfya cfdflt ervar) */
/*CS*/ accc.AGG_NAME AS AGG_NAME
,p.ATTR_01_NAME
,p.ATTR_02_NAME
,p.ATTR_03_NAME
,p.ATTR_04_NAME
,p.ATTR_05_NAME
,p.ATTR_06_NAME
,p.ATTR_07_NAME
,p.ATTR_08_NAME
,p.ATTR_09_NAME
,p.ATTR_10_NAME
,p.ATTR_11_NAME
,p.ATTR_12_NAME
,p.ATTR_13_NAME
,p.ATTR_14_NAME
,f.fact_measr_57_val AS AVG_PURCH_AMT
,f.fact_measr_58_val AS AVG_PURCH_YA_AMT
,p.BRAND_NAME
,f.fact_measr_28_val AS BRAND_SHARE_DSPLY_PCT
,f.fact_measr_29_val AS BRAND_SHARE_DSPLY_YA_PCT
,f.fact_measr_30_val AS BRAND_SHARE_FEATR_PCT
,f.fact_measr_31_val AS BRAND_SHARE_FEATR_YA_PCT
,p.CATEG_NAME
,f.fact_measr_1_val AS CATEG_SHARE_VAL_PCT
,f.fact_measr_2_val AS CATEG_SHARE_VAL_YA_PCT
,f.fact_measr_4_val AS CATEG_SHARE_VOL_PCT
,f.fact_measr_3_val AS CATEG_SHARE_VOL_YA_PCT
,accc.CNTRT_ID AS CNTRT_ID
,p.CO_NAME
,NVL(NVL(cfa.CVRG_FACTR_THSND_LC_RATE,cf.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE)) AS CVRG_FACTR_THSND_LC_RATE
,NVL(NVL(cfa.CVRG_FACTR_THSND_SU_RATE,cf.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE)) AS CVRG_FACTR_THSND_SU_RATE
,NVL(NVL(cfyaa.CVRG_FACTR_THSND_LC_RATE,cfya.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE)) AS CVRG_YA_FACTR_THSND_LC_RATE
,NVL(NVL(cfyaa.CVRG_FACTR_THSND_SU_RATE,cfya.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE)) AS CVRG_YA_FACTR_THSND_SU_RATE
,f.fact_measr_49_val AS DIST_EXTRA_2_VAL
,f.fact_measr_50_val AS DIST_EXTRA_2_YA_VAL
,f.fact_measr_47_val AS DIST_EXTRA_VAL
,f.fact_measr_48_val AS DIST_EXTRA_YA_VAL
,NVL2(t.end_date, t.end_date, CASE WHEN t.TIME_PERD_ID LIKE 'P%MYA' THEN add_months(f.VENDR_INPUT_DATE,-12)
WHEN t.TIME_PERD_ID LIKE 'P%MPP' THEN add_months(f.VENDR_INPUT_DATE,DECODE(t.TIME_PERD_ID,'P1MPP',-1,'P2MPP',-2,'P3MPP',-3,'P6MPP',-6,'P12MPP',-12,0))
ELSE f.VENDR_INPUT_DATE END
) AS END_DATE
,er.exchg_rate AS exchg_rate
,CASE WHEN T.TIME_PERD_TYPE_CODE IN ('MH','WKMS') THEN NVL(tfyqr.tqr_name, '(empty)') ELSE '(empty)' END AS FISC_QTR_NAME
,CASE WHEN T.TIME_PERD_TYPE_CODE IN ('MH','WKMS') THEN NVL(tfyqr.tfy_name, '(empty)') ELSE '(empty)' END AS FISC_YR_NAME
,g.GEO_ID
,g.GEO_LONG_DESC
,p.LOWST_LVL
,f.fact_measr_51_val AS MEASR_EXTRA_VAL
,f.fact_measr_52_val AS MEASR_EXTRA_YA_VAL
,f.fact_measr_5_val AS NUM_DIST_PCT
,f.fact_measr_6_val AS NUM_DIST_YA_PCT
,f.fact_measr_43_val AS OUT_STOCK_PCT
,f.fact_measr_44_val AS OUT_STOCK_YA_PCT
,p.PROD_ID
,g.RPT_AREA_LVL
,g.RPT_AREA_NAME
,p.RPT_ATTR_01_NAME
,p.RPT_ATTR_02_NAME
,p.RPT_ATTR_03_NAME
,p.RPT_ATTR_04_NAME
,p.RPT_ATTR_05_NAME
,p.RPT_ATTR_06_NAME
,p.RPT_ATTR_07_NAME
,p.RPT_ATTR_08_NAME
,p.RPT_ATTR_09_NAME
,p.RPT_ATTR_10_NAME
,p.RPT_ATTR_11_NAME
,p.RPT_ATTR_12_NAME
,p.RPT_ATTR_13_NAME
,p.RPT_ATTR_14_NAME
,NVL(plbf.fld_val, '(empty)') AS RPT_BRAND_FRNCH_NAME
,p.RPT_BRAND_NAME
,p.RPT_CATEG_NAME
,g.RPT_CNTRY_CODE
,g.RPT_CNTRY_NAME
,p.RPT_CO_NAME
,g.RPT_CUST_TYPE_NAME
,p.RPT_HIER_NAME ,g.RPT_HIGST_PRMTN_LVL
,g.RPT_HUB_NAME
,p.RPT_LOWST_LVL
,p.RPT_MRBU_NAME
,p.RPT_PROD_ID
,p.RPT_PROD_LVL
,p.RPT_RBU_NAME
,g.RPT_REGN_NAME
,(f.fact_measr_13_val /
DECODE(NVL(NVL(cfa.CVRG_FACTR_THSND_SU_RATE,cf.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE)),
0,NULL,
NVL(NVL(cfa.CVRG_FACTR_THSND_SU_RATE,cf.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE))
)
) AS SALES_EXPND_THSND_SU_QTY
,((f.fact_measr_10_val /
DECODE(NVL(NVL(cfa.CVRG_FACTR_THSND_LC_RATE,cf.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE)),
0,NULL,
NVL(NVL(cfa.CVRG_FACTR_THSND_LC_RATE,cf.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE))
)
) * er.exchg_rate) AS SALES_EXPND_THSND_USD_AMT
,(f.fact_measr_12_val /
DECODE(NVL(NVL(cfyaa.CVRG_FACTR_THSND_SU_RATE,cfya.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE)),
0,NULL,
NVL(NVL(cfyaa.CVRG_FACTR_THSND_SU_RATE,cfya.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE))
)
) AS SALES_EXPND_YA_THSND_SU_QTY
,((f.fact_measr_11_val /
DECODE(NVL(NVL(cfyaa.CVRG_FACTR_THSND_LC_RATE,cfya.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE)),
0,NULL,
NVL(NVL(cfyaa.CVRG_FACTR_THSND_LC_RATE,cfya.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE))
)
) * er.exchg_rate) AS SALES_EXPND_YA_THSND_USD_AMT
,(f.fact_measr_11_val - f.fact_measr_22_val) AS SALES_NON_PRMTN_AMT
,(f.fact_measr_13_val - f.fact_measr_20_val) AS SALES_NON_PRMTN_QTY
,(f.fact_measr_10_val - f.fact_measr_23_val) AS SALES_NON_PRMTN_YA_AMT
,(f.fact_measr_12_val - f.fact_measr_21_val) AS SALES_NON_PRMTN_YA_QTY
,f.fact_measr_23_val AS SALES_PRMTN_AMT
,f.fact_measr_20_val AS SALES_PRMTN_QTY
,f.fact_measr_18_val AS SALES_PRMTN_UNIT_QTY
,f.fact_measr_19_val AS SALES_PRMTN_UNIT_YA_QTY
,f.fact_measr_22_val AS SALES_PRMTN_YA_AMT
,f.fact_measr_21_val AS SALES_PRMTN_YA_QTY
,f.fact_measr_10_val AS SALES_THSND_LC_AMT
,f.fact_measr_7_val AS SALES_THSND_QTY
,f.fact_measr_13_val AS SALES_THSND_SU_QTY
,f.fact_measr_55_val AS SALES_TPR_AMT
,f.fact_measr_56_val AS SALES_TPR_YA_AMT
,f.fact_measr_9_val AS SALES_USD_AMT
,f.fact_measr_11_val AS SALES_YA_THSND_LC_AMT
,f.fact_measr_40_val AS SALES_YA_THSND_QTY
,f.fact_measr_12_val AS SALES_YA_THSND_SU_QTY
,f.fact_measr_8_val AS SALES_YA_USD_AMT
,f.fact_measr_32_val AS SHARE_DSPLY_PCT
,f.fact_measr_33_val AS SHARE_DSPLY_YA_PCT
,f.fact_measr_34_val AS SHARE_FEATR_PCT
,f.fact_measr_35_val AS SHARE_FEATR_YA_PCT
,f.fact_measr_36_val AS SHARE_SHELF_PCT
,f.fact_measr_37_val AS SHARE_SHELF_YA_PCT
,f.fact_measr_38_val AS SHARE_SKU_PCT
,f.fact_measr_39_val AS SHARE_SKU_YA_PCT
,f.fact_measr_45_val AS STOCK_PCT
,f.fact_measr_46_val AS STOCK_YA_PCT
,CASE WHEN t.name LIKE 'FYTD_-%' THEN 'FYTD-' || substr(t.name,7)
WHEN t.name LIKE 'FYTD__-%' THEN 'FYTD-' || substr(t.name,8)
ELSE NVL(t.name, '(empty)') END AS TIME_NAME
,t.TIME_PERD_ID
,NVL(CASE WHEN T.TIME_PERD_ID LIKE 'P%M' THEN T.TIME_PERD_ID || 'PP'
WHEN T.TIME_PERD_ID LIKE 'P%MYA' THEN '(empty)'
WHEN T.TIME_PERD_ID LIKE 'P%MPP' THEN '(empty)'
ELSE fn_crs_get_time_perd_id(T.TIME_PERD_TYPE_CODE,NULL,t.start_date-1)
END, '(empty)') as TIME_PERD_PREV_ID
,NVL(CASE WHEN T.TIME_PERD_ID LIKE 'P%M' THEN 'PXM'
WHEN T.TIME_PERD_ID LIKE 'P%MYA' THEN 'PXMYA'
WHEN T.TIME_PERD_ID LIKE 'P%MPP' THEN 'PXMPP'
ELSE T.TIME_PERD_TYPE_CODE
END, '(empty)') AS TIME_PERD_TYPE_CODE
,NVL(CASE WHEN T.TIME_PERD_ID LIKE 'P%M' THEN T.TIME_PERD_ID || 'YA'
WHEN T.TIME_PERD_ID LIKE 'P%MYA' THEN '(empty)'
WHEN T.TIME_PERD_ID LIKE 'P%MPP' THEN '(empty)'
ELSE fn_crs_get_time_perd_id(T.TIME_PERD_TYPE_CODE,add_months(t.start_date,-12),add_months(t.end_date,-12))
END, '(empty)') as TIME_PERD_YA_ID
,decode(g.srce_iso_crncy_code, 'USD', 1, ervar.exchg_rate) AS var_exchg_rate
,f.VENDR_INPUT_DATE
,f.fact_measr_25_val AS WGHT_DIST_ANY_PRMTN_PCT
,f.fact_measr_24_val AS WGHT_DIST_ANY_PRMTN_YA_PCT
,f.fact_measr_41_val AS WGHT_DIST_DSPLY_FEATR_PCT
,f.fact_measr_42_val AS WGHT_DIST_DSPLY_FEATR_YA_PCT
,f.fact_measr_16_val AS WGHT_DIST_DSPLY_PCT
,f.fact_measr_17_val AS WGHT_DIST_DSPLY_YA_PCT
,f.fact_measr_26_val AS WGHT_DIST_FEATR_PCT
,f.fact_measr_27_val AS WGHT_DIST_FEATR_YA_PCT
,f.fact_measr_14_val AS WGHT_DIST_PCT
,f.fact_measr_53_val AS WGHT_DIST_TPR_PCT
,f.fact_measr_54_val AS WGHT_DIST_TPR_YA_PCT
,f.fact_measr_15_val AS WGHT_DIST_YA_PCT
/*CE*/
FROM crs_rpt_cntrt_mv accc - - - - - 1574 records
JOIN crs_data_fct f - - - - - 155063978 records , and 31.5Gb space
ON accc.CNTRT_ID = f.CNTRT_ID
JOIN CRS_RPT_GEO_DMNSN_VW g - - - - - - - it’s view and it has 19906 records
ON f.CNTRT_ID = g.CNTRT_ID
AND f.GEO_ID = g.GEO_ID
AND accc.AGG_NAME = g.AGG_NAME
JOIN CRS_RPT_PROD_DMNSN_VW p - - - - - - it’s view , this view is very slow to retune all the result and it has 1508388 records
ON f.CNTRT_ID = p.CNTRT_ID
AND f.PROD_ID = p.PROD_ID
AND accc.AGG_NAME = p.AGG_NAME
JOIN crs_time_perd_fdim t - - - - - - - 15808 records
ON t.TIME_PERD_ID = f.TIME_PERD_ID
LEFT JOIN exchange_rate_cutover er
ON er.srce_iso_crncy_code = g.srce_iso_crncy_code
LEFT JOIN crs_exchg_rate_lkp ervar
ON ervar.srce_iso_crncy_code = g.srce_iso_crncy_code
AND CASE WHEN t.TIME_PERD_ID LIKE 'P%M' THEN f.VENDR_INPUT_DATE
WHEN t.TIME_PERD_ID LIKE 'P%MYA' THEN last_day(add_months(f.VENDR_INPUT_DATE,-12))
WHEN t.TIME_PERD_ID LIKE 'P%MPP' THEN last_day(add_months(f.VENDR_INPUT_DATE,DECODE(t.TIME_PERD_ID,'P1MPP',-1,'P2MPP',-2,'P3MPP',-3,'P6MPP',-6,'P12MPP',-12,0)))
ELSE last_day(t.end_date) END = last_day(ervar.end_date)
LEFT JOIN time_perd_fy_qr tfyqr
ON NVL2(t.end_date, t.end_date, CASE WHEN t.TIME_PERD_ID LIKE 'P%MYA' THEN add_months(f.VENDR_INPUT_DATE,-12)
WHEN t.TIME_PERD_ID LIKE 'P%MPP' THEN add_months(f.VENDR_INPUT_DATE,DECODE(t.TIME_PERD_ID,'P1MPP',-1,'P2MPP',-2,'P3MPP',-3,'P6MPP',-6,'P12MPP',-12,0))
ELSE f.VENDR_INPUT_DATE END) = tfyqr.end_date -- end_date NULL for TIME_PERD_ID P%M
LEFT JOIN crs_cntry_prod_map_ulkp plbf
ON plbf.CNTRY_ID = g.CNTRY_ID
AND plbf.extrn_categ_map_name = p.RPT_CATEG_NAME
AND plbf.fld_name = 'BRAND_FRNCH'
AND plbf.extrn_fld_val = p.RPT_BRAND_NAME
--default area name cf
-- Coverage Factors only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cf
ON cf.CNTRY_ID = g.CNTRY_ID
AND cf.rpt_categ_name = p.RPT_CATEG_NAME
AND cf.end_date = tfyqr.tfy_end_date
AND cf.area_lvl_name = g.rpt_area_lvl
AND cf.rpt_area_name = 'DEFAULT'
-- Coverage Factors YA only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cfya
ON cfya.CNTRY_ID = g.CNTRY_ID
AND cfya.rpt_categ_name = p.RPT_CATEG_NAME
AND cfya.end_date = add_months(tfyqr.tfy_end_date,-12)
AND cfya.area_lvl_name = g.rpt_area_lvl
AND cfya.rpt_area_name = 'DEFAULT'
-- Default/Manual Coverage Factors for time periods missing in WWMD
LEFT JOIN cvrg_factr cfdflt
ON cfdflt.CNTRY_ID = g.CNTRY_ID
AND cfdflt.rpt_categ_name = p.RPT_CATEG_NAME
AND cfdflt.TIME_PERD_ID = 0 -- DUMMY TIME_PERD_ID for CFs missing from WWMD
AND cfdflt.area_lvl_name = g.rpt_area_lvl
AND cfdflt.rpt_area_name = 'DEFAULT'
--exception area name cf
-- Coverage Factors only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cfa
ON cfa.CNTRY_ID = g.CNTRY_ID
AND cfa.rpt_categ_name = p.RPT_CATEG_NAME
AND cfa.end_date = tfyqr.tfy_end_date
AND cfa.area_lvl_name = g.rpt_area_lvl
AND cfa.rpt_area_name = g.rpt_area_name
-- Coverage Factors YA only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cfyaa
ON cfyaa.CNTRY_ID = g.CNTRY_ID
AND cfyaa.rpt_categ_name = p.RPT_CATEG_NAME
AND cfyaa.end_date = add_months(tfyqr.tfy_end_date,-12)
AND cfyaa.area_lvl_name = g.rpt_area_lvl
AND cfyaa.rpt_area_name = g.rpt_area_name
-- Default/Manual Coverage Factors for time periods missing in WWMD
LEFT JOIN cvrg_factr cfdflta
ON cfdflta.CNTRY_ID = g.CNTRY_ID
AND cfdflta.rpt_categ_name = p.RPT_CATEG_NAME
AND cfdflta.TIME_PERD_ID = 0 -- DUMMY TIME_PERD_ID for CFs missing from WWMD
AND cfdflta.area_lvl_name = g.rpt_area_lvl
AND cfdflta.rpt_area_name = g.rpt_area_name
WHERE f.top_x_ind = 'Y' -- only Top X facts
AND t.time_perd_type_code NOT IN ('EB', 'OB')
Table crs_data_fct is FACT table, and has 31.5Gb , It’s partition table.
SQL> select sum(bytes/1024/1024/1024) "Gb" from dba_segments where segment_name= 'CRS_DATA_FCT' AND OWNER='ADWGQ_CRS';
Gb
----------
31.5250854
But the table did not use COMPRESS, why not use COMPRESS FOR QUERY HITH since it’s FACT table?
SQL> SELECT DEF_COMPRESSION,DEF_COMPRESS_FOR FROM DBA_PART_TABLES WHERE owner='ADWGQ_CRS' AND TABLE_NAME='CRS_DATA_FCT';
DEF_COMPRESSION DEF_COMPRESS_FOR
-------------------------------------------------- ------------------------------------------------
NONE
Please see my test
SQL> CREATE TABLE ADWGQ_CRS.TEST_FOR_CRS parallel 8 NOLOGGING COMPRESS FOR QUERY HIGH AS SELECT /*+ parallel(a 8) */ * FROM ADWGQ_CRS.CRS_DATA_FCT a;
Table created.
SQL> select sum(bytes/1024/1024/1024) "Gb" from dba_segments where segment_name= 'TEST_FOR_CRS' AND OWNER='ADWGQ_CRS';
Gb
----------
7.67913818
Use compress for query high, it only take 7.7Gb , 1/4 of the original size.
For the VIEW CRS_RPT_PROD_DMNSN_VW , if only retune few rows, it’s runs very fast... but here , it will retune whole rows , and it’s very slow...
So we need to create MV for the view, here I didn’t create MV, I just create a table named PP instead , also use COMPRESS FOR QUERY HIGH
Also I create a table for view CRS_RPT_GEO_DMNSN_VW name G
Then I create a test view crs_rpt_basic_check_vw AS BELOW
create or replace view crs_rpt_basic_check_vw_test as
WITH cvrg_factr AS ( ---1661688
SELECT /*+ MATERIALIZE */
cf1.cntry_id,
rc.rpt_categ_name,
cf1.area_lvl_name,
cf1.rpt_area_name,
cf1.time_perd_id,
t.end_date,
nvl(cf2.cvrg_factr_thsnd_su_rate, cf1.cvrg_factr_thsnd_su_rate) AS cvrg_factr_thsnd_su_rate,
nvl(cf2.cvrg_factr_thsnd_lc_rate, cf1.cvrg_factr_thsnd_lc_rate) AS cvrg_factr_thsnd_lc_rate
FROM crs_cvrg_factr_uprc cf1
JOIN crs_rpt_categ_uprc rc
ON cf1.wwmd_seg_name = rc.wwmd_seg_name
JOIN crs_time_perd_fdim t
ON cf1.time_perd_id = t.time_perd_id
LEFT JOIN crs_cvrg_factr_uprc cf2
ON cf1.wwmd_seg_name = cf2.wwmd_seg_name
AND cf1.time_perd_id = cf2.time_perd_id
AND cf1.rpt_area_name = cf2.rpt_area_name
AND cf1.area_lvl_name = cf2.area_lvl_name
AND cf1.cntry_id = cf2.cntry_id
AND cf2.srce_name = 'MANUAL'
WHERE cf1.srce_name != 'MANUAL'
)
,time_perd_fy_qr AS ( ---565
SELECT /*+ MATERIALIZE */
t.TIME_PERD_ID
,t. name
,t.end_date
,tfy. name AS tfy_name
,tfy.end_date AS tfy_end_date
,tqr. name AS tqr_name
FROM crs_time_perd_fdim t
LEFT JOIN crs_time_perd_fdim tfy
ON tfy.time_perd_type_code = 'FY'
AND t.end_date BETWEEN tfy.start_date
AND tfy.end_date
LEFT JOIN crs_time_perd_fdim tqr
ON tqr.time_perd_type_code = 'QR'
AND t.end_date BETWEEN tqr.start_date
AND tqr.end_date
WHERE t.time_perd_type_code = 'MH'
),
exchange_rate_cutover AS ( ---106
SELECT /*+ MATERIALIZE */ srce_iso_crncy_code,
MAX(exchg_rate) AS exchg_rate
FROM crs_exchg_rate_lkp
WHERE (last_day(end_date) = last_day(( SELECT to_date(parm_val,'YYYY-MM-DD')
FROM crs_cnfg_uprc
WHERE parm_name = 'WWMD_CUT_OVER_DATE')))
OR (srce_iso_crncy_code = 'USD')
GROUP BY srce_iso_crncy_code
)
SELECT /*+ parallel(acct 8) PARALLEL(f 8) USE_HASH(acct,f) */
/*CS*/ accc.AGG_NAME AS AGG_NAME
,p.ATTR_01_NAME
,p.ATTR_02_NAME
,p.ATTR_03_NAME
,p.ATTR_04_NAME
,p.ATTR_05_NAME
,p.ATTR_06_NAME
,p.ATTR_07_NAME
,p.ATTR_08_NAME
,p.ATTR_09_NAME
,p.ATTR_10_NAME
,p.ATTR_11_NAME
,p.ATTR_12_NAME
,p.ATTR_13_NAME
,p.ATTR_14_NAME
,f.fact_measr_57_val AS AVG_PURCH_AMT
,f.fact_measr_58_val AS AVG_PURCH_YA_AMT
,p.BRAND_NAME
,f.fact_measr_28_val AS BRAND_SHARE_DSPLY_PCT
,f.fact_measr_29_val AS BRAND_SHARE_DSPLY_YA_PCT
,f.fact_measr_30_val AS BRAND_SHARE_FEATR_PCT
,f.fact_measr_31_val AS BRAND_SHARE_FEATR_YA_PCT
,p.CATEG_NAME
,f.fact_measr_1_val AS CATEG_SHARE_VAL_PCT
,f.fact_measr_2_val AS CATEG_SHARE_VAL_YA_PCT
,f.fact_measr_4_val AS CATEG_SHARE_VOL_PCT
,f.fact_measr_3_val AS CATEG_SHARE_VOL_YA_PCT
,accc.CNTRT_ID AS CNTRT_ID
,p.CO_NAME
,NVL(NVL(cfa.CVRG_FACTR_THSND_LC_RATE,cf.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE))
AS CVRG_FACTR_THSND_LC_RATE
,NVL(NVL(cfa.CVRG_FACTR_THSND_SU_RATE,cf.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE))
AS CVRG_FACTR_THSND_SU_RATE
,NVL(NVL(cfyaa.CVRG_FACTR_THSND_LC_RATE,cfya.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE))
AS CVRG_YA_FACTR_THSND_LC_RATE
,NVL(NVL(cfyaa.CVRG_FACTR_THSND_SU_RATE,cfya.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE))
AS CVRG_YA_FACTR_THSND_SU_RATE
,f.fact_measr_49_val AS DIST_EXTRA_2_VAL
,f.fact_measr_50_val AS DIST_EXTRA_2_YA_VAL
,f.fact_measr_47_val AS DIST_EXTRA_VAL
,f.fact_measr_48_val AS DIST_EXTRA_YA_VAL
,NVL2(t.end_date, t.end_date, CASE WHEN t.TIME_PERD_ID
LIKE 'P%MYA' THEN add_months(f.VENDR_INPUT_DATE,-12)
WHEN t.TIME_PERD_ID
LIKE 'P%MPP' THEN add_months(f.VENDR_INPUT_DATE,DECODE(t.TIME_PERD_ID,'P1MPP',-1,'P2MPP',-2,'P3MPP',-3,'P6MPP',-6,'P12MPP',-12,0))
ELSE f.VENDR_INPUT_DATE
END
) AS END_DATE
,er.exchg_rate AS exchg_rate
, CASE WHEN T.TIME_PERD_TYPE_CODE IN ('MH','WKMS')
THEN NVL(tfyqr.tqr_name, '(empty)') ELSE '(empty)'
END AS FISC_QTR_NAME
, CASE WHEN T.TIME_PERD_TYPE_CODE IN ('MH','WKMS')
THEN NVL(tfyqr.tfy_name, '(empty)') ELSE '(empty)'
END AS FISC_YR_NAME
,g.GEO_ID
,g.GEO_LONG_DESC
,p.LOWST_LVL
,f.fact_measr_51_val AS MEASR_EXTRA_VAL
,f.fact_measr_52_val AS MEASR_EXTRA_YA_VAL
,f.fact_measr_5_val AS NUM_DIST_PCT
,f.fact_measr_6_val AS NUM_DIST_YA_PCT
,f.fact_measr_43_val AS OUT_STOCK_PCT
,f.fact_measr_44_val AS OUT_STOCK_YA_PCT
,p.PROD_ID
,g.RPT_AREA_LVL
,g.RPT_AREA_NAME
,p.RPT_ATTR_01_NAME
,p.RPT_ATTR_02_NAME
,p.RPT_ATTR_03_NAME
,p.RPT_ATTR_04_NAME
,p.RPT_ATTR_05_NAME
,p.RPT_ATTR_06_NAME
,p.RPT_ATTR_07_NAME
,p.RPT_ATTR_08_NAME
,p.RPT_ATTR_09_NAME
,p.RPT_ATTR_10_NAME
,p.RPT_ATTR_11_NAME
,p.RPT_ATTR_12_NAME
,p.RPT_ATTR_13_NAME
,p.RPT_ATTR_14_NAME
,NVL(plbf.fld_val, '(empty)') AS RPT_BRAND_FRNCH_NAME
,p.RPT_BRAND_NAME
,p.RPT_CATEG_NAME
,g.RPT_CNTRY_CODE
,g.RPT_CNTRY_NAME
,p.RPT_CO_NAME
,g.RPT_CUST_TYPE_NAME
,p.RPT_HIER_NAME ,g.RPT_HIGST_PRMTN_LVL
,g.RPT_HUB_NAME
,p.RPT_LOWST_LVL
,p.RPT_MRBU_NAME
,p.RPT_PROD_ID
,p.RPT_PROD_LVL
,p.RPT_RBU_NAME
,g.RPT_REGN_NAME
,(f.fact_measr_13_val /
DECODE(NVL(NVL(cfa.CVRG_FACTR_THSND_SU_RATE,cf.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE)),
0, NULL,
NVL(NVL(cfa.CVRG_FACTR_THSND_SU_RATE,cf.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE))
)
) AS SALES_EXPND_THSND_SU_QTY
,((f.fact_measr_10_val /
DECODE(NVL(NVL(cfa.CVRG_FACTR_THSND_LC_RATE,cf.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE)),
0, NULL,
NVL(NVL(cfa.CVRG_FACTR_THSND_LC_RATE,cf.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE))
)
) * er.exchg_rate) AS SALES_EXPND_THSND_USD_AMT
,(f.fact_measr_12_val /
DECODE(NVL(NVL(cfyaa.CVRG_FACTR_THSND_SU_RATE,cfya.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE)),
0, NULL,
NVL(NVL(cfyaa.CVRG_FACTR_THSND_SU_RATE,cfya.CVRG_FACTR_THSND_SU_RATE),NVL(cfdflta.CVRG_FACTR_THSND_SU_RATE,cfdflt.CVRG_FACTR_THSND_SU_RATE))
)
) AS SALES_EXPND_YA_THSND_SU_QTY
,((f.fact_measr_11_val /
DECODE(NVL(NVL(cfyaa.CVRG_FACTR_THSND_LC_RATE,cfya.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE)),
0, NULL,
NVL(NVL(cfyaa.CVRG_FACTR_THSND_LC_RATE,cfya.CVRG_FACTR_THSND_LC_RATE),NVL(cfdflta.CVRG_FACTR_THSND_LC_RATE,cfdflt.CVRG_FACTR_THSND_LC_RATE))
)
) * er.exchg_rate) AS SALES_EXPND_YA_THSND_USD_AMT
,(f.fact_measr_11_val - f.fact_measr_22_val) AS SALES_NON_PRMTN_AMT
,(f.fact_measr_13_val - f.fact_measr_20_val) AS SALES_NON_PRMTN_QTY
,(f.fact_measr_10_val - f.fact_measr_23_val) AS SALES_NON_PRMTN_YA_AMT
,(f.fact_measr_12_val - f.fact_measr_21_val) AS SALES_NON_PRMTN_YA_QTY
,f.fact_measr_23_val AS SALES_PRMTN_AMT
,f.fact_measr_20_val AS SALES_PRMTN_QTY
,f.fact_measr_18_val AS SALES_PRMTN_UNIT_QTY
,f.fact_measr_19_val AS SALES_PRMTN_UNIT_YA_QTY
,f.fact_measr_22_val AS SALES_PRMTN_YA_AMT
,f.fact_measr_21_val AS SALES_PRMTN_YA_QTY
,f.fact_measr_10_val AS SALES_THSND_LC_AMT
,f.fact_measr_7_val AS SALES_THSND_QTY
,f.fact_measr_13_val AS SALES_THSND_SU_QTY
,f.fact_measr_55_val AS SALES_TPR_AMT
,f.fact_measr_56_val AS SALES_TPR_YA_AMT
,f.fact_measr_9_val AS SALES_USD_AMT
,f.fact_measr_11_val AS SALES_YA_THSND_LC_AMT
,f.fact_measr_40_val AS SALES_YA_THSND_QTY
,f.fact_measr_12_val AS SALES_YA_THSND_SU_QTY
,f.fact_measr_8_val AS SALES_YA_USD_AMT
,f.fact_measr_32_val AS SHARE_DSPLY_PCT
,f.fact_measr_33_val AS SHARE_DSPLY_YA_PCT
,f.fact_measr_34_val AS SHARE_FEATR_PCT
,f.fact_measr_35_val AS SHARE_FEATR_YA_PCT
,f.fact_measr_36_val AS SHARE_SHELF_PCT
,f.fact_measr_37_val AS SHARE_SHELF_YA_PCT
,f.fact_measr_38_val AS SHARE_SKU_PCT
,f.fact_measr_39_val AS SHARE_SKU_YA_PCT
,f.fact_measr_45_val AS STOCK_PCT
,f.fact_measr_46_val AS STOCK_YA_PCT
, CASE WHEN t. name LIKE 'FYTD_-%'
THEN 'FYTD-' || substr(t. name,7)
WHEN t. name LIKE 'FYTD__-%'
THEN 'FYTD-' || substr(t. name,8)
ELSE NVL(t. name, '(empty)')
END AS TIME_NAME
,t.TIME_PERD_ID
,NVL( CASE WHEN T.TIME_PERD_ID LIKE 'P%M'
THEN T.TIME_PERD_ID || 'PP'
WHEN T.TIME_PERD_ID LIKE 'P%MYA'
THEN '(empty)'
WHEN T.TIME_PERD_ID LIKE 'P%MPP'
THEN '(empty)'
ELSE fn_crs_get_time_perd_id(T.TIME_PERD_TYPE_CODE, NULL,t.start_date-1)
END, '(empty)') as TIME_PERD_PREV_ID
,NVL( CASE WHEN T.TIME_PERD_ID LIKE 'P%M'
THEN 'PXM'
WHEN T.TIME_PERD_ID LIKE 'P%MYA'
THEN 'PXMYA'
WHEN T.TIME_PERD_ID LIKE 'P%MPP'
THEN 'PXMPP'
ELSE T.TIME_PERD_TYPE_CODE
END, '(empty)') AS TIME_PERD_TYPE_CODE
,NVL( CASE WHEN T.TIME_PERD_ID LIKE 'P%M'
THEN T.TIME_PERD_ID || 'YA'
WHEN T.TIME_PERD_ID LIKE 'P%MYA'
THEN '(empty)'
WHEN T.TIME_PERD_ID LIKE 'P%MPP'
THEN '(empty)'
ELSE fn_crs_get_time_perd_id(T.TIME_PERD_TYPE_CODE,add_months(t.start_date,-12),add_months(t.end_date,-12))
END, '(empty)') as TIME_PERD_YA_ID
,decode(g.srce_iso_crncy_code, 'USD', 1, ervar.exchg_rate) AS var_exchg_rate
,f.VENDR_INPUT_DATE
,f.fact_measr_25_val AS WGHT_DIST_ANY_PRMTN_PCT
,f.fact_measr_24_val AS WGHT_DIST_ANY_PRMTN_YA_PCT
,f.fact_measr_41_val AS WGHT_DIST_DSPLY_FEATR_PCT
,f.fact_measr_42_val AS WGHT_DIST_DSPLY_FEATR_YA_PCT
,f.fact_measr_16_val AS WGHT_DIST_DSPLY_PCT
,f.fact_measr_17_val AS WGHT_DIST_DSPLY_YA_PCT
,f.fact_measr_26_val AS WGHT_DIST_FEATR_PCT
,f.fact_measr_27_val AS WGHT_DIST_FEATR_YA_PCT
,f.fact_measr_14_val AS WGHT_DIST_PCT
,f.fact_measr_53_val AS WGHT_DIST_TPR_PCT
,f.fact_measr_54_val AS WGHT_DIST_TPR_YA_PCT
,f.fact_measr_15_val AS WGHT_DIST_YA_PCT
/*CE*/
FROM crs_rpt_cntrt_mv accc ---1494
JOIN TEST_FOR_CRS f --- USE COMPRESS FOR QUERY HIGH
ON accc.CNTRT_ID = f.CNTRT_ID
JOIN g --- USE TABLE INSTEAD , YOU CAN USE MV
ON f.CNTRT_ID = g.CNTRT_ID
AND f.GEO_ID = g.GEO_ID
AND accc.AGG_NAME = g.AGG_NAME
JOIN pp p ---- USE TABLE INSTEAD , YOU CAN USE MV
ON f.CNTRT_ID = p.CNTRT_ID
AND f.PROD_ID = p.PROD_ID
AND accc.AGG_NAME = p.AGG_NAME
JOIN crs_time_perd_fdim t
ON t.TIME_PERD_ID = f.TIME_PERD_ID
LEFT JOIN exchange_rate_cutover er
ON er.srce_iso_crncy_code = g.srce_iso_crncy_code
LEFT JOIN crs_exchg_rate_lkp ervar
ON ervar.srce_iso_crncy_code = g.srce_iso_crncy_code
AND CASE WHEN t.TIME_PERD_ID LIKE 'P%M'
THEN f.VENDR_INPUT_DATE
WHEN t.TIME_PERD_ID LIKE 'P%MYA' THEN last_day(add_months(f.VENDR_INPUT_DATE,-12))
WHEN t.TIME_PERD_ID
LIKE 'P%MPP' THEN last_day(add_months(f.VENDR_INPUT_DATE,DECODE(t.TIME_PERD_ID,'P1MPP',-1,'P2MPP',-2,'P3MPP',-3,'P6MPP',-6,'P12MPP',-12,0)))
ELSE last_day(t.end_date)
END = last_day(ervar.end_date)
LEFT JOIN time_perd_fy_qr tfyqr
ON NVL2(t.end_date, t.end_date, CASE WHEN t.TIME_PERD_ID
LIKE 'P%MYA' THEN add_months(f.VENDR_INPUT_DATE,-12)
WHEN t.TIME_PERD_ID
LIKE 'P%MPP' THEN add_months(f.VENDR_INPUT_DATE,DECODE(t.TIME_PERD_ID,'P1MPP',-1,'P2MPP',-2,'P3MPP',-3,'P6MPP',-6,'P12MPP',-12,0))
ELSE f.VENDR_INPUT_DATE
END) = tfyqr.end_date -- end_date NULL for TIME_PERD_ID P%M
LEFT JOIN crs_cntry_prod_map_ulkp plbf
ON plbf.CNTRY_ID = g.CNTRY_ID
AND plbf.extrn_categ_map_name = p.RPT_CATEG_NAME
AND plbf.fld_name = 'BRAND_FRNCH'
AND plbf.extrn_fld_val = p.RPT_BRAND_NAME
--default area name cf
-- Coverage Factors only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cf
ON cf.CNTRY_ID = g.CNTRY_ID
AND cf.rpt_categ_name = p.RPT_CATEG_NAME
AND cf.end_date = tfyqr.tfy_end_date
AND cf.area_lvl_name = g.rpt_area_lvl
AND cf.rpt_area_name = 'DEFAULT'
-- Coverage Factors YA only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cfya
ON cfya.CNTRY_ID = g.CNTRY_ID
AND cfya.rpt_categ_name = p.RPT_CATEG_NAME
AND cfya.end_date = add_months(tfyqr.tfy_end_date,-12)
AND cfya.area_lvl_name = g.rpt_area_lvl
AND cfya.rpt_area_name = 'DEFAULT'
-- Default/Manual Coverage Factors for time periods missing in WWMD
LEFT JOIN cvrg_factr cfdflt
ON cfdflt.CNTRY_ID = g.CNTRY_ID
AND cfdflt.rpt_categ_name = p.RPT_CATEG_NAME
AND cfdflt.TIME_PERD_ID = 0 -- DUMMY TIME_PERD_ID for CFs missing from WWMD
AND cfdflt.area_lvl_name = g.rpt_area_lvl
AND cfdflt.rpt_area_name = 'DEFAULT'
--exception area name cf
-- Coverage Factors only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cfa
ON cfa.CNTRY_ID = g.CNTRY_ID
AND cfa.rpt_categ_name = p.RPT_CATEG_NAME
AND cfa.end_date = tfyqr.tfy_end_date
AND cfa.area_lvl_name = g.rpt_area_lvl
AND cfa.rpt_area_name = g.rpt_area_name
-- Coverage Factors YA only for FY time aggregates, exploded on end_date for other time aggregates
LEFT JOIN cvrg_factr cfyaa
ON cfyaa.CNTRY_ID = g.CNTRY_ID
AND cfyaa.rpt_categ_name = p.RPT_CATEG_NAME
AND cfyaa.end_date = add_months(tfyqr.tfy_end_date,-12)
AND cfyaa.area_lvl_name = g.rpt_area_lvl
AND cfyaa.rpt_area_name = g.rpt_area_name
-- Default/Manual Coverage Factors for time periods missing in WWMD
LEFT JOIN cvrg_factr cfdflta
ON cfdflta.CNTRY_ID = g.CNTRY_ID
AND cfdflta.rpt_categ_name = p.RPT_CATEG_NAME
AND cfdflta.TIME_PERD_ID = 0 -- DUMMY TIME_PERD_ID for CFs missing from WWMD
AND cfdflta.area_lvl_name = g.rpt_area_lvl
AND cfdflta.rpt_area_name = g.rpt_area_name
WHERE f.top_x_ind = 'Y' -- only Top X facts
AND t.time_perd_type_code NOT IN ('EB', 'OB');
And create test view for CRS_RPT_ERR_DUP_VW
create or replace view CRS_RPT_ERR_DUP_VW_test as SELECT
/*CS*/ AGG_NAME
,CVRG_FACTR_THSND_LC_RATE
,CVRG_FACTR_THSND_SU_RATE
,CVRG_YA_FACTR_THSND_LC_RATE
,CVRG_YA_FACTR_THSND_SU_RATE
,END_DATE -- AS END_DATE
,EXCHG_RATE
,FISC_QTR_NAME
,FISC_YR_NAME
, count(*) AS RCD_CNT
,RPT_AREA_LVL
,RPT_AREA_NAME
,RPT_ATTR_01_NAME
,RPT_ATTR_02_NAME
,RPT_ATTR_03_NAME
,RPT_ATTR_04_NAME
,RPT_ATTR_05_NAME
,RPT_ATTR_06_NAME
,RPT_ATTR_07_NAME
,RPT_ATTR_08_NAME
,RPT_ATTR_09_NAME
,RPT_ATTR_10_NAME
,RPT_ATTR_11_NAME
,RPT_ATTR_12_NAME
,RPT_ATTR_13_NAME
,RPT_ATTR_14_NAME
,RPT_BRAND_FRNCH_NAME
,RPT_BRAND_NAME
,RPT_CATEG_NAME
,RPT_CNTRY_CODE
,RPT_CNTRY_NAME
,RPT_CO_NAME
,RPT_CUST_TYPE_NAME
,RPT_HIGST_PRMTN_LVL
,RPT_HUB_NAME
,RPT_LOWST_LVL
,RPT_MRBU_NAME
,PROD_ID
,RPT_PROD_ID
,RPT_PROD_LVL
,RPT_RBU_NAME
,RPT_REGN_NAME
, sum(decode(substr(CNTRT_ID,7,1),'S',1,0)) AS SF_RCD_CNT
,TIME_NAME
,TIME_PERD_TYPE_CODE
, sum(decode(substr(CNTRT_ID,7,1),'T',1,0)) AS TRDPN_RCD_CNT
, sum(decode(substr(CNTRT_ID,7,1),'A',1,0)) AS TRDPN_SF_RCD_CNT
/*CE*/
FROM crs_rpt_basic_check_vw_test
GROUP BY AGG_NAME
,CVRG_FACTR_THSND_LC_RATE
,CVRG_FACTR_THSND_SU_RATE
,CVRG_YA_FACTR_THSND_LC_RATE
,CVRG_YA_FACTR_THSND_SU_RATE
,END_DATE -- AS END_DATE
,EXCHG_RATE
,FISC_QTR_NAME
,FISC_YR_NAME
,RPT_AREA_LVL
,RPT_AREA_NAME
,RPT_ATTR_01_NAME
,RPT_ATTR_02_NAME
,RPT_ATTR_03_NAME
,RPT_ATTR_04_NAME
,RPT_ATTR_05_NAME
,RPT_ATTR_06_NAME
,RPT_ATTR_07_NAME
,RPT_ATTR_08_NAME
,RPT_ATTR_09_NAME
,RPT_ATTR_10_NAME
,RPT_ATTR_11_NAME
,RPT_ATTR_12_NAME
,RPT_ATTR_13_NAME
,RPT_ATTR_14_NAME
,RPT_BRAND_FRNCH_NAME
,RPT_BRAND_NAME
,RPT_CATEG_NAME
,RPT_CNTRY_CODE
,RPT_CNTRY_NAME
,RPT_CO_NAME
,RPT_CUST_TYPE_NAME
,RPT_HIGST_PRMTN_LVL
,RPT_HUB_NAME
,RPT_LOWST_LVL
,RPT_MRBU_NAME
,PROD_ID
,RPT_PROD_ID
,RPT_PROD_LVL
,RPT_RBU_NAME
,RPT_REGN_NAME
,TIME_NAME
,TIME_PERD_TYPE_CODE
having
-- for contracts with separate TP/SF data
( count(*) > 1
and ( sum(decode(substr(CNTRT_ID,7,1),'T',1,0)) > 1
OR sum(decode(substr(CNTRT_ID,7,1),'S',1,0)) > 1
)
)
-- for contracts with combined TP/SF data
OR ( count(*) > 1
AND sum(decode(substr(CNTRT_ID,7,1),'A',1,0)) > 1
)
Now let’s test
SQL> set timi on
SQL> SELECT COUNT(*) FROM ADWGQ_CRS.CRS_RPT_ERR_DUP_VW_test WHERE AGG_NAME = 'CNTRY_IT';
COUNT(*)
----------
19673
Elapsed: 00:09:50.39
Because data is huge, the SQL will use lots Memory to do HASH JOIN, and SROT.
If workarea_size_policy = auto, ORACLE can just provide 1G for one session to do HASH JOIN, or SORT, so we must set it to manual
SQL> alter session set workarea_size_policy = manual;
Session altered.
Elapsed: 00:00:00.40
SQL> alter session set hash_area_size = 2000000000;
Session altered.
Elapsed: 00:00:00.40
SQL> alter session set sort_area_size = 2000000000;
Session altered.
Elapsed: 00:00:00.40
SQL> SELECT COUNT(*) FROM ADWGQ_CRS.CRS_RPT_ERR_DUP_VW_test WHERE AGG_NAME = 'CNTRY_IT';
COUNT(*)
----------
19673
Elapsed: 00:01:14.88
Now it take only 1minutes 14 second….
优化这个SQL我用了很多技术，COMPRESS FOR QUERY HITH 去压缩大行的FACT表，减少表SEGMENT SIZE 缩小为以前的1/4 ，创建MV 代替视图，启用并行，手工分配 work area。这就是对付超大型的OLAP SQL的常用方法。