一个案例教包含优化OLAP超大型SQL的常用方法

标签: 包含 优化 olap | 发表时间:2012-02-17 23:11 | 作者:robinson1988
出处:http://blog.csdn.net

环境: 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的常用方法。

 

 

 

作者:robinson1988 发表于2012-2-17 23:11:03 原文链接
阅读:8 评论:0 查看评论

相关 [包含 优化 olap] 推荐:

一个案例教包含优化OLAP超大型SQL的常用方法

- - CSDN博客推荐文章
环境: EXADATA V2 , 版本 11.2.0.2.0. 我的PM抱怨对我说,APEX上面有个SQL 要跑很久,最开始它跑3-5分钟,过了段时间,随着业务的变换,数据的增长,它要跑20分钟,随着数据的不断增长到现在它要跑1小时. PM说,这个SQL执行非常频繁,这里你想到了什么呢. 因为是11g,我们可以利用result cache去优化它,但是即使用了result cache,我们依然要面对一个事实--------第一次执行的时候它很慢,所以这个SQL必须优化.

Apache Flink OLAP引擎性能优化及应用

- - InfoQ推荐
导读:本次分享的主题为Apache Flink新场景——OLAP引擎,主要内容包括:. Apache Flink OLAP引擎. OLAP是一种让用户可以用从不同视角方便快捷的分析数据的计算方法. 主流的OLAP可以分为3类:多维OLAP ( Multi-dimensional OLAP )、关系型OLAP ( Relational OLAP ) 和混合OLAP ( Hybrid OLAP ) 三大类.

多维分析OLAP引擎Mondrian学习

- - Web前端 - ITeye博客
随着信息技术的飞速发展,在电力、电信、金融、大型制造等各个行业ERP、CRM、SCM、OA等越来越多的IT系统得以成功实施,这些分散建设的IT系统为各部门的运营效率提升发挥了很大的作用. 同时,为了满足业务管理和决策的报表系统(包括传统报表、数据仓库、OLAP等)也被创建出来,企业主管通过报表了解企业的总体运行状态.

开源OLAP引擎综评:HAWQ、Presto、ClickHouse

- - InfoQ推荐
谈到大数据就会联想到Hadoop、Spark整个生态的技术栈. 大家都知道开源大数据组件种类众多,其中开源OLAP引擎包含Hive、SparkSQL、Presto、HAWQ、ClickHouse、Impala、Kylin等. 当前企业对大数据的研究与应用日趋理性,那么,如何根据业务特点,选择一个适合自身场景的查询引擎呢.

web-scale OLAP系统应用解决方案

- - 冰火岛
为了支持linkedin在线应用“Who’s Viewed My Profile?” 和 “Who’s Viewed This Job?”等等. 构建OLAP 一个可伸缩和快速的serving system called Avatara to solve this many, small cubes problem.

Kylin:基于Hadoop的开源数据仓库OLAP分析引擎

- - 标点符
Kylin是一个开源、分布式的OLAP分析引擎,它由eBay公司开发,并且基于Hadoop提供了SQL接口和OLAP接口,能够支持TB到PB级别的数据量. OLAP即联机分析处理,它能够帮助分析人员、管理人员或执行人员从多角度快速、一致、交互地存取信息和更加深入的了解信息. OLAP的目标是满足决策支持或者满足在多维环境下特定的查询和报表需求.

MySQL与OLAP:分析型SQL查询最佳实践探索

- - Web前端 - ITeye博客
搞点多维分析,糙快猛的解决方案就是使用ROLAP(关系型OLAP)了. 数据经维度建模后存储在MySQL,ROLAP引擎(比如开源的Mondrian)负责将OLAP请求转化为SQL语句提交给数据库. OLAP计算分析功能导致MySQL需要进行较多复杂SQL查询,性能调优必不可少,本文总结了一些实用原则.

唯品会海量实时OLAP分析技术升级之路

- - 运维派
本文根据谢麟炯老师在〖DAMS 2017中国数据资产管理峰会〗现场演讲内容整理而成. 谢麟炯,唯品会大数据平台高级技术架构经理,主要负责大数据自助多维分析平台,离线数据开发平台及分析引擎团队的开发和管理工作,加入唯品会以来还曾负责流量基础数据的采集和数据仓库建设以及移动流量分析等数据产品的工作. 海量数据实时OLAP场景的困境.

最火实时大数据OLAP技术原理和实践

- -
Druid在大数据领域已经不是新人了,因此可能很多读者都已经听说过Druid,甚至用过Druid,但是未必每个人都真正清晰地了解Druid到底是什么,以及在什么情况下可以用Druid. 同时,为了避免大家听了半天,却一直陷在各种细节中但仍然不知道到底在听什么东西,我们还是有必要在开始的时候先总体谈一谈Druid到底是什么.

Kylin 大数据时代的OLAP利器 - CSDN博客

- -
Olap全称为在线联机分析应用,是一种对于多维数据分析查询的解决方案. 典型的Olap应用场景包括销售、市场、管理等商务报表,预算决算,经济报表等等. 最早的Olap查询工具是发布于1970年的Express,然而完整的Olap概念是在1993年由关系数据库之父 Edgar F.Codd 提出,伴随而来的是著名的“twelve laws of online analytical processing”.