记QQ群里一次sql优化

来源：互联网发布：一知f君百度网盘2.13 编辑：程序博客网时间：2024/05/12 04:08

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 ;