꿈꾸는 개발자, DBA 커뮤니티 구루비

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

그룹 함수에 대해 올바르지 못한 NULL 처리로 인해 잘못된 데이터를 추출하는 문제는 심심치 않게 발생한다.
NULL 연산이 잘못되어 발생할 수 있는 심각한 문제로는, 추출하고자 하는 데이터의 결과가 달라져 데이터 무결성을 훼손할 수 있다는 것과, 그 자체를 사용자가 잘못된 결과라는 것을 인지하기가 어렵다는 점이다



SQL> SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */
  2  SUM (t1.c3_sum - (NVL(t2.c3_sum, 0) + NVL(t3.c3_sum, 0)) ) AS total_sum  -- NVL 추가
  3  FROM ( SELECT c1, c2, SUM(c3) AS c3_sum
  4  FROM null_t1
  5  WHERE c2 = 'A'
  6  GROUP BY c1, c2 ) t1,
  7  ( SELECT c1, c2, SUM(c3) AS c3_sum
  8  FROM null_t2
  9  GROUP BY c1, c2 ) t2,
 10  ( SELECT c1, c2, SUM(c3) as c3_sum
 11  FROM null_t3
 12  GROUP BY c1, c2 ) t3
 13  WHERE t1.c1 = t2.c1(+)
 14  AND t1.c2 = t2.c2(+)
 15  AND t1.c1 = t3.c1(+)
 16  AND t1.c2 = t3.c2(+) ;

 TOTAL_SUM
----------
 576734568

Elapsed: 00:00:00.22

Execution Plan
----------------------------------------------------------
Plan hash value: 2568442611

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    90 |       |   979   (2)| 00:00:12 |
|   1 |  SORT AGGREGATE                |         |     1 |    90 |       |            |          |
|*  2 |   HASH JOIN OUTER              |         |  2720 |   239K|       |   979   (2)| 00:00:12 |
|*  3 |    HASH JOIN OUTER             |         |  2720 |   159K|       |   511   (2)| 00:00:07 |
|   4 |     VIEW                       |         |  2720 | 81600 |       |    70   (3)| 00:00:01 |
|   5 |      HASH GROUP BY             |         |  2720 | 32640 |       |    70   (3)| 00:00:01 |
|*  6 |       TABLE ACCESS STORAGE FULL| NULL_T1 |  3846 | 46152 |       |    69   (2)| 00:00:01 |
|   7 |     VIEW                       |         |   100K|  2929K|       |   440   (2)| 00:00:06 |
|   8 |      HASH GROUP BY             |         |   100K|   878K|  1976K|   440   (2)| 00:00:06 |
|   9 |       TABLE ACCESS STORAGE FULL| NULL_T2 |   100K|   878K|       |    57   (2)| 00:00:01 |
|  10 |    VIEW                        |         |   100K|  2929K|       |   466   (2)| 00:00:06 |
|  11 |     HASH GROUP BY              |         |   100K|   976K|  1976K|   466   (2)| 00:00:06 |
|  12 |      TABLE ACCESS STORAGE FULL | NULL_T3 |   100K|   976K|       |    62   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C1"="T3"."C1"(+) AND "T1"."C2"="T3"."C2"(+))
   3 - access("T1"."C1"="T2"."C1"(+) AND "T1"."C2"="T2"."C2"(+))
   6 - filter("C2"='A')


Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        638  consistent gets
        186  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

함수를 이용하는 경우 NULL 은!?

COUNT : Number DataType -> NULL Data 를 0 (리턴 값)
SUM : Number DataType -> NULL Data 를 NULL (리턴 값)
MAX : Char DataType -> NULL Data 를 NULL (리턴 값)
MIN : Char DataType -> NULL Data 를 NULL (리턴 값)



SQL> SELECT 'Null' AS Nullable,
  2  COUNT(c3) AS count_c3,
  3  SUM(c3) AS sum_c3,
  4  MAX(c3) AS max_c3,
  5  MIN(c3) AS min_c3
  6  FROM NULL_T2
  7  WHERE c3 IS NULL
  8  AND c2 = 'A'
  9  UNION ALL
 10  SELECT 'Not Null',
 11  COUNT(c3),
 12  SUM(c3),
 13  MAX(c3),
 14  MIN(c3)
 15  FROM NULL_T2
 16  WHERE c3 IS NOT NULL
 17  AND c2 = 'A'
 18  UNION All
 19  SELECT 'ALL',
 20  COUNT(c3),
 21  SUM(c3),
 22  MAX(c3),
 23  MIN(c3)
 24  FROM NULL_T2
 25  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