메뉴 건너뛰기

bysql.net

3. 데이터 모델 측면에서의 검토

2011.06.16 02:21

AskZZang 조회 수:2723

03 데이터 모델 측면에서의 검토

 

불합리한 데이터 모델이 소트 오퍼레이션을 유발하는 경우를 흔히 접할 수 있다.

SQL group by, union, distinct 같은 연산자가 불필요하게 많이 사용되는 패턴을 보인다면 대개 데이터 모델이 잘 정규화되지 않았음을 암시한다.

데이터 모델 이상으로 발생한 데이터 중복을 제거하려다 보니 소트 오퍼레이션을 수행하게 되는 것이다.

 

사례 1. M:M 관계를 갖도록 테이블을 설계한 경우

 

) 과금은 매달 한 번씩 발생하고, 하나의 과금에 대해 고객이 여러 번에 걸쳐 입금할 수 있기

때문에 수납과는 1:M 관계. => 그림 5-3 참고

 

잘못된 설계로 인해 그림 5-4와 같이 M:M 관계로 되어 있으면

과금과 수납 내역을 조회하려면, 매번 아래처럼 과금 테이블을 먼전 group by 해야만 한다.

 

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.순번

;

M:M 관계가 발생되어진 이유.

과거 데이터 이관시 발생한 예외 케이스 때문.

ð  실제 데이터를 조회해 본 결과 불과 0.09%에 해당하는 14,979건만 M:M 관계

ð  데이터를 정제하고 1:M 관계를 갖도록 모델을 수정

ð  그 결과, 불필요한 group by 연산을 제거할 수 있어 쿼리가 간단해졌음은 물론 성능도 전반적으로 향상

 

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. 테이블 개수를 줄인다는 이유로 자식 테이블에 통합시키는 경우

 

그림 5-6, 그럼 5-7 참고

 

PK 외에 관리할 속성이 아예 없거나 소수일 때, 테이블 개수를 줄인다는 이유로 자식 테이블에 통합시키는 경우가 종종 있다.

 

) 고객별 가입상품 레벨의 데이터를 조회하고자 하는 경우

ð  그때마다 고객별상품라인테이블을 group by 해야 한다.

 

SELECT 과금.고객ID,

       과금.상품ID,

       과금.과금액,

       가입상품.가입일시

FROM   과금,

       (SELECT 고객ID,

               상품ID,

               MIN(가입일시) 가입일시

        FROM   고객별상품라인

        GROUP BY 고객ID,

                 상품ID) 가입상품

WHERE  가입상품.고객ID  = 과금.고객ID(+)    

AND    가입상품.상품ID  = 과금.상품ID(+)    

AND    과금.과금연월(+) = :YYYYMM

;

 

정규화된 데이터 모델을 사용했다면

 

SELECT 과금.고객ID,

       과금.상품ID,

       과금.과금액,

       가입상품.가입일시

FROM   과금,

       가입상품

WHERE  가입상품.고객ID  = 과금.고객ID(+)    

AND    가입상품.상품ID  = 과금.상품ID(+)    

AND    과금.과금연월(+) = :YYYYMM

;

 

위에 쿼리처럼 간단해지고 시스템 전반의 성능 향상에도 도움이 된다.

 

 

사례 3. 순번 컬럼을 증가시키면서 순서대로 데이터를 적재하는 경우

 

순번 컬럼을 증가시키면서 순서대로 데이터를 적재하는 점이력 모델은 선분이력에 비해 DML 부하를 최소화할 수 있는 장점이 있지만, 대량 집합의 이력을 조회할 때 소트를 많이 발생시키는 단점이 있다.

특히, 마지막 이력만 조회하는 업무가 대부분일 때 비효율이 크다.

 

) 여러 이력 테이블로부터 최근 데이터만을 읽어 조인하는 경우

 

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  ......)

;

 

문제점) 이력 조회하는 모든 부분을 인라인 뷰로 감싸고, 분석함수를 이용해 순번상 가장 큰 값을 갖는 레코드만을 추출. 대부분 쿼리가 이런 형태를 띠고 있다면 다른 대안 모델을 고려해 볼 필요가 있다.

 

대안) 순번 컬럼을 999로 입력하거나 플래그 컬럼을 둔다면 소트를 일으키지 않고도 마지막 레코드를 쉽게 추출할 수 있고, 선분이력 모델을 채택한다면 더 큰 유연성을 얻을 수 있다.

 

이들 대안 모델을 채택하면

           새로운 이력이 쌓일 때마다 기존 값을 갱신해야 하는 부담이 생김.

           하지만 한 번의 갱신으로 수백 뻔의 조회를 빠르게 할 수 있다면 더 나은 선택.

 

현재 데이터만 주로 조회한다면

           데이터 중복이 있더라도 마스터 테이블을 이력 테이블과 별도로 관리