[데이터베이스] 아는만큼 보이는 데이터베이스(3) - 이력유형 데이터 모델링

이력유형 데이터 모델링

---

시간에 따라 발생하는 데이터 형식을 이력이라고 합니다. 이력 데이터는 시간에 따라 발생하고, 동일한 컬럼 유형에 발생합니다. 또한 시간에 따라 반복적으로 발생하기 때문에 다른 테이블에 비해 대량의 데이터가 테이블에 적재될 가능성이 큽니다. 이 특성은 데이터베이스에 발생하는 트랜잭션의 성능 저하로 이어지는 경우가 있습니다. 

이력테이블의 특징

 - 시간에 따라 발생한다.

 - 동일한 컬럼에 발생한다.

 - 대량데이터가 발생할 가능성이 높다

 - 성능에 영향을 주는 경우가 많다.

SQL 구문이 정상이고, 실행계획도 정상이라면 데이터모델에 구조적인 문제가 있을 경우가 매우 높습니다. 실전에서 적용하는 이력의 유형은 테이블 구분에 따라, 컬럼과 로우 단위에 따라, 그리고 이력데이터의 발생 방법에 따라 분류할 수 있습니다.

* 테이블 구분에 따라 구분하는 방법

유형1) 내부스냅샷 이력	- 별도의 테이블 없이 시간에 따라 자기 자신의 테이블에 데이터가 발생되는 구조  - 이력 테이블이 마스터이므로 관계를 통해 PK구조도 모두 상속됨  - 특징: 일부 속성 값이 변경되어도 전체 속성값 생성
유형2) 1:M 스냅샷 전체이력	- 별도의 테이블에 마스터 테이블의 현재 정보를 포함해 변경된 모든 정보를 보관함  - 특징: 현재 정보 포함, 일부 변경되어도 전체 생성
유형3) 1:M 스냅샷 과거이력	- 마스터 테이블에는 현재 정보만 존재, 이력 테이블에는 과거 변경 정보만 보관  - 특징: 과거정보, 일부 변경되어도 전체 생성
유형4) 1:M 스냅샷 군집 전체이력	- 마스터 테이블의 일부 컬럼드을 묶어 별도의 테이블에 마스터 테이블의 현재정보를 포함한 모든 변경된 정보를 보관함  - 특징: 트랜잭션 형식이 비슷하게 발생하는 컬럼을 붂어서 이력 테이블을 구성, 현재 + 과거
유형5) 1:M 스냅샷 군집 과거이력	- 마스터 테이블의 일부 컬럼들을 묶어 별도의 테이블에 마스터 테이블의 과거정보만을 변경된 정보도 보관  - 특징: 트랜잭션 형식이 비슷하게 발생하는 컬럼을 붂어서 이력 테이블 구성, 과거 정보

* 테이블 구분에 따라 구분하는 방법

유형1) 로우단위 이력	- 이력테이블에는 마스터 테이블의 변경된 내용이 하나의 로우에 전체적으로 기록되는 형태(일반적인 이력)
유형2) 컬럼단위 이력	- 이력 테이블에는 마스터 테이블의 변경된 내용이 하나의 로우에 하나의 컬럼 변경된 내용이 기록되는 형태

* 이력 데이터 발생 방법에 따라 구분하는 방법

실제로 성능상 고려해야할 기능성 컬럼은 이 분류방법에 따라 지정됩니다. 이력 데이터의 발생방법과 활용되는 유형에 따라 구분되는 방법입니다.

유형1) 변경이력	- 마스터 테이블의 컬럼이 변경되면 이력을 관리하는 형태
유형2) 발생이력	- 마스터 테이블의 pk를 포함하여 전체에 대해 인스턴스 생성  - 엄격하게 구분하면 이력형식이 아닌 인스턴스 생성이라고도 구분할 수 있음
유형3) 진행이력	- 업무진행 상태에 따라 업무의 정보를 관리하는 테이블  - 상태 정보가 계속 영향을 미치는 상태

일반적으로 변경이력과 발생이력의 경우는 최근값 조회빈도가 높기 때문에 '최신여부'등의 기능성 컬럼을 추가하여 설계하는 것이 성능 면에서 좋습니다. 

상태정보가 계속 유지되면서 불특정 날짜에 데이터를 조회하는 진행이력의 경우 특정한 시간을 기점으로 데이터를 조회하는 경우가 나타날 수 있습니다. 이와 같은 경우는 하나의 로우 상태정보가 바뀌어 생성된 일시와 그 로우의 상태정보가 다른 정보로 바뀐, 즉 자기 자신에 대해서는 종료일자를 지정하는 것이 성능면에서 좋은 방법이 됩니다. (REG_DATE, CHG_DATE)

실전 프로젝트에서 이력 데이터 모델을 만들때는 항상 이력 데이터 관리를 어떤 유형으로 할 것인지 고민하게 됩니다. 하나의 테이블로 만들것인지 아니면 두개의 테이블로 분리시킬것인지, 과거 데이터만을 포함할 것인지 현재 데이터를 같이 포함할 것인지 고민하지 않을 수 엇습니다.

여기서 실수하게 되는 것은 대량의 데이터가 테이블에 적재되는데도 성능을 고려한 요소들을 고려하지 않고 일반적인 테이블로 설계하는 경우입니다.

대량의 데이터가 하나의 테이블에 집약되는 특징 탓에 데이터를 처리하는 SQL 구문의 양이 증가하여 성능이 저하되는 경우가 많은데, 이런 현상을 극복하기 위해 데이터 모델러는 특별한 고려사항을 추가하여 모델링을 해야합니다. (이력 유형 결정, 성능 고려)

즉, 업무적으로 먼저 이력 유형을 결정하고, 그 이후에 이력 테이블에 데이터가 대량으로 적재될 것이 예상되면 성능을 고려한 기능상 컬럼을 추가하거나 테이블 통합/분리를 고려하면 됩니다.

이력 데이터 발생에 따른 구분에서 테이블의 특성에 따른 기능성 컬럼을 고려하지 않으면 SQL, 실행계획은 모두 정상이여도 관련 SQL 실행이 모두 느려지는 현상이 발생할 수 있습니다.

이력은 시간에 따라 발생이력, 변경이력, 진행이력의 형식으로 구분할 수 있습니다. 발생이력과 변경이력은 어떤 특정시점에 정보가 발생하고 마지막에 생성된 정보가 빈번하게 이용하게 됩니다. 진행이력의 경우 어떤 특정 시점에 정보가 발생하고 발생된 정보는 계속 그 다음에 새로운 정보가 생성될 때까지 연속적으로 영향을 미칩니다. 발생이력과 변경이력은 최종 생성된 데이터를 구분하기 위한 기능성 컬럼이 필요하고, 진행 이력은 연속적인 특징이 있으므로 생성된 시점과 완료된 시점에 대한 기능성 컬럼이 필요합니다.

SQL 구문 작성시 그룹함수를 사용하면 그룹의 대상이 많아지면서 성능이 저하되는 것은 당연한 현상입니다.

SELECT A.사업소코드, SUM(A.접수물량)
FROM   접수통계 A, (SELECT 사업소코드, MAX(변경일자) AS 변경일자
			FROM 접수통계
			WHERE 접수구분코드 = '01'
			GROUP BY 사업부코드) B
WHERE A.사업소코드 = B.사업소코드
  AND A.변경일자 = B.변경일자
GROUP BY A.사업소코드

* Execution Plan

0       SELECT STATEMENT Optimizer=CH

1  2      SORT (GROUP BY)

2  1         TABLE ACCESS (BY INDEX ROW)

3  2            NESTED LOOPS

4  3                VIEW

5  4                   SORT (GROUP BY)

6  5                      TABLE ACCESS (BY INDEX ROWID) OF '접수통계'

7  6                         INDEX (UNIQUE SCAN) OF '접수통계_I01' (UNIQUE)

8  3                INDEX (RANGE SCAN) OF 'PIAMT0010_PK' (UNIQUE)

최신여부를 나타내는 기능성 컬럼 최신여부='Y'만 있으면 쉽게 데이터를 처리할 수 있으므로 SQL 문장의 처리 성능이 향상됩니다. 단, 새로운 데이터가 입력될때 이전 변경일자에 대한 최신여부 값을 'Y'에서 'N'으로 바꾸어야하는 부가적인 작업이 발생합니다. 즉 입력, 수정, 삭제시 기능성 컬럼에 대해 추가적인 고려가 필요합니다.

SELECT 사업부코드, SUM(접수물량)
FROM   접수통계
WHERE  접수구분코드='01' AND 최신여부='Y'
GROUP BY 사업소코드

* Excecution Plan

0       SELECT STATEMENT Optimize

1  0      SORT (GROUP BY)

2  1          TABLE ACCESS (BY INDEX ROWID) OF '접수통계'

3  2              INDEX (RANGE SCAN) OF '접수통계_I01' (NON-UNIQUE)

* 진행이력 관리하기

진행이력의 경우 시작과 종료에 대한 기능성 컬럼이 존재하지 않으면 성능이 저하됩니다. 진행이력은 발새이야력이나 변경이력과는 달리 발생된 시점 이외에도 데이터 조회가 빈번하게 이루어집니다.

SELECT A.기관코드, A.기관거래등급
FROM   기관정보 A, (SELECT 기관코드, MAX(적용일자)
		      FROM 기관정보
		     WHERE 적용일자 <= '20080701'
		    GROUP BY 기간코드) B
WHERE A.기관코드 = B.기관코드
  AND A.적용일자 = B.적용일자

* Execution Plan

0       SELECT STATEMENT Optimizer

1  0      TABLE ACCESS (BY INDEX ROW)

2  1         NESTED LOOPS

3  2            VIEW

4  3               SORT (GROUP BY)

5  4                  TABLE ACCESS (FULL) OF '기관 정보'

6  2            INDEX (RANGE SCAN) OF '국가정보_PK' (UNIQUE)

인라인뷰의 그룹함수를 사용해 값을 가져오므로 FULL TABLE SCAN이나 인덱스 스캔의 범위가 넓어져 성능이 저하되는 것을 실행게획을 통해 확인할 수 있습니다.

SELECT A.기관코드, A.기관거래등급
FROM   기관정보 A
WHERE  적용일자 <= '20080701'
  AND  적용종료일자 < '20080702'

* Execution Plan

0       SELECT STATEMENT Optimize

1  0      TABLE ACCESS (BY INDEX ROWID) OF '기관정보'

2  1         INDEX (RANGE SCAN) OF '국가정보_I01'(UNIQUE)

기관정보 테이블에 적용종료일자라는 기능성컬럼이 추가됨으로써 SQL 구문도 단순해지고 이전모델에 비해 성능도 훨씬 빨라지게 되었습니다.

* 이력모델 선택 방법

프로젝트에서 가장 많이 사용되는 모델은 스냅샷 전체 이력 모델과 스냅샷 과거이력 모델 유형입니다. 일반적으로 컬럼단위 이력모델은 자주 이용하는 모델이 아닌데, 금융권 프로젝트의 경우 컬럼에 대한 엄격한 추적을 위해 컬럼단위 이력 모델을 적용하는 경우도 있습니다.

가장많이 사용되는 유형은 1:M 스냅샷 전체 이력 형식입니다. 일부 중복은 있지만 관리항목이 명확하고 대량 데이터 처리시 현재 테이블에 영향을 주지않는 특징을 가지고 있기 때문입니다. 이력 데이터의 양이 적다면 테이블을 분리하여 관리항목을 증가시키기보다는 하나의 테이블로 구성하여 단순한 모습을 확보하늦 것도 좋은 방법이 될 수 있습니다.