7.2 반정규화

7.2.1 반정규화 주의사항

• 1. 반정규화란 정규화된 엔티티타입,속성,관계를 시스템의 성능향상,개발과 운영의 단순화를 위해 모델을 통합하는 프로세스를 의미한다.
• 2. 이상적인 논리모델은 모든 엔티티타입,속성,관계가 반드시 한개만 존재하며 따라서 입력,수정,삭제도 한군데에서만 발생하므로 데이타 값이 변질되거나 이질화될 가능성이 없다. 반면 여러 테이블이 생성되어야 하므로 SQL작성이 용이하지 않고 과다한 테이블 조인이 발생하여 성능이 저하될 가능성이 높다.
• 3. 반대로 반정규화를 하면 여러 개의 테이블이 단순해지므로 SQL작성이 용이하고 성능이 향상될 가능성이 많다. 그러나 같은 데이타가 여러 테이블에 걸쳐 존재하므로 무결성이 깨질 우려가 있다.
• 4. 결국 반정규화시 가장 중요한 검토기준은 엔티티타입,속성,관계에 대해 데이타의 정합성과 무결성을 우선으로 할지 데이타베이스 구성의 단순화와 성능을 우선으로할지에 달려있다.

7.2.2 반정규화 절차

1.반정규화의 대상 선정

• 1)접근하는 프로세스 수가 많고 항상 일정한 범위를 조회하는 테이블
• 2)대량의 데이타를 처리하는 테이블
• 3)통계성 프로세스의 대상이 되는 테이블
• 4)조회시 지나치게 많은 조인이 일어나는 테이블군

2.다른 방법으로 유도

• 1)뷰의 활용,클러스터링,인덱스조정,파티셔닝,어플리케이션 로직 변경
• 2)위의 방법을 먼저 고려해 보고 해결되지 않을 경우 반정규화를 시도한다

3.반정규화 적용

• 테이블,속성,관계를 반정규화의 대상으로 삼을 수 있으며 꼭 중복으로 가져가는 것만이 반정규화가 아니라 추가,분할,제거의 방법이 사용될 수도 있다.

7.2.3 반정규화 방법

1.테이블 반정규화 방법

1)테이블 병합

• SQL문장에서 항상 두개의 테이블이 조인하여 작성되면 두 테이블은 병합 대상이 된다. 1:1의 경우 PK가 동일하므로 무리없이 병합이 가능하며, 설사 1:M의 관계일지라도 항상 조인이 발생하는 경우 1쪽 데이타의 중복을 무릅쓰고 병합할 수 있다.
• 수퍼타입과 서브타입 관계로 연결되어 있을 경우 필요에 따라 병합 할 수 있다.

2)테이블 분할

• 테이블에 속한 속성중 특정 속성들만 집중적으로 사용되거나 모든 속성이 이용되더라도 PK에 따라 특별한 범위만 집중적으로 사용될 경우 이 부분적인 속성들만 이용하여 분할할 수 있다.
• 지역별 데이타 분산전략이나 데이타 서버별 분산전략에 의해 테이블이 분할될 수 있다

테이블 분할의 특징

• 테이블 스캔범위가 축소되므로 테이블에 접근하는 양이 감소한다.
• 분할로 인해 테이블 잠금 및 경합 현상이 감소한다.
• 분할된 테이블을 같이 조회해야 할 경우 SQL문이 복잡해진다.
• 분할된 모든 테이블을 조회할 경우 처리속도가 느려질 수 있다.

• (?)정규화도 하나의 테이블을 분할해 가는 과정이다. 그렇다면 정규화의 분할과 반정규화의 분할의 차이점은 무엇인가?
• 정규화에서 분할은 필수불가결한 것이다. 즉, 원래 나누어져 있어야 테이블이 설계의 잘못으로 하나로 합쳐져 있어 입력,수정,삭제시 오류가 발생되기 때문에 반드시 분할해야 하는 것이다. 반면 반정규화의 경우 설계의 잘못이 아니다. 하나로 합쳐져 있는 것이 옳바른 모델링이지만 쿼리문의 단순화,성능의 향상등 운영의 필요성에 의해 분할하는 것이다.

테이블의 수직적분할(칼럼분할 개념)

• 1)269페이지 그림 7-21 참조
• 2)하나의 테이블에 있는 특정 칼럼에 대해 사용빈도 차이가 심하고 서로 다른 사용자그룹에 의해 특정 칼럼들만 자주 사용하고 처리된다면 별도의 테이블로 구성할 수 있다. 이 경우 자주 쓰는 데이타들이 인접 블럭에 저장되어 성능이 향상될 수 있다.
• 3)특정 컬럼이 아주 길거나 특별한 데이타타입인 경우(Blob,Clob) 별도의 테이블로 구성할 수 있다.

테이블의 수평적분할(로우분할 개념)

