9. 상이한 집합의 통일

복잡한 현실의 업무를 처리하다 보면 데이터 모델상으로는 서로 다르지만 몇 가지 서로 상이한 부분만 보정하면 통일된 방식으로 처리할 수 있는 경우가 많이 나타난다.
복잡하고 변화 무쌍한 것들을 단순 - 명확하게 해결할 수 있는것이 바로 실력이라 생각한다.

9.1. 유사한 집합의 통일

이 데이터 모델은 서로 닮아 있는 부분이 많다. - 색깔별로 표시한 것.
이 모델을 토대로 몇가지 사례를 모아보자

• 어떤 특정한 기간에 '상환' 및 '예입'으로 출금된 현황을 조회하는 화면을 나타낸다. 아래 도표와 같이 '상화'과 '예입'이 공통적으로 사용되기를 희망한다

화면

해당 SQL

SELECT  w.구분       as 구분
       ,to_char(to_date(w.일자,'yyyymmdd'),'mm/dd') as 일자  -- 메인쿼리에서 가공함
       ,y.계정과목명 as 종류
       ,x.은행명     as 은행명 
       ,w.개시일     as 개시일
       ,w.만기일     as 만기일
       ,w.원화금액   as 금액
       ,z.부문명     as 손익부문
  FROM (
         SELECT  '상환'        as 구분
               ,b.상환일자     as 일자  -- 상환일자
               ,a.차입종류     as 종류코드
               ,a.차입처       as 거래처코드
               ,a.개설일자     as 개시일
               ,a.만기일
               ,b.원금상환원화 + b.원화이자 + (b.원금상환외화+b.외화이자)*c.환율 as 원화금액
               ,b.부문코드
          FROM 차입금원장 a, 상환내역 b, 일별환율 c
         WHERE a.관리번호 = b.관리번호
           AND c.환율일자 = b.상환일자
           AND c.환율종류 = 'TTB'
         UNION ALL
        SELECT  '예금'      as 구분
               ,b.매입일자  as 일자  --예입일자
               ,a.예금종류  as 종류코드
               ,a.예입처    as 거래처코드
               ,a.만기일
               ,b.원화금액
               ,b.부문코드
          FROM 예적금원장 a, 예입내역 b, 부서 c
         WHERE b.관리번호 = a.관리번호
           AND c.부서코드 = a.예입부서
        ) w, 거래처 x, 계정과목 y, 손익부문 z
 WHERE x.거래처코드 = w.거래처코드
   AND y.계정과목 = w.종류코드||'00' 
   AND z.부문코드 = w.부문코드
   AND w.일자 between '980305' and '980310'

서로 상이하거나 조인해야 할 대상이 서로다른 경우

• 인라인뷰로 조건을 부여하여 통일집합생성하고 SELECT-LIST에서도 가공
• 주의 : 인라인뷰 내의 컬럼에 함부로 연산이나 함수 등을 사용하여 가공하였다면 메인 SELECT 문의 WHERE 절에서 아무리 정상적으로 사용하였더라도 인덱스를 사용할 수 없다.

h2.9.2.UNION을 사용한 인라인뷰의 주의사항

SELECT COL3, SUM(RESULT1), SUM(RESULT2)
  FROM (
        SELECT COL3, SUM(QTY) AS RESULT1, 0 AS RESULT2
          FROM TAB1
         WHERE DAT1 BETWEEN :VAL1 AND :VAL2
           AND COL2 IN ('1','2')
         GROUP BY COL3
         UNION
        SELECT COL3, 0 AS RESULT1, SUM(QTY) AS RESULT2  
          FROM TAB1
         WHERE DAT1 BETWEEN :VAL1 AND :VAL2
           AND COL2 = '5'
           AND COL4 = 'A'
        GROUP BY COL3
       )
  WHERE others_conditions......
  GROUP BY COL3 ;

• 위 쿼리의 세가지 잘못
  1. 불필요하게 'UNION' 사용.
  2. UNION 사용
  3. 불필요하게 GROUP BY가 더 수행됨

1. 불필요하게 'UNION' 사용.

• 동일한 테이블로 구성되어 있으나 조건이 약간 상이할 따름임. : 집합의 범위가 중복된 부분이 있다면 불필요한 엑세스가 추가로 발생
• 불필요한 엑세스가 발생하였는지의 판단은 인댁스의 구성에 따라 전혀 달라짐
  • 만약 인덱스가 DAT1 혹은 DAT1+COL2일경우
    : 동일한 범위를 두번 걸쳐 엑세스 : 선행컬림이 =이나 IN과 같은 '점'으로 되어있지 않고 BETWEEN, LIKE 등의 '선'으로 되어있으면 뒤의 컬럼은 체크기능으로 전락

• 1차 변경쿼리

