데이터베이스 설계

1.1. 엔티티 타입
정의 : 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 것으로 영속적으로 존재하는 단위

특징
1) 반드시 시스템을 구축하고자 하는 업무에서 필요하고 관리하고자 하는 정보이어야 한다.
2) 유일한 식별자에 의해 식별이 가능해야 한다.
3) 영속적으로 존재하는 엔티티의 집합이어야 한다.
4) 업무 프로세스는 그 엔티티타입을 반드시 이용해야 한다.
5) 엔티티타입에는 반드시 속성(Attribute)이 있어야 한다.
6) 엔티티타입은 다른 엔티티타입과 최소 한 개 이상의 관계가 있어야 한다.

분류
1) 유무형에 따른 분류 : 유형, 개념, 사건 엔티티타입
2) 발생시점에 따른 분류 : 기본, 중심, 행위 엔티티타입


1.2. 관계
정의 : 두 개의 엔티티타입 사이의 논리적인 관계 즉 엔티티와 엔티티가 존재의 형태나 행위로서 서로에게 영향을 주는 형태

1.3. 속성
정의 : 업무에 필요한 엔티티에서 관리하고자 하는 더 이상 분리되지 않는 최소의 데이터 단위

1.4. 엔티티 수퍼타입과 서브타입
정의 : 여러개의 엔티티 타입이 비슷하고 일부의 속성이나 관계만 다를 경우 여러 개의 엔티티타입을 한 개의 엔티티타입 안에 다른 엔티티타입의 모습을 서브타입으로 나뉘어 표시하는 경우가 있다. 이러한 것을 엔티티타입이 통합되었다라고 한다. 통합하여 표시하는 엔티티타입을 수퍼타입이라하고 수퍼타입 안에 포함되어 표시된 비슷한 성격의 엔티티타입을 서브타입이라 한다.

1.5. 서브젝트 에어리어
정의 : 해당 업무 내에서 연관이 많은 엔티티타입을 그룹으로 묶어 표시하는 개념이다.

1.6. 정규화
특징
1) 정규화는 적절한 엔티티타입에 각각의 속성들을 배치하고 엔티티타입을 충분히 도출해가는 단계적인 분석 방법이다.
2) 정규화 기술은 엔티티타입에 속성들이 상호 종속적인 관계를 갖는 것을 배경으로 종속 관계를 이용하여 엔티티타입을 정제하는 방법이다.
3) 각각의 속성들이 데이터 모델에 포함될 수 있는 정규화의 원리를 이용하여 데이터를 분석하는 방법에서 활용될 수 있다.
4) 정규화는 현재 데이터를 검증할 수 있고 엔티티타입을 데이터가 표현하는 관점에서 정의하는데 이용할 수 있다.
5) 정규화는 엔티티타입을 분석하는 관점이 오브젝트 별 분석하는 방법이 아닌 개별 데이터를 이용한 수학적인 접근 방법을 통해 분석하는 방법이다.

정규화 내용
1) 1차 정규화 : 복수의 속성값을 갖는 속성을 분리
2) 2차 정규화 : 주식별자에 종속적이지 않은 속성을 분리
3) 3차 정규화 : 속성에 종속적인 속성을 분리
4) 보이스-코드 정규화 : 다수의 주식별자 분리
5) 4차 정규화 : 다가 종속(Multi-Valued Dependency) 속성 분리
6) 5차 정규화 : 결합종속(Join Dependency)일 경우는 두개이상의 N개로 분리

* 5차 정규화는 실제 프로젝트에서는 거의 발생하지 않는다.

1.7 ERD 표기법
ERD 작성 순서
엔티티타입을 그린다. -> 엔티티타입을 적걸하게 배치한다. -> 엔티티타입간 관계를 설정한다. -> 관계명을 기술한다. -> 관계의 참여도를 기술한다. -> 관계의 필수여부를 기술한다.

ERD에서 엔티티타입의 배치방법
1) 업무를 진행하는 순서에 따라 엔티티타입을 왼쪽 편부터 오른쪽으로 그리고 위에서 아래로 표시한다.
2) 업무 흐름에 중심이 되는 엔티티타입, 보통 업무 흐름에 있어 중심이 되는 엔티티타입은 타 엔티티타입과 많은 관계를 가지고 있으므로 중앙에 배치한다.
3) 업무를 진행하는 중심 엔티티타입과 관계를 갖는 엔티티타입들은 중심에 배치된 엔티티타입의 주위에 배치하도록 한다.

2.1. 엔티티타입 정의
분석 초기에 엔티티타입을 선정하기 위한 자료
1) 업무와 관련해서 설명한 업무 기술서를 이용한다.
2) 현업 담당자와의 인터뷰를 활용하라
3) 현업의 장표를 활용한다.
4) 기존 시스템이 이미 구축되어 있다면 기존 시스템의 산출물을 검토한다.
5) DFD를 통해 업무분석을 진행하였다면 DFD의 DATA STORE를 활용하여 엔티티타입을 도출할 수 있다.
6) 현업의 업무를 직접 견학하고 인터뷰와 업무기술서에서 누락된 정보가 있는지 검토해야 한다.

엔티티 선정 작업 방법
1) 업무기술서, 장표, 인터뷰 정리문서 등에서 명사를 구분한다.
2) 개념이 불분명한 것, 광범위한 것은 제거한다.
3) 엔티티타입의 특성이거나 속성의 값은 제거한다.
4) 업무 프로세스에 해당되는 명사는 제거한다.
5) 중복되는 명사는 제거한다.
6) 누락된 엔티티타입이 존재하는지 유추해 본다.

엔티티 정의서 작성 시 포함 항목
1) 엔티티타입명
2) 엔티티타입 설명
3) 동의어/ 유의어
4) 엔티티타입 구분

엔티티타입 분석 과정
1) 엔티티타입 도출 방법에 의해 엔티티타입을 선정한다.
2) 선정된 엔티티타입에 대한 초기 엔티티정의서를 작성한다.
3) 고객과 검증 회의를 한다.
4) ERD에 엔티티타입을 표현한다.

2.2. 관계 정의
관계에 있어서 중요한 점 3가지 : 방향, 카디낼리티(CARDINALITY), 선택도

엔티티타입 사이의 관계 선정 방법
1) 업무 기술서, 장표, 인터뷰 정리 문서 등에서 동사를 구분한다.
2) 도출된 엔티티타입과 관계를 이용하여 관계 정의서를 작성한다.
3) 고객에게 질문하여 관계를 더 세분화하고 정확하게 도출하는 작업을 한다.
4) 데이터모델링 툴이나 칠판, 포스트잇을 이용하여 모델을 직접 그려본다.
5) 고객과 질문하고 협의하여 모델을 검토하는 시간을 갖는다.

2.3 식별자 정의
엔티티타입의 식별자가 가지는 특징
1) 식별자에 의해 엔티티타입 내에 모든 엔티티들이 유일하게 구분되어야 한다.
2) 특정 엔티티타입의 식별자가 지정되면 그 식별자는 변하지 않아야 한다.
3) 식별자가 지정되면 반드시 데이터 값이 존재해야 한다.

식별자의 구분
1) 주식별자 / 보조식별자
2) 내부식별자 / 외부식별자
3) 단일식별자 / 복합식별자

주식별자 선정 기준
1) 가능하면 해당 업무에서 자주 이용되는 속성을 주식별자로 지정한다.
2) 명칭, 내역 등과 같이 이름으로 기술되는 것들은 가능하면 주식별자로 지정하지 않도록 한다.
3) 만약 주키로 선정하는 속성이 복합으로 구성되어 주식별자가 될 때 가능하면 주식별자를 선정하는 속성 수가 7~8개가 넘지 않도록 한다.

2.4. 속성 정의
속성의 중요한 원리 : 각각의 속성은 반드시 하나의 엔티티타입 속에 있어야 하고 전체 데이터모델에서 하나의 의미만을 가져야 한다.

속성을 발견할 수 있는 방법
1) 해당 업무의 자료를 수집하는 동안 업무 사실에 근거해서 속성을 발견할 수 있다.
2) 엔티티타입을 추출하는 동안(현 시스템 분석을 포함하여) 어느 정도 안정적이고 확실한 속성을 대략 60~70% 정도 분석할 수 있다.
3) 프로세스 모델링을 진행하는 동안 업무 기능에 따라 관련 있는 속성 정보를 10~20% 정도 얻을 수 있다.
4) 나머지 10% 정도는 데이터모델과 프로세스 모델 상호를 교차 검사하는 상관 모델링 단계에서 도출이 된다.