• 1)하나의 매출테이블에 전국 지역의 매출정보가 담겨져 있지만 각 지사는 자신이 속한 지역의 매출정보만을 집중적으로 이용할 것이므로 지역별로 로우를 쪼개 별도의 테이블로 만들 수 있다.
• 2)수납이력의 데이타가 일자별로 저장되어 있는데 월별로 이용하는 경우가 많은 경우 월별로 데이타를 나누어 별도 테이블로 만들 수 있다.
• 3)이와같은 수평적분할은 DBSM마다 제공하는 파티셔닝 테이블 기법 이용으로 대체할 수도 있다.

테이블 추가

• 통계관리,이력관리등 데이타의 효율적인 이용을 위하여 부가적인 정보가 필요한 경우 별도의 테이블로 만들 수 있다.

2.칼럼 반정규화 방법

• 자주 조인이 발생하는 칼럼,간단한 하나의 칼럼을 읽어오기 위해 접근경로가 너무 복잡한 경우 칼럼을 중복시킴으로써 성능을 향상시킬 수 있다. 그러나 이러한 중복은 데이타의 무결성확보가 어렵다,중복 데이타를 저장하기 위해 추가적인 디스크 공간이 필요하다,여러 개의 테이블에 같은 칼럼이 흩어져 있을 경우 어떤 테이블에서 처음으로 칼럼이 생성되었는지 혼란스럽다 등의 문제점이 발생되기도 한다.

1)중복칼럼의 추가

해당 테이블에서 자주 사용하는 칼럼의 중복

• <지점>과 <영업사원>이 부모-자식관계에 있으며 영업사원은 반드시 어떤 지점에 속해 있는 경우이다.
• 영업사원이 속한 지점의 지점위치를 자주 검색하고 사용하여 항상 지점과 조인을 해야 한다면 지점위치 칼럼을 <영업사원> 테이블에 중복시킬 수 있다.
• 만일 지점의 위치가 변경되거나 영업사원의 소속지점이 변경된다면 <영업사원> 테이블의 지점위치 칼럼을 수정해야 하는 번거로움이 따른다.

조회하는 경로를 단축가히 위한 칼럼의 중복

• 위 그림과 같은 테이블관계도가 있을 때 만일 배송에서 주문한 고객의 고객명을 알고 싶다면 <배송>과 <주문>을 주문번호로 조인하여 주문한 고객번호를 알아낸 후 이 칼럼을 키로 하여 다시 <고객>테이블과 조인해야 하는 불편이 생긴다
• 그러나 만일 <배송> 테이블에 고객번호 칼럼을 중복시켰다면 <배송>과 <고객>테이블이 직접 조인될 수 있을 것이다.

• 같은 예인데 만일 <고객>과 <주문> 주식별자관계라면 상황이 달라진다
• 주식별자관계의 경우 고객번호가 연관된 테이블인 <주문>,<주문목록>에 상속이 되며 <주문목록>과 <배송>의 경우 비록 비식별자관계지만 관계에 의해 주문번호,고객번호가 FK로 상속됨으로써 자연스럽게 <배송>테이블이 고객번호를 가질 수 있게 된다.
• <배송> 테이블에 제품번호가 빠져있슴!!!

2) 파생칼럼추가

칼럼에 의한 파생칼럼 추가

• 위와 같은 테이블관계도가 있을 때 만일 주문 총금액을 구하려고 한다면 <주문>,<주문목록>,<제품> 테이블을 조인한 후 주문번호에 의해 제품의 단가를 sum()해야하는 불편이 따른다.
• 그러나 그림에서 처럼 <주문>테이블에 계산된 주문총금액을 추가한다면 쿼리문이 한결 단순해 질 것이다.
• 그렇지만 주문목록의 데이타가 새로입력,수정,삭제가 일어난다면 그때마다 주문총금액을 변경해야 하는 불편이 따른다.(경험상 계산에 의해 발생되는 파생칼럼을 추가하는 것은 그리 추천하지 않는다)

로우에 의한 파생칼럼 추가

• 공사비누적 칼럼이 없다면 누적금액을 가져올 때마다 분석함수를 사용하여 sum()해야 하는 불편이 따르므로 별도의 누적금액을 추가한 경우이다.
• 그러나 이러한 방식은 금액의 변경이 있을 시마다 금액변경이 일어난 날짜 이후의 모든 누적금액을 변경해야 하는 엄청난 작업이 뒤따르므로 절대 해서는 안된다고 생각한다.
• 누적금액이 필요할 때마다 분석함수에 의한 sum()을 하도록하며 만일 이것때문에 성능이 크게 문제가 된다면 별도의 통계테이블을 작성해야 할 것이다.

3) 이력데이타모델의 칼럼추가

변경,발생 이력에 대한 최신정보 추가

• 위와 같은 테이블관계도에서 만일 <주문목록이력> 테이블에 최신의 데이타임을 표시하는 진행여부 칼럼이 없다면 주문번호 1001에서 1100까지 최신의 제품별 주문수량을 가져오기 위해선 아래처럼 쿼리문을 작성해야 한다.

SELECT A.주문번호,A.제품명,B.주문수량
FROM 주문목록 A,주문목록이력 B
WHERE A.주문번호 BETWEEN '1001' AND '1100'
   AND A.주문번호=B.주문번호
   AND A.제품번호=B.제품번호
   AND B.변경일자 = (
                    SELECT MAX(변경일자)
                    FROM 주문목록이력 C
                    WHERE B.주문번호=C.주문번호
                       AND B.제품번호=C.제품번호
                   )

