데이터 모델링 과정에서 자주 나타나는 여러 각종 코드성 키 엔터티(Key Entitys)에 대한 실전적인 물리적 DB Design 해법.
SCOPE & APPLICATION
효과적인 물리적 DB Design 방안
KEY IDEA
(KEY WORD : KEY ENTITY, SUPER TYPE, CODE, CODE MASTER, 주코드, 부코드, 코드, 코드테이블 )
SUPPOSITION
DESCRIPTION
데이터 모델링 단계에서 가장 많이 나타나는 엔터티 중의 대표적인 키엔터티(Key Entity)들이 우리가 흔히 예기하는 코드마스터 성격이다. 예로, 학력, 직급/직책, 성별, 연령대, 상품구분, 업무구분 등 수 없이 많은 종류의 코드성 엔터티를 우리는 흔히 접할 수 있다.
여기서 많은 설계자들이 고민하는 부분 중에 하나는 이러한 코드와 명칭과 같은 단순 속성만을 필요로 하는 여러 가지 엔터티에 대하여 물리적인 DB Design기법을 어떻게 해야 하는지에 대한 고민을 하는 경우가 다반사이다. 여러 고객사의 물리적 DB Design시에서 나타나는 공통적인 대표적인 질문 유형이며 이에 대한 명확한 판단기준이 있는데도 불구하고 아직도 미궁에서 난상토론을 하는 경우가 많다.
여기서 선택하는 두 가지의 기법 중 하나는 각각의 엔터티를 독립적인 테이블로 설계하는 방법을 택할 것인지 아니면, 또 하나의 다른 방법으로 비슷한 모양이므로 하나의 SUPER TYPE으로 통분하여 여러 가지의 SUB TYPE으로 표현하고 최종으로 하나의 테이블로서 표현하게 하는 방법이다.
학력과 직급 엔터티를 그림으로 표현하면 다음과 같은 두 가지의 경우로 대별할 수 있다.
데이터모델링 과정에서는 속성과 엔터티의 명확화를 위하여 (그림1)과같이 독립적인 엔터티로 표현하여 모델링을 수행하는 것이 모범 안이다.
그러나, 물리적인 DB Design시에는 (그림2)와 같은 통합 테이블구조로 설계하는 것이 모범 안이다. 이유는 물리적인 DBMS특성과 I/O엑세스 효율을 고려하여야 하기 때문이다.
본 사안의 정보에 대한 몇 가지 특징을 먼저 열거해 보자.
√이러한 각종 코드성 테이블의 자료들은 극소수의 Instance(Row)를 가지고 있다.
√대부분의 정보들은 해당 식별코드와 명칭,내용 정도의 단순한 정보를 표현하며 별도의 이력 관리나 정보의 변화가 거의 없는 코드성 정보이며
√이러한 형태의 정보들은 시스템별로 상당히 여러 가지로 존재하게 된다.
√심지어는 시스템 구축 이후에도 추가적인 시스템 규칙성 Business Rule을 뒷바침하게 되는 각종 코드(예 : 업무처리구분, 주문처리상태 등)가 지속적으로 발생될 수 있다.
이제 RDBMS의 몇 가지 주요한 특징을 나열해보자.
√DBMS의 I/O단위는 DB Block이며 DBMS는 물리적인 I/O 엑세스 효율을 높이기 위하여 연속적인 물리적인 저장공간(DB Block)을 할당받아 Extent로 사용하게 된다.
√인덱스 또는 테이블과 같은 하나의 물리적인 DB Object는 최소 하나의 EXTENT가 할당되어야 한다.
√하나의 물리적인 DB EXTENT는 2개 이상의 DB Block이 할당되어야 한다. 일반적으로 하나의 DB Block은 OS Block Size로 지정되어 사용하는 것이 보편적이라고 예상한다면 하나의 DB Extent에는 최소 4K정도의 공간을 할당받게 되며, 이는 인덱스에서도 동일하게 적용된다.
√이러한 가정을 하게 된다면 하나의 테이블이 최소 하나의 Primary Key Index를 가지고 있는다고 하여도 최소 8K라는 공간을 차지하게 될 것이다.
이러한 상기 특징을 고려하여 다음과 같은 가정을 통하여 통합 설계의 당위성을 설명한다.
√학력 정보에는 무학, 초등학교졸,…,박사 까지 대략 7,8종류의 학력정보가 필요하다고 가정하고 관리하고자 하는 정보의 Size는 코드 2자리(Bytes) + 명칭 10자리(Bytes) 총 12 Bytes를 필요로 하며 학력정보 모두를 관리하기 위하여 약 12*8 = 96 Bytes 가 필요하게 된다.
√그럼, 이렇게 96 Bytes 정도의 데이터 관리를 위하여 별도의 독립적인 테이블로 설계한다면 테이블 공간 최소 확보 사이즈인 4K를 사용하게 되며 실제 98%에 해당하는 3.91 Kbytes 를 빈 공간으로 낭비하게 될 것이다. 여기에 인덱스 Object에 대한 고려까지 한다면 너무도 억울한 경우라 할 수 있다.
√DBMS 활용시 성능상 직결되는 문제는 바로 I/O 성능이다. CPU의 성능이나 Memory의 성능보다 현실적으로 가장 성능에 영향을 미치는 부분은 바로 물리적인 Block I/O의 효율이다.
√원하는 정보 추출을 위하여 얼마나 많은 물리적인 Block을 I/O하였느냐가 바로 성능에 미치는 직접적인 요소인 것이다.
√만일 학력,직급 등 5,6가지의 여러가지 코드성 테이블을 하나의 물리적인 테이블로 통합할 경우에는 아마 모든 코드정보들을 단 하나의 DB Extent에 모두 저장할 수 있을 것이며, 역으로 추론한다면 단 1,2개의 DB Block 한번으로 5,6가지의 여러 코드성 테이블의 정보는 이미 Memory에 상주하는 DB Buffer Cache에 올라와 있어 대부분 Memory I/O로 모든 요구를 충족할 수 있을것이다. 이러한 원리로 Index Object에 대한 물리적인 I/O 효율까지 고려한다면 이는 엄청난 효율의 차이를 얻을 수 있다.
그렇다면 이러한 통합 코드 테이블 DB Design 활용 방안에 대하여 요약해 보자.
√앞서 표현한 (그림2)의 통합 코드 정보에 대한 SUPER TYPE 엔터티라고 생각한다면 물리적인 DB Design시에는 SUBTYPE 구분이 UID(Unique Identifier : Primary Key)자격으로 등장한다.
√고로, 통합 코드 정보의 UID(PK)는 코드분류+상세코드로 설정되어지게 되며, 예로 통합코드 테이블에서 관리되고 있는 전체 코드 종류 정보를 파악하기 위한 코드분류를 ‘00’으로 설정하여 지정하고 상세코드에 각각의 코드종류를 표현하는 값을 부여하여 코드정보를 가변적으로 추가가 자연스럽게 가능하도록 적용할 수 있다.
√각각의 코드들은 해당 코드분류에 지정한 값을 지정하고 각 코드정보로 관리하고자 하는 정보들을 상세코드와 명칭으로 표현하여 하나의 테이블에 여러가지의 코드정보를 관리하도록 한다.
√아래 몇 가지의 실 사례 Instance를 참고로 나열한다.
이제 이러한 물리적인 DB Table Design 환경에서 실제 활용 SQL을 생각해 보자.
고객의 정보를 추출하면서 학력정보를 추출하기 위한 SQL Sample은 다음과 같은 형태로 활용될 것이다.
SELECT A.cust_id, A.cust_name, A.jumin_no, B.code_name
FROM customer A, code_master B
WHERE A.last_educ = B.code
AND B.code_type = ‘01’ /* 학력코드구분조건 */
AND A.cust_name like ‘조광%’;
이러한 SQL 활용 방안은 결코 바람직하지 않은 방법이며, 코드 종류별로 모두 지정 상수 조건을 사용한다는 것은 응용프로그램의 유연성에도 문제가 있게 마련이다. 이렇게 프로그램 코딩시에 한줄의 코딩의 수고를 모두 한다는 것은 더더욱 못할 일이다.
이럴때 바로 VIEW를 활용하게 되면 내우 유용하게 활용할 수 있으며 옵티마이져가 판단하기에는 동일한 효과를 얻을 수 있으면서 관리측면에서도 효율적으로 융통성 있게 활용할 수 있다. 다음의 VIEW생성을 통하여 SQL활용시에는 내부적으로 상기 SQL과 동일한 효과를 얻을 수 있다.
- VIEW생성 SCRIPT
CREATE OR REPLACE VIEW CODE_EDUC
AS
SELECT * FROM CODE_MASTER WHERE CODE_TYPE = ‘01’;
- VIEW를 활용한 SQL
SELECT A.cust_id, A.cust_name, A.jumin_no, B.code_name
FROM customer A, code_educ B
WHERE A.last_educ = B.code
AND A.cust_name like ‘조광%’;
상기 SQL은 앞서 기술한 SQL과 내부적인 처리는 동일한 SQL 역할을 하게 되며 마치 별도의 독립적인 테이블 구조를 갖는 것과 동일한 SQL활용이 가능하게 된다.
이러한 통합 테이블 구조로 설계할 경우에는 특히, 업무상 지속적으로 추가 발생이 빈번한 여러가지 단순 코드성 정보 추가가 통합코드 테이블의 단순한 Value의 추가 및 VIEW의 정의로 해결이 가능하므로 상당한 장점을 얻을 수 있을 것이다.
그러나, 이러한 통합 코드 설계를 한답시고 각종 모든 테이블을 통합해서는 곤란하다. 즉, 정보의 용도나 관리목적으로 이력이 필요하거나 여타 별도의 목적에 의하여 각종 추가 속성이 필요한 경우나 해당 코드 관리 정보의 양이 상당부분의 자료량으로 관리되어야 하는 경우는 별도의 테이블로 정석 설계를 하여야 한다.
출처 : http://www.en-core.com