속성의 원칙
1) 엔티티타입 내에서 한 속성값으 ㄴ한 시점에 한 개의 값만 가지는 것이 원칙이다.
2) 시간에 따라 엔티티타입 내 속성값이 변할 수 있다. 이러한 속성을 '다중값 속성'이라 하며, 이경우 새로운 엔티티타입을 생성해야 한다.

속성을 더 상세하게 분석하고 적용하기 위한 방법
1) 데이터가 어떤 특징을 가지며, 어떤 부류에 속하는지 정의한다.(BASIC, DESIGNED, DERIVED)
2) 그 속성이 값을 반드시 필요로 하는지를 정의한다.
3) 그 속성은 반드시 정해진 값만 가져야 하는지를 정의한다.

2.5. 도메인 정의
도메인 : 데이터모델링에서 도메인이란 엔티티타입 내에 속성에 대한 데이터타입과 크기, 제약 사항을 지정하는 것이다.

도메인 정의에 대한 유형
1) 코드에 대한 도메인은 그룹으로 모으지 않고, 모두 개별로 도메인을 생성한다.
2) 번호, 일련번호 즉 데이터모델에서 주식별자 관리를 위해 인공적으로 생성한 속성에 대해 도메인을 별도로 부여하여 관리하는 것이다.
3) 일반적인 속성은 그 성격에 따라 그룹으로 묶되 길이나 데이터타입 별로 묶는 것보다는 속성이 의미하는 분류대로 도메인 분류를 구성하는 것이 좋다.

도메인 정의 방법
1) 데이터의 모든 속성을 나열한다.
2) 모든 속성 중에 뒤부터 2~4 정도를 분리해 본다.
3) 공통으로 발생하는 접미어를 분리하여 하나로 만든다.
4) 분리된 접미어를 비슷한 것끼리 묶어 그룹을 만들어 이름을 부여한다.
5) 각 도메인 별로 데이터 타입과 길이를 지정한다.
6) 각 엔티티타입의 속성에 도메인을 할당한다.

2.6. 용어사전 정의
용어 도출 과정
1) 엔티티타입의 속성명을 모두 한곳에 모아 기술한다.
2) 속성명을 업무에서 사용하는 단어의 단위로 분리한다.
3) 각각의 단위 속성에 의미를 기술하고 물리 속성명을 업무 특성에 적합하게 정의한다.
4) 물리 속성명 명명 규칙을 정한다.
5) 단위 속성명에 따라 엔티티타입의 모든 속성명에 대해 논리 속성명을 일치시키고 물리 속성명은 생성해 주도록 한다.

3.1. M:N 관계해소 방법
관계엔티티타입 : 프로젝트를 진행하는 도중 많이 발생되는 엔티티타입으로 대부분 도출되지 않은 업무 규칙이 새롭게 발견되었거나 이력 관리와 같이 업무 규칙을 추가하는 작업에 의해 많이 도출된다.

M:N 관계 해소 방법
1) 관계 엔티티타입 분리
2) PRIMARY KEY 통합
3) 부모 엔티티타입에 속성 추가

3.2. 1:1 관계 해소 방법
1) 별개의 엔티티타입으로 따로따로 표현하는 방법
2) 하나의 엔티티타입으로 완전히 통합하는 방법
3) 부분 통합을 하는 방법
4) 수퍼 엔티티타입 생성

3.3. 엔티티타입의 통합은 어떻게 할 것인가?
엔티티타입을 통합하는 목적
1) 여러 엔티티타입에 있는 비슷한 정보를 한군데서 표현하므로 종합적으로 정보를 조회하는 작업이 용이해진다.
2) 비슷한 속성이 합해지므로 엔티티타입 간 중복성이 제거된다.
3) 비슷한 유형의 엔티티타입이 발생할 경우 동일한 규칙에 따라 하나의 엔티티타입으로만 표현이 가능하다.

엔티티타입이 통합되었을 때 나타나는 문제점
1) 업무의 확장성이 감소할 수 있다.
2) 엔티티타입이 한 군데에 모두 표현되었기 때문에 업무 흐름을 데이터모델만 가지고 이해하기는 쉽지 않다
3) 시스템 성능이 저하될 수도 있다.
4) 여러 엔티티타입의 속성이 존재하므로 필요시 속성에 제약을 걸어야 하는데 제약을 걸지 못하는 경우가 발생한다.
5) 서로 다른 성격의 엔티티타입이 한 군데 있으므로 체크해야 할 조건이 늘어난다.
6) 두 개의 엔티티타입이 각각 다른 PK를 가지면서 통합되었을 경우 두 엔티티를 동시에 한 ROW로 표현하기 위해 조회해야 할 경우 SQL 문장은 작성하기 힘들거나 복잡해진다.

엔티티타입을 통합하는 순서
1) 엔티티타입과 PK 그리고 PK와 관련된 업무 규칙을 통합한다.
2) 관계와 관계에 의해 발생된 FK 그리고 FK와 관련된 업무규칙을 통합한다.
3) 속성과 속성에 관련된 업무규칙을 통합한다.

엔티티타입을 통합하는 경우
1) PK가 동일한 엔티티타입
2) 두 엔티티타입의 PK가 꼭 일치하지는 않지만 엔티티타입의 성격이 비슷하여 둘 중 하나의 PK를 선택하여 통합하여도 나머지 PK는 ALTERNATE KEY로 이용될 수 있는 경우에 두 엔티티타입은 통합 대상이 된다.
3) 두 엔티티타입의 PK가 일치하지는 않지만 도메인이 비슷하고 엔티티타입에 포함된 속성이 비슷할 경우 하나의 엔티티타입으로 통합할 수 있다.
4) 복합 PK를 가진 엔티티타입에서 두 엔티티타입의 PK 구성이 전혀 다르더라도 PK를 구성하는 복합 속성 중 일부의 비슷한 속성을 이용하여 통합 엔티티타입을 구성할 수 있다.

3.4. 코드 엔티티타입 설계 방법
코드 : 업무적으로 또는 정보화시스템에서 구분하기 쉽도록 데이터들을 간단하게 구분해 놓은 단위

업무코드의 2가지 형식
1) 한 가지 코드값이 반복적으로 나타나는 경우
2) 한 가지 코드에 대해 여러 개의 속성이 반복되어 나타나는 경우

한 가지 코드 값이 반복적으로 나타나는 경우 데이터모델링 방법
1) 코드 구분에 대해서 먼저 설계한다.
2) 상세 코드와 코드값을 조사한다.
3) 엔티티타입을 설계한다.
4) 각각의 코드에 별도의 제약이 있을 수 있는 것을 가정하여 설계한다.

한 가지 코드에 대해 여러 개의 속성이 반복되어 나타나는 경향이 있는 유형에 대한 데이터 모델링
1) 보통 일반적인 엔티티타입 설계와 마찬가지로 설계한다.
2) 각각의 코드에 따라 여러 개의 속성이 존재하며 벼도의 엔티티타입으로 관리하도록 한다.

3.5. 도미노 속성에 대한 데이터모델링 방법
도미노 속성 : 앞의 값에 규칙적인 제약이 연쇄적으로 발생하는 경우

도미노 속성의 데이터 모델링 방법
1) 해당 업무에서 도미노가 발생할 수 있는 최대값을 정의하여 모델링하는 방법
2) 도미노 속성을 BOM을 이용하여 모델링하는 방법

도미노 속성의 활용
1) 도미노 속성 전체를 하나의 속성처럼 활용하는 형태이다.
2) 사용자 인터페이스에서 앞 값에 의해 뒤의 값이 한정되어 보여주는 경우이다.

3.6. 메시지 엔티티타입 설계 방법
메시지 데이터모델링 방법
1) 업무와 관련하여 개발하고자 하는 시스템과 연관된 모든 메시지를 조사한다
2) 메시지 성격에 맞게 데이터모델링을 진행한다.

3.7. 이력 엔티티타입 설계 방법
이력 관리 : 하나의 업무단위가 시간이 흐름에 따라 발생하는 과거와 현재 데이터를 지속적으로 유지하는 관리방법

