执行长达2分多钟的大型sql优化

在工作中，有些报表的展示较复杂，且需要统计的内容不断变化，开发的sql越来越长，运行越来越慢，这时优化必不可免，先来看下如下冗长的执行了2分多钟的sql，看看从中是否能找出些规律
SELECT  T1.BASE_YW
                ,T1.BRNC_ID
                ,T1.OFFC_ID
                ,T1.MKT_MODL_ID
                ,T1.BASC_MODL
                ,T1.BI_CHNL_TP_02
                ,T1.BI_CHNL_TP_03
                ,T1.BRNC_ID_NM
                ,T1.OFFC_ID_NM
                ,T1.MKT_MODL_DI_NM
                ,T1.BASC_MODL_NM
                ,T1.BI_CHNL_TP_02_NM
                ,T1.BI_CHNL_TP_03_NM
                ,T2.SHOP_CNT
                ,T1.YW_01_SHOP_CNT
                ,T1.YW_02_SHOP_CNT
                ,T1.YW_03_SHOP_CNT
                ,T1.YW_04_SHOP_CNT
                ,T1.YW_05_SHOP_CNT
                ,T1.YW_06_SHOP_CNT
               
                ,0 GR_BI_CHNL_TP_02
                ,0 GR_BI_CHNL_TP_03
                ,0 GR_MKT_MODL_ID --ADD
                ,0 GR_BASC_MODL --ADD
                ,0 GR_OFFC_ID 
                 ,0 GR_BRNC_ID
        FROM    (
                    SELECT  MAX(T1.BASE_YW) BASE_YW
                            ,T1.BRNC_ID
                            ,T1.OFFC_ID
                            ,T1.MKT_MODL_ID
                            ,T1.BASC_MODL
                            ,T1.BI_CHNL_TP_02
                            ,T1.BI_CHNL_TP_03
                           
                            ,MAX(T1.BRNC_ID_NM) BRNC_ID_NM
                            ,MAX(T1.OFFC_ID_NM) OFFC_ID_NM
                            ,MAX(T1.MKT_MODL_ID) MKT_MODL_DI_NM
                            ,MAX(T1.BASC_MODL) BASC_MODL_NM
                            ,MAX(T1.BI_CHNL_TP_02_NM) BI_CHNL_TP_02_NM
                            ,MAX(T1.BI_CHNL_TP_03_NM) BI_CHNL_TP_03_NM
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 1 THEN SHOP_ID END) YW_01_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 2 THEN SHOP_ID END) YW_02_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 3 THEN SHOP_ID END) YW_03_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 4 THEN SHOP_ID END) YW_04_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 5 THEN SHOP_ID END) YW_05_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 6 THEN SHOP_ID END) YW_06_SHOP_CNT
                    FROM    T_MART T1
                    WHERE   NVL(T1.PSI_VAL,0) >= 1
                    GROUP BY T1.BRNC_ID
                            ,T1.OFFC_ID
                            ,T1.MKT_MODL_ID
                            ,T1.BASC_MODL
                            ,T1.BI_CHNL_TP_02
                            ,T1.BI_CHNL_TP_03
                ) T1
                ,T_SHOP_DIM T2
        WHERE   T1.BRNC_ID = T2.BRNC_ID
        AND     T1.OFFC_ID = T2.OFFC_ID
        AND     NVL(T1.BI_CHNL_TP_02,'-') = NVL(T2.BI_CHNL_TP_02,'-')
        AND     NVL(T1.BI_CHNL_TP_03,'-') = NVL(T2.BI_CHNL_TP_03,'-')
        UNION ALL
         
        SELECT  DISTINCT T1.BASE_YW
                ,T1.BRNC_ID
                ,'-' OFFC_ID
                ,'-' MKT_MODL_ID
                ,'-' BASC_MODL
                ,T1.BI_CHNL_TP_02
                ,T1.BI_CHNL_TP_03
                ,T1.BRNC_ID_NM
                ,T1.OFFC_ID_NM
                ,T1.MKT_MODL_DI_NM
                ,T1.BASC_MODL_NM
                ,T1.BI_CHNL_TP_02_NM
                ,T1.BI_CHNL_TP_03_NM
                ,SUM(T2.SHOP_CNT)OVER(PARTITION BY T1.BASE_YW,T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)SHOP_CNT
                ,T1.YW_01_SHOP_CNT
                ,T1.YW_02_SHOP_CNT
                ,T1.YW_03_SHOP_CNT
                ,T1.YW_04_SHOP_CNT
                ,T1.YW_05_SHOP_CNT
                ,T1.YW_06_SHOP_CNT
               
                ,0 GR_BI_CHNL_TP_02
                ,0 GR_BI_CHNL_TP_03
                ,1 GR_MKT_MODL_ID --ADD
                ,1 GR_BASC_MODL --ADD
                ,1 GR_OFFC_ID 
                 ,0 GR_BRNC_ID
        FROM    (
                    SELECT  MAX(T1.BASE_YW) BASE_YW
                            ,T1.BRNC_ID
                           -- ,T1.OFFC_ID
                            --,T1.MKT_MODL_ID
                            --,T1.BASC_MODL
                            ,T1.BI_CHNL_TP_02
                            ,T1.BI_CHNL_TP_03
                           
                            ,MAX(T1.BRNC_ID_NM) BRNC_ID_NM
                            ,'TTL' OFFC_ID_NM
                            ,'TTL' MKT_MODL_DI_NM --ADD
                            ,'TTL' BASC_MODL_NM   --ADD
                            ,MAX(T1.BI_CHNL_TP_02_NM) BI_CHNL_TP_02_NM
                            ,MAX(T1.BI_CHNL_TP_03_NM) BI_CHNL_TP_03_NM
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 1 THEN SHOP_ID END) YW_01_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 2 THEN SHOP_ID END) YW_02_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 3 THEN SHOP_ID END) YW_03_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 4 THEN SHOP_ID END) YW_04_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 5 THEN SHOP_ID END) YW_05_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 6 THEN SHOP_ID END) YW_06_SHOP_CNT
                    FROM    T_MART T1
                    WHERE   NVL(T1.PSI_VAL,0) >= 1
                    GROUP BY T1.BRNC_ID
                            --,T1.OFFC_ID
                            --,T1.MKT_MODL_ID
                            --,T1.BASC_MODL
                            ,T1.BI_CHNL_TP_02
                            ,T1.BI_CHNL_TP_03
                ) T1
                ,T_SHOP_DIM T2
        WHERE   T1.BRNC_ID = T2.BRNC_ID
       -- AND     T1.OFFC_ID = T2.OFFC_ID
        AND     NVL(T1.BI_CHNL_TP_02,'-') = NVL(T2.BI_CHNL_TP_02,'-')
        AND     NVL(T1.BI_CHNL_TP_03,'-') = NVL(T2.BI_CHNL_TP_03,'-')

 
UNION ALL
……
有些统计报表的sql编写过于复杂，常常是如上的形式，多个union到一起，且不同字段组合在一起分组计算，工作中遇到上面这样的类似sql，由11个上面的select语句块 union到一起，统计出2000条数据要2分钟。这样的语句不在少数而且有时会手工修改满足更改的报表需求，为提高sql运行和维护sql效率，必然需要优化。
对于这类union很多的分组计算的sql常常想到用报表分析函数合并，这样类似cube的报表函数要比union all的效率高，io少，因此首要优化的想法就是用更高效的报表分析函数合并这些union语句。但看过具体实际的例子会发现很多select都带有自己的查询条件，上面这段sql每个select还和另一个查询集合关联，且每次连接的条件都不一样。这样就要找到一种通用的方式才能合并这样的sql
首先在分析sql的时候发现，sql中的select中都有distinct这样的关键字，经与开发人员分析后改distinct的存在主要是为了分析函数求出的商店总数，而对于这个商店总数在 T_SHOP_DIM中是可以计算的，于是首先在T_SHOP_DIM计算商店总数
 SELECT         SUM(T1.SHOP_CNT) SHOP_CNT
                  FROM T_SHOP_DIM T1
                  GROUP BY (T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
计算后在与T1表关联，于是首先去掉distinct的排中方法。
继续观察分析sql，T_SHOP_DIM的计算也是根据不同情况分组计算的，这个情况和T1集合中的分组情况是一致的，而关联条件也可以想办法根据不同的分组情况动态的设置成相应值，比如OFFC_ID在本次分组中未加入group by list中，这时就可以设置该值为’-’ ，而T1集合中的OFFC_ID也可以设置成’-’,这样在对应到本次分组连接时两个字段就能连接了。连接的形式固定了，就可以采用分组分析函数合并union all了，于是最终优化sql是
SELECT  MAX(T1.BASE_YW) BASE_YW
                            ,T1.BRNC_ID
                            ,(CASE WHEN GROUPING(T1.OFFC_ID)=1 THEN '-' ELSE T1.OFFC_ID END) OFFC_ID--,'-' OFFC_ID-- ,T1.OFFC_ID
                            ,(CASE WHEN GROUPING(T1.MKT_MODL_ID)=1 THEN '-' ELSE T1.MKT_MODL_ID END) MKT_MODL_ID--'-' MKT_MODL_ID --,T1.MKT_MODL_ID
                            ,(CASE WHEN GROUPING(T1.BASC_MODL)=1 THEN '-' ELSE T1.BASC_MODL END) BASC_MODL--'-' BASC_MODL --,T1.BASC_MODL
                            ,T1.BI_CHNL_TP_02
                            ,T1.BI_CHNL_TP_03
                           
                            ,MAX(T1.BRNC_ID_NM) BRNC_ID_NM
                            ,(CASE WHEN GROUPING(T1.OFFC_ID)=1 THEN 'TTL' ELSE MAX(T1.OFFC_ID_NM) END) OFFC_ID_NM--'TTL' OFFC_ID_NM
                            ,(CASE WHEN GROUPING(T1.MKT_MODL_ID)=1 THEN 'TTL' ELSE MAX(T1.MKT_MODL_ID) END) MKT_MODL_DI_NM--'TTL' MKT_MODL_DI_NM --ADD
                            ,(CASE WHEN GROUPING(T1.BASC_MODL)=1 THEN 'TTL' ELSE MAX(T1.BASC_MODL) END) BASC_MODL_NM--'TTL' BASC_MODL_NM   --ADD
                            ,MAX(T1.BI_CHNL_TP_02_NM) BI_CHNL_TP_02_NM
                            ,MAX(T1.BI_CHNL_TP_03_NM) BI_CHNL_TP_03_NM
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 1 THEN SHOP_ID END) YW_01_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 2 THEN SHOP_ID END) YW_02_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 3 THEN SHOP_ID END) YW_03_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 4 THEN SHOP_ID END) YW_04_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 5 THEN SHOP_ID END) YW_05_SHOP_CNT
                            ,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 6 THEN SHOP_ID END) YW_06_SHOP_CNT
                            ,GROUPING_ID(T1.BRNC_ID) C1
                            ,CASE WHEN MAX(T1.OFFC_ID) IS NULL THEN GROUPING_ID(T1.BRNC_ID) END C2
                            ,GROUPING_ID(T1.BRNC_ID,T1.BI_CHNL_TP_02) C3
                            ,GROUPING_ID(T1.BRNC_ID,T1.OFFC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03) C4

                            ,GROUPING(T1.BI_CHNL_TP_02) GR_BI_CHNL_TP_02
                            ,GROUPING(T1.BI_CHNL_TP_03) GR_BI_CHNL_TP_03
                            ,GROUPING(T1.MKT_MODL_ID) GR_MKT_MODL_ID --ADD
                            ,GROUPING(T1.BASC_MODL) GR_BASC_MODL --ADD
                            ,GROUPING(T1.OFFC_ID) GR_OFFC_ID 
                             ,GROUPING(T1.BRNC_ID) GR_BRNC_ID
                    FROM    T_MART T1
                    WHERE   NVL(T1.PSI_VAL,0) >= 1
                    GROUP BY GROUPING SETS( (T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
                                          ,(T1.BRNC_ID,T1.OFFC_ID,T1.MKT_MODL_ID,T1.BASC_MODL,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
                                         )

                ) T1
                ,(SELECT (CASE WHEN GROUPING(T1.BRNC_ID) = 1 THEN '-' ELSE T1.BRNC_ID END) BRNC_ID
                        ,(CASE WHEN GROUPING(T1.OFFC_ID) = 1 THEN '-' ELSE T1.OFFC_ID END) OFFC_ID
                        ,(CASE WHEN GROUPING(BI_CHNL_TP_02) = 1 THEN '-' ELSE T1.BI_CHNL_TP_02 END) BI_CHNL_TP_02
                        ,(CASE WHEN GROUPING(BI_CHNL_TP_03) = 1 THEN '-' ELSE T1.BI_CHNL_TP_03 END) BI_CHNL_TP_03
                        ,SUM(T1.SHOP_CNT) SHOP_CNT
                  FROM T_SHOP_DIM T1
                  GROUP BY GROUPING SETS( (T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
                                        ,(T1.BRNC_ID,T1.OFFC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
                                        )
                 ) T2
        WHERE   T1.BRNC_ID = T2.BRNC_ID
        AND     T1.OFFC_ID = T2.OFFC_ID
        AND     NVL(T1.BI_CHNL_TP_02,'-') = NVL(T2.BI_CHNL_TP_02,'-')
        AND     NVL(T1.BI_CHNL_TP_03,'-') = NVL(T2.BI_CHNL_TP_03,'-')

这样根据不同的统计情况，还可以在group by grouping set中添加其他统计情况，这样核心的sql合并后再统计的效率是47s，因此采用合适的分组分析函数对sql的效率和维护都是非常有必要的。