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

9. 상이한 집합의 통일




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 절에서 아무리 정상적으로 사용하였더라도 인덱스를 사용할 수 없다.

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 ;

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

인덱스 구조가 문제없는 경우 인덱스 구조가 문제있는 경우
중복 엑세스 없음 중복 엑세스
'COL2+DAT1' 일경우 문제없음, 'DAT1+COL2' 일경우 DAT1의 BETWEEN -> IN 변경 DAT1 로만 되어있는경우, DAT1이 COL2가 아닌 다른 컬럼과 결합되어 있는경우

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

  • 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하도록 함.

문서에 대하여

문서정보

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