이력이 발생하는 유형
1) 하나의 엔티티타입이 변경되었을 때 변경 이전과 이후의 데이터르 ㄹ모두 관리하는 변경 이력
2) 년,월,일 등 시간 순으로 발생하는 발생 이력
3) 업무 진행 상태에 따라 업무의 상태 정보를 관리하는 진행 이력

4.1. 업무 기능 분해
업무기능 : 기업의 임무를 한층 더 멀리 조명하여 하나로 표현하는 완벽한 단위 업무기능의 그룹 (ex:영업,회계)
프로세스 : 업무기능을 분해하면서 나타나는 각각의 업무기능 중 세분화된 업무 구분을 의미할 뿐만 아니라 업무의 실행 단위가 되면서 정보의 입출력이 되는 단위
업무기능 분해 : 업무기능을 단위 프로세스가 도출될 때까지 지속적으로 나누어 가는 과정

업무기능 분해의 목적
1) 정보화시스템을 구축하는 가장 최저 단위의 단위 프로세스를 도출하기 위함이다.
2) 전체 업무의 체계를 프로세스를 중심으로 도식화하여 업무에 대한 기능을 파악하는 것이다.

프로세스 판단 기준
1) 해당 업무 기능이 실행 단위인지 관리 단위인지 확인
2) 업무 기능에 입력과 출력의 행위가 이루어지는지를 확인

업무기능 분해 방법
1) 해당 업무에서 단일 업무기능으로 정의하고자 하는 큰 구분, 즉 뿌리 기능을 지정한다.
2) 단일기능ㅇ으로 정의해 놓은 업무기능을 더 세부적으로 쪼개어 나간다.
3) 기능들을 업무 발생 순서대로 업무기능 분해도에 배치한다.
4) 단위 프로세스가 도출될 때까지 모든 업무기능에 대해 위 작업들을 반복적으로 수행한다.

업무기능 분해를 하기 위한 자료
1) 해당 업무를 구체화하여 업무기능을 분해한다.
2) 업무기능의 생명주기를 분석하여 업무기능을 분해한다.
3) 엔티티의 상태를 이용하여 업무기능을 분해한다.
4) 업무 이벤트에 따라 기능을 분해한다.
5) 데이터의 구조에 따라 기능을 분해한다.

4.2. 단위 프로세스의 도출
단위 프로세스 검증 방법
1) 단위 프로세스의 실행은 업무 데이터를 변형하는데 모두 필요하고 모든 데이터의 무결성 규칙을 보장하는 변함없는 상태를 유지하는 역할을 한다.
2) 프로세스는 서로 밀접하게 관련이 있는 형제 프로세스와 동시에 데이터 집합에 작용한다.
3) 각각의 프로세스는 적어도 하나의 이벤트에 대해 응답이 존재해야 한다.
4) 어느 단위 프로세스도 데이터에 전혀 상관없이 독립적으로 수행해서는 안된다

4.3. 단위 프로세스와 데이터
CRUD 문서등을 통해 단위 프로세스와 엔티티타입 간의 관계를 정리해 볼 수 있다.

5.1. CRUD MATRIX 상관 모델링
CRUD MATRIX 체크사항
1) 모든 엔티티타입에 CRUD가 한 번 이상 표기되었는가?
2) 모든 엔티티타입에 "C"가 한 번 이상 존재하는가?
3) 모든 엔티티타입에 "R"가 한 번 이상 존재하는가?
4) 모든 단위 프로세스는 하나 이상의 엔티티타입에 표기가 되었는가?

CRUD MATRIX를 이용함으로써 프로젝트에서 얻을 수 있는 장점
1) 분석단계의 데이터모델과 프로세스 모델에 대한 작업을 검증하는 역할을 한다.
2) 시스템 구축단계에서 애플리케이션을 개발하는데 필요한 주요한 산출물이 된다.
3) 테스트 단계에서 개발한 애플리케이션을 객관적인 자료를 사용하여 테스트하는 데도 중요한 역할을 한다.
4) 전체 업무의 인터페이스를 파악할 수 있다.

5.2. 엔티티타입 생명 주기 분석
엔티티타입 생명 주기 분석 : 엔티티타입을 중심으로 엔티티타입과 관계된 프로세스만을 표기하여, 엔티티타입 내에 엔티티들이 어떠한 생명주기를 가지고 있는지 검증하는 방법이다.

2가지 측면의 검토
1) 업무적 측면의 검토 : 모델링이 시스템의 업무적 요건을 충분히 반영하고 있는지, 모델링의 관계가 해당 업무 프로세스에 잘 부합하는지 검토한다.
2) 모델규약 측면의 검토 : 데이터모델링이 갖추어야 할 모델링에 관한 일반적인 규약을 잘 준수하고 있는지 검토한다.

6.1. 엔티티타입 검토
엔티티타입 검토 시 검정해야 할 내역
1) 선정된 PK가 업무적으로 발생하는 자료의 유일성을 보장하는가?
2) 선정된 PK는 효율적인 모습을 가지고 있는가?
2.1) 선정된 속성은 해당 업무에 대해서 대표성을 가지는가?
2.2) 최소의 속성으로 자료의 유일성을 확보할 수 있는가?
3) 자료의 발생유형이 유사한 엔티티는 통합되었는가?
4) 독립된 엔티티타입이나 엔티티타입의 그룹은 존재하지 않는가?
5) 병합 또는 분리되어야 할 엔티티타입은 존재하지 않는가?
6) 추가적으로 도출되어야 하거나 불필요한 엔티티타입은 없는가?
7) 엔티티타입이 주변 여러 엔티티타입의 공통 엔티티타입의 경우, 자료 원천이 어느 엔티티타입인지 추적가능한가?

6.2. 속성 검토
속성에 대해 검증해야 할 내역
1) 반정규화된 속성은 식별되는가?
2) 반정규화는 시스템 복잡도와 성능을 고려하여 적절하게 이루어졌는가?
3) 명칭이 같은 속성의 타입과 크기는 동일한가?
4) 내부적인 속성을 갖고 있는 속성은 존재하지 않는가?
5) 병합되어야 할 속성은 존재하지 않는가?
6) 전후 레코드 간 영향을 미칠 수 있는 속성은 없는가?
7) 감사, 통계 등을 고려하여 속성이 정의되었는가? (코드화할 수 있는 텍스트는 코드화 되었는가?)

6.3. 관계 검토
관계들에 대해 검증해야 할 내역
1) 엔티티타입 간의 관계가 M:N인 속성은 없는가?
2) 엔티티타입 간의 관계는 업무적 흐름과 규약이 일치하는가?
3) 업무적 흐름에 비추어 미 도출된 관계는 없는가?
4) 관계에 대한 표현은 적절한 수준에서 이루어졌는가?

6.4. 도메인 검토
1) 도메인이 적절히 정의되어 관리되고 있는가?
2) 도메인의 변경에 따라 속성이 변경되고 있는가?

7.1. 관계형 테이블로 전환
ERD를 테이블 관계도로 변환하는 항목
1) 엔티티타입은 테이블로 전환한다.
2) 주식별자는 PK로 변환한다.
3) 속성은 컬럼으로 변환한다.
4) 관계에 의한 외부 식별자는 FK로 변환한다.

엔티티타입을 테이블로 구성하는 방법
1) 독립 엔티티타입은 독립 테이블로 전환한다.
2) 완전종속 엔티티타입은 완전종속 테이블로 전환된다.
3) 부분 종속 엔티티타입은 부분 종속 테이블로 전환된다.
4) 주식별자를 PK로 전환한다.
5) 관계는 FK로 변환된다.

물리 모델에서 PK의 특징
1) 데이터 모델의 주식별자는 PK로 전환된다.
2) 무결성제약을 유지시키는 역할을 한다.
3) PK는 테이블이 있는 각각의 ROW를 유일하게 식별한다. PK 이외에 로우를 유일하게 식별하게 하는 대체키가 존재할 수 있다.
4) PK는 NULL값을 갖지 않는다.
5) PK는 변경되지 않으며 업무적으로 변경이 발생한다는 것은 테이블에서는 삭제와 입력이 동시에 발생하는 것을 의미한다.
6) 가능하면 모든 테이블에 PK를 정의한다.

