SQL优化

Question

SELECT A.EVA_KPI_ID,
       A.EVA_KPI_NAME,
       A.EVA_KPI_WEIGHT,
       ROWID1,
       SUM(NVL(A.F1, 0)) F1,
       SUM(NVL(A.F2, 0)) F2,
       SUM(NVL(A.F3, 0)) F3,
       SUM(NVL(A.F4, 0)) F4,
       SUM(NVL(A.F5, 0)) F5,
       SUM(NVL(A.F6, 0)) F6,
       SUM(NVL(A.F7, 0)) F7,
       SUM(NVL(A.F8, 0)) F8,
       SUM(NVL(A.F9, 0)) F9,
       SUM(NVL(A.F10, 0)) F10,
       SUM(NVL(A.F11, 0)) F11,
       SUM(NVL(A.F12, 0)) F12,
       SUM(NVL(A.F13, 0)) F13,
       SUM(NVL(A.F14, 0)) F14,
       SUM(NVL(A.F15, 0)) F15,
       SUM(NVL(A.F16, 0)) F16,
       SUM(NVL(A.F17, 0)) F17,
       SUM(NVL(A.F18, 0)) F18,
       SUM(NVL(A.F19, 0)) F19,
       SUM(NVL(A.F20, 0)) F20,
       SUM(NVL(A.F21, 0)) F21,
       SUM(NVL(A.F22, 0)) F22,
       SUM(NVL(A.F23, 0)) F23,
       SUM(NVL(A.F24, 0)) F24,
       SUM(NVL(A.F25, 0)) F25,
       SUM(NVL(A.F26, 0)) F26,
       SUM(NVL(A.F27, 0)) F27,
       SUM(NVL(A.F28, 0)) F28,
       SUM(NVL(A.F29, 0)) F29,
       SUM(NVL(A.F30, 0)) F30,
       SUM(NVL(A.F31, 0)) F31,
       MAX(A.COU) ISEND
  FROM (SELECT T.REGION_ID,
               T.EVA_KPI_ID,
               T.EVA_KPI_NAME,
               T.EVA_KPI_WEIGHT,
               MAX(T.ROWID1) ROWID1,
               MAX(T.COU) COU,
               DECODE(T.REGION_ID, 10, ROUND(AVG(T.SCORE), 2)) F1,
               DECODE(T.REGION_ID, 20, ROUND(AVG(T.SCORE), 2)) F2,
               DECODE(T.REGION_ID, 21, ROUND(AVG(T.SCORE), 2)) F3,
               DECODE(T.REGION_ID, 22, ROUND(AVG(T.SCORE), 2)) F4,
               DECODE(T.REGION_ID, 23, ROUND(AVG(T.SCORE), 2)) F5,
               DECODE(T.REGION_ID, 24, ROUND(AVG(T.SCORE), 2)) F6,
               DECODE(T.REGION_ID, 25, ROUND(AVG(T.SCORE), 2)) F7,
               DECODE(T.REGION_ID, 27, ROUND(AVG(T.SCORE), 2)) F8,
               DECODE(T.REGION_ID, 28, ROUND(AVG(T.SCORE), 2)) F9,
               DECODE(T.REGION_ID, 29, ROUND(AVG(T.SCORE), 2)) F10,
               DECODE(T.REGION_ID, 311, ROUND(AVG(T.SCORE), 2)) F11,
               DECODE(T.REGION_ID, 351, ROUND(AVG(T.SCORE), 2)) F12,
               DECODE(T.REGION_ID, 371, ROUND(AVG(T.SCORE), 2)) F13,
               DECODE(T.REGION_ID, 431, ROUND(AVG(T.SCORE), 2)) F14,
               DECODE(T.REGION_ID, 451, ROUND(AVG(T.SCORE), 2)) F15,
               DECODE(T.REGION_ID, 471, ROUND(AVG(T.SCORE), 2)) F16,
               DECODE(T.REGION_ID, 531, ROUND(AVG(T.SCORE), 2)) F17,
               DECODE(T.REGION_ID, 551, ROUND(AVG(T.SCORE), 2)) F18,
               DECODE(T.REGION_ID, 571, ROUND(AVG(T.SCORE), 2)) F19,
               DECODE(T.REGION_ID, 591, ROUND(AVG(T.SCORE), 2)) F20,
               DECODE(T.REGION_ID, 731, ROUND(AVG(T.SCORE), 2)) F21,
               DECODE(T.REGION_ID, 771, ROUND(AVG(T.SCORE), 2)) F22,
               DECODE(T.REGION_ID, 791, ROUND(AVG(T.SCORE), 2)) F23,
               DECODE(T.REGION_ID, 851, ROUND(AVG(T.SCORE), 2)) F24,
               DECODE(T.REGION_ID, 871, ROUND(AVG(T.SCORE), 2)) F25,
               DECODE(T.REGION_ID, 891, ROUND(AVG(T.SCORE), 2)) F26,
               DECODE(T.REGION_ID, 898, ROUND(AVG(T.SCORE), 2)) F27,
               DECODE(T.REGION_ID, 931, ROUND(AVG(T.SCORE), 2)) F28,
               DECODE(T.REGION_ID, 951, ROUND(AVG(T.SCORE), 2)) F29,
               DECODE(T.REGION_ID, 971, ROUND(AVG(T.SCORE), 2)) F30,
               DECODE(T.REGION_ID, 991, ROUND(AVG(T.SCORE), 2)) F31,
               ROUND(AVG(T.SCORE), 2) SCORE
          FROM (SELECT T.REGION_NAME,
                       T.REGION_ID,
                       T.EVA_KPI_ID,
                       T.EVA_KPI_NAME,
                       T.EVA_KPI_WEIGHT,
                       T.COU,
                       T.ROWID1,
                       PKP_EVALUATION.GETKPIVALUE(T.REGION_ID,
                                                  T.EVA_KPI_ID,
                                                  '2009-10-25',
                                                  '2009-10-26') SCORE
                  FROM (SELECT *
                          FROM (SELECT *
                                  FROM MANAGE_REGION
                                 WHERE REGION_ID NOT IN (-1, 0, 1, 100)) A,
                               (SELECT A.*,
                                       ROWNUM ROWID1,
                                       (SELECT COUNT(*)
                                          FROM EVALUSTION_KPI_CONFIG
                                         WHERE PARENT_EVA_KPI_ID = A.EVA_KPI_ID) COU
                                  FROM EVALUSTION_KPI_CONFIG A
                                 WHERE A.STATE = '0SA'
                                CONNECT BY PRIOR
                                            A.EVA_KPI_ID = A.PARENT_EVA_KPI_ID
                                 START WITH A.EVA_KPI_ID IN (1, 2, 3)) B) T) T
         GROUP BY T.REGION_ID,
                  T.EVA_KPI_ID,
                  T.EVA_KPI_NAME,
                  T.EVA_KPI_WEIGHT) A
 GROUP BY A.EVA_KPI_WEIGHT, A.EVA_KPI_NAME, A.EVA_KPI_ID, ROWID1
 ORDER BY ROWID1
这个语句大概要怎么优化啊

Answer 1

你的SQL语句中嵌套的select子查询太多了，可是试试使用临时表记录中间结果！

Answer 2

(SELECT * 
                                  FROM MANAGE_REGION 
                                WHERE REGION_ID NOT IN (-1, 0, 1, 100)) 
这个多少条记录？
SELECT A.*, 
                                      ROWNUM ROWID1, 
                                      (SELECT COUNT(*) 
                                          FROM EVALUSTION_KPI_CONFIG 
                                        WHERE PARENT_EVA_KPI_ID = A.EVA_KPI_ID) COU 
                                  FROM EVALUSTION_KPI_CONFIG A 
                                WHERE A.STATE = '0SA' 
                                CONNECT BY PRIOR 
                                            A.EVA_KPI_ID = A.PARENT_EVA_KPI_ID 
                                START WITH A.EVA_KPI_ID IN (1, 2, 3)) 
这个多少条记录？为什么要做笛卡尔积？