SELECT COL3
       ,SUM(DECODE(COL2,'5',NULL,QTY)    AS RESULT1
       ,SUM(DECODE(COL2||COL4,'5A',QTY)) AS RESULT2
  FROM TAB1
 WHERE DAT1 BETWEEN :VAL1 AND :VAL2
           AND COL2 IN ('1','2','5')
 GROUP BY COL3
 HAVING others_conditions......

• • 첫번째 쿼리보다는 많이 단순해짐..
    그러나 인덱스가 'DAT1+COL2'로 되어있고 COL2의 '1','2','5' 데이터가 많지 않다면 BETWEEN으로 사용되어 COL2의 불필요한 데이터가 같이 엑세스 하게 됨

• 2차 변경쿼리

SELECT COL3
       ,SUM(DECODE(COL2,'5',NULL,QTY)    AS RESULT1
       ,SUM(DECODE(COL2||COL4,'5A',QTY)) AS RESULT2
  FROM TAB1
 WHERE DAT1 IN (SELECT YMD||''
                  FROM YMD_DUAL -- 일자만 문자타입으로 가지고 있는 보조테이블
                 WHERE YMD BETWEEN :VAL1 AND :VAL2) -- '점'을 '선'으로 만들어줌
   AND COL2 IN ('1','2','5')
 GROUP BY COL3
 HAVING others_conditions......

• • 인덱스가 'COL2+DAT1' 였을경우는 문제가 없다. 그러나 'DAT1+COL2'로 되어있는경우는
    : 첫번째 컬럼이 IN(SUBQUERY) 가 사용되고 두번째 조건이 IN으로 되면 두번째조건은 체크조건으로 빠지게 된다.
• 체크조건 : 읽어둔 인덱스 로우를 취할 것인지, 버릴 것이지를 결정. 일명 '비주류'
• IN조건 참고주소 : 중복된 IN 조건의 활용(전기수 자료)

• 3차 변경쿼리

SELECT COL3
       ,SUM(DECODE(COL2,'5',NULL,QTY)    AS RESULT1
       ,SUM(DECODE(COL2||COL4,'5A',QTY)) AS RESULT2
  FROM TAB1
 WHERE (DAT1, COL2) IN (SELECT YMD, TO_CHAR(NO)
                          FROM YMD_DUAL X, COPY_T Y
                         WHERE X.YMD BETWEEN :VAL1 AND :VAL2
                           AND Y.NO IN (1,2,5))
 GROUP BY COL3
 HAVING others_conditions......

• • 인덱스가 'DAT1+COL2'로 되어있는경우
    : 서브쿼리 내에서 먼저 모든 '점' 집합을 만들어 메인쿼리에 공급함으로써 우리가 반드시 읽어야 할 데이터만 엑세스 하게 된다.

2. UNION 사용 -> 'UNION ALL' 사용

• UNION은 두개의 집합을 무조건 결합한 다음에 그 결과의 집합을 대상으로 다시 로우 단위로 유일한 집합을 찾는다. (SORT 작업을 함)
• 'UNION ALL'은 두개의 집합을 무조건 결합한다.

3. 불필요하게 GROUP BY가 더 수행됨.

• UNION되는 각 집합에 GROUP BY는 어차피 원시 데이터 로우는 한번씩 GROUP BY 되므로 먼저 실시할 필요가 없다.
  • GROUP BY가 바람직한 경우
• 1차로 GROUP BY한 결과의 집합과 조인을 하고자 할경우
• 그 집합을 GROUP BY해두지 않으면 여러 번 동일한 처리를 반복해야 하는 경우
• 저장형 함수의 중복 수행을 방지하고자 할 경우

다른 집합과의 연결 대상이 다르다고 해서 무조건 SELECT를 분리하는것이 바람직한 것인가..

SELECT CUST_CD, SUM(QTY)
  FROM (
        SELECT CUST_CD, QTY
          FROM TAB1
         WHERE DAT1 BETWEEN :VAL1 AND :VAL2
           AND COL2 = '1'
         UNION
        SELECT Y.BYSINESS_NO AS CUST_CD, X.QTY
          FROM TAB1 X, TAB2 Y
         WHERE X.KEY = Y.KEY
           AND X.DAT1 BETWEEN :VAL1 AND :VAL2 
           AND COL2 = '2'
       )
  WHERE others_conditions......
  GROUP BY CUST_CD ;

위 쿼리 실행시 인덱스 구조에 따른 효율성 문제.

인덱스 구조가 문제있는 경우 처리방안

• 1차 개선쿼리

SELECT DECODE(X.COL1, '1', CUST_CD, Y.BYSINESS_NO) AS CUST_NO, SUM(QTY)
  FROM TAB1 X, TAB2 Y
 WHERE (X.COL2 = '1' OR X.COL2 = '2' AND X.KEY = Y.KEY)
   AND X.DAT1 BETWEEN :VAL1 AND :VAL2 
   AND others_conditions......
 GROUP BY DECODE(X.COL1, '1', CUST_CD, Y.BYSINESS_NO);

• 조인의 연결고리에 'OR'가 있을 때의 실행계획은은 대개의 경우 매우 문제가 많이 발생한다.
• 대부분의 실행계획은 UNION을 사용한 경우와 유사하게 'OR'를 두개의 별도의 처리로 나누어 수행하여 그 결과를 결합(Concatenation)하여 중복처리를 발생시킴
• 가능한 조인의 연결조건에 'OR'를 사용하는것이 피하는 것이 바람직함

• 2차 개선쿼리

SELECT NVL(Y.BYSINESS_NO,X.COL1) AS CUST_NO, SUM(QTY)
  FROM TAB1 X, TAB2 Y
 WHERE Y.KEY1(+) = DECODE(X.COL2,'2',X.KEY1) -- COL2가 '1'이면 NULL로, '2'면 COL2로 조인
   AND X.DAT1 BETWEEN :VAL1 AND :VAL2 
   AND others_conditions......
 GROUP BY NVL(Y.BYSINESS_NO,X.COL1);

• 1차보다는 효율적.
• KEY2는 NULL값이 존재하지 않으므로 무조건 실패하고 이를위하여 OUTER 조인시킴
• 비효율적인 엑세스 존재 : COL2가 '1'인 경우에도 TAB2에 엑세스 시도함
• 이를 해결하기 위해 '사용자 지정 저장형함수' 사용하여 COL2가 '1'인 경우는 함수를 바로 빠져 나오고 '2'인경우만 TAB2를 SELECT하도록 함.

문서에 대하여

• 최초작성자 : 한남주
• 최초작성일 : 2009년 5월 25일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 대용량 데이터베이스 솔루션 2 (관계형 데이터베이스 실무활용편) 서적을 스터디하면서 정리한 내용 입니다.