오라클 성능 고도화 원리와 해법 II (2010년)
데이터 모델 측면에서의 검토 0 0 3,068

by 구루비 소트튜닝 [2010.06.02]


  • 불합리한 데이터 모델이 소트 오퍼레이션을 유발하는 경우가 많은데, GROUP BY, UNION, DISTINCT 같은 연산자가 많을 경우
    데이터 모델이 잘 정규화되지 않았거나, 데이터 모델 이상으로 발생한 데이터 중복을 제거하려는 과정에서 소트를 유발함

1. 사례1

  • 아래의 테이블 관계는 1:M 관계인 과금과 수납 테이블인데, 컨버전 시 발생한 예외 Case 때문에 M:M 관계가 되어
    항상 GROUP BY를 하게 되고 이로 인해 성능이 나빠지는 내용이다.

1) 1:M 정상 관계 ERD

2) M:M 비 정상 관계 ERD

3) M:M 관계를 극복하기 위한 SQL


SELECT A.상품ID,
       A.과금액,
       B.수납액,
       B.수납일시
FROM   (SELECT 고객ID,
               상품ID,
               과금연월,
               SUM(과금액) 과금액
        FROM   과금
        WHERE  과금연월 = :과금연월
        AND    고객ID   = :고객ID
        GROUP BY 고객ID,
                 상품ID,
                 과금연월) A,
       수납 B
WHERE  A.고객ID   = B.고객ID(+)
AND    A.상품ID   = B.상품ID(+)
AND    A.과금연월 = B.과금연월(+)
ORDER BY A.상품ID,
         B.순번
;

4) M:M 관계 상세 ERD

5) 1:M 관계로 변경 후(컨버전) SQL


SELECT A.상품ID,
       A.과금액,
       B.수납액,
       B.수납일시
FROM   과금 A,
       수납 B
WHERE  A.고객ID   = B.고객ID(+)
AND    A.상품ID   = B.상품ID(+)
AND    A.과금연월 = B.과금연월(+)
AND    A.과금연월 = :과금연월
AND    A.고객ID   = :고객ID
ORDER BY A.상품ID,
         B.순번
;

2. 사례2

  • PK외에 관리할 속성이 없거나, '가입상품'처럼 소수일 때, 테이블 개수를 줄이기 위해 자식 테이블과 통합하는 경우 발생되는 소트 부하 예제
  • 데이터 누락 및 정합성에도 문제가 없지만, 만약 고객별 가입상품 레벨의 데이터를 조회가 빈번할 경우 '고객별상품라인' 테이블이 이미 하위
    레벨(상태코드)로 내려왔기 때문에 가입일시만을 가져오기 위해서는 반드시 GROUP BY를 사용해야 하므로 성능에 안좋음
  • 그러므로 반 정규화를 할 때 반드시 어플리케이션 레벨까지 고려를 해야 함.

1) 정규화가 잘 된 ERD

2) 반 정규화 된 ERD

3) 반 정규화 된 SQL


SELECT 과금.고객ID,
       과금.상품ID,
       과금.과금액,
       가입상품.가입일시
FROM   과금,
       (SELECT 고객ID,
               상품ID,
               MIN(가입일시) 가입일시
        FROM   고객별상품라인
        GROUP BY 고객ID,
                 상품ID) 가입상품
WHERE  가입상품.고객ID  = 과금.고객ID(+)     
AND    가입상품.상품ID  = 과금.상품ID(+)     
AND    과금.과금연월(+) = :YYYYMM
;

4) 정규화 된 SQL


SELECT 과금.고객ID,
       과금.상품ID,
       과금.과금액,
       가입상품.가입일시
FROM   과금,
       가입상품
WHERE  가입상품.고객ID  = 과금.고객ID(+)     
AND    가입상품.상품ID  = 과금.상품ID(+)     
AND    과금.과금연월(+) = :YYYYMM
;     

3. 사례_3

  • 순번(Sequence) 컬럼을 증가시키면서 순서대로 데이터를 적재하는 점이력 모델은 선분이력에 비해 DML 부하는 최소화되지만,
    이력조회 시 많는 소트를 발생시킴
  • 아래 예제는 순번으로 관리될 때 분석함수를 사용하여 마지막 이력만 조회하는 업무를 구현한 내용인데, SQL만 보고도 많은
    소트가 발생될 거라는 것을 짐작해 볼 수 있다.

SELECT ......
FROM   (SELECT ......
        FROM   소송심급피해대상 A,
               소송대상청구     B,
               소송심급         C,
               소송             D,
               소송결재         E,
               민원마스터       F,
               VOC유형코드      G,
               (SELECT ......
                FROM   (SELECT ......,
                               RANK() OVER(PARTITION BY ...... ORDER BY 종결년도 DESC, 종결순번 DESC) RANK
                        FROM   소송심급대상종결 A)
                WHERE   RANK = 1) H,
               (SELECT ......
                FROM   (SELECT ......,
                               RANK() OVER(PARTITION BY ...... ORDER BY 마감일자 DESC) RANK
                        FROM   피해청구 A)
                WHERE   RANK = 1) I,
               (SELECT ......
                FROM   (SELECT ......,
                               RANK() OVER(PARTITION BY ...... ORDER BY A.판결년도 DESC, A.순번 DESC) RANK
                        FROM   판결         A,
                               소송피해내용 B,
                               소송금       C,
                               소승소득내용 D,
                               (SELECT ......
                                FROM   (SELECT ......,
                                               RANK() OVER(PARTITION BY ...... ORDER BY 순번 DESC) RANK
                                        FROM   소송결재
                                        WHERE  ......)
                                WHERE  RANK = 1) E
                        WHERE  A.판결년도 = B.판결년도
                        AND    A.판결순번 = B.판결순번
                        AND    ......)
                WHERE   RANK = 1) J,
               (SELECT ......
                FROM   (SELECT ......,
                               RANK() OVER(PARTITION BY ...... ORDER BY A.특인차수 DESC, B.순번 DESC) RANK
                        FROM   특인     A,
                               특인결재 B
                        WHERE  A.특인년도 = B.특인년도
                        AND    A.특인순번 = B.특인순번
                        AND    ......)
                WHERE   RANK = 1) K,
               (SELECT ......
                FROM   (SELECT ......,
                               RANK() OVER(PARTITION BY ...... ORDER BY A.소송심급피해대상순번 DESC) RANK
                        FROM   소송대상청구     A,
                               소송심급피해대상 B,
                               소송심급         C,
                               소송             D
                        WHERE  ......)
                WHERE   RANK = 1) L
        WHERE  ......)
;

  • 이를 해결하기 위한 대안으로는 FLAG 컬럼을 추가하여 마지막 이력에 대한 레코드를 관리할 경우 소트 부하를 없앨 수 있다.

문서에 대하여

"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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