7.2. 반정규화
반정규화 : 정규화된 엔티티타입, 속성, 관계를 시스템의 성능 향상, 개발과 운영을 단순화하기 위해 데이터 모델을 통합하는 프로세스를 의미한다.

반정규화 절차
1) 반정규화의 대상을 조사한다.
2) 반정규화의 대상을 다른 방법으로 처리할 수 있는지 검토한다. (뷰, 클러스터링, 인덱스 조정, 파티셔닝 기법 등)
3) 반정규화를 적용한다.

반정규화 방법 3가지
1) 테이블 반정규화 방법 (테이블 병합, 테이블 분할, 테이블 추가)
2) 컬럼 반정규화 방법 (중복컬럼 추가 -> 파생컬럼추가 -> 이력 테이블 컬럼 추가 -> PK에 의한 컬럼 추가 -> 응용 시스템의 오작동 처리를 위한 컬럼 추가)
3) 관계 반정규화 방법 (데이터 조회 경로를 단축하기 위해 일반 컬럼 또는 PK 컬럼을 중복하는 방법)

7.3. 무결성 제약 정의
참조 무결성의 종류
1) 입력 참조 무결성
2) 수정 참조 무결성
3) 삭제 참조 무결성

입력 참조 무결성 : 각 테이블에 레코드가 생성될 때 자신이 참조하고 있는 테이블의 PK에 대해 데이터의 정합성을 일치시켜 주는 기능

입력 참조 무결성을 유지하기 위한 방법
1) 의존 : 자신의 테이블에 데이터를 입력할 때, 참조하고 있는 테이블의 PK가 존재해야만 데이터 입력이 가능하다.
2) 자동 : 자신의 테이블에 데이터를 입력할 때, 참조하고 있는 테이블의 PK가 존재하지 않으면 PK를 생성하고 자신의 테이블에 데이터를 생성한다.
3) 기본 : 자신 테이블의 레코드를 입력할 때, 참조하는 테이블의 PK를 기본값으로 바꾼 후 자신의 레코드를 입력한다.
4) 지정 : 사용자가 정의 해 놓은 일정한 조건을 만족한 이후에 자신의 레코드를 입력한다.
5) NULL : 자신 테이블의 레코드를 입력할 때 참조하는 테이블의 PK가 없어도 그대로 입력한다.

수정 참조 무결성 : 자신이 참조되는 모든 테이블의 FK 정보와 일치를 위해 자신의 PK가 수정되면 관련된 모든 테이블의 FK 정보와 일치를 위해 자신의 PK가 수정되면 관련된 모든 테이블의 FK도 수정하여 데이터의 정합성을 유지하는 기능이다.

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

삭제 참조 무결성 : 자신이 참조되는 모든 테이블의 FK 정보와 일치를 위해 자신의 PK가 삭제되면 관련된 모든 테이블의 FK를 기본값이나 NULL로 수정하여 데이터의 정합성을 유지하는 기능이다.

삭제 참조 무결성을 유지하기 위한 기능
1) 제한 : 자신 테이블의 레코드를 삭제하려면 자신을 참조하는 테이블의 레코드가 없어야 한다.
2) 연쇄 : 자신 테이블의 레코드를 삭제하려면 참조되는 모든 테이블의 레코드를 삭제하고 자신을 삭제한다.
3) 기본 : 자신 테이블의 레코드를 삭제할 때 참조되는 모든 테이블의 레코드를 기본값으로 바꾼 후 자신의 레코드를 삭제한다.
4) 지정 : 사용자가 정의 해 놓은 일정한 조건을 만족한 이후에 자신의 레코드를 삭제한다.
5) NULL : 자신 테이블의 레코드를 삭제할 때 참조되는 모든 테이블의 레코드를 NULL로 바꾼 후 자신의 레코드를 삭제한다.
6) 미지정 : 자신 테이블의 레코드를 삭제해도 특별한 참조 무결성 규칙을 적용하지 않는 경우이다.

참조 무결성 적용시 주의사항
1) DBMS에 참조 무결성 원칙을 과다하게 적용할 경우 데이터베이스의 성능을 저하시키는 주요한 이유가 될 수 있다.

FK 제약이 걸려 있는 컬럼들의 인덱스 생성
1) FK 제약이 걸려 있는 컬럼들은 가능하면 인덱스를 걸어 줄 것을 권유한다. (업무상 인덱스를 사용하지 않더라도 DBMS 내부적으로 사용)

7.4. 트랙잭션 분석
트랜잭션 분석 절차
1) 트랜잭션 정의 : 데이터베이스에 행해지는 작업의 논리적인 단위
2) 트랜잭션 분석 : 단위 프로세스와 CRUD MATRIX를 이용하여 트랜잭션 분석서를 작성한다.
3) 트랜잭션 분석도 이용 (용량 산정의 근거자료로서 이용, 디스크 구성의 이용, 데이터베이스와 연결되는 채널의 분산)

7.5. 뷰 설계
뷰의 특징
1) 테이블의 구조를 단순화한다.
2) 다양한 관점에서 데이터를 제시할 수 있다.
3) 데이터의 보안을 유지한다.
4) 논리적인 데이터의 독립성을 제공한다.

뷰의 정의
1) 뷰의 대상이 되는 테이블을 선정한다.
2) 뷰의 대상이 되는 컬럼을 선정한다.
3) 뷰 정의서를 작성한다.

7.6. 인덱스 설계
인덱스 대상 선정
1) 대상 테이블 선정 (테이블 크기가 64KB [1BLOCK 크기 * MULTI BLOCK READ] 이내인 경우 인덱스를 지정하지 않아도 된다. 단 테이블이 참조관계 또는 조인에 의해 처리되어야 하는 경우는 인덱스를 생성해 주어야 한다.)
2) PK 컬럼,FK 컬럼은 인덱스를 설정해야 한다. ( 각각 조인과 참조관계가 발생하기 때문이다.)
3) 테이블 내에서 자주 이용되며 분포도가 좋은 컬럼[평균 분포도 : 10~15%]은 인덱스 지정 대상 컬럼이 된다.
(분포도(%) = 데이터별 평균 로우 수 / 테이블의 총 로우 수 * 100)


인덱스 최적화
1) 인덱스 효율 검토
(인덱스 컬럼은 가능하면 수정이 자주 발생되지 않는 컬럼을 선정한다.
분포가 일정하지 않고 기형적으로 불균형하면 인덱스를 설정하지 않는 것이 낫다.
한 테이블에 인덱스 개수가 5개를 초과한 경우에는 테이블의 입력, 수정, 삭제가 자주 발생하는지 검증하고, 만약 자주 발생한다면 꼭 필요한 인덱스를 남기고 삭제하도록 한다.)
2) 인덱스 데이터 타입 적용 (길이가 가변일 경우 VARCHAR를 이용한다.)
3) 인덱스 정렬
4) 클러스터링 검토 (인덱스와 반대로 분포도가 넓은 것이 좋다. 나머지 성격은 인덱스와 비슷하며, 대량의 데이터 처리에는 적합하지 않다.)

인덱스 정의서 작성

7.7. 데이터베이스 용량 설계
데이터베이스 용량 설계 : 물리 데이터베이스 설계에서 데이터가 저장되는 공간을 정의하는 작업

데이터베이스 용량 분석의 목적
1) 정확한 데이터 용량을 산정하여 디스크 사용의 효율을 높인다.
2) 디스크 구성방법에 따라 디스크 입출력이 달라질 수 있지만 기본적으로 업무량이 집중되어 있는 디스크를 분리하여 설계함으로써 집중화된 디스크에 대한 입출력 부하를 분산시킬 수 있다.
3) 똑같은 자원에 여러 프로세스가 동시에 접근할 때 발생하는 디스크 입출력 경합을 최소화하여 데이터의 접근 성능을 향상시킨다.
4) 데이터베이스 오브젝트의 익스텐트 발생을 줄인다.

데이터베이스 용량 분석 절차
1) 용량 분석을 위한 기초 데이터를 수집한다. (ROW 길이, 보존기간, 초기건수, 발생건수, 발생주기, 년 증가율)
2) 기초 데이터를 이용하여 DBMS에 이용하는 오브젝트 별로 용량을 산정한다.

테이블 크기 계산 방법
1) 총 블럭 헤더 크기를 계산
TOTAL BLOCK HEADER   = BLOCK HEADER, PART A + BLOCK HEADER, PART B
BLOCK HEADER, PART A = (FIXED HEADER + VARIABLE TRANSACTION HEADER)
BLOCK HEADER, PART B = (TABLE DIRECTORY + ROW DIRECTORY)

- FIXED HEADER : 57 BYTE (고정된 블럭 헤드)
- VARIABLE TRANSACTION HEADER : 23 * I (I는 해당 TABLE의 INITRANS의 값)
- TABLE DIRECTORY : 4
- ROW DIRECTORY : 2*R (R은 블럭의 ROW 수-R(AVG.# OF ROWS/BLOCK))

2) 데이터 블럭 당 사용 가능한 데이터 영역을 계산
AVAILABLE DATA SPACE = (BLOCK SIZE - TOTAL BLOCK HEADER) - ((BLOCK SIZE - BLOCK HEADER, PART A) * (PCTFREE/100))

- BLOCK SIZE : 데이터베이스의 블럭 크기 (SVRMGRL의 SHOW PARAMETER에서 DB_BLOCK_SIZE를 확인)

3) 평균 ROW의 전체 컬럼의 길이를 계산
ROW 길이를 계산하기 위하여 테이블 정의에서 컬럼 수, 각 컬럼의 데이터 타입, 가변 길이 컬럼의 평균 크기 등을 참조한다.

D(DATA SPACE/AVERAGE ROW) : 테이블 ROW의 평균 길이

4) 총 평균 ROW 크기를 계산
BYTE/ROW = ROW HEADER + F + V + D

- ROW HEADER : ROW당 3BYTES (NON-CLUSTERED TABLE)
- F : 250 BYTES 이하를 사용하는 컬럼의 TOTAL LENGTH BYTES (각 컬럼 당 1 BYTE)
- V : 250 BYTES 이상를 사용하는 컬럼의 TOTAL LENGTH BYTES (각 컬럼 당 3 BYTE)
- D : D(DATA SPACE/AVERAGE ROW)

5) 데이터 블록 내의 평균 ROW 수를 계산
R(AVG.# OF ROWS/BLOCK) = AVAILABLE SPACE / AVERAGE ROW SIZE

- AVAILABLE SPACE : AVAILABLE DATA SPACE (데이터 블럭 당 사용 가능한 데이터 영역)
- AVERAGE ROW SIZE : BYTE/ROW (총 평균 ROW 크기)

6) 테이블에서 요구되는 블럭과 바이트 수를 계산
BLOCKS FOR TABLE = # ROWS / R

# ROWS : 테이블의 ROW 수
R : R(AVG.# OF ROWS/BLOCK)

BYTES FOR TABLE = # BLOCKS FOR TABLE * BLOCK SIZE

인덱스 크기 계산 방법
1) 블록 헤더 크기 계산
BLOCK HEADER SIZE = FIXED HEADER + VARIABLE TRANSACTION HEADER
- FIXED HEADER : 113 BYTE
- VARIABLE TRANSACTION HEADER : 27*I (I는 인덱스에 대한 INITRANS)

2) 데이터 블록 당 사용 가능한 데이터 영역 계산
AVAILABLE DATA SPACE PER BLOCK = (BLOCK SIZE - BLOCK HEADER SIZE) - ((BLOCK SIZE - BLOCK HEADER)*(PCTFREE/100))

3) 결합된 열 길이 계산
- 바이트 길이를 포함한 컬럼 크기 계산
COLUMN SIZE INCLUDING BYTE LENGTH = COLUMN SIZE + (IF COUUMN SIZE < 250 1 ELSE 3)

- 열 크기 계산
ROWSIZE = ROW HEADER(3*UB1) + SUM(COLUMN SIZE INCLUDING BYTE LENGTH)

- 행 당 사용되는 영역
SPACE USED PER ROW (ROWSPACE) = MIN(UB1*3 + UB4 + SB2, ROWSIZE) + SB2

4) 인덱스 값 크기의 전체 평균 계산
BYTES/ENTRY = ENTRY HEADER + ROWID LENGTH + F + V + D
- ENTRY HEADER : 2 BYTE
- ROWID LENGTH : 6 BYTE
- F : 127 BYTE 이하를 저장하는 모든 열의 전체 길이 바이트, 이 유형의 열에 필요한 길이 바이트 수는 1 BYTE
- V : 127 BYTE 이상를 저장하는 모든 열의 전체 길이 바이트, 이 유형의 열에 필요한 길이 바이트 수는 2 BYTE
- D : 테이블 ROW의 평균 길이

5) 인덱스 값 크기의 전체 평균 계산
BLOCK FOR INDEX = 1.05 * NOT NULL ROWS / FLOOR(AVAILABLE DATA SPACE PER BLOCK / AVG. ENTRY SIZE)

테이블 스페이스 용량 산정방법
테이블스페이스 내에 생성되는 테이블의 용량을 더한 값에다 추가로 40%를 더한 값을 기입한다.


디스크 용량 산정방법
데이터베이스에 저장될 모든 데이터 용량과 데이터베이스를 설치하고 관리하기 위한 시스템 용량을 더하여 여유있게 산정한다.

7.8. 접근방법 설계
접급 방법 구분
직접 접근 : 스캔
간접 접근 : B-TREE 인덱스, 비트맵 인덱스, 해싱
저장 방법에 따른 접근 : 클러스터링

스캔방식
테이블에 있는 데이터를 검색할 때 순차적으로 로우를 비교하여 원하는 데이터를 가져오는 접근 방법이다.
전체스캔과 범위스캔이 있다.

스캔 방식을 적용하는 경우
1) 테이블의 크기가 작을 때, 즉 물리적인 블록에 6블록 정도까지 데이터가 점유하고 있을 때는 스캔 방식으로 데이터를 읽어도 처리 속도가 빠르다.
2) 테이블의 가져오려는 데이터의 분포도가 20%를 초과할 때 스캔 방식으로 처리한다.

B-TREE 인덱스의 기본구조
B-TREE 인덱스는 TREE 구조를 가지고 있다. TREE는 LEAF BLOCK, BRANCHED BLOCK, ROOT BLOCK로 구성되어 있다.
1) LEAF BLOCK : 테이블의 각 레코드의 인덱스 정보를 가지고 있는 블럭이다.
2) BRANCHED BLOCK : LEAF BLOCK과 ROOT BLOCK의 중간에서 블럭 사이의 정보에 대한 다리 역할을 하는 블럭이다.
3) ROOT BLOCK : TREE의 최상위 수준에 위치하며, 조회, 입력, 수정, 삭제가 발생하면 제일 먼저 접근된다.

비트맵 인덱스
컬럼정보를 0과 1을 이용하여 별도의 인덱스로 저장하는 방법으로, WHERE 조건의 AND나 OR 연산에 의해 데이터를 검색하는 방법이다.

비트맵 인덱스의 검색 원리
비트맵 인덱스는 레코드의 물리적인 ROW ID 값을 가지고 있지 않지만 ROW ID 값을 계산해서 추적할 수 있다.

B-TREE 인덱스와 비트맵 인덱스 비교
1) B-TREE : 인덱스의 크기가 크므로 상대적으로 소량의 데이터를 검색할 때 유리하다. TREE 알고리즘에 의해 손쉽게 입력, 수정, 삭제가 가능하다. AND 연산에는 좋은 성능을 나타내나, OR, != 등에서는 성능에 취약하다. 데이터 분포도가 높은 컬럼에 적합하다. OLAP에 유리
2) 비트맵 : 인덱스의 크기가 작으므로 상대적으로 대량의 데이터를 읽을 때 유리하다. 입력, 수정, 삭제는 전체 인덱스를 조정해야 하는 부담이 있다. 비트맵 인덱스는 비트 연산을 통해 or ,!= 등에서도 빠르게 처리할 수 있다. 데이터 분포도가 아주 낮은 컬럼에 적합하다. DSS에 유리

해싱 기법 적용
데이터의 빠른 접근을 위해 해시 알고리즘에 의해 테이블 정보를 읽는 것을 말한다.

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

클러스터링
클러스터링된 테이블이라 할지라도 인덱스를 지정하지 않으면 전체 스캔이 발생하게 되므로 클러스터링 인덱스를 생성도록 한다.

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

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

7.9. 데이터베이스 분산 설계
분산 데이터베이스 : 데이터베이스를 한 곳에 집중시켜 관리하던 중앙집중식 시스템 환경구축에서 빨라진 네트워크를 이용하여 데이터베이스를 여러 서버나 지역에 분산시켜 놓고 하나의 데이터베이스처럼 사용할 수 있게 만든 데이터베이스 체계

테이블 위치 분산
1) 테이블 위치 분산에서는 테이블의 구조는 변하지 않는다.
2) 테이블이 다른 데이터베이스에 중복되어 생성되지 않는다.
3) 설계된 테이블의 위치를 각각 다르게 위치시키는 것이다.

테이블 분할 분산
1) 각각의 테이블을 쪼개어 분산하는 방법이다.
2) 테이블을 로우 단위로 분리하는 수평분할과 컬럼 단위로 분할하는 수직분할이 있다.

테이블 복제 분산
1) 동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형이다.
2) 마스터 데이터베이스에서 테이블의 일부 내용만 다른 지역이나 서버에 위치시키는 부분복제가 있고, 마스터 데이터베이스의 테이블 내용을 각 지역이나 서버에 존재시키는 광역복제가 있다.
3) 데이터를 복제하는 방버은 데이터를 복제하는 데 많은 시간이 소요되고, 데이터베이스와 서버에 부하가 발생하므로, 보통 실시간 처리로 복사하는 것보다는 야간에 배치 작업으로 수행되는 경우가 많다.

테이블 요약 분산
1) 지역 간에 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우이다.
2) 테이블 구조가 동일하면서, 분산되어 있는 내용이 동일한 데이터를 이용하여 통합된 데이터를 산출하는 방식의 분석 요약과 분산되어 있으며 내용이 다른 데이터를 이용하여 통합된 데이터를 산출하는 방식의 통합 요약이 있다.
3) 일반 업무가 종료되는 야간에 테이블 요약 분석 작업을 수행한다.

8.1. 데이터베이스 구축을 위한 사전 준비
데이터베이스 구축을 위한 자료 : 데이터베이스 환경 정의서, 용량 산정서, 각종 오브젝트 설계서, 분산 환경 설계서

설계 단계에서 작성된 산출물 : ERD, 테이블 정의서, 인덱스 정의서, 뷰 정의서, 테이블스페이스 용량 산정서, 인덱스페이스 용량 산정서, 데이터파일 용량 산정서, 분산 설계서, 데이터베이스 환경 설계서, 시스템 파일 용량 설계서

8.2. 데이터베이스 생성
오라클에서 데이터베이스 생성하는 방법
1) 환경 파일 등을 스크립트에 지정하여 수작업으로 생성
2) ODCA 그래픽 툴을 사용하여 생성하는 방법

8.3. 테이블스페이스 생성
테이블스페이스 : 설계단계에서 정의한 각종 오브젝트가 실제 데이터 파일에 저장되기 위한 공간을 정의하는 데이터베이스 오브젝트

테이블스페이스 생성 확인
SELECT * FROM DBA_DATA_FILES

8.4. 사용자 및 역할과 권한 지정
사용자 및 역할과 권한 지정 절차
1) 사용자 생성 및 확인 (SELECT * FROM DBA_USERS)
2) 역할 생성 및 확인 (SELECT * FROM DBA_ROLES)
3) 사용자에게 권한 부여 및 확인 (SELECT * FROM DBA_ROLE_PRIVS)

8.5. 오브젝트 생성
오브젝트 생성 절차 :테이블 생성 -> 인덱스 생성 -> 뷰 생성

테이블 생성 확인
1) 테이블 확인
SELECT SEGMENT_NAME, ROUND(INITIAL_EXTENT/1024,0) INI_KB,
ROUND(NEXT_EXTENT/1024,0) NXT_KB, ROUND(BYTES/1024,0) TOT_KB,
PCT_INCREASE PCT, EXTENTS, TABLESPACE_NAME
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE';

2) PK 확인
SELECT a.table_name, b.constraint_name
FROM user_tables a, user_constraints b
WHERE a.table_name = b.table_name(+)
AND b.constraint_type(+) = 'P';

3) FK 확인
SELECT CONSTRAINT_NAME, R_CONSTRAINT_NAME,STATUS
FROM USER_CONSTRAINTS;

인덱스 생성
1) 인덱스 생성 확인
SELECT SEGMENT_NAME, ROUND(INITIAL_EXTENT/1024,0) INI_KB,
ROUND(NEXT_EXTENT/1024,0) NXT_KB, ROUND(BYTES/1024,0) TOT_KB,
PCT_INCREASE PCT, EXTENTS, TABLESPACE_NAME
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX';

뷰 생성
1) 뷰 생성 확인
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW';

8.6. 분산 환경 생성
오라클의 데이터베이스 간 통신을 위한 분산 환경 구성 시 전제 사항
1) 연결 대상인 데이터베이스 쪽 서버에 리스너가 기동되어 있어야 한다.
2) 연결하고자 하는 쪽에서는 오라클 클라이언트가 설치되어 있으면서 Tnsnames.ora 파일이 구성되어야 한다.
3) 로컬 데이터베이스와 원격 데이터베이스 간 데이터베이스 링크를 설정하면 분산 환경 구성이 완료된다.

스냅샷 테이블
스냅샷 테이블을 이용하여 원격 상의 테이블 내용을 동기화한다.

9.1. 데이터베이스 튜닝의 목적
튜닝을 수행하는 목적
1) 업무적인 환경과 시스템적 환경에 적합한 데이터베이스 파라미터를 설정한다.
2) 데이터베이스에 접근하는 SQL 문장은 가능한 한 디스크 블록에 최소로 접금하도록 한다.
3) 디스크 블록에서 한 번 읽은 데이터는 가능하면 메모리 영역에 보관하여 나중에 데이터를 빨리 가져오게 한다.
4) 모든 사용자의 SQL 문장은 공유가 가능하도록 대소문자 등 명명 표준을 준수하여 작성한다.
5) 잠금 발새이 최소가 되도록 한다.
6) 배치작업과 백업 작업 수행을 빠른 시간 안에 완료할 수 있어야 한다.

9.2. 데이터베이스 설계 단계에서 튜닝하기
설계단계 튜닝
1) 데이터 정합성을 유지할 수 있는 대책을 마련하고, 성능을 위해 필요하다면 테이블, 컬럼, 관계에 대해 반정규화를 적용한다.
2) 대용량 테이블의 경우 필요한 데이터에 대해서는 파티셔닝을 이용하여 테이블 분할을 검토한다.
3) 이력을 관리해야 하는 테이블에 대해서는 필요하다면 시작과 종료나 현재 진행상태 등을 명확하게 명시하여 SQL 문장의 실행 성능을 보장하도록 한다.
4) 테이블 접근 유형에 따라 전체 스캔 방식이나 B-TREE 인덱스, 비트맵 인덱스, 클러스터링과 해싱 적용 등을 고려한다.
5) 테이블이 조회를 주로 하는지 입력, 수정, 삭제 작업이 주로 발생하는지를 고려하여 적당한 인덱스 개수를 지정해야 한다.
6) 분산 데이터베이스를 적용했을 경우 원격 데이터베이스를 이용할 때 서능저하가 예산된다면 스냅샷을 이용한 복제 테이블 생성 등을 고려한다.
7) 공통적으로 관리하는 코드 데이터에 대한 접근이 빈번하다면 응용 애플리케이션의 메모리에 상주시키고 함수를 사용하여 코드 변환을 하도록 유도한다.
8) PK는 일반적으로 지정된 순서대로 인덱스를 생성하므로 KEY를 구성하는 컬럼 순서를 복합 컬럼 인덱스를 지정하는 규칙에 따라 나열한다.
9) FK에 대해서는 가급적 인덱스를 생성하여 전체 스캔이 발생하는 경우와 불필요하게 발생하는 잠금을 피하도록 한다.
10) SYSTEM 테이블스페이스에는 데이터를 관리하는 딕셔너리 정보만 포함하고, 일반 오브젝트는 저장하지 않도록 한다.
11) 테이블을 위한 테이블 스페이스와 인덱스를 위한 테이블 스페이스를 분리한다.
12) 롤백 세그먼트에 대한 경합을 피하기 위해 롤백 세그먼트를 여러 개로 구성한다.
13) 자주 수정되거나 변경 혹은 삭제되는 불안전한 데이터는 별도의 테이블 스페이스를 만들어 생성한다.

9.3. 데이터베이스 환경 튜닝
공유풀 영역 : SQL문장과 PL/SQL 문장이 저장되는 라이브러리 캐시와 데이터베이스에 관련된 각종 정보들을 저장하는 딕셔너리 캐시로 구분된다.

1) 라이브러리 캐시튜닝
1.1) SQL 문장을 재사용하는 비율 확인
SELECT GETHITRATIO FROM V$LIBRARYCACHE
WHERE namespace = 'SQL AREA'
(수치가 90% 이상 나와야 한다.)
1.2) MISS RATE 확인
SELECT SUM(reloads)/sum(pins) * 100 FROM V$LIBRARYCACHE
(MISS 비율이 1%보다 작게 나와야 한다.)
1.3) 캐시 HIT율 높이는 방법 : init.ora 파일에서 SHARED_POOL_SIZE를 늘려준다. SQL 문장에 대한 명명표준을 준수하여 동일한 문장에 대해 재파싱 작업이 일어나지 않도록 한다.
2) 딕셔너리 캐시튜닝
2.1) 딕셔너리 캐시 MISS 비율 확인
SELECT SUM(GETMISSES)/SUM(GETS) * 100
FROM V$ROWCACHE
(MISS 비율이 15%보다 작게 나와야 한다.)
2.2) MISS 비율 낮추는 방법 : SHARED_POOL_SIZE를 늘려준다.

데이터베이스 버퍼 캐시 튜닝
1) 버퍼 캐시 : SQL 문장을 실행하면 오라클은 디스크에서 데이터를 읽어들여 이를 데이터베이스 버퍼 캐시에 저장한다.
2) 버퍼 캐시 재 사용율
SELECT (1-(c.value / (a.value + b.value))) * 100
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.name = 'db block gets' AND b.name = 'consistent gets' AND c.name = 'physical reads'
(80% 이하로 값이 나올 경우 DB_BLOCK_BUFFER 크기를 조절한다.)

리두로그 버퍼 튜닝
1) 리두로그 버퍼 : 모든 SQL 문장이 실행될 때 이용하는 메모리 영역
2) 리두 할당 대기율
SELECT b.misses/b.gets * 100
FROM v$latchname a, v$latch b
WHERE a.name = 'redo allocation'
AND a.latch# = b.latch#
(1% 이상 나오면 리두로그가 발생할 때 경합이 발생한 경우이다.)
3) 리두로그 버퍼를 획득하기 위해 기다린 회수
SELECT value
FROM v$sysstat
WHERE name = 'redo log space requests'

디스크 I/O 튜닝
1) 데이터 파일에 대한 I/O 현황
SELECT name,phyrds, phywrts, phyblkrd, phyblkwrt
FROM v$filestat a, v$datafile b
WHERE a.file# = b.file#

2) 데이터베이스 디스크 I/O를 줄이기 위한 방법
2.1) 시스템 테이블 스페이스를 별도로 생성한다.
2.2) 임시 테이블 스페이스는 테이블과 인덱스의 테이블 스페이스와 분리하여 생성한다
2.3) 롤백 세그먼트와 온라인 리두로그 파일은 분리하여 생성한다.
2.4) 온라인 리두로그 파일과 아카이브 리두로그 파일은 분리하여 생성한다.
2.5) 롤백 세그먼트와 테이블과 인덱스 테이블 스페이스와 분리하여 생성한다.
2.6) 테이블의 테이블스페이스와 인덱스의 테이블스페이스는 분리하여 생성한다.
2.7) 리두로그 파일은 지속적으로 I/O가 발생하므로 I/O가 가장적은 디스크에 배치한다.
2.8) 테이블과 인덱스 테이블 스페이스 중에 I/O가 많은 테이블스페이스는 별도의 디스크에 배치한다.

로우 마이그레이션과 로우 체이닝
1) 로우 마이그레이션 : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블럭에 저장하지 못하고, 다른 블록의 빈 공간을 찾아 데이터를 저장하는 방식

2) 로우 체이닝 : 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고, 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태

3) 로우 마이그레이션 확인
analyze table [테이블명칭] compute statistics;

SELECT num_rows, chain_cnt
FROM dba_tables
WHERE table_name = '[테이블명칭]'
(로우 마이그레이션 건수가 많은 경우 PCTFREE를 증가시킨다.)

9.4. SQL 문장 튜닝
SQL 문장 작성 시 주의사항
1) 접근 경로에 대한 우선순위를 알고 있어야 한다
2) 옵티마이져 모드가 비용기반 모드인지 규칙기반 모드인지 알고 있어야 한다
3) 숫자 형식은 숫자 형식의 타입으로 문자 형식은 문자 형식 타입으로 비교한다.
4) WHERE 절 안에 비교하는 조건에 인덱스를 이용해야 할 경우 인덱스 컬럼을 기술한다.
5) 여러 개의 컬럼이 인덱스 하나로 지정된 경우 WHERE절에 모두 기술한다.
6) 여러 개의 컬럼이 인덱스 하나로 지정된 경우 앞쪽에 있는 컬럼을 모두 지정하여 인덱스 범위를 조회할 수 있게 한다.
7) 인덱스를 이용하고자 하는 컬럼에 대해 변형을 하지 않는다.
8) 인덱스 컬럼에 NULL 값을 사용하지 않는다.
9) 부정형으로 지정할 경우에는 인덱스를 사용하지 않는다.
10) OR을 사용하면 인덱스를 이용하지 않을 수 있다.
11) 불필요하게 DUAL 테이블을 이용하지 않는다.
12) WHERE 조건 절에 걸린 인덱스가 데이터를 20% 이상 반환하리라 예상되면 인덱스 스캔을 피한다.
13) 불필요하게 DBMS에서 제공하는 함수를 사용하지 않는다.
14) 인덱스가 걸려있는 컬럼에 대해 LIKE 형식으로 비교하는 경우에는 반드시 뒷쪽에 비교자(%)가 위치해야 하며, 앞쪽에 위치할 경우에는 인덱스를 이용하지 않는다.
15) 힌트를 적절하게 사용한다.

테이블에 있는 인덱스 컬럼 조회
SELECT TABLE_NAME,INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
ORDER BY TABLE_NAME,INDEX_NAME, COLUMN_POSITION

9.5. 기타 빈번하게 성능에 영향을 주는 요소
테이블에 일련번호(순번)을 증가시키는 방법
1) 두 개의 SQL 문장으로 처리 : SELECT -> INSERT
2) MAX(컬럼) + 1을 이용한 채번 방법 : INSERT .. SELECT MAX(컬럼) +1
3) 시퀀스 테이블을 이용하는 방법
4) 채번 테이블을 이용한 채번 방법 (신규 일련번호 채번 -> 본 테이블에 데이터입력 -> 채번 테이블 수정)
5) 인덱스 역방향 정렬을 이용한 채번 방법

인덱스 관리 방안
1) 인덱스 레벨 검사
ANALYZE INDEX [인덱스명] ESTIMATE STATISTICS;

SELECT BLEVEL, LEAF_BLOCKS FROM USER_INDEXES
WHERE INDEX_NAME = '[인덱스명]'

2) 인덱스 재생성
ALTER INDEX SYS_C001724 REBUILD;

10.1. 데이터베이스의 기동과 종료
데이터베이스 기동 단계
1) 데이터베이스 NOMOUNT 단계
2) 데이터베이스 MOUNT 단계
3) 데이터베이스 OPEN 단계

데이터베이스 NOMOUNT 단계
1) 데이터베이스의 파라미터 파일을 읽는다.
2) TRACE 파일 및 ALERT 파일을 연다. 생성 정보를 alert.log 파일에 기록한다.
3) 오라클 데이터베이스에서 이용되는 공유 메모리 구조인 SGA(SYSTEM GLOBAL AREA)를 할당한다.
4) 백그라운드 프로세스를 생성한다.
5) 데이터베이스는 생성된 메모리 구조화 프로세스들과 아직 연결이 되어 있지 않다.

데이터베이스 MOUNT 단계
1) 데이터베이스를 구성하는 정보가 모두 있는 컨트롤 파일의 정보를 읽어서 디스크에 데이터 파일이나 리두 로그 파일 등이 올바로 존재하는지 또한 각 파일이 현재 사용가능한지 점검한다.
2) 데이터베이스의 백업이나 복구 시점에 많이 이용된다.
3) 데이터 파일명 변경, 리두 로그 파일 추가, 삭제, 이름변경, 아카이브모드 옵션의 변경, 전체 데이터베이스의 복구, 백업하여 데이터베이스 재생성, 불완전 데이터베이스 복구 등이 가능하다.
4) MOUNT 단계는 일반 사용자가 데이터베이스를 접속할 수 없다.

데이터베이스 OPEN 단계
1) 컨트롤 파일에 기술된 모든 데이터 파일 및 리두 로그 파일 등을 연다.
2) 일반 사용자가 데이터베이스를 이용할 수 있는 상태이다.
3) 만약 종료 시점에 테이블스페이스가 오프라인이었다면 데이터베이스를 기동하여도 해당 테이블스페이스는 오프라인 상태이다.
4) 인스텐스가 데이터베이스를 연 이후 하나 이상의 롤백 세그먼트를 확보한다.

데이터베이스 종료
오라클의 데이터베이스 종료 방법
1) SHUTDOWN NORMAL : 정상적으로 데이터베이스를 종료하는 방법, 별도의 복구작업이 필요없음
2) SHUTDOWN IMMEDIATE : 데이터베이스를 관리할 때 가장 많이 사용하는 정지 옵션, COMMIT 되지 않은 문장은 ROLLBACK 시킴, 현재 사용자가 접속을 해제하는 것을 기다리지 않음, 잘못된 애플리케이션에 의해 부득이 데이터베이스를 정지하는데 사용, 다음 기동 시 별도의 복구 작업이 필요하지 않음
3) SHUTDOWN TRANSACTIONAL : 현재 작업중인 모든 트랜잭션이 정상적으로 수행되어 종료될때까지 기다린다. 별도의 복구작업이 필요없음
4) SHUTDOWN ABORT : 연결된 모든 세션을 강제로 종료, COMMIT되지 않은 문장은 ROLLBACK도 되지 않음, 백업과 복구 작업이 필요한 경우에 사용, 다음 기동 시 별도의 복구 작업이 필요함

10.3. 데이터베이스 운영 시 정기적으로 점검해야 할 사항
매일 점검해야 할 항목
1) 모든 데이터베이스 인스턴스가 기동되었는지 확인한다.
2) ALERT LOG를 확인한다.
3) 데이터베이스가 성공적으로 백업되었는지 확인한다.
4) 데이터베이스의 아카이브 로그 파일이 성공적으로 테이프에 백업되었는지 확인한다.
5) 데이터베이스 성능을 위해 충분한 리소스가 존재하는지 확인한다.
6) 데이터베이스 매뉴얼을 하루에 한 시간씩 탐독한다.

주 단위로 점검해야 할 항목
1) 잘못된 규칙에 의해 오브젝트가 존재하는지 확인
2) 보완관리가 잘 유지되고 있는지 확인
3) 클라이언트 쪽의 로그 파일(SQLNET.log)는 에러나 이슈 사항은 없는지 확인한다.
4) 모든 Alert 로그파일을 보관한다.

월 단위로 점검해야 할 항목
1) 데이터베이스의 비정상적인 현상이 발생하는지 확인한다.
2) 튜닝해야 할 부분이 있는지 확인한다.
3) I/O CONTENTION이 존재하는지 확인한다.
4) 단편화 현상이 존재하는지 확인한다.

10.3. 데이터베이스 문제 발생 및 해결 방법
ROLLBACK 세그먼트 EXTENT 에러
1) 예상했던 ROLLBACK 세그먼트의 크기보다 더 많은 작업량이 발생하거나 다른 트랜잭션이 롤백 세그먼트를 할당받지 못해 에러가 발생된다.
2) ROLLBACK 세그먼트 상태를 확인한다.
SELECT segment_name, extents, bytes/(1024*1024) FROM dba_segments
WHERE segment_type = 'ROLLBACK';

SELECT r.name, l.sid, p.sid,nvl(p.username,'NO TRANSACTION'), p.terminal
FROM v$lock l, v$session p, v$rollname r
WHERE l.sid=p.sid(+)
AND trunc(l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY 1;

3) 특정 ROLLBACK 세그먼트에 EXTENTION이 많이 발생하여 이를 RELEASE하려면 다음과 같이 한다.
ALTER ROLLBACK SEGMENT SHRINK;

SNAPSHOT TOO OLD 에러
1) 배치 작업 중 과도하게 ROLLBACK 세그먼트를 사용할 경우 발생한다.
2) 데이터베이스에 변경을 가하는 트랜잭션은 만혹, ROLLBACK 세그먼트는 크기도 작고, 개수도 적은 경우에 발생한다.
3) 개발자나, 운영자가 긴 트랜잭션을 발생시켰다면 ROLLBACK 세그먼트를 사용하지 않는 다른 시간에 작업을 수행할 수 있는지를 협의한다.
4) 긴 배치 트랜잭션을 기동하기 위해서는 별도의 큰 ROLLBACK 세그먼트를 생성하여, 해당 트랜잭션에서 사용하도록 한다.
4.1) 큰 ROLLBACK 세그먼트 생성
CREATE ROLLBACK SEGMENT vldb_rbs
STORAGE(INITIAL 100M NEXT 5M)
TABLESPACE vldb_rbs;
4.2) 평상시에는 OFFLINE 상태유지
ALTER ROLLBACK SEGMENT vldb_rbs OFFLINE;
4.3) 배치작업 수행시 ONLINE 상태로 변경
ALTER ROLLBACK SEGMENT vldb_rbs ONLINE;
4.4) 각 작업문장이나 세션에서 ROLLBACK 세그먼트 지정
SET TRANSATION USE ROLLBACK SEGMENT vldb_rbs;
4.5) 작업후 OFFLINE으로 변경
ALTER ROLLBACK SEGMENT vldb_rbs OFFLINE;

5) ROLLBACK 세그먼트 크기를 크게 하거나 개수를 늘리면, ROLLBACK 세그먼트가 도는 주기가 늦어져 덮어쓸 확률이 적어진다.
6) 권장하는 ROLLBACK 세그먼트의 개수는 최고 200 명의 사용자의 경우 동시에 4개의 트랜잭션이 데이터베이스에 발생한다고 가정할 때 한 개의 ROLLBACK 세그먼트가 필요하다.
7) 해당 시스템에 발생되는 동시 트랜잭션 양과 트랜잭션 길이에 따라 적절하게 선정하도록 한다.

테이블스페이스 EXTENT 에러
1) 용량 산정이 잘못되었거나 개발중 지나치게 많은 테스트 데이터가 존재할 경우 발생된다.
2) 테이블이나 인덱스의 테이블스페이스를 잘못 지정하여 특정 테이블스페이스에 데이터가 계속 누적될 경우에도 발생한다.
3) 해결을 위해 테이블스페이스의 여유 공간을 조회한다.
SELECT tablespace_name, max(bytes) "여유공간", sum(bytes) "전체공간"
FROM dba_free_space
GROUP BY tablespace_name;

4) 테이블스페이스를 사용하는 오브젝트를 조회한다.
SELECT tablespace_name, segment_name, bytes
FROM dba_segments
ORDER BY tablespace_name, segment_name

5) 테이블스페이스를 크기를 변경하거나 추가한다.
ALTER DATABASE DATAFILE [데이터파일명] RESIZE [MB 용량]M; -- 크기 변경
ALTER TABLESPACE [테이블스페이스명] ADD DATAFILE [데이터파일명]; -- 추가

잠금 문제
1) 테이블에 잠금이 설정되어 DML작업과 DDL작업이 수행되지 못하는 경우가 발생한다.
2) 현재 걸려 있는 잠금을 조회한다.
SELECT a.sid, a.serial#
FROM v$session a, v$lock b, dba_objects c
WHERE a.sid = b.sid
AND b.id1 = c.object_id
AND b.type='TM'
AND c.object_name='테이블명칭';

3) 세션을 KILL 시킨다.
alter system kill session 'sid, serial#';

[출처] 데이터베이스 설계와 구축|작성자 jeong129