• 그러나 진행여부칼럼이 있다면 아래처럼 단순해 진다

SELECT A.주문번호,A.제품명,B.주문수량
FROM 주문목록 A,주문목록이력 B
WHERE A.주문번호 BETWEEN '1001' AND '1100'
   AND A.주문번호=B.주문번호
   AND A.제품번호=B.제품번호
   AND B.진행여부='Y'

진행 이력에 대한 최신정보 추가

• 위와같은 테이블관계도에서 만일 상태종료일자 칼럼이 없는 경우 20040815 현재 공사진행에 대한 상태조회 쿼리문

SELECT A.공사명,B.상태시작일자,B.진행상태
FROM 공사 A,공사진행 B
,(
   SELECT 공사번호,MAX(상태시작일자) 상태시작일자
   FROM 공사진행
   WHERE 상태시작일자 <= '20040815'
   GROUP BY 공사번호
) C
WHERE A.공사번호=B.공사번호
   AND B.공사번호=C.공사번호
   AND B.상태시작일자=C.상태시작일자

SELECT A.공사명,B.상태시작일자,B.진행상태
FROM 공사 A,공사진행 B
WHERE A.공사번호=B.공사번호
   AND B.상태시작일자= (
                       SELECT MAX(상태시작일자)
                       FROM 공사진행 C
                       WHERE B.공사번호=C.공사번호
                              AND C.상태시작일자 <= '20040815'
                         )

• 그러나 만일 상태종료일자 칼럼이 있다면 다음과 같이 쿼리문이 단순해 진다.

SELECT A.공사명,B.상태시작일자,B.진행상태
FROM 공사 A,공사진행 B
WHERE A.공사번호=B.공사번호
   AND B.상태시작일자 <= '20040815'
   AND B.상태종료일자 > '20040815'

• (?) 그러나 약간의 의문점이 있다.
• 상태종료일자가 상태종료일자2처럼 연속적으로 존재한다면 1,2번째 쿼리와 3번째 쿼리의 결과는 같지만 만일 진행일력이 상태종료일자1처럼 비연속적으로 존재한다면
• 1,2번 쿼리의 결과는 20040802-D의 결과가 나오지만 3번째 쿼리의 결과는 없다.
• 이런 경우 결국엔 1,2번의 쿼리를 이용해야 한다.

4) PK에 의한 칼럼추가

복합PK에 의한 칼럼추가

• 접수테이블의 PK가 접수일자(8)사업소번호(4)일련번호(3) 총 15자리로 구성되어 있는데 특정 사업소에서 진행중인 접수를 읽으려고 한다면 PK를 SUBSTR()함수로 절개해서 조회해야 하며 결국 칼럼변형으로 인해 인덱스를 탈 수 없는 문제가 발생한다.
• 그러므로 사업소번호를 칼럼으로 별도로 추가한다면 비록 접수번호에 이미 사업소번호가 있어 중복에 해당하지만 인덱스 형성이 가능하고 쿼리문도 PK칼럼변형이라는 문제가 발생하지 않는다.

PK에 의한 칼럼추가

• PK가 여러개의 칼럼으로 구성되는 복합키인 경우 자식,손자테이블에 그대로 상속되어 쿼리문 작성이 번거로움이 따른다. 그러므로 PK를 구성하는 칼럼을 일반속성으로 내리고 별도의 의미가 있는 인공키를 PK로 삼도록 한다.

5) 시스템 오작동 처리를 위한 칼럼추가

• 시스템을 사용하는 도중 실수에 의해 입력된 데이타의 수정을 위해 추가되는 칼럼이다.
• 공사진행 테이블에 진행상태라는 칼럼이 있고 이 칼럼이 현재 D로 되어있으나 잘못된 변경일 경우 이전 상태로 되돌려야 하나 이전 상태가 어떤 것이었는지 알 수가 없을 때에는 조정을 할 수 없다.
• 이런 경우 이전상태라고 하는 칼럼을 두어 상태 변경시마다 이전상태를 넣어둔다면 상태원복을 쉽게 처리할 수 있다.

3.관계반정규화 방법

• 위와같은 테이블관계도에서 <배송> 테이블에 고객번호가 없다고 가정하자.
• 이 상태에서 만일 배송을 주문한 고객명을 알고 싶다면 <배송>,<주문목록>,<주문>,<고객>등 복잡한 조인을 통해 구할 수밖에 없다.
• 그러나 만일 <배송>테이블에 고객번호를 FK로 추가한다면 쿼리문이 아래처럼 훨씬 단순해진다.

SELECT B.고객번호,B.고객명,A.배송번호,A.배송일자
FROM 배송 A,고객 B
WHERE A.배송번호='123456'
   AND A.고객번호=B.고객번호

• <배송>테이블은 <주문목록>,<고객> 테이블과 이중의 관계를 가지게 됨으로써 관계의 반정규화가 일어났다.