oracle decode优化,利用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  --这个地方很关键 不然不等价哦