7.1 관계형 테이블로 전환

1) 논리ERD와 테이블관계도는 1:1이 아니다.

• 하나의 엔티티타입이 두개 이상의 테이블로 전환(세분화)되기도 하고 두개 이상의 엔티티가 하나의 테이블로 전환(통합화)되기도 한다. 또한 반정규화를 통해 엔티티타입에는 없던 속성이 테이블에서 칼럼으로 추가되기도 한다.

2) 테이블관계도 변환항목

• 엔티티타입은 테이블로 전환한다.
• 주식별자는 PK로 변환한다.
• 속성은 칼럼으로 변환한다.
• 관계에 의한 외부식별자는 FK로 변환한다.

7.1.1 엔티티타입을 테이블로 전환한다.

독립 엔티티타입은 독립 테이블로 전환된다.

• 1)독립 엔티티타입이란 다른 엔티티타입과의 관계에 의해 생성되지 않고 독립적으로 생성되며 주로 업무에 원래부터 존재하는 정보이다
• EX)부서,사원,고객
• 2)엔티티타입 분류에 따르면 기본 엔티티타입이 이에 속한다.
• 3)엔티티타입의 주식별자가 PK가 되며 속성은 칼럼이 된다. PK와 칼럼은 다른 테이블과의 관계에 의해 생성된 것이 아니라 스스로 가지고 있는 정보이다
• 4)252페이지 <그림7-2> 참조

완전 종속 엔티티타입은 완전 종속 테이블로 전환한다.

• 1)완전종속 엔티티타입의 주식별자는 부모 엔티티타입으로 부터 상속받아 발생한다.
• 2)<사원>과 <발령> 엔티티타입을 볼 때 <발령>은 어떤 사원이 발령을 받을 때마다 어커런스가 생성되므로 결국 사원번호+발령일자를 식별자로 가지며 여기서 사원번호는 부모인 <사원>으로 부터 상속받은 것이다.
• 3)부모의 식별자가 자식의 식별자의 일부가 되므로 식별자관계가 된다.
• 4)253페이지 <그림7-3> 참조

부분 종속 엔티티타입은 부분 종속 테이블로 전환한다.

• 1)부분 종속 엔티티타입의 주식별자는 스스로 생성되지만 자신의 일반 속성중 일부는 부모엔티티타입에서 상속받아 발생된다.
• 2)<부서>와 <사원> 엔티티타입을 볼 때 <사원>에 소속부서라는 속성이 있을 경우 이 소속부서는 <부서>엔티티타입에서 상속받은 것이다.
• 3)부모의 식별자가 자식의 일반속성이 되므로 비식별자관계가 된다.
• 4)254페이지 <그림7-4> 참조

7.1.2 주식별자를 PK로 전환한다.

주식별자가 전환된 PK의 역할은 다음과 같다.

• 1) 무결성 제약유지:<사원>테이블에 <부서>테이블의 PK인 부서코드가 칼럼으로 있다면 <부서>테이블에 없는 부서코드를 가진 사원의 존재할 수 없게된다
• 2) PK는 테이블에 있는 각 로우를 유일하게 식별한다.
• 3) PK는 NULL값을 가질 수 없다.(PK가 NULL일 경우 2번의 역할을 하지 못하게 될 것이다)
• 4) PK는 기본적으로 UPDATE를 허용하지 않는다. UPDATE보다는 DELETE/INSERT 과정을 거치는 것이 좋다.
• 5) 가능하면 모든 테이블에는 PK를 정의하는 것이 좋다.

7.1.3 관계에 의한 외부 식별자는 FK로 변환된다.

1:1 주식별자 관계변환

• 1)주는 쪽의 PK가 받는 쪽의 FK가 되면서 또한 PK가 되는 경우이다
• 2)하나의 청구에 대해 하나의 출금이 발생한다면 청구의 PK인 청구번호가 출금의 PK인 출금번호와 동일하게 된다
• 3)출금을 하기 위해서는 반드시 청구쪽에 해당 데이타가 있어야 하며 청구쪽 데이타를 삭제하려는 경우 출금에 데이타가 있는지 확인하여 있다면 출금 데이타를 삭제하고 난 후에야 청구데이타를 삭제할 수 있다.
• 4)257페이지 그림7-7 참조

1:1 비식별자 관계변환

• 1)주는 쪽의 PK가 받는 쪽의 FK지만 PK는 되지 못하고 일반칼럼이 되는 경우이다.
• 2)위에서 든 예에서 만일 출금이 청구요청이 없어도 이루어지는 경우라면 <청구>,<출금> 양쪽 모두가 선택관계가 되어 비식별자관계가 된다.
• 3)그러나 삭제시
  • 청구데이타를 삭제시 출금에 데이타가 있다면 출금쪽도 지워야 한다.
  • 출금데이타 삭제시 청구에 데이타가 있다면 청구쪽도 지워야 한다.
• 4)258페이지 그림7-8참조

1:M 관계변환

• 1)1:M의 경우도 주식별자,비식별자 관계가 존재한다.
• 2)주식별자관계일 경우 주는 쪽의 PK가 받는 쪽의 PK의 일부를 구성하게 된다. 즉 받는 쪽에선 받은 칼럼외에 또 다른 칼럼이 있어야 PK를 구성할 수 있게 된다.
• 3)258페이지 그림7-9참조

자기참조 관계변환

• 1)하나의 (상위)메뉴에는 하위메뉴가 없을 수도,1개일 수도,여러개일 수도 있다(해당 메뉴가 최하위메뉴라면 하위메뉴가 없을 것이다)
• 2)하나의 (하위)메뉴에는 상위메뉴가 없을 수도 있고 만일 있다면 반드시 1개가 있다(해당 메뉴가 최상위메뉴라면 상위메뉴가 없을 것이다)
• (?) 잠깐질문-만일 FK로 상위메뉴 대신 하위메뉴가 칼럼으로 있다면 어떻게 될까?
• 하위메뉴가 FK로 잡혀 있다면 PK인 메뉴ID는 상위메뉴라는 의미다. 하나의 상위메뉴에는 복수개의 하위메뉴가 존재할 수 있으므로 결국 같은 PK가 여러개 존재하는 상황이 발생하게 된다. 이것은 1:다의 관계에 있는 별개의 엔티티타입을 하나의 엔티티타입으로 묶어서 발생한 문제로 제 1 정규화의 대상이 된다

수퍼타입-서브타입 관계변환

1)각각의 테이블로 변환

• 수퍼타입과 서브타입간에는 1:1 주식별자관계로 정립된다
• 수퍼타입의 접수종류코드에 의해 수퍼타입의 로우가 어느 서브타입과 연결되는지가 결정된다
• 수퍼타입에 있는 하나의 로우의 상대 어커런스는 <인터넷접수>,<방문접수>,<전화접수> 셋중에 하나의 테이블에만 존재하게 되므로 서브타입쪽에 O가 나타난다

장점

• 칼럼의 중복이 없으므로 저장공간이 절약된다
• 관계에 의해 발생하는 복잡한 참조무결성 규칙을 적용할 수 있다
• 각각의 테이블로 처리할 경우 수행속도가 좋으며 수퍼타입에 속한 정보만 조회하는 경우 문장작성이 용이하다

단점

• 수퍼타입과 서브타입의 정보를 같이 처리할 경우 항상 조인이 발생하여 성능이 저하될 수 있다
• 전체 테이블에 대한 통합조회가 많이 필요한 경우 과다한 조인으로 쿼리문이 복잡해진다
• EX)특정 접수일자에 발생한 접수를 찾는 경우 접수종류가 어느 타입에 속할지 알 수 없으므로 세개의 서브타입을 뷰로 만들어 수퍼타입과 조인해야한다

2)서브타입 테이블로 변환

• 서브타입 칼럼에 수퍼타입에 있는 모든 속성을 포함하도록 구성한 경우이다
• 각각의 서브타입간에는 관계가 존재하지 않으며 로우가 서브타입별로 존재하므로 구분칼럼이 필요없다

장점

• 수퍼타입의 칼럼을 상속한 서브타입을 각각의 테이블로 변환시킨다. 따라서 구분자 칼럼이 필요없게 된다
• 정보를 서브타입별로 조회하는 경우 칼럼을 명확하게 구분하여 가져올 수 있다
• 테이블 풀스캔의 경우 유리하다(?)
• 다른 테이블과 관계에 대해 복잡한 참조 무결성 관계를 유지할 수 있다(?)
• 칼럼에 대한 NOT NULL,기본값 지정이 가능하다(?)

단점

• 서브타입을 동시에 이용하는 경우 쿼리문 작성이 어렵고 조인발생으로 인해 성능이 저하될 수 있다
• 서브타입을 묶어서 하나의 데이터 범위에서 부분처리를 해야 하는 경우에는 처리가 복잡하거나 불가능할 수 있다
• 즉, 업무처리시 서브타입별로 구분하여 처리하는 경우는 편리하나 통합하여 처리하는 경우는 권장되지 않는다

3)통합 테이블로 변환

• 서브타입에 있는 모든 칼럼을 수퍼타입에 하나로 통합하여 테이블로 만든다.
• 수퍼타입에 모든 서브타입의 정보가 있으므로 서브타입을 구분하기 위한 칼럼이 필요하다(접수구분종류)

장점

• 데이타조회가 간편하며 쿼리문이 단순해진다
• 여러 테이블을 조인하지 않아도 되므로 수행속도가 좋아진다
• 서브타입을 구분하지 않고 데이타를 조회할 경우 처리가 용이한다

단점

• 칼럼수가 증가하여 디스크 저장공간이 증가될 수 있다(해당 서브타입에 없는 칼럼은 NULL이 되므로 VARCHAR 타입 사용시 예외)
• 항상 구분자에 의해 로우를 구분하는 작업이 필요하다
• 테이블 인덱스를 사용할 경우 서브타입별 로우의 수보다 많은 인덱스 로우가 존재하여 인덱스가 커지고 효율이 떨어질 수 있다
• 풀스캔시 많은 로우수때문에 성능이 떨어진다
• 다른 테이블과 서브타입별로 참조 무결성 조약이 복잡한 경우 데이타를 처리하기가 복잡하다

4) 결론

• 위에서 제시한 3가지 방법중 어느 것을 사용할 것인가는 업무에서 요구하는 데이타의 특징,즉 트랜잭션의 성격과 양에 따라 결정해야 한다
• 데이타 처리시 수퍼타입과 서브타입 전체에 대해 처리하는 경우가 많은지(3번), 개별로 처리하는 경우가 많은지(1,2번)에 따라 적절한 방식을 선택하도록 한다

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.고객번호

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

7.3 무결성 제약 정의

7.3.1 입력참조 무결성

• 1.각 테이블에 레코드가 입력될 때 자신이 참조하고 있는 테이블의 PK에 대해 데이타의 정합성을 일치시켜 주는기능이다.

• 2.입력참조 무결성을 유지하기 위한 6가지 기능
  • 1)의존:테이블에 데이타가 입력될 때 참조하고 있는 테이블의 PK가 존재해야만 데이타 입력이 가능하다.
  • 2)자동:테이블에 데이타가 입력될 때 참조하고 있는 테이블의 PK가 존재하지 않으면 참조테이블에 PK를 생성하고 데이타를 입력한다.
  • 3)기본:테이블에 데이타가 입력될 때 참조하고 있는 테이블의 PK를 기본값으로 바꾼 후 입력한다 (?)무슨 의미인가?
  • 4)지정:사용자가 지정해 놓은 일정한 조건을 만족한 이후에 테이블에 레코드를 입력한다.
  • 5)NULL:테이블에 데이타가 입력될 때 참조하고 있는 테이블의 PK가 없다면 NULL값으로 입력한다.
  • 6)미지정:테이블에 데이타가 입력될 때 참조하고 있는 테이블의 PK가 없다면 NULL값으로 입력한다. (?) NULL과의 차이점은 무엇인가?

7.3.2 수정참조 무결성

• 1.한 테이블의 PK가 수정되면 이 테이블을 참조하고 있는 모든 테이블의 FK도 수정하여 데이타의 정합성을 유지하는 기능이다.

• 2.수정참조 무결성을 유지하기 위한 2가지 기능
  • 1)제한:테이블의 PK를 수정하면 자신을 참조하는 테이블의 FK가 없어야 한다. 테이블의 FK가 존재하면 자신의 테이블의 PK가 수정되지 않는다.
  • 2)연쇄:테이블의 PK를 수정해아 한다면 참조되는 모든 테이블의 FK를 수정한 후 자신의 PK를 수정한다.

7.3.3 삭제참조 무결성

• 1.어떤 테이블의 PK가 삭제될 때 참조하는 모든 테이블의 FK를 기본값이나 NULL로 수정하여 데이타의 정합성을 유지하는 기능이다.

• 2.삭제참조 무결성을 유지하기 위한 6가지 기능
  • 1)제한:자신의 테이블의 레코드를 삭제하려면 자신을 참조하는 테이블의 레코드가 없어야 한다. 만약 이러한 레코드가 있다면 삭제해서는 안된다.
  • 2)연쇄:자신의 테이블의 레코드를 삭제하려면 참조하는 모든 테이블의 레코드를 삭제하고 난 후 자신의 레코드를 삭제한다.
  • 3)기본:참조하는 모든 테이블의 레코드를 기본값으로 변경한 후 자신의 레코드를 삭제한다. 주로 비식별자 관계에서 사용한다.
  • 4)지정:사용자가 정의해 놓은 일정한 조건을 만족한 이후에 자신의 레코드를 삭제한다.
  • 5)NULL:참조하는 모든 테이블의 레코드를 NULL로 바꾼 후 자신의 레코드를 삭제한다. 비식별자 관계에서 사용한다.
  • 6)미지정:자신의 테이블의 레코드를 삭제해도 특별한 참조무결성 규칙을 적용하지 않는다. 조건없이 삭제가 가능하다.
  • (?) 조건없이 삭제가 가능한 것이 어떻게 참조무결성을 유지하는 방법이 될 수가 있는가?

7.3.4 참조무결성 적용시 주의사항

• 1)한 테이블의 PK가 수정,삭제가 된다면 참조하고 있는 모든 테이블를 무결성 원칙에 따라 점검한다.(PK테이블의 입력는 자유롭다)
• 2)FK가 있는 테이블에 입력,수정이 일어나면 PK가 있는 테이블에 레코드가 존재하는지 검사한다.(자식테이블에서의 삭제는 자유롭다)
• 3)이러한 이유로 성능이 저하되는 문제가 발생할 수도 있다.

7.3.5 FK 제약이 걸려 있는 칼럼들의 인덱스 생성

• 1)FK제약이 걸려있는 칼럼의 경우 인덱스를 생성하는게 좋다. 비록 업무상 그 인덱스를 사용하지 않을지라도 DBMS내부적으로 사용하기 때문이다.
• 2)<사원>과 <발령>테이블이 사원번호로 연결되어 있는경우 <발령> 테이블의 사원번호에 인덱스를 생성하지 않으면 특정 사원정보가 삭제될 때 DBMS내부적으로는 <발령> 테이블을 풀스캔하여 삭제한 데이타를 찾기때문에 성능에 문제가 될 수 있다.
• 3)물론 FK의 인덱스도 평균분포도가 10~15%인 컬럼에 대해 지정하는 규칙은 적용된다.

7.4 트랜잭션 분석

7.4.1 트랜잭션 정의

• 트랜잭션이란 데이타베이스에서 행해지는 작업의 논리적인 단위로써 테이블에 발생하는 업무 트랜잭션 양에 따라 데이타베이스의 용량을 산정하여 DB의 구조를 최적화하는데 트랜잭션 분석의 목적이 있다.

7.4.2 트랜잭션 분석

• 1.프로세스 모델링을 이용하여 단위 프로세스를 도출한다.

• 2.상관모델링을 이용하여 단위프로세스와 엔티티타입간의 관계인 CRUD MATRIX를 작성한다

• 3.트랜잭션 분석도를 만들고 테이블별 총 트랜잭션 수를 파악한다.

• 트랜잭션당:트랜잭션당 테이블을 읽는 횟수를 말한다. 제품과 주문목록이 5인 것은 하나의 주문에 제품 5개를 주문했다고 가정한것이다.
• 트랜잭션수:주기 일당 총 일어나는 트랜잭션 수이다. 고객,주문이 200이므로 5번을 읽는 제품,주문목록은 1000이 된다.
• 트랜잭션수의 총합인 2400이 하루동안 일어나는 총 트랜잭션 수가 된다.

7.4.3 트랜잭션 분석도 이용

1.용량산정의 근거자료가 된다.

• 각 테이블의 생성 트랜잭션을 분석해 나가면 테이블에 저장되는 데이타 양을 유추할 수 있고 이를 근거로 DB용량을 산정할 수 있다
• EX)사원이 10000명이고 사원 한명당 발령은 3번,급여는 상여포함 20번이 발생한다면 발령 테이블에는 30000건,급여테이블에는 600000건의 데이타가 발생할 것임을 유추할 수 있다.

2.디스크 구성의 이용

• 트랜잭션 분석도를 이용하여 프로세스가 과도하게 발생하는 테이블에 대해서는 디스크를 여러군데 배치하여 디스크 입출력을 분산시켜 성능에 많은 향상을 기할 수 있다.

3.데이타베이스와 연결되는 채널의 분산

• 각 채널별로 집중화된 트랜잭션을 분산시킴으로써 대기현상이나 TIME-OUT현상을 방지할 수 있다.

7.5 뷰 설계

7.5.1 뷰의 특징

1.테이블의 구조를 단순화한다.

• <주문>과 주문의 상세내용을 담고 있는 <주문목록> 테이블이 있고 이들이 항상 조인되어 사용된다면 뷰를 만들어 사용함으로써 쿼리문을 간소화시킬 수 있다.

• (?) 그렇다면 애초 테이블 설계시 하나의 테이블로 만들면 될 것을 분리해 놓고 막상 개발시는 두 테이블을 하나로 합친 뷰를 만들어 사용하는가?
• ->테이블을 하나로 합쳐 놓은 다면 주문번호,신청자명처럼 1쪽의 데이타는 중복이 일어나 디스크용량을 차지하게 된다.이 또한 정규화의 대상이 될 것이다. 그러나 뷰로 만든다면 효과는 하나의 테이블과 똑같은 효과를 내면서 sql문만 저장되기에 디스크 용량을 차지하는 일은 없을 것이다.

2.다양한 관점에서 데이타를 제시한다.

• 많은 부서에서 수 많은 직원이 사용하는 테이블이 있지만 그 테이블에 있는 모든 칼럼을 보여줄 필요없이 각 사용자에게 필요한 칼럼만을 뷰로 만들어 제공할 수 있다.

3.데이타 보안유지

• 사원테이블에는 학력등 인사담당자 외에는 제공되어서는 안되는 민감한 정보들이 있을 수 있다. 이때 테이블로만 제공한다면 어쩔 수 없이 모든 이에게 민감한 정보가 공개될 수밖에 없지만 뷰로 각 유저에게 필요한 만큼의 정보만을 제공할 수 있다.

4.논리적인 데이타의 독립성을 제공한다.

• 뷰의 근거가 되는 테이블의 구조가 변경되어도 뷰는 수정할 필요가 없다. 사원테이블에 취미라는 칼럼이 추가되었다면 취미칼럼을 필요로 하는 뷰를 제외한 나머지 뷰의 쿼리문은 수정할 필요는 없다.
• (?)사원테이블에 취미라는 칼럼이 있었는데 어느 날 없어진다면 당근 취미를 조회하는 뷰의 쿼리문은 변경되어야 할 것이다.

5.데이타의 select기능은 제한이 없으나 입력,수정,삭제는 일부 제약이 있을 수 있다.

6.뷰에는 인덱스,클러스터링을 지정할 수 없다.

7.5.2 뷰의 정의

1.뷰의 대상이 되는 테이블

• 1)외부 시스템과 인터페이스에 관여하는 테이블
• 2)크러드메트릭스에서 자주 조인되는 테이블들
• 3)많은 쿼리문에서 인라인 뷰 방식으로 접근되는 테이블은 인라인 뷰 쿼리로 뷰를 만든다.

2.뷰의 대상이 되는 칼럼선정

• 사용하는 프로세스,유저등급등을 고려하여 원하는 대로 칼럼을 지정하여 뷰를 만들 수 있다.

3.뷰 정의서 작성

• 1)뷰명:뷰 이름 기록
• 2)설명:뷰의 기능 기록
• 3)관련테이블:뷰가 생성되기 위한 원래의 테이블명
• 4)칼럼명:뷰 내에 포함될 칼럼 이름
• 5)데이타 타입:칼럼의 데이타 타입

7.6 인덱스 설계

7.6.1 인덱스 대상 선정

대상 테이블 선정

• 1.인덱스의 필요 판단기준은 DBMS가 테이블을 읽어 들일 때 수행하는 MULTI BLOCK READ수에 의해 판단한다.
• 1)MULTI BLOCK READ 란 테이블 엑세스시 한꺼번에 메모리에 읽어 들이는 블럭의 수이다.
• 2)MULTI BLOCK READ 가 16일 경우 테이블 크기가 16블럭 이상이면 인덱스를 설정하도록 한다.
• ->오라클의 경우 1블럭이 8K인 경우 8블럭을,4K인 경우 16블럭을 지정할 것을 권유하고 있슴
• ->8*8=64,4*16=64이므로 결국 64K 이내의 테이블은 인덱스를 설정하지 않고 테이블 풀스캔을 해도 별 지장이 없다는 결론
• ->그러나 다른 테이블에 의해 참조되는 관계이거나 조인에 의해 처리되는 경우 PK,FK에는 인덱스를 생성해 주는 것이 좋다.

PK칼럼 인덱스

• 1.아무리 작은 테이블일지라도 PK칼럼은 반드시 인덱스를 사용해야 한다.
• 2.<부서>,<사원> 테이블이 있고 <사원>테이블에 소속부서 칼럼이 있는데 만일 <부서> 테이블의 데이타가 적다고 하여 PK인덱스를 사용하지 않는다고 가정해 보자.

SELECT A.사원번호,A.사원명,B.부서명
FROM 사원 A,부서 B
WHERE A.부서코드=B.부서코드
AND A.사원번호 BETWEEN '1' AND '100'

• 1)사원번호가 PK로 경합성에서 우수하므로 <사원> 테이블이 드라이빙 테이블이 된다.
• 2)<사원>에서 하나의 로우가 추출된 후 부서명을 가져오기 위해 <부서> 테이블과 조인이 일어나는데 <부서> 테이블에는 PK인덱스가 없기에 결국 해당 사원이 속한 부서명을 찾기 위해 <부서> 테이블을 풀스캔해야 한다.
• 3)즉, <사원> 테이블에서 추출된 로우 수 만큼 <부서> 테이블에 풀스캔 엑세스가 일어나 성능에 심각한 문제점을 초래하게 된다. 그러나 만일 <부서>테이블의 부서코드에 PK인덱스가 걸려 있다면 <사원>에서 추출된 부서코드와 조인하여 단 한번의 테이블 엑세스로 부서명을 가져올 수 있게 될 것이다.

3.PK와 유니크인덱스

2)유니크 인덱스만을 사용할 경우

<장점>

• DBA가 데이타베이스를 관리하기가 쉽다. (?)
• 개발 시점에 데이타 제약이 없으므로 개발이 용이하다.
• PK,FK를 이용하지 않으므로 성능이 다소 좋아질 수 있다. (DBMS 내부적으로 데이타딕셔내리에 관리해야할 항목이 줄어든다 (?))

<단점>

• 데이타의 무결성이 깨질 수 있다.
• 데이타의 무결성이 깨진 경우 데이타 전환작업시 데이타 정리작업이 추가적으로 필요하다.
• 데이타 모델과 테이블의 관계가 일치하지 않다.
• 유니크 인덱스는 한개의 테이블이 여려개를 만들 수 있기에 PK가 무엇인지 구분할 수가 없다.

3)결론

• 데이타베이스 구축과 운영시 중요도는 데이타정합성>성능>관리의 용이성>개발편의성이다.
• 유니크 인덱스만을 사용할 경우 성능,관리면에서는 어느 정도 더 효율적일지 모르나 가장 중요한 데이타의 정합성이 깨질 우려가 있다는 점에서 반드시 PK를 사용하는 것이 좋다.

FK칼럼 인덱스

• 1.FK칼럼에 인덱스가 걸려 있지 않다면 FK제약때문에 테이블 풀 스캔이 일어난다. (?)
• 2.또한 인덱스가 없다면 데이타의 입력,수정,삭제가 발생할 때 테이블 블럭을 유발한다. (?)
• 3.그러므로 FK칼럼에 반드시 인덱스를 걸어 주도록 한다.

인덱스 대상 칼럼 선정

• 1.테이블 내에서 자주 사용되며 분포도가 10~15% 정도인 칼럼을 대상으로 한다.
  • 분포도(%)=데이타별 평균로우수/테이블의 총 로우수*100
• 2.여러개의 컬럼이 항상 같이 사용되는 경우도 인덱스를 걸어주는게 좋다.

7.6.2 인데스 최적화

인덱스 효율검토

• 1.인덱스 칼럼은 자주 수정이 발생되지 않는 칼럼을 선정하는 것이 좋다.
• 2.평균 분포도가 10~15% 이내일지라도 분포가 기형적이면 인덱스를 설정하지 않는게 좋다. 한 칼럼의 값이 A,B,C,D 네가지 종류인데 이들의 평균분포도가 비록 15이내일지라도 어떤것은 1,어떤것은 30 이럴경우 테이블 풀 스캔이 더 좋을 수도 있는데 불필요하게 인덱스를 거쳐 테이블에 엑세스하여 성능이 저하될 수 있다.
• 3.한 테이블에 인덱스가 5개가 넘어가는 경우 진정으로 필요한 인덱스인지를 검토후 삭제하여 그 수를 줄일 필요가 있다.

인덱스 데이타타입 적용

• 1.데이타 길이가 변하는 칼럼의 경우 VARCHAR타입을 사용한다.
• 2.날짜타입의 경우 시,분,초까지 저장할 필요가 없다면 DATE형보다 VARCHAR형이 더 좋다.
• 3.가급적 NUMBER형 보단 VARCHAR형의 인덱스가 더 효율적이다.

인덱스 정렬

• 1.인덱스를 사용하는 유형이 대부분 역정렬 형태라면 인덱스를 역정렬순서로 생성하여 INDEX_DESC 힌트를 추가적으로 사용하지 않도록 한다.
• 2.결합인덱스를 생성할시 첫번째 칼럼은 분포도가 좋고,항상 사용되고,=연산자로 비교되는(검색범위를 획기적으로 줄여줄 수 있다) 칼럼을 첫번째 칼럼으로 삼도록 한다.

클러스터링 검토

• 1.분포도가 넓은 경우,즉 동일한 값들이 많이 분포하는 경우는 인덱스를 사용하는 것이 적합지 않다. 이럴경우 클러스터링을 사용하도록한다.
• 2.클러스터링은 칼럼값이 동일한 로우들을 한곳에 모아서 저장함으로써 대량의 조회시 획기적인 성능향상을 기할 수 있는 방법이다.
• 3.그러나 이 역시 조회속도는 향상시키지만 입력,수정,삭제시는 성능저하를 야기한다.

7.6.3 인덱스 정의서 작성

• 1.엔티티타입명:
• 2.인덱스 스페이스:인덱스가 물리적으로 생성할 테이블 스페이스 이름 (대용량의 DB라면 데이타가 저장되는 테이블 스페이스와 별로도 인덱스만의 테이블 스페이스를 지정하여 사용하기도 한다)
• 3.인덱스 유형:유니크,NON-유니크,클러스터
• 4.정렬:오름차순,내림차순
• 5.구분:PK인덱스,FK인덱스

7.7 데이타베이스 용량 설계

7.7.1 데이타베이스 용량 분석의 목적

• 1.정확한 데이타 용량을 산정하여 디스크 사용의 효율을 높인다.
• 2.업무량이 집중되어 있는 디스크를 분리,설계하여 디스크에 대한 입출력 부하를 분산시킬 수 있다.
• 3.디스크 입출력 경합을 최소화할 수 있다.
• 4.데이타베이스 오브젝트의 익스텐트(범위,Extent) 발생을 줄인다 - 데이타가 증가하면서 데이타베이스 내의 각종 오브젝트들도 추가적인 스페이스 확보 작업이 일어나는데 이를 최소하 해야 한다.

7.7.2 데이타베이스 용량 분석 절차

1.용량분석을 위한 기초 데이타를 수집한다

• 1)로우길이:해당 테이블의 칼럼길이를 모두 합하여 기록한다.
• 2)보존기간:테이블을 디스크에 어느 정도의 기간동안 보관할 것인지 기록
• 3)초기건수:기존 시스템에서 신시스템으로 마이그레이션 한 데이타 건수
• 4)발생건수:일정주기별 발생한 내용
• 5)발생주기:
• 6)년 증가율:

2.기초 데이타를 이용하여 DBMS에 이용하는 오브젝트별로 용량을 산정한다

• 1)오브젝트설계:저장공간을 주로 차지하는 오브젝트에는 테이블스페이스,테이블,인덱스등이다.
• 2)테이블스페이스 용량산정

• 분산위치:테이블이 분산되는 위치
• 테이블명:
• 테이블용량:
• 테이블스페이스명:
• 테이블스페이스 용량:테이블 스페이스내에 생성되는 테이블의 용량을 더한 값의 약 40%를 더한 값을 기입한다. 40%는 절대적인 값은 아니며 경험과 해당 업무의 확장성을 고려하여 적절히 더한다.

3)데이타파일 용량 산정

• 디스크:데이타 파일이 물리적으로 생성될 디스크의 이름
• 데이타파일 디렉토리:
• 데이타파일명:
• 데이타파일 크기:저장될 테이블 스페이스 용량을 합하여 지정한다.
• 테이블 스페이스:
• 테이블 스페이스 용량:
• 비고:테이블 스페이스가 여러 개의 데이타 파일을 이용할 경우 참조자료로 기입한다.

(?) 디스크의 종류에 따라 데이타파일 크기를 지정해야 한다? ->로우디스크,파일시스템 (페이지 315)

4)디스크 용량 산정

• 디스크:데이타파일이 물리적으로 생성될 디스크 이름
• 테이타파일 디렉토리:
• 디스크용량:하나의 디스크가 가진 원초적인 용량을 기록한다.
• 사용된 디스크 용량:디스크에 데이타파일이 생성된 총 용량을 기록한다.
• 디스크 사용비율 : (디스크용량/사용된 디스크 용량)*100 디스크 구성방법에 따라 사용비율을 조절해야 한다.
• 데이타파일명:
• 데이타파일크기:저장될 테이블 스페이스 용량을 합하여 지정한다.
• 데이타파일용량

7.8 접근방법 설계

7.8.1 스캔방식

• 1.테이블에 존재하는 데이타의 20%이상을 검색해야 하는 경우 테이블 스캔방식으로 데이타를 가져오는 것이 인덱스를 경유하는 것보다 더 효율적이다.
• 2.테이블 전체를 스캔하는 것을 풀스캔이라고 하고 정렬된 테이블에서 특정 부분만을 스캔하는 것을 범위스캔이라고 한다.
• 3.테이블 스캔의 경우 대부분 풀스캔이 발생하지만 DBMS의 종류에 따라 범위스캔을 지원하기도 한다. (?)

7.8.2 B트리 인덱스

B트리 인덱스의 기본구조

1.비트리 인덱스에는 루트,브랜치,리프블록이 존재한다.

1)리프블록

• 테이블의 인덱스에 대한 실질적인 정보가 담겨있는 블록이다.
• 테이블에서 입력,수정,삭제,조회가 발생하면 관련 인덱스가 리프블록내에서 입력,수정,삭제,조회를 발생시킨다.
• 블럭헤더:인덱스 칼럼의 구간값과 인덱스바의 물리적 위치 정보를 가지고 있다.
  -> (?) 320페이지 그림 7-58에 블럭헤더가 표시되어 있는가?
  -> (?) 칼럼의 구간값이란? : 만일 위 그림이 <부서> 테이블의 부서코드에 대한 인덱스이고 맨 왼쪽의 리프블럭에 10,20,30 부서코드 인덱스가 저장되어 있다면 10~30 값이 저장되어 있다는 의미?
  -> (?) 인덱스바의 물리적 위치정보:위 그림처럼 만일 맨 왼쪽블록에 3개의 인덱스바가 저장되어 있다면 블록헤더에 3개 각각에 대한 물리적(하드디스크) 위치 정보가 있다
• 인덱스바는 인덱스헤더,로우칼럼값,ROWID로 구성되어 있다.
  ->인덱스헤더:각 인덱스를 구분하는 값
  ->로우의 칼럼값:인덱스 대상이 되는 칼럼 값
  ->ROWID: 레코드의 물리적 저장위치

2)브랜치블록

• 리프블록과 루트블록의 중간에서 블록사이의 정보에 대한 다리 역할을 하는 블록이다.
• 블록헤더 : 인덱스 칼럼의 구간값과 인덱스바의 물리적 위치정보가 있다
  -> (?) 인덱스칼럼의 구간값 : 맨 왼쪽의 브랜치블록에는 맨 왼쪽 3개의 리프블록이 연결되어 있는 바, 10-90의 값이 저장됨
• 인덱스바 : 인덱스헤더,브랜치블록ID,브랜치블록 키값으로 구성되어 있다.
  ->인덱스 헤더:각 인덱스를 구분하는 값이다.
  ->브랜치블록ID:자신에게 연결된 하위 블록의 물리적 위치가 저장되어 있다.
  ->브랜치블록 키값:자신에게 연결된 하위 블록의 인덱스 칼럼의 구간값이 있다.

3)루트블록

• 트리의 최상위에 위치하며 조회,입력,수정,삭제시 제일 먼저 접근된다.
• 인덱스바는 자신에게 연결된 하위 블럭의 수를 의미한다 (3개면 3개 블록 연결,2개면 2개 블록 연결)
• 하나의 인덱스바는 인덱스헤더,브랜치블록ID,브랜치블록 키값으로 구성되어 있다.
  ->인덱스헤더:하나의 블록에는 보통 수백개의 인덱스바가 존재하는 바, 각 인덱스바를 구분하는 헤더값이 들어있다.
  ->브랜치블록ID:자신에게 연결된 하위블록의 물리적 위치가 담겨있다.
  ->브랜치블록 키값:자신에게 연결된 하위블록의 인덱스칼럼의 구간값이 들어있다.

B트리 인덱스의 검색원리

1.위 그림이 <부서> 테이블의 부서코드에 대한 인덱스라고 가정하고 아래 쿼리문이 실행되는 절차를 살펴보자.

SELECT 부서코드,부서명
FROM 부서
WHERE 부서코드='200'

• 먼저 루트블록에서 부서코드 200이 속한 인덱스바가 무엇인지 찾는다. 세번째 인덱스바는 190~240의 값을 가지고 있기에 바로 이것임을 알 수 있으며 인덱바의 브랜치블록ID에 하위블록의 물리적 위치가 있으므로 이 위치를 근거로 브랜치 블록을 탐색한다.
• 하위 브랜치블록에는 두개의 인덱스바가 있는데 부서코드 200은 첫번째 인덱스바에 속함을 알 수 있다. 역시 이 인덱스바의 브랜치블록ID를 통해 리프블록의 물리적 위치를 알아내어 이를 근거로 리프블록을 탐색한다.
• 오른쪽에서 두번째 리프블록이 해당 리프블록인데 여기에 있는 세개의 인덱스바중 2번째가 바로 찾고자하는 인덱스이며 따라서 이 인덱스바에 있는 ROWID로 테이블에 엑세스하여 최종 탐색을 끝마친다.

B트리 인덱스의 입력,수정,삭제

1.입력 (각 블록은 최대 3개의 인덱스바를 갖는다고 가정한다)

• 1)위 그림과 같은 인덱스 구조에서 부서코드 235 추가된다고 가정하자.
• 2)새로 추가되는 인덱스는 자신이 저장될 수 있는 구간값을 가지는 리프블록을 찾는다. 여기서는 맨 왼쪽의 리프블록이 해당된다.
• 3)그런데 해당 리프블록은 이미 3개의 인덱스바를 가지고 있어 더 이상 추가될 수 없으므로 이 사실을 상위 브랜치블록에 통보한다.
• 4)브랜치블록은 인덱스바가 2개이므로 인덱스바를 하나 더 추가하고 인덱스바가 추가된 만큼 리프블록을 하나 더 만든다.
• 5)그리고 추가된 값을 포함하여 해당 인덱스 구간값을 가지는 리프블록의 인덱스바 수를 반으로 나누고 그 반을 새로은 리프블록으로 이동시켜 입력작업을 완료한다.
• 6)예로 든 것은 간단히 브랜치블록에 인덱스바가 추가되고 새로운 리프블록이 생성되는 것으로 해결되었지만 때로는 루트블록이 분할되어 브랜치블록으로 변경되고 새로운 루트블록이 생성되기도 한다. 이런 경우 인덱스의 깊이는 한단계 더 깊어지게 될 것이다.
• 7)현실적으로 하나의 블록에는 수백개의 인덱스바가 저장된다. 만일 100개씩만 저장된다 하더라도 3단깊이이면 100*100*100=1,000,000으로 백만개의 인덱스 관리가 가능하다.
• 8)그러므로 인덱스의 깊이가 4레벨 이상이거나 혹은 잦은 입력,수정,삭제로 인해 이빨빠짐 현상이 발생하는 경우 인덱스 리빌드를 통해 인덱스 선능을 보존해야 한다.

7.8.3 비트맵 인덱스

비트맵 인덱스 구조

• 1.컴퓨터에서 사용하는 최소 단위인 비트를 이용하여 칼럼값을 저장하고 이용하며 ROWID를 자동으로 생성하는 인덱스 기법이다.
• 2.비트로 관리하므로 저장공간이 크게 감소하고 비트별로 각종 연산을 수행함으로써 기존 인덱스가 해결할 수 없었던 많은 문제를 해결하였다.
• 3.대용량처리,복잡한 AND,OR 연산에 강력한 힘을 발휘한다.
• 4.그러나 아직 여러가지 제한이 있어 모든 분야에는 적용되지 못하고 있으며 주로 데이타웨어하우스에서 활용되고 있다.
• 5.비트맵도 비트리인덱스처럼 루트,브랜치,리프블록으로 구성되지만 리프블록에 칼럼값과 ROWID 대신 0과 1로 구성된 비트맵이 저장된다는 점이 다르다.
• 6.NULL값도 인덱스로 저장할 수 있다.

특정 테이블의 성별 컬럼에 비트맵 인덱스를 생성했다고 가정하자. 
성별 컬럼에는 '남성' 아니면 '여성'이라는 값이 저장된다는 것은 누구나 아는 사실이다. 
예를 들어, 테이블에 4개의 데이터가 저장되어 있으며 저장된 데이터의 순서는'여성', '남성', '남성', '여성'이다. 
성별 컬럼에 비트맵 인덱스를 생성하게 되면 비트맵 인덱스의 리프 블록에는 두개의 비트맵이 생성된다. 
생성되는 두개의 비트맵이 바로 '남성' 비트맵과 '여성' 비트맵이다. 
테이블에 데이터가 '여성', '남성', '남성', '여성' 순으로 저장되어 있기 때문에 
'남성' 비트맵에는 '0110'이라는 비트맵으로, 
'여성' 비트맵은 반대의 '1001'의 비트맵을 구성하게 된다. 
그럼 '남성' 비트맵에서 '0110'은 무엇을 의미할까? 
저장된 데이터의 순서에 의해 '1'이면 '남성'을, '0'이면 '남성'이 아님을 말한다. 
따라서, 비트맵 인덱스의 남성 비트맵이 '0110'이면 해당 테이블의 성별 컬럼의 두 번째 데이터와 세 번째 데이터는 
'남성'이라는 값이 저장된다. 
또한, 성별 컬럼의 첫 번째 데이터와 네 번째 데이터는 '남성'이 아닌 데이터라는 것을 표시한다. 
'여성' 비트맵의 경우는 '1001'이므로 첫 번째와 네 번째 데이터는 '여성' 데이터가 저장되며 
두 번째와 세 번째의 데이터는 '여성'이 아닌 데이터라는 것을 의미한다. 
결국, '남성' 비트맵과 '여성' 비트맵을 모두 합치면 해당 테이블에는 
'여성', '남성', '남성', '여성' 순으로 데이터가 저장되는 셈이다.

(?) 테이블의 총 로우수가 4개여서 비트자리수가 4자리인가? 그렇다면 만일 로우수가 100개라면 비트자리수는 100자리가 되는 것인가?

비트맵 인덱스 검색원리

• 1.<부품> 테이블의 SIZE칼럼에는 SAMLL,MED,LARGE 세개의 값이 있고,COLOR 칼럼에는 BLUE,RED,GREEN 세개의 값만이 나타난다고 가정하자.
• 2.이 두개의 칼럼에 대해 비트맵인덱스를 만든다면 하단처럼 나타날 것이다.
• 3.만일 상단왼쪽 쿼리문을 실행한다면 다음과 같은 플랜이 나타날 것이다.

Execution Plan
-------------------------------------------------------
SELECT STATEMENT
  SORT (AGREGATE)
    TABLE ACCESS (BY INDEX ROWID) OF 'PARTS'
      BITMAP CONVERSION (TO ROWIDS)
        BITMAP AND
          BITMAP INDEX (SINGLE VALUE) OF 'COLOR_BIX'
          BITMAP INDEX (SINGLE VALUE) OF 'SIZE_BIX'

• 'COLOR_BIX'와 'SIZE_BIX' 인덱스를 엑세스하여 AND 연산을 하여 조건을 만족하는 로우만 추출한다. 이 예제에서는 010010이 취할 예제이다
  -> color=red 인덱스의 비트맵 : 011010
  -> size=med 인덱스 비트맵 : 110010
  -> and 연산

011010
110010

• 그 결과를 BITMAP CONVERSION 를 통해 ROWID를 추출하여 PARTS 테이블에 엑세스한다.

<위 그림처럼 흰색의 비트맵 인덱스에서 ROWID를 계산하여 실제 테이블에 엑세스 하는 과정>

• ROWID 계산법=시작ROWID+상대거리
• 시작로우ID(5.2.11) : FILE11의 5번째 블럭에서 시작한다는 의미이다. (?) 2의 의미는?
• 종료로우ID(15.3.11) : FILE11의 15번째 블럭에서 종료한다는 의미이다. (?) 3의 의미는?
• 위 그림에서 보면 0이 아닌 1의 값을 가진 경우가 바로 흰색이라는 의미이며 1,4,7번째가 해당된다.
• 첫번째 1의 경우 무조건 시작블럭이 5블럭의 첫번째 로우를 읽으면 된다.
• 두번째 4의 경우 첫블록의 로우수가 3이므로 다음 블록의 첫번째 로우를 읽으면 된다.
• 세변째 7의 경우 첫번째 블럭의 로우수 3에 현재블록인 블럭6의 로우수 4를 합하면 7로써 자신의 순번인 7에 해당되므로 6블럭 4번째 로우를 읽으면 된다.
• 주의: 블록의 수는 데이타사전에서 관리하므로 해당 로우가 나올 때까지 이전 블록의 모든 로우를 읽을 필요는 없다

• SELECT된 결과를 SUM()하여 최종 결과를 추출한다

비트맵 인덱스 입력,수정,삭제

• 1.비트맵 인덱스의 입력,수정,삭제는 비트리 인덱스에 비해 상대적으로 처리속도가 매우 느리다.
• 2.총 20만건의 레코드가 있는 테이블에서 물리적인 위치가 10만번 째가 되는 새로운 인덱스를 삽입한다고 하자. 먼저 추가할 인덱스를 삽입하고 100,001부터 이후 모든 인덱스 값을 한 칸씩 뒤로 미루는 작업을 해 주어야 한다.
• 3.한건이 추가된다는 것은 인덱스 전체구조를 바꾼다는 것과 같다. 따라서 입력,수정,삭제가 빈번한 테이블에 대해서는 비트맵 인덱스가 적절하지 않다.

7.8.5 해싱기법

1.해시 인덱스의 특징

• 1)6블록 이상의 물리적인 블록의 크기를 갖는 테이블에 적용한다.
• 2)정렬순서에 따른 접근방식이 아닌 임의접근방식이 많이 발생하는 경우에 적합하다.
• 3)자주 변경되지 않는 칼럼값에 해시키를 적용한다.
• 4)클러스터키를 사용하는 비슷한 검색조건으로 부터 해시클러스터 인덱스는 인덱스 클러스터보다 빠른 성능을 제공한다.
• 5)하나의 테이블에는 하나의 해시키만 가지 수 있으므로 가장 많이 조회하거나 중요한 칼럼에 해시키를 지정한다.
• 6)해시 알고리즘은 범위검색에는 적용되지 않는다 ex) ORDDATE > '20081010'
• 7)정렬되어 있는 테이블를 조회할 때는 해시 알고리즘이 적용되지 않는다.
• 8)여러개의 칼럼을 하나의 해시키로 구성하였을 때 일부에 대해서만 비교한다면 해시 알고리즘은 이용되지 않는다.

7.8.6 클러스터링

1.동일한 성격의 데이타를 동일한 블럭에 저장하여 검색의 속도를 높이는 기법이다.

2.보통 인덱스의 경우 칼럼값이 NULL이면 인덱스에 저장이 안되나 클러스터 인덱스는 NULL키를 저장한다.

3.클러스터링의 특징

• 1)6블록 이상의 테이블에 적용한다.
• 2)칼럼 값의 분포도가 좋지 않을 수록,즉 동일한 값이 많을 수록 유리하다.
• 3)일정한 순서로 조회되는 경우 적용한다.
• 4)입력,수정,삭제가 자주 발생되지 않은 칼럼에 적용한다.
• 5)클러스터링을 생성한 기준값은 수정되지 않아야 한다.
• 6)테이블이 분할되어 있지만 거의 동시에 조인하여 조회하는 경우 적용한다.
• 7)일반 테이블보다 저장 공간을 많이 차지하므로 전체스킨을 할 경우 검색속도가 느리다.

4.클러스터링이 적용안되는 경우

• 1)테이블에 전체스캔이 종종 발생한다면 클러스터링을 적용하지 않는다
• 2)파티셔닝을 적용하면 클러스터링 기능을 사용할 수 없다. (?)
• 3)동일한 클러스터 키를 가진 클러스터링된 데이타의 크기가 하나의 블럭을 초과하는 경우 클러스터링을 적용하지 않는다.

7.9 데이타베이스 분산설계

7.9.1 테이블 위치분산

1.정보를 이용하는 형태가 각 위치별로 차이가 있을 경우 사용하는 기법이다.
2.만일 본사에서 자재품목을 구입하고 각 지사별로 자재품목을 이용하여 제품을 생산한다면 <자재품목> 테이블은 본사에, <생산제품> 테이블은 지상에 위치시킨다.
3.테이블 위치를 파악할 수 있는 도식화된 위치별 데이타베이스 문서가 필요하다.

7.9.2 테이블 분할분산

수평분할

1.하나의 테이블에 있는 로우를 분할하는 기법이다.
ex) <매출> 테이블에 각 지사별 매출데이타가 있는데 각 지사는 자신의 매출정보만을 이용한다면 지사별로 로우를 쪼개어 분할분산한다.
2.각 지사별로 존재하는 테이블의 로우는 서로 배타적이며 하나로 합하는 경우 PK에 의한 중복이 일어나지 않는다.
3.만일 로우의 소속 지사가 A에서 B로 변경된다면 A에 B로 데이타 이송이 일어나야 한다.

수직분할

1.하나의 테이블을 칼럼단위로 쪼개는 기법이다
ex) 제품의 재고량은 지사별로 관리하고 단가는 본사가 관리한다면 지사테이블에는 제품번호,재고량 본사테이블에는 제품번호,단가 칼럼을 가지게 된다.
2.각지에 흩어져 있는 테이블들의 PK은 동일해야하며 테이블을 합칠시 로우의 증가가 일어나서는 안되고 완벽한 하나의 테이블로 나타나야 한다.

7.9.3 테이블 복제분산

부분복제

1.하나의 테이블에 대해 전체 데이타를 가진 테이블은 본사에 두고 각 지사별로 필요한 데이타만을 복제해서 별도의 테이블로 운용하는 기법이다. 따라서 각 지사에 존재하는 데이타는 반드시 본사 테이블에 존재해야 한다.
2.각 지사별 테이블은 배타적이야하며 각 지사별 테이블의 로우의 합은 본사의 테이블의 로우와 정확히 일치해야 한다.
3.이러한 기법은 본사 테이블과 각 지사 테이블간의 데이타 정합성을 유지하는 것이 가장 중요하며 보통 실시간 싱크를 맞추기 보다는 야간에 배치작업을 통해 데이타 싱크작업을 하게 된다. 보통 지사에서 데이타의 입력,수정,삭제가 일어나고 이를 본사테이블에 반영하는 방식을 이용한다.

광역복제

1.본사와 똑같은 테이블을 각 지사에도 배치하는 기법이다.
2.이 기법도 데이타 정합성을 유지하는 것이 가장 중요하며 보통 본사에서 데이타의 입력,수정,삭제가 발생하고 이를 각 지사에 반영하는 형태로 운영된다.

7.9.4 테이블 요약분산 (?)

분석요약

테이블에 있는 모든 칼럼과 로우가 모든 지사에도 동일하게 존재하지만 각 지사는 동일한 내용에 대해 각 지사별로 요약되어 있는 정보를 가지고 있고 본사는 각 지사의 요약정보를 통합하여 전체에 대한 요약정보를 가지고 있다.

통합요약

각 지사별로 존재하는 내용이 다른 정보를 본사에서 통합하여 다시 전체에 대한 요약정보를 산출하는 방식이다. 테이블에 있는 모든 칼럼과 로우가 지사에도 동일하게 있지만 각 지사는 타 지사와 다른 요약정보를 가지고 있고, 본사에는 각 지사의 요약정보를 통합하여 전체에 대한 요약정보를 가지게 된다.

• 분석요약은 지사에 있는 데이타를 이용하여 본사에서 통합하여 요약데이타를 산정하지만
  통합요약은 지사에서 요약한 정보를 본사에서 통합하여 각 지사별로 데이타를 비교하기 위해 이용된다.