by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3. 그룹 함수(COUNT,SUM,MAX,MIN)와 NVL 처리.




Ex)
100 + NULL = NULL
  • NULL값은 어떤 값과도 연산이 되지 않고 NULL값을 리턴
성능개선 전 SQL
SQL> SELECT SUM (
            t1.sum_c3
            - ((SELECT SUM(c3) FROM null_t2 t2 WHERE t2.c1 = t1.c1 AND t2.c2 = t1.c2)
            + (SELECT SUM(c3) FROM null_t3 t3 WHERE t3.c1 = t1.c1 AND t3.c2 = t1.c2))
            ) AS total_sum
     FROM (SELECT c1,
                  c2,
                  SUM(c3) AS sum_c3
             FROM   null_t1
           WHERE  c2 = 'A'
           GROUP BY c1, c2) t1;

 TOTAL_SUM                                                                           
----------                                                                           
 461,381,537 
성능 문제점
  • 인라인 뷰안에 있는 결과 집합 만큼 메인 쿼리의 SELECT-LIST절에 서브쿼리를 수행하면서 성능 저하
성능개선 후 SQL
SQL> SELECT /*+ LEADING(T1) USE HASH(T1 T2 T3) */
           SUM(t1.c3_sum - (t2.c3_sum + t3.c3_sum)) AS total_sum
     FROM (SELECT c1
                 ,c2
                 ,SUM(c3) AS c3_sum
           FROM NULL_T1
           WHERE c2 = 'A'
           GROUP BY c1, c2 ) t1
         ,(SELECT c1
                 ,c2
                 ,SUM(c3) AS c3_sum
           FROM  NULL_T2
           GROUP BY c1, c2 ) t2
         ,(SELECT c1
                 ,c2
                 ,SUM(c3) AS c3_sum
           FROM  NULL_T3
           GROUP BY c1, c2 ) t3
     WHERE t1.c1 = t2.c1(+)
     AND t1.c2 = t2.c2(+)
     AND t1.c1 = t3.c1(+)
     AND t1.c2 = t3.c2(+);

 TOTAL_SUM                                                      
----------                                                      
 461,381,537                                                                     

- 스칼라 서브쿼리를 인라인 뷰 Rows수 많큼 호출 하던 것을 HASH JOIN을 통해 성능 개선
데이터 접합성 문제점
1. 인라인 뷰 T1에서는 NULL 데이터가 추출되지 않는다.
2. T2.C3, T3.C3은 Nullable 컬럼이고, NULL이 추출된 경우에는 T1.C3_SUM값을 그대로 추출하여야 한다.
최종 개선 한 SQL
SQL> SELECT /*+ LEADING(T1) USE HASH(T1 T2 T3) */
           --SUM(t1.c3_sum - (t2.c3_sum + t3.c3_sum)) AS total_sum
           SUM(t1.c3_sum - (NVL(t2.c3_sum,0) + NVL(t3.c3_sum,0))) AS total_sum
     FROM (SELECT c1
                 ,c2
                 ,SUM(c3) AS c3_sum
           FROM NULL_T1
           WHERE c2 = 'A'
           GROUP BY c1, c2 ) t1
         ,(SELECT c1
                 ,c2
                 ,SUM(c3) AS c3_sum
           FROM  NULL_T2
           GROUP BY c1, c2 ) t2
         ,(SELECT c1
                 ,c2
                 ,SUM(c3) AS c3_sum
           FROM  NULL_T3
           GROUP BY c1, c2 ) t3
     WHERE t1.c1 = t2.c1(+)
     AND t1.c2 = t2.c2(+)
     AND t1.c1 = t3.c1(+)
     AND t1.c2 = t3.c2(+);

 TOTAL_SUM
----------
 576,734,568 <-- NULL값으로 누락 되었던 데이터 정상 추출!!
  • 다른 그룹함수인 COUNT, SUM, MAX, MIN의 NULL 처리 수행 방법
    SQL> SELECT 'NULL' AS NULLABLE
               ,COUNT(C3) AS COUNT_C3
               ,SUM(C3) AS SUM_C3
               ,MAX(C3) AS MAX_C3
               ,MIN(C3) AS MIN_C3
         FROM  NULL_T2
         WHERE  C3 IS NULL
         AND    C2 = 'A'
         UNION ALL
         SELECT 'NOT NULL'
               ,COUNT(C3)
               ,SUM(C3)
               ,MAX(C3)
               ,MIN(C3)
         FROM  NULL_T2
         WHERE C3 IS NOT NULL
         AND   C2 = 'A'
         UNION ALL
         SELECT 'ALL'
               ,COUNT(C3)
               ,SUM(C3)
               ,MAX(C3)
               ,MIN(C3)
         FROM  NULL_T2
         WHERE C2 = 'A';
    
    NULLABLE           COUNT_C3     SUM_C3 MAX_C3    MIN_C3
    ---------------- ---------- ---------- --------- ------
    NULL                      0                            
    NOT NULL               3077      15386 8         2     
    ALL                    3077      15386 8         2     
    
정리
  • COUNT를 제외한 SUM, MAX, MIN,은 NULL 데이터를 NULL로 리턴
  • 추출된 데이터를 이용하여 또 다른 수식 연산이 별도로 진행되어야 하는 경우에는 반드시 NVL처리

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.