利用decode函数进行语句的优化

XLJ

oracle提供了非常多的函数运算，有的时候使用这些函数可以大大减少sql语句的复杂度，并且减少表的扫描，

下面可以看一个利用decode函数进行语句优化的案列：

SELECT 1 AS CLIENT_SEQ,
                 1 AS ZQ,
                 NVL(A.OBJ_AREA, '000000') AS AREA_CODE,
                 (SELECT AA.AREA_NAME
                    FROM T_SYM_AREA AA
                   WHERE AA.AREA_CODE = NVL(A.OBJ_AREA, '000000')) AS AREA_NAME,
                 COUNT(DISTINCT A.OBJ_CODE) AS CLIENT_NUM,
                 '周期' AS PERIOD,
                 '客户端新增用户数' AS CLIENT_NAME
            FROM T_XXX_USER_ATTR A
           WHERE A.OBJ_TYPE = 0
             AND A.OBJ_DATE = :B1
           GROUP BY A.OBJ_AREA
          UNION
          SELECT 3 AS CLIENT_SEQ,
                 1 AS ZQ,
                 NVL(A.OBJ_AREA, '000000') AS AREA_CODE,
                 (SELECT AA.AREA_NAME
                    FROM T_XXX_AREA AA
                   WHERE AA.AREA_CODE = NVL(A.OBJ_AREA, '000000')) AS AREA_NAME,
                 SUM(A.OBJ_VAL1) AS CLIENT_NUM,
                 '周期' AS PERIOD,
                 '客户端登录次数' AS CLIENT_NAME
            FROM T_XXX_USER_ATTR A
           WHERE A.OBJ_TYPE = 1
             AND A.OBJ_DATE = :B1
           GROUP BY A.OBJ_AREA

对表访问了两次 我们自己来观察，完全可以用decode函数来处理，这样可以减少表的访问次数，来看看优化后的语句

SELECT decode(OBJ_TYPE, 0, 1, 3) AS CLIENT_SEQ,
       1 AS ZQ,
       NVL(A.OBJ_AREA, '000000') AS AREA_CODE,
       (SELECT AA.AREA_NAME
          FROM T_XXX_AREA AA
         WHERE AA.AREA_CODE = NVL(A.OBJ_AREA, '000000')) AS AREA_NAME,
       decode(OBJ_TYPE, 0, COUNT(DISTINCT A.OBJ_CODE), sum(A.OBJ_VAL1)) AS CLIENT_NUM,
       '周期' AS PERIOD,
       decode(OBJ_TYPE, 0, '客户端新增用户数', '客户端登录次数') AS CLIENT_NAME
  FROM mepf_ods.T_XXX_USER_ATTR A
 WHERE A.OBJ_TYPE in (0, 1)
   AND A.OBJ_DATE = :B1
 GROUP BY OBJ_TYPE, A.OBJ_AREA  --这个地方很关键 不然不等价哦

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1871745/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1871745/