SELECT A.HOME_AREA,
F_ASP_COM_TYPE(A.HOME_COM) HOME_COM,
A.HOME_NUMBER,
SUM(DECODE(A.CALL_TYPE, '01', 1, 0)) CALL_CNT_OUT,
SUM(DECODE(A.CALL_TYPE, '02', 1, 0)) CALL_CNT_IN,
COUNT(1) CALL_CNT_ALL,
SUM(TRUNC((DECODE(A.CALL_TYPE, '01', A.DURATION, 0) + 5) / 6) * 6) DURATION_TIME_OUT,
SUM(TRUNC((DECODE(A.CALL_TYPE, '02', A.DURATION, 0) + 5) / 6) * 6) DURATION_TIME_IN,
SUM(TRUNC((A.DURATION + 5) / 6) * 6) DURATION_TIME_ALL,
SUM(INCOME_FEE)/1000 INCOME_FEE,
SUM(PAY_FEE)/1000 PAY_FEE,
SUM(INCOME_FEE - PAY_FEE)/1000 TOTAL_FEE
FROM (SELECT T1.*,
DM.F_PLATFORM_NUMBER(T1.OPPO_NUMBER, T1.OPPO_AREA) OPPO_NUMBER_N
FROM MID.MID_BILL_CDR_SETTLE_TTYD T1
WHERE T1.USER_ID IS NOT NULL
AND T1.DAY_ID = '18'
AND T1.MONTH_ID = '201108'
UNION ALL
SELECT T2.*,
DM.F_PLATFORM_NUMBER(T2.OPPO_NUMBER, T2.OPPO_AREA) OPPO_NUMBER_N
FROM MID.MID_BILL_CDR_SETTLE_TTGW T2
WHERE T2.USER_ID IS NOT NULL
AND T2.DAY_ID = '18'
AND T2.MONTH_ID = '201108') A,
DMCODE.DIM_PLATFORM_TYPE B
WHERE LENGTH(A.OPPO_NUMBER) < 11
AND A.OPPO_NUMBER like B.PLATFORM_ID ||'%'
GROUP BY A.HOME_AREA, A.HOME_COM, A.HOME_NUMBER这样查询下来效率很低,有没有其他方法呢?
F_ASP_COM_TYPE(A.HOME_COM) HOME_COM,
A.HOME_NUMBER,
SUM(DECODE(A.CALL_TYPE, '01', 1, 0)) CALL_CNT_OUT,
SUM(DECODE(A.CALL_TYPE, '02', 1, 0)) CALL_CNT_IN,
COUNT(1) CALL_CNT_ALL,
SUM(TRUNC((DECODE(A.CALL_TYPE, '01', A.DURATION, 0) + 5) / 6) * 6) DURATION_TIME_OUT,
SUM(TRUNC((DECODE(A.CALL_TYPE, '02', A.DURATION, 0) + 5) / 6) * 6) DURATION_TIME_IN,
SUM(TRUNC((A.DURATION + 5) / 6) * 6) DURATION_TIME_ALL,
SUM(INCOME_FEE)/1000 INCOME_FEE,
SUM(PAY_FEE)/1000 PAY_FEE,
SUM(INCOME_FEE - PAY_FEE)/1000 TOTAL_FEE
FROM (SELECT T1.*,
DM.F_PLATFORM_NUMBER(T1.OPPO_NUMBER, T1.OPPO_AREA) OPPO_NUMBER_N
FROM MID.MID_BILL_CDR_SETTLE_TTYD T1
WHERE T1.USER_ID IS NOT NULL
AND T1.DAY_ID = '18'
AND T1.MONTH_ID = '201108'
UNION ALL
SELECT T2.*,
DM.F_PLATFORM_NUMBER(T2.OPPO_NUMBER, T2.OPPO_AREA) OPPO_NUMBER_N
FROM MID.MID_BILL_CDR_SETTLE_TTGW T2
WHERE T2.USER_ID IS NOT NULL
AND T2.DAY_ID = '18'
AND T2.MONTH_ID = '201108') A,
DMCODE.DIM_PLATFORM_TYPE B
WHERE LENGTH(A.OPPO_NUMBER) < 11
AND A.OPPO_NUMBER like B.PLATFORM_ID ||'%'
GROUP BY A.HOME_AREA, A.HOME_COM, A.HOME_NUMBER这样查询下来效率很低,有没有其他方法呢?
is not null 会限制索引,就是该字段有索引,也不会走。 在内层select 时,就可以把结果限制住,这样外层select 查询的data 就会少。Oracle 索引 详解
http://blog.csdn.net/tianlesoftware/article/details/5347098Oracle SQL的优化
http://blog.csdn.net/tianlesoftware/article/details/4672023常用的PL/SQL开发原则 by dbsanke
http://blog.csdn.net/tianlesoftware/article/details/6618115