QQ群里大侠经常讨论一些优化问题,给大家解决问题,自己也在学优化,记录一下。
CREATE OR REPLACE FORCE VIEW "RENKOU"."V_RENKOU_NL" ("NL", "RENSHU") AS
SELECT AA.NL,
COUNT(*) AS RENSHU
FROM
(SELECT
CASE
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) >= 0
AND
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) < 10
THEN '0-9'
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) >= 10
AND
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) < 20
THEN '10-19'
...............................
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) >= 100
AND
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) < 150
THEN '100-150'
ELSE ''
END AS NL
FROM T_ZX_RENKOU A
) AA
GROUP BY AA.NL
1 要是存储过程的话,并且将renkou.V_RENKOU_NL 做为临时表的话,将会快很多
因为这个查询主要耗费时间的是
1:需要全表扫描
2:需要每次都进行:EXTRACT(YEAR FROM SYSDATE) 以及EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd')) 转换以及运算,
如果用存储过程的话,就可以直接替换为变量了,而变量的值可以在定义的时候先给运算出来
3对每条记录进行运算两次减法
修改后为: 这样的话,每条记录只需要运算一次了
select AA.nl,count(*) from
(select CASE
WHEN k >= 0
and k < 10
THEN '0-9'
WHEN k >= 10
................
WHEN k >= 100
AND k < 150
THEN '100'
ELSE ''
END AS NL
from (
select (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(A.CSRQ, 'yyyymmdd'))) as k from T_ZX_RENKOU A
)
)AA
group by AA.nl ;