우리 회사 데이터베이스를 티베로로 변경하기
합계, 중간합계의 비효율 구성 0 0 1,041

by 티베로 티베로전환 티베로성능개선 [2018.09.30]


먼저 소속 회사가 ‘http://freesis.kofia.or.kr’을 통해 서비스하고 있는 그림 4-2 를 보자. 해당 서비스는 기준 항목(회사, 구분)별 규모, 각각의 중간 합계(소계) 그리고 최종 합계를 제공한다.

  • [그림 4-2] 회사별 추가 단위 규모 (출처 : 한국금융투자협회 종합통계 서비스, http://freesis.kofia.or.kr)

기준 항목별 규모는 GROUP BY를 통해 손쉽게 구현이 가능한데 중간 합계 및 최종 합계는 어떻게 구현해야 할까? 쉬운 방법으로는 UNION ALL을 통해 메인 SQL 하단에 소계 SQL를 추가하는 방법을 생각할 수 있을 것이나 필요한 소계 횟수만큼 무거운 테이블들을 반속 수행해야 하므로 성능 비효율이 심각해진다.

또한 (필요한 소계 횟수 * 메인 SQL 라인 수) 만큼 총 SQL 라인수가 증가하므로 가독성 또한 해치게 된다. 따라서 티베로가 제공하는 합계용 그룹핑 함수(GROUP BY ROLLUP, GROUP BY CUBE, GROUP BY GROUPING SETS)를 사용하여 메인 SQL을 한번만 액세스하는 것이 바람직하다.

어플리케이션 개발 업무를 진행하다 보면 선임급 개발자가 사용하는 패턴을 다른 개발자들이 모두 복사하여 사용하는 경향이 있어 업무별로 특이한 패턴을 한두 개씩 보유하고 있는데 위와 같은 비효율적 합계 구현 형태 역시 소속 회사 업무 중 1개 업무에서 집중적으로 발견되어 티베로 오픈 전 모두 수정조치 하였다.

참고4-10에서 합계용 그룹핑 함수의 사용법을 간단히 소개하니 참고하기 바란다.

  • [참고 4-10] GROUP BY 합계 함수
  • GROUP BY 합계 함수
  • -->합계가 없는 기준 항목별 금액 구현 SQL 예시
    
    SELECT 회사명, 지역, 추가방식, SUM(금액) 금액
    FROM MAIN_TABLE GROUP BY 회사명, 지역, 추가방식
    
    
    -->기준 항목별 금액 + 소계와 합계의 잘못된 구현(UNION ALL)
    
    SELECT 회사명, 지역, 추가방식, SUM(금액) FROM MAIN_TABLE GROUP BY 회사명, 지역, 추가방식 UNION ALL
    SELECT 회사명, 지역, '소계', SUM(금액) FROM MAIN_TABLE GROUP BY 회사명, 지역 UNION ALL
    SELECT 회사명, '소계', 추가방식, SUM(금액) FROM MAIN_TABLE GROUP BY 회사명, 추가방식 UNION ALL
    …
    SELECT '전체', '전체', '전체', SUM(금액) FROM MAIN_TABLE
    
    
    --> 그룹핑 합계 함수를 이용한 기준 항목별 금액 + 소계와 합계의 구현 및 사용방법
    
    사용방법은 다음과 같이 간단히 CUBE 혹은 ROLLUP만 GROUP BY 오른쪽에 추가해주면 된다.
    
    > SELECT 회사명, 지역, 추가방식, SUM(금액) FROM MAIN_TABLE GROUP BY CUBE (회사명, 지역, 추가방식)
    > SELECT 회사명, 지역, 추가방식, SUM(금액) FROM MAIN_TABLE GROUP BY ROLLUP (회사명, 지역, 추가방식)
    
    한편 CUBE와 ROLLUP은 그룹을 나눈 컬럼의 수(N)에 따라 다음과 같이 각각 2^N개, N+1개의 소계(합계)를 제공
    하므로 업무 요건에 맞게 사용하면 된다. 또한 GROUPING 함수를 통해 업무요건에 알맞게 SORTING, NAMING
    등을 구성하거나 업무요건에 없는 소계를 제외할 수도 있다(RETURN이 1인 경우 그룹 기준에서 제외됨을 의미하
    며 0인 경우 그룹 기준에 포함됨을 의미한다)
    
  • GROUP BY 합계 함수
  • * CUBE와 GROUPING 함수 사용 예시
    
    SELECT 회사명, 지역, 추가방식, 금액,
     CASE WHEN COND1='0' AND COND2='0' AND COND3='0' THEN '01'
    WHEN COND1='0' AND COND2='0' AND COND3='1' THEN '02'
     WHEN COND1='0' AND COND2='1' AND COND3='0' THEN '03'
    WHEN COND1='0' AND COND2='1' AND COND3='1' THEN '04'
     WHEN COND1='1' AND COND2='0' AND COND3='0' THEN '05'
    WHEN COND1='1' AND COND2='0' AND COND3='1' THEN '06'
     WHEN COND1='1' AND COND2='1' AND COND3='0' THEN '07'
     WHEN COND1='1' AND COND2='1' AND COND3='1' THEN '08'
     END AS SORT_ORDER
    FROM(SELECT 회사명, 지역, 추가방식, SUM(금액) AS 금액, GROUPING(회사명) AS COND1,
    GROUPING(지역) AS COND2, GROUPING(추가방식) AS COND3
     FROM MAIN_TABLE GROUP BY CUBE (회사명, 지역, 추가방식))
    ORDER BY 회사명, SORT_ORDER, 지역, 추가방식
    

참고1) 업무 요건상 특정 소계가 불필요하다면(COND1=’1’ AND COND2=’0’ AND COND3=’0’) OR (COND1=’1’ AND COND2=’0’ AND COND3=’1’)와 같이 필요한 소계들만 추출되도록 WHERE 조건에 추가한다.

참고2) 소계의 NAMING 또한 위 SORT_ORDER 컬럼과 같이 GROUPING 함수의 결과인 COND1, COND2, COND3을 조합하여 업무 요건에 알맞게 생성한다.

참고3) 변경 전/후 쿼리를 dbms_xplan.display_cursor 등으로 비교하여 실제 일의 양을 비교해보면 큰 성능개선을 확인 할 수 있을 것이다.

참고4) 필요한 소계의 종류가 적다면 GROUPING SETS 함수를 통해 명시적으로 그룹핑에 참여할 COLUMN을 지정하는 것이 성능에 유리하다. 예를 들어 GROUP BY GROUPING SETS((회사명, 지역, 추가방식), (지역, 추가방식), 지역,() )은 총 4가지(회사별/지역별/추가방식별 금액, 지역별/추가방식별 소계, 지역별 소계, 전체)를 나타낸다.

  • - 해당 강좌는 도서 " [우리 회사 데이터베이스를 티베로로 변경하기]"의 내용을 옮겼습니다.
  • - 해당 도서는 기간계 DBMS(DATABASE MANAGEMENT SYSTEM)를 티베로로 전환하는 실제 프로젝트를 수행한 실무자가 DBMS 전환 과정과 실제 적용 사례, 문제 해결 과정 등을 자세하게 설명하고 있습니다.

- 강좌 URL : http://www.gurubee.net/lecture/4130

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입