http://www.dbguide.net/knowledge.db?cmd=view&boardUid=125319&boardConfigUid=19&boardStep=&categoryUid=205
데이터의 이력 관리를 위한 이력 모델 설계하기
질문 3) 데이터의 이력 관리를 위한 이력 모델 설계는 어떻게 해야 하는지 궁금하다.
하나의 업무 단위가 시간 흐름에 따라 발생하는 과거와 현재 데이터를 지속적으로 유지하는 관리 방법을 이력(history) 관리라고 한다. 이력 관리에 대한 내용을 ‘데이터베이스 설계와 구축’에 있는 내용을 기준으로 설명하겠다.
우리가 데이터 모델링을 진행할 때 이력이라고 하는 기준은 업무 단위의 시작과 끝을 가지고 판단해야 한다. 인사 관리에서 사원은 입사를 하면 반드시 퇴사가 존재한다.
주문 관리에서 상품을 주문하면 배송하든지 취소하든지 업무를 종결할 것이다. 시작과 끝이 존재하는 업무에서 하나의 업무 단위가 진행되는 중에 시간에 따라 변화하는 데이터가 존재하는 경우가 있다. 이때 데이터를 단순히 수정하여 관리한다면 그 데이터는 항상 그 시점의 현재 데이터만을 유지하는 형태가 될 것이며 만약 데이터를 수정하지 않고 계속해서 입력하여 관리한다면 그 시점의 과거 데이터도 유지되면서 현재 데이터도 발생될 것이다. 정보화 시스템을 구축하면서 이력의 대상이 되는 항목은 후자, 즉 하나의 업무 단위가 동일한 관리 항목에 대해 과거 데이터도 유지되면서 현재 데이터도 관리되는 형태를 지칭한다.
이력의 종류는 이력이 발생하는 유형에 따라 세 가지로 구분될 수 있다. 첫번째로 하나의 엔티티 타입에 대한 변경이 있을 때 변경 이전과 이후의 데이터를 모두 관리하는 변경 이력, 두번째로 년월일별로 특정 시점에 발생하는 발생 이력, 그리고 세번째는 어떤 데이터가 시간에 따라 연속성을 가지고 존재하는 형태의 진행 이력이 존재한다.
일단 해당 업무에서 이력 데이터를 관리하고자 했다면 데이터 모델에서 가장 중요하게 고려해야 할 부분이 엔티티 타입에 대한 식별자의 구성과 그 엔티티 타입이 가지는 관계이다. 먼저 엔티티 타입은 변경 이력, 발생 이력, 진행 이력에 따라 세 가지 형태로 구분하여 식별하도록 한다. 세 가지 모두 업무상 꼭 필요한지, 통계 정보나 기업 의사 결정에 필요한 정보인지를 먼저 검토하고 필요한 업무에 대해서만 데이터 모델에 반영하도록 해야 한다.
그리고 엔티티 타입의 구성을 결정한다. 이력이 발생한다는 것은 동일한 엔티티 타입에 대해 시간에 따라 데이터가 발생하는 것이므로 시간을 나타내는 속성이 필요할 것이다. 이 때 엔티티 타입을 한 개로 유지할 것인지 두 개로 분리할 것인지를 결정해야 한다. 시간에 따라 속성의 일부만 변하는지 아니면 모든 속성이 변하는지 파악하고, 일부 속성만 변할 경우 데이터의 중복(redundancy)을 피하기 위해 두 개의 엔티티 타입으로 분리하여 설계한다. 또한 동일 엔티티 타입의 모든 속성이 시간에 따라 같이 변한다고 할지라도 데이터를 조회하는 유형에 따라 엔티티 타입의 분리 여부를 결정해야 한다. 만약 일반적인 업무에서는 현재 유지되는 정보만을 조회하고 과거 데이터에 대해서는 가끔씩 조회한다면 대량으로 존재하는 이력 엔티티 타입을 별개로 구분하기 위해 엔티티 타입을 현재에 해당하는 엔티티 타입과 이력에 해당하는 엔티티 타입으로 분리하여 설계할 수 있다.
발생 이력과 변경 이력
데이터 처리의 성능을 위해 발생 이력과 변경 이력의 경우는 최종 생성된 데이터의 구분을 위한 기능성 컬럼이 필요하고, 진행 이력의 경우는 연속적인 특징이 있으므로 생성된 시점과 완료된 시점에 대한 기능성 컬럼이 필요한 특징을 가지고 있다. 발생 이력, 변경 이력의 경우 최신 값에 대한 기능성 컬럼이 존재하지 않아 성능이 저하된 경우이다.
일반적으로 업무적인 필요에 따라 모델링을 진행한 접수 통계 테이블은 변경 이력 테이블로서 사업소마다 접수받는 물량에 대한 정보를 가지고 있는 테이블이다. 사업소마다 접수 구분 코드가 ‘01’인 접수 물량을 합한 정보를 가져와야 한다면 <그림 11>과 같이 복잡한 SQL 구문이 작성된다.
<그림11> 발생 이력/변경 이력에서 최신 여부에 컬럼이 없을 경우
SQL 구문 작성시 그룹 함수를 사용하면 그룹의 대상이 많아짐에 따라 성능이 저하되는 것은 당연한 현상이다. <그림 11>의 접수 통계 테이블에서는 사업소 코드에 따른 최근에 변경된 내용에 대해 데이터를 가져오기 위해 ‘SELECT 사업소 코드, MAX(변경일자) ~ GROUP BY 사업소 코드’의 형식으로 SQL문을 작성할 수밖에 없다. 실행 계획을 보면 인라인 뷰를 사용했으므로 VIEW라고 하는 단어가 있고 SORT(GROUP BY)가 있어 데이터를 가져오기 위해 중간 단계에서 정렬 작업이 발생되었음을 알 수 있다. 그래서 접수 통계 테이블에 최신 여부를 나타내는 기능성 컬럼을 포함하면 <그림 12>와 같이 SQL 구문이 작성될 것이다.
<그림12> 발생 이력/변경 이력에서 최신 여부를 생성한 경우
최신 여부 컬럼이 접수통계 테이블에 있으므로 데이터를 조회할 때 별도의 인라인 뷰가 작성될 필요 없이 SQL WHERE 절에 최신여부 = ‘Y’만 있으면 쉽게 데이터를 처리할 수 있고 SQL문의 처리 성능은 향상된다. 단 새로운 데이터가 입력될 때 이전 변경일자에 대한 최신 여부 값을 ‘Y’에서 ‘N’으로 바꾸어야 하는 부가적인 작업은 발생된다. 즉 입력·수정·삭제시 기능성 컬럼에 대한 추가적인 고려가 필요하다.
필자가 경험한 E 프로젝트의 경우는 응용 프로그램의 60%가 이와 같은 발생 이력을 이용하여 업무 처리를 하는 프로세스를 가지고 있었는데 개발된 SQL 구문이 모두 인라인뷰와 그룹 함수까지 사용하여 최근에 발생한 값을 가지고 처리하는 방식으로 되어 있었다. 발생 이력이 업무적으로 중요한 테이블이었기 때문에 웬만한 SQL들은 모두 발생 이력 테이블을 이용할 수밖에 없었던 것이다. 개발 도중에 테스트 데이터가 많지 않았을 때는 별로 문제가 없었지만 개발이 끝나고 어느 정도 데이터를 생성한 이후에 테스트해보니 응용 프로그램에 심각한 성능 저하 현상이 발견됐다. 테이블에 인덱스를 추가하고 SQL 구문의 변경을 아무리 해도 근본적으로 성능이 개선되지 않았다. 그래서 발생 이력 테이블에 기능성 컬럼(최신 여부 컬럼) 하나를 추가하고 데이터 입력·수정·삭제시 이 컬럼을 고려하여 처리함으로써 성능 저하 현상을 막을 수 있었다. 비록 프로젝트 막바지에 개발된 프로그램의 60%를 수정해야 했지만 막대한 공수를 투입하여 데이예방한 것이다.
진행 이력
진행 이력의 경우는 발생 이력과 변경 이력과 다르게 발생된 시점 이외에도 데이터 조회가 빈번하게 이루어진다. <그림 13>에 있는 기관 정보 테이블은 어떤 시점에 따라 해당 기관이 가지고 있는 기관 거래 등급을 가지고 있으면서 다른 테이블에서 기관거래 등급 정보를 참조하여 업무를 처리하는 경우이다. 2004년 7월 1일자에 해당하는 기관 코드와 기관 거래 등급을 조회하는 SQL은 다음과 같이 복잡한 SQL 구문이 작성된다.
<그림13> 진행 이력에 상태 관련 종료 값이 없는 경우
<그림14> 진행 이력에 상태 관련 종료 값이 있는 경우
<그림 13>의 기관 정보 테이블에서는 지정된 날짜인 2004년 7월 1일에 기관 코드에 따른 기관 거래 등급 데이터를 가져오기 위해 ‘SELECT 기관 코드, MAX(적용일자) ~ GROUP BY 기관 코드’의 형식으로 먼저 지정된 날과 같거나 작은 적용일자를 가져와야 한다. 그리고 다시 메인 쿼리에서 적용일자를 비교해야 데이터를 가져올 수가 있다. 실행 계획을 보면 인라인 뷰를 사용했으므로 VIEW라고 하는 단어가 있고 SORT(GROUP BY)가 있어 데이터를 가져오기 위해 중간 단계에서 정렬 작업이 발생되었음을 알 수 있다. 적용일자에 인덱스가 있어도 범위가 넓어 성능 저하가 예상된다. 그래서 기관 정보 테이블에 기간을 알 수 있도록 적용 종료일자를 나타내는 기능성 컬럼을 포함하면 <그림 13>과 같이 간단한 SQL 구문이 작성될 것이다.
기관 정보 테이블에 적용일자라는 기능성 컬럼이 추가됨으로써 SQL 구문도 단순해지고 이전 모델에 비해 성능도 훨씬 빨라지게 된다. 단 새로운 데이터가 입력될 때 적용 종료일자 컬럼에 업무적으로는 입력되는 데이터가 있을 수 없지만 편의상 최대 값(예를 들어 9999년 1월 1일)을 입력하여 인덱스 이용에 문제가 없도록 해야 한다. 이와 같이 이력 데이터 모델은 발생 이력, 변경 이력, 진행 이력으로 구분하여 각각에 따른 적절한 기능성 컬럼을 부여함으로써 효율적인 데이터베이스 성능을 나타내게 할 수 있음을 기억해야 한다.