:: 작업장

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 3절 조인 수행 원리

- 조인이란 두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다.

- SQL문에서 FROM 절에 두 개 이상의 테이블이 나열된 경우 조인이 수행된다.

- 조인 연산은 두 테이블 사이에 수행된다.

- 테이블을 조인할 때는 조인 단계별로 다른 조인 기법을 사용할 수 있다.

( 테이블 A와 테이블 B를 조인할 때는 NL Join 기법을 수행하고, 해당 조인결과를 테이블 C와 조인할 때는 Hash Join 기법을 사용할 수 있다.)

1. NL Join

- 중첩된 반복문과 유사한 방식으로 조인을 수행함.

- 반복문의 외부에 있는 테이블을 선행테이블 또는 Outer table이라고 하고, 반복문 내부에 있는 테이블을 후행 테이블 Inner 테이블 이라고한다.

FOR 선행테이블 읽음 -> 외부테이블

    FOR 후행테이블 읽음 -> 내부테이블

선행 테이블과 후행 테이블을 조인

- 먼저 선행 테이블의 조건에 맞는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.

(선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행한다)

- NL Join에서는 선행 테이블의 조건을 만족하는 행의 수가 많으면, 그 만큼 후행 테이블의 조인 작업은 반복 수행된다.

 그러므로 테이블 결과 행의 수가 적은 테이블을 조인 순서상 선행 테이블로 선택하는 것이 전체 일량을 줄일 수 있다.

- NL Join의 작업 방법

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음.

2) 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행

3) 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복 수행

ex) NL Join 수행 방식

1) 선행 테이블에서 조건에 맞는 첫번째 행을 찾음.

- 이경우 선행테이블에 주어진 조건을 만족하지 않을 경우 해당 데이터는 필터링됨.

2) 선행테이블의 조인 키를 가지고 후행 테이블에 조인 키가 존재하는지 찾으러감

- 조인시도

3) 후행 테이블의 인덱스에 선행 테이블의 조인키가 존재하는지 확인

- 선행 테이블의 조인 값이 후행 테이블에 존재하지 않으면 선행 테이블 데이터는 필터링됨

(더이상 조인 작업을 진행할 필요가 없음)

4) 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 엑세스

- 인덱스 스캔을 통한 테이블 엑세스

후행 테이블에 주어진 조건까지 모두 만족하면 해당 행을 출력버퍼에 넣음.

추출버퍼는 SQL문의 실행결과를 보관하는 버퍼로서 일정 크기를 설정하여 추출버퍼에 결과가 모두 차거나 더 이상 결과가 없어서 추출버퍼를 채울것이 없으면 결과를 반환한다.

추출버퍼는 운반단위, Array Size, Prefetch size라고도 한다.

NL Join 기법은 조인이 성공하면 바로 조인결과를 사용자에게 보여줄 수 있다. 그래서 결과를 가능한 빠르게 보여줘야하는 온라인 프로그램에 적당한 조인 기법이다.

2. Sort Merge Join

- 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다.

- NL Join은 랜덤엑세스 방식으로 데이터를 읽는 반면 Sort Merge 조인은 주로 스캔 방식으로 데이터를 읽는다.

- Sort Merge Join은 랜덤엑세스로 NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되는 조인기법이다.  그러나 Sort Merge Join은 정렬할 데이터가 많아 메모리에서 모든 정렬을 수행하기 어려운경우 임시영역을 사용하기 때문에 성능이 떨어질 수 있다.

- 일반적으로 대량의 데이터 조인 작업에서 정렬작업을 필요로 하는 Sort merge join 보다는 CPU 작업 위주로 처리하는 Hash Join이 성능상 유리하다.

- Sort Merge Join은 Hash Join과는 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점이 있다.

- Sort Merge Join은 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인기법. 

- 조인할 테이블 중에서 이미 앞 단계의 작업을 수행하는 도중에 정렬 작업이 미리 수행되었다면 조인을 위한 정렬 작업은 발생하지 않을 수 있다.

- Sort Merge Join 동작

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음.

2) 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행.

1 ~ 2번 작업을 선행 테이블 조건에 만족하는 모든 행에 대해 반복 수행

3) 후행 테이블에서 주어진 조건에 맞는 행을 찾음.

4) 후행 테이블의 조인 키를 기준으로 정렬 작업 수행

3~4번 작업을 후행 테이블의 조건에 만족하는 모든 행에 대해 반복 수행

5) 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출 버퍼에 넣음.

3. Hash Join

- 해슁 기법을 이용하여 조인을 수행한다.

- 조인을 수행할 테이블의 조인칼럼을 기준으로 해쉬 함수를 수행하여 소로 딩알한 해쉬값을 갖는 것들 사이에 실제 값이 같은지 비교하면서 조인을 수행한다.

- Hash Join은 NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 문제점인 정렬작업의 부담을 해결 위한 대안으로 등장하였다.

- HASH Join은 칼럼의 인덱스를 사용하지 않기 때문에 조인칼럼의 인덱스가 존재하지 않아도 사행 가능하다.

- Hash Join은 해쉬함수를 사용해서 Join을 수행하기 때문에 '='로 수행하는 조인 즉 동든 조인에서만 사용할 수 있다.

- 해쉬함수가 적용될 때 동일한 값은 항상 같은 값으로 해슁됨이 보장된다.

- 해쉬함수를 적용할 때 보다 큰 값이 항상 더 큰 값으로 되고 작은값이 항상 작은 값으로 된다는 보장이 없으므로 동등 조인에만 적용가능하다.

- 해쉬조인은 조인작업을 수행하기 위해 해쉬테이블을 메모리에 생성해야 한다.

- 해쉬조인을 할 때는 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다. (선행테이블의 결과를 완전히 메모리에 저장할 수 있다면 임시 영역에 저장하는 작업이 발생하지 않기 때문)

- Hash Join에서는 선행 테이블을 이용하여 먼저 해쉬 테이블을 생성한다고 해서 선행테이블을 Build Input 이라고도 하며, 후행 테이블은 만들어진 해쉬테이블에 대해 해쉬값의 존재여부를 검사한다고 해서 Prove Input 이라고도 한다.

- Hash Join 동작

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음

2) 선행 테이블의 조인키를 기준으로 해쉬 함수를 적용하여 해쉬테이블 생성.

-> 조인칼럼과 SELECT  절에서 필요로 하는 칼럼도 함꼐 저장됨.

1 ~ 2번 작업을 선행테이블의 조건을 만족하는 모든 행에 대해 반복 수행

3) 후행 테이블에서  주어진 조건에 맞는 행을 찾음.

4) 후행 테이블의 조인키를 기준으로 해쉬함수를 적용하여 해당 버킷을 찾음.

-> 조인키를 이용해서 실제 조인될 데이터를 찾음

5) 조인에 성공하면 추출 버퍼에 넣음

3~5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 2절 인덱스 기본

1.인덱스 특징과 종류

- 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념.

- 테이블을 기반으로 선택적으로 생성할 수 있는 구조.

- 인덱스의 기본적인 목적은 검색 기능의 최적화이다.

가. 트리기반 인덱스

- DBMS에서 가장 일반적인 인덱스는 B-Tree 인덱스이다.

* B Tree 인덱스

- B Tree 인덱스는 브랜치블록과 리프블록으로 구성된다. 브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록 이라고 한다. 브랜치 블록은 분기를 목적으로 하는 블록이다.

- 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

- 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 데이터를 가지고있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성되어 있다.

- 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.(데이터의 값이 동일하면 레코드 식별자의 순서로 저장됨)

- 리프 블록은 양방향 링크를 가지고 있다.  이것을 통해 오름 차순과 내림 차순 검색을 쉽게 겁색할 수 있다.

- B Tree 인덱스는 =로 검색하는 일치 검색과 BETWEEN, >, 등과 같은 범위 검색에 모두 적합한 구조이다.

인덱스에서 원하는 값을 찾는 과정.

1단계: 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽포인터로 이동

2단계: 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동

3단계: 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

이과정을 리프 블록을 찾을 때까지 반복한다. 리프 블록에서 찾고자 하는 값이 존재하면 해당 값을 찾을 것이고, 없으면 해당 값은 존재하지 않아 검색에 실패하게된다.

인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다. 

인덱스 구성칼럼은 동일하지만 순서가 서로 다르면 생성가능하다 ( JOB + SAL, SAL + JOB 가능)

오라클에서 트리 기반 인덱스에는 B-Tree 인덱스 이외에도 비트맵 인덱스, 리버스키 인덱스, 함수기반 인덱스 등이 존재한다.

나. SQL Server의 클러스터형 인덱스

- SQL Server의 인덱스 종류는 저장 구조에 따라 클러스터형 인덱스와 비클러스터형 인덱스로 나뉜다.

클러스터형 인덱스는 두 가지 중요한 특징이있다.

1. 인덱스의 리프 페이지가 곧 데이터 페이지다.

- 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다

(인덱스 키 칼럼과 나머지 칼럼을 리프페이지에 같이 저장하기 때문에 테이블을 랜덤 엑세스할 필요가 없다.)

- 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.

2. 리프 페이지의 모든 로우는 인덱스 키 칼럼순으로 물리적으로 정렬되어 저장된다.

- 테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

- 리프블록에 인덱스 키 칼럼 외에도 테이블의 나머지 칼럼이 모두 함께 있다.

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

- 전체 테이블 스캔 방식으로 데이터를 검색하는 것은 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식으로 검색한다.

- Oracle의 경우 검색조건에 맞는 데이터를 찾기 위해 테이블의 고수위마크(HWM) 아래의 모든 데이터를 읽는다.

- 고수위 마크는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치를 의미한다.

- 전체 테이블 스캔 방식으로 데이터를 검색할 때 고수위 마크까지의 블록 내 모든 데이터를 읽어야 하기 때문에 모든 결과를 찾을 때까지 시간이 오래 걸릴 수 있다.

옵티마이저가 Full table scan을 선택하는 이유는 일반적으로 다음과 같다.

1) SQL문에 조건이 존재하지 않는 경우

- SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든데이터가 답이 된다는 의미이므로 테이블의 모든 블록을 읽으면서 무조건 결과로서 반환한다.

2) SQL문의 주어진 조건에 사용가능한 인덱스가 존재하지 않는 경우.

- 사용가능한 인덱스가 존재하지 않는다면 데이터를 엑세스할 수 있는 방법은 모든 데이터를 읽으면서 주어진 조건을 만족하는지를 검색하는 방법 뿐이다.

- 또한 사용가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변경한 경우에도 인덱스를 사용할 수 없다.

3) 옵티마이저의 취사 선택

조건을 만족하는 데이터가 많을 경우 결과 데이터를 추출하기위해 테이블의 대부분 블록을 엑세스 한다고 옵티마이저가 판단하면 조건에 맞는 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

4) 그 밖의 경우

- 병렬 처리 방식으로 처리할 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우 전체 테이블 스캔 방식으로 데이터를 읽을 수 있다.

나. 인덱스 스캔

- 인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 엑세스 기법.

- 인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있다. (A+B 구성의 인덱스일 경우 A 칼럼으로 정렬되고 A칼럼 값이 동일할 경우 B 칼럼으로 정렬된다. B 칼럼까지 동일한경우 RID로 정렬된다.)

- 인덱스 순서와 동일한 정렬 순서를 사용자가 원하는 경우 정렬작업을 수행하지 않을 수 있다.

1) Index Unique Scan (인덱스 유일 스캔)

유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식. 유일 인덱스는 중복을 허락하지 않는 인덱스로 유일인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.

인덱스 유일 스캔은 유일 인덱스 구성칼럼에 대해 모두 '='로 값이 주어진 경우에만 가능한 인덱스 스캔방식이다.

2) Index Range Scan

- 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

- 유일 인덱스의 구성 컬럼 모두에대해  '='로 값이 주어지지 않는 경우와 비유일 인덱스를 이용하여 모든 엑세스 방식은 인덱스 범위 스캔 방식으로 데이터를 엑세스 하는것이다.

3) 인덱스 역순 범위스캔은 리프블록의 양방향 링크를 통해 내림차순으로 데이터를 읽는 방식. 이 방식을 이용하여 Max Value를 쉽게 찾을 수 있다.

다. Full Table scan과 Index Scan 방식의 비용.

- 인덱스 스캔 방식은 사용가능한 적절한 인덱스가 있을 경우에만 이용할 수 있는 스캔방식 이지만 전체 테이블 스캔 방식은 인덱스의 존재 유무와 상관없이 항상 이용가능한 스캔방식.

- 인덱스 스캔은 인덱스에 존재하는 레코드 식별자를 이용해서 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다. 그러므로 불필요하게 다른 블록을 더 읽을 필요가 없다.

 따라서 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

- 전체 테이블 스캔은 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

* 데이터를 찾을 때 여러 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리할 수 있다.

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 1절SQL 옵티마이져와 실행계획

1.옵티마이져

- 옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할을 함

- 최적의 실행방법을 실행계획이라고함.

- 다양한 실행 방법들 중 최적의 실행 방법을 결정하는 것이 바로 옵티마이져의 역할.

- 실행방법을 결정하는 방법에 따라 비용기반 옵티마이져와 규칙기반 옵티마이져로 나눌 수 있다.

가. 규칙기반 옵티마이져.

- 규칙기반 옵티마이져는 규칙(우선순위)을 가지고 실행계획을 생성함.

- 규칙기반 옵티마이져가 실행계획을 생성할 때 참조하는 정보에는 SQL문을 실행하기위해 이용가능한 인덱스의 종류, SQL 문에서 사용하는 연산자의 종류, SQL문에서 사용하는 객체의 종류 등이 있다. 이러한 정보에 따라 우선순위가 정해져 있고, 이 우선순위를 기반으로 실행계획을 생성한다.

규칙1. Single row by rowid : 

ROWID를 통해서 테이블에서 하나의 행을 엑세스 하는 방법. 하나의 행을 엑세스하는 가장 빠른 방법.

규칙4. Single row by unique or primary key

유일 인덱스를 통해서 하나의 행을 엑세스 하는 방식. 인덱스를 먼져 엑세스 하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행을 엑세스한다.

규칙8. Composite index

복합인덱스에 동등 연산자 조건으로 검색하는 경우.

예를들어 A+B 칼럼으로 복합 인덱스가 새엇ㅇ되어 있을 때 WHERE A=1 AND B=1 형태로 검색하는 방식.

복합인덱스 사이의 우선순위 규칙은 인덱스 구성 칼럼의 개수가 더 많고 해당 인덱스의 모든 구성 칼럼에 대해 '='로 값이 주어질 수록 우선순위가 더 높다.

ex)A+B 와 A+B+C로 구성된 인덱스가 각각 존재할 때 a,b,c 칼럼에 대해 모두 =로 값이 주어진다면 A+B+C인덱스의 우선순위가 높으며, A,B 칼럼에만 = 로 값이 주어진다면 A+B+C 인덱스가 우선순위가 높다.

규칙9. Single Column Index

단일 칼럼 인덱스에 = 조건으로 검색하는 경우.

규칙 10. BOUNDED RANGE SEARCH ON INDEXED COLUMNS

인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색하는 방식. BETWEEN, LIKE 등의 연산자가 있다.

규칙11. UNBOUNDED RANGE SEARCH ON INDEXED COLUMNS

인덱스가 생성되어 있는 칼럼에 한쪽 버뮈만 한정하는 형태로 검색하는 방식. >, >=,<,<= 등이 있다. 만약 A 칼럼에 인덱스가 생성되어 있고, A > '10' 또는 A <'20' 형태로 검색하는 방식.

규칙 15. FULL TABLE SCAN

전체 테이블을 엑세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출.

* 규칙기반 옵티마이저는 인덱스를 이용한 엑세스 방식이 전체 테이블 엑세스 방식보다 우선순위가 높다.

* 규칙기반 옵티마이저가 조인 순서를 결정할 때는 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준이다.

1) 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재한다면 설명한 규칙에 따라 우선순위가 높은 테이블을 선행테이블로 선택한다. 

2)한쪽 조인칼럼에만 인덱스가 존재하는 경우에는 인덱스가 없는 테이블을 선행 테이블로 선택해서 조인을 수행한다. 

3) 조인 칼럼에 모두 인덱스가 존재하지 않는 경우에는 FROM 절의 뒤에 나열된 테이블을 선행 테이블로 선택한다.

4) 만약 조인테이블의 우선순위가 동일할 경우 FROM 절에 나열된 테이블의 역순으로 선행 테이블을 선택한다.

* 규칙 옵티마이저의 조인 기법

양쪽 조인 칼럼에 모두 인덱스가 없는 경우 Sort Merge Join을 사용하고.

둘 중 하나라도 조인 칼럼에 인덱스가 있을 경우 일반적으로 NL Join을 사용한다.

나. 비용기반 옵티마이져

- 비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.

- 비용기반 옵티마이저는 비용을 예측하기 위해서 규칙기반 옵티마이저가 사용하지않는 테이블, 인덱스, 칼럼등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다.

- 통계정보가 없는 경우 비용기반 옵티마이저는 정확한 비용예측이 불가능해서 비효율적인 실행계획을 생성할 수 있다.

- 비용기반 옵티마이저는 FULL TABLE SCAN이 INDEX SCAN 보다 유리하다고 판단할 경우 FULL TABLE SCAN을 수행한다.

* 비용기반 옵티마이저 구성

비용기만 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예축기 등의 모듈로 구성되어있다.

1) 질의변환기

사용자가 작성한 SQL 문을 처리하기에 보다 용이한 형태로 변환하는 모듈.

2)대안 계획 생성기

돌일한 결과를 생성하는 다양한 대한 계획을 생성하는 모듈이다.

대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해서 생성된다.

( 많은 대안계획의 생성은 성능에 저하를 유발시킬 수 있으므로 대안 계획의 수를 제약하는 다양한 방법을 사용한다.)

3) 비용예측기

대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈이다.

대안계획의 정확한 비용을 예측하기 위해서 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다.

2. 실행계획

- SQL이 요구한 사항을 처리하기 위한 처리와 방법을 의미한다.

- 실행계획 생성한다는 것은 SQL을 어떤 순서로 처리할 지 결정하는 작업이다.

- 옵티마이저는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아준다.

- 실행계획을 구성하는 요소에는 조인순서, 조인기법, 엑세스기법, 최적화정보, 연산 등이 있다.

1) 조인순서: 조인 작업을 수행할 때 참조하는 테이블의 순서이다.

2) 조인기법: 두 개의 테이블을 조인할 때 사용하는 방법으로 NL Join, Hash Join, Sort Merge Join 등이 있다.

3) 엑세스 기법: 인덱스를 이용하여 테이블을 엑세스하는 인덱스 스캔과 테이블 전체를 모두 읽으면서 조건을 만족하는 행을 찾는 풀 테이블 스캔이 있다.

4) 최적화정보: 옵티마이저가 실행계획의 각 단계마다 예상되는 비용을 표시한것.

cost: 상대적인 비용 정보

card: 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집합 건 수

bytes: 결과집합이 차지하는 메모리 양을 바이트로 표시한 것.

이러한 정보들은 통계정보를 바탕으로 옵티마이저가 계산한 예상치이다.

5) 연산: 여러 가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업이다.

연산에는 조인 기법, 엑세스기법, 필터, 정렬, 집계, 뷰 등 다양한 종류가 존재한다.

3. SQL 처리 흐름도.

- SQL의 내부적인 처리절차를 시각적으로 표현한 도표이다.

- 실행계획을 시각화한 것이다.

조인순서는 TAB1 -> TAB2 일 때 TAB1을 Outer Table 또는 Driving Table 이라고 하고, TAB2를 Inner Table 또는 Lookup Table 이라고 한다.

[SQLD] 2과목 SQL 기본및 활용 - 1장 6절 함수

1. 내장함수 개요

- 함수는 벤더에서 제공하는 함수인 내장함수와 사용자가 정의할 수 있는 사용자 정의함수로 구분할 수 있다.

- 내장함수는 함수의 입력값이 단일행 값이 되는 단일행 함수와 여러개의 값이 입력되는 다중행 함수로 구분할 수 있다.

- 다중행 함수는 집계함수, 그룹함수, 윈도우함수로 나눌 수 있다.

* 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1관계라는 특징을 가지고있다.

1) 단일행 함수의 종류 ( 오라클 / SQL server)

* 단일행 함수의 중요한 특징.

- SELECT, WHERE, ORDER BY 절에 사용 가능하다.

- 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.

- 여러 인자를 입력해도 단 하나의 결과만 리턴한다.

- 함수의 인자로 상수, 변수, 표현식이 가능하며 하나의 인수를 가지는 경우도 있지만 여려개의 인자를 가질 수 있다.

- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다.

2. 문자형 함수.

- 문자 데이터를 매개변수로 받아들여서 문자나 숫자값의 결과를 돌려주는 함수이다.

몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.

* 문자형 함수 적용 시 리턴되는 결과

3. 숫자형 함수

- 숫자데이터를 입력받아 처리하고 숫자를 리턴한다.

- 숫자형 함수 적용되었을 때 리턴되는 값

4. 날짜형 함수

- 날짜형 함수는 DATE 타입의 값을 연산하는 함수

- 단일행 날짜형 데이터 연산

ex) 오라클

SELECT ENAME, HIREDATE,

TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) YYYY,

TO_NUMBER(TO_CHAR(HIREDATE,'MM')) MM,

TO_NUMBER(TO_CHAR(HIREDATE,'DD')) DD

FROM EMP;

ENAME   HIREDATE   YYYY MM   DD

---------- --------- ---------- ---------- ----------

seungho    17-DEC-80   1980 12   17

ALLEN   20-FEB-81   1981  2   20

WARD   22-FEB-81   1981  2   22

JONES   02-APR-81   1981  4     2

ex) SQL server 함수

SELECT ENAME, HIREDATE,

DATEPART(YEAR, HIREDATE) 입사년도,

DATEPART(MONTH, HIREDATE) 입사월,

DATEPART(DAY, HIREDATE) 입사일

FROM EMP;

ex) SQL server 함수2

SELECT ENAME, HIREDATE

YEAR(HIREDATE) 입사년도,

MONTH(HIREDATE) 입사월,

DAY(HIREDATE) 입사일

FROM EMP;

5. 변환형 함수

- 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수

* 암시적 데이터 유형변환의 경우 성능 저하가 발생할 수 있으며 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어서 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환방법을 사용하는것이 바람직함.

EX) 오라클

EX) SQL server

EX) 금액을 달러와 원화로 표기

6. CASE 표현

- IF -THAN ELSE 논리와 유사한 방식

- ANSI/ISO SQL 표준에서는 CASE Expression이라고 표시되어 있는데 함수와 같은 성격을 갖는다.

- ORACLE의 Decode 함수와 같은 기능을 한다.

SELECT ENAME

               CASE WHEN SAL > 2000

                          THEN SAL

                          ELSE 2000

               END REVISED_SALARY

FROM EMP;

- SIMPLE_CASE_EXPRESSION

 * CASE 조건 뒤에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 판단하는 문장으로 (=) 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용할 수 있는 장점이 있다.

CASE 

   EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR

   ELSE 표현절

END

- SEARCHED_CASE_EXPRESSION

*SEARCHED_CASE_EXPRESSION 다음에 칼럼이나 표현식을 표시하지않고 다음 WHEN 절에서 EQUI(=), >, >= , <, <= 을 이용한 조건절을 사용하여 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 저장할 수 있는 장점이 있다.

CASE 

    WHEN CONDITION THEN RETURN_EXPR

    ELSE  표현절

END

7. NULL 관련 함수.

가. NVL / ISNULL 함수.

* NULL 특성

- 넓값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.(0은 숫자이며 공백은 문자이다.)

-테이블을 생성할 때 NOT NULL 조건 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널값을 표현할 수 있다.

- 널 값을 포함하는 연산의 경우 결과값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는것과 같은 이치.

- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로 문자유형 데이터인 경우 블랭크보다는 'x'같이 해당 시스템에서 의미없는 문자로 바꾸는 경우가 많다.

- NVL(NULL 판단대상컬럼, 'NULL 일 때 대체값')

EX)

SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음')

FROM PLAYER

WHERE TEAM_ID='K08';

- ISNULL(SQL server)

EX)

SELET PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음')

FROM PLAYER;

- CASE를 이용한 표현

EX)

SELECT PLAYER_NAME 선수명, POSITION

              CASE WHEN POSITION IS NULL THEN '없음'

              ELSE POSITION

              END AS 포지션

FROM PLAYER;

*  NULL값의 비교연선은 IS NULL!!!!

Q. 급여와 커미션을 포함한 연봉을 계산하라.

A.

SELECT ENAME, SAL, COMM, (SAL*12)+COMM 연봉A, SAL*12+NVL(COMM,0) 연봉B

FROM EMP;

* NVL 함수를 사용하지 않은 연봉A의 결과값은 NULL과 더하기 연산을 하기 때문에 NULL이 나온다.

나. NULL과 공집합.

- 일반적인 NULL / ISNULL 함수 사용.

1) 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저를 출력한다.

SELECT MGR FROM EMP WHERE ENAME='SCOTT';

       MGR

------------------

      7566

2 )매니저에 NULL이 있는 KING의 매니저를 출력.

SELECT MGR FROM EMP WHERE ENAME='KING';

       MGR

------------------

3) 매니저가 NULL인 경우 빈칸이 아닌 9999로 표현

SELECT MGR, NVL(MGR,9999) FROM EMP WHERE ENAME='KING';

 MGR     NVL(MGR,9999)

---------- -------------

              9999

- 공집합의 NVL / ISNULLL 함수 사용

* SELECT 1 FROM DUAL WHERE 1=2;   (공집합 발생시키는 쿼리.)

1) 공집합을 발생시키기 위해 사원 테이블에 존재하지않는 'HANI' 라는 이름으로 데이터 검색

SELECT MGR FROM EMP WHERE ENAME='HANI';

no rows selected

2) NVL / ISNULL 함수를 이용하여 9999로 변경 시도

SELECT NVL(MGR,9999) FROM EMP WHERE ENAME='HANI';

no rows selected

* 인수의 값이 공집합인 경우에는 NVL, ISNULL을 사용해도 역시 공집합이 출력됨.

* NVL 함수는 NULL값을 다른 대상으로 바꾸는 함수지 공집합을 대상으로 하지 않는다.

3) 적절한 집계함수를 찾아서 NVL 함수 대신 적용.

SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='HANI';

       MGR

----------

* NULL값이 출력됨.

* 다른 함수와 달리 집계함수와 Scalar Subquery의 경우 인수의 결과값이 공집합이 null 인 경우에도 null을 출력한다.

4) 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 9999를 출력하게한다.

SELECT NVL(MAX(MGR),9999) MGR FROM EMP WHERE ENAME='HANI';

       MGR

----------

      9999

* 공집합의 경우 NVL 을 사용해도 공집합이 출력되므로 그룹함수와 NVL 함수를 같이 사용해서 처리한다.

다. NULLIF

- NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL 출력 다르면 EXPR1 리턴한다.

- 특정 값을 NULL로 대채하는 경우에 유용함.

1) NULLIF 함수 사용

SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NULLIF

FROM EMP;

2) CASE문 사용

SELECT ENAME, EMPNO, MGR, 

              CASE WHEN MGR=7698 THEN NULL

              ELSE MGR

              END NULL_CASE

FROM EMP;

라. 기타 NULL 관련함수 COALESCE

- COALESCE 함수는 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR를 나타낸다. 만약 모든 EXPR이 NULL이라면 NULL을 리턴한다.

SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL) COAL

FROM EMP;

* CASE로 표현

SELECT ENAME, COMM, SAL,

CASE WHEN COMM IS NOT NULL THEN COMM

           ELSE  (CASE WHEN SAL IS NOT NULL THEN SAL

                                                                              ELSE NULL

                        END)

            END COAL

FROM EMP; 

[SQLD] 2과목 SQL 기본및 활용 - 1장 5절 WHERE

1. WHERE 조건절 개요

- 사용자들은 자신이 원하는 자료만을 검색하기 위해서 SQL문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있다.

- WHERE절에는 두 개 이상의 테이블에 대한 조인 조건을 기술하거나 결과를 제한하기위한 조건을 기술할 수도 있다.

- WHERE절에 조건이 없는 경우 FULL TABLE SCAN이 발생하므로 SQL튜닝의 1차적인 검토대상이 된다.

2. 연산자의 종류

* 연산자 우선순위 (처리순서)

1) 괄호를 묶은 연산이 가장 먼저 처리된다.

2) 연산자들 중에는 부정 연산자가 먼저 처리되고

3) 비교연산자( =, > ,>=, <, <=), SQL 비교연산자 (BETWEEN a AND b, IN (list), LIKE, IS NULL) 처리되고

4) 논리연산자 중에는 AND, OR 순으로 처리된다.

Q. K 리그 일부 선수들의 이름과 포지션, 백넘버를 알고싶다.

조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에 포지션이 미드필드이면서 키는 170이상 180 이하여야한다.

A.

SELECT PLAYER_NAME, POSITION, BACKNO

FROM PLAYER

WHERE (TEAMNAME ='삼성블루윙즈' 

        OR TEAMNAME='전남드래곤즈')

      AND POSITION = 'MF'

      AND HEIGHT 170 BETWEEN 180 ;

3. 비교연산자.

* 문자 유형 비교 방법

4. SQL 연산자

1) IN 연산자 사용

SELECT PLAYER_NAME 

FROM PLAYER

WHERE TEAM_ID IN ('K02', 'K07');

Q. 사원테이블에서 JOB이 MANAGER 이면서 20번 부서에 속하거나 JOB이 CLERK 이면서 30번 부서에 속하는 사원의 정보를 IN 연산자를 이용하여 출력하라

A1.

   SELECT * 

   FROM EMP

   WHERE (JOB,DEPTNO) IN (('MANAGER',20),('CLERK',30));

     EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7566 JONES      MANAGER       7839 02-APR-81   2975    20

      7900 JAMES      CLERK       7698 03-DEC-81     950    30

* 이렇게 조회하면 결과가 다르므로 용도에 맞춰 사용한다.

SELECT * 

FROM EMP

WHERE JOB IN ('MANAGER','CLERK')

      AND  DEPTNO IN (20,30);

EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7566 JONES      MANAGER       7839 02-APR-81   2975    20

      7698 BLAKE      MANAGER       7839 01-MAY-81   2850    30

      7876 ADAMS      CLERK       7788 23-MAY-87   1100    20

      7900 JAMES      CLERK       7698 03-DEC-81     950    30

2) LIKE 연산자 사용

-  LIKE 연산자에서는 와일드카드 사용이 가능하다.

3) IS NULL연산자

* NULL값의 특성

- NULL값과 수치연산은 NULL을 리턴한다.

- NULL과 비교 연산은 FALSE를 리턴한다.

- 어떤 값과 비교할 수도 없으며 특정 값보다 크다, 적다라고 표현할 수 없다.

 * NULL값의 비교연산은 IS NULL, IS NOT NULL을 사용해야 원하는 결과를 얻을 수 있다.

4) ROWNUM, TOP 사용

- 오라클 ROWNUM 사용

SQL 처리 결과 집합과 각 행에 대해 임시로 부여되는 일련번호로 테이블이나 집합에서 원하는 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다.

- SQL server TOP 사용

TOP (Expression) [PERCENT] [WITH TIES]

- Expression : 반환할 행의 수를 지정하는 숫자.

- PERCENT : 쿼리 결과 집합에서 처음 Expresseion%의 행만 반환됨.

- WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.

* SQL 문장에서 ORDER BY 절이 사용되지않으면 ROWNUM과 TOP절은 같은 기능을 하지만 ORDER BY 절이 사용되면 기능상 차이를 보인다.

[SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL

1. 데이터 유형

- 특정 칼럼을 정의할 때 선언한 데이터 유형은 그 칼럼이 받아들일 수 있는 자료의 유형을 규정한다.

* VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다.

* CHAR에서는 문자열을 비교할 때 공백을 채워서 비교하는 방법을 사용한다.

- 공백 채우기 비교에서는 우선 짧은쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한 후 앞에서부터 비교한다.(공백만 다른 문자열은 같다고 판단함.)

* VARCHAR

- 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.

ex) 

CHAR 유형:  'AA' = 'AA    '

VARCHAR 유형 'AA' != 'AA     '

2. CREATE TABLE

가. 테이블과 칼럼 정의

- 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본칼럼으로 지정한다.

- 기본키는 단일 칼럼이 아닌 여러개의 칼럼으로 만들어질 수 있다.

- 테이블과 테이블간에 정의된 관계는 기본키와 외부키를 활용해서 설정하도록 한다.

나. CREATE TABLE

- 테이블을 생성하는 구문.

- 테이블 생성 시 주의할 규칙.

1) 테이블명은 객체를 의미할 수 있는 적절한 이름으로 사용한다. 가능한 단수형을 권고.

2) 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

3) 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

4) 테이블 이름을 지정하고 각 칼럼들은 괄호로 묶어서 지정한다.

5) 각 칼럼들은 콤마로 구분되고, 테이블 생성명의 끝은 ;로 끝난다.

6) 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.

7) 칼럼 뒤에 데이터 유형은 꼭 지정되어야한다.

8) 테이블명과 칼럼명은 반드시 문자로 시작해야 하고 벤더별로 길이에 대한 한계가 있다.

9) 벤더에서 사전에 지정한 예약어는 사용할 수 없다.

10) A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

ex) 테이블명이 잘못 정의된 사례

10_player

star-player

- 규칙에 맞게 선수 테이블 생성

1) ORACLE

2) SQL SERVER

- 테이블 생성시 대소문자 구분하지 않는다.

- 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

- DATETIME 데이터 유형에는 별도의 크기를 지정하지 않는다.

- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야한다.

- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

* 제약조건은 PLAYER_NAME, TEAM_ID 컬럼의 데이터 유형 뒤에 NOT NULL을 정의한 사례와 같은 칼럼레벨 정의방식과, PLAYER_PK PRIMARY KEY, PLAYER FK FOREIGN KEY 사례처럼 테이블 마지막에 모든 제약조건을 기술하는 테이블레벨 방식이 있다.

다. 제약조건 (CONSTRAINT)

- 제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터 무결성을 유지하기 위한 데이터베이스의 보편적 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.

- 제약조건의 종류

- 조건에 따른 테이블 생성

1) ORACLE

2)SQL server

라. 생성된 테이블 구조 확인.

오라클 : DESC 테이블명; 또는 DESCRIBE 테이블명;

SQL SEVER :  exec sp_help 'dbo.테이블명'

                        go

마. SELECT 문장을 통한 테이블 생성.

- Create Table ~  As select ~

해당 방법을 이용할 경우 칼럼별로 데이터 유형을 다시 정의하지 않아도된다.

- CATS기법 사용시 기존테이블의 제약 조건중에 Not null만 새로운 테이블에 적용되며 기본키, 고유키, 외래키, Check 등의 다른 제약조건은 없어진다.

- SQL Server에서는 Select ~ into ~ 를 활용하여 같은 결과를 얻을 수 있으며 칼럼속성에 Identity를 사용했다면 Identity 속성까지 같이 적용된다.

* 오라클

CREATE TABLE TEMP_PLAYER

AS SELECT * FROM PLAYER;

* SQL SERVER

SELECT * INTO TEAM_TEMP FROM TEAM;

3. ALTER TABLE

- 한번 생성된 테이블의 구조를 변경하기위해 사용한다.

- 칼럼을 추가/삭제하거나 제약조건을 추가/삭제 하는 작업을 진행하게된다.

가. ADD COLUMN.

ALTER TABLE 테이블명

ADD 추가할 칼럼명 데이터 타입;

* 추가된 칼럼은 마지막에 위치하게되며 위치를 지정할 수 없다.

- 오라클

ALTER TABLE PLAYER

ADD (ADDRESS VARCHAR2(80));

* 아래 명령어도됨.

ALTER TABLE PLAYER

ADD ADDRESS VARCHAR2(80);

- SQL SERVER

ALTER TABLE PLAYER

ADD ADDRESS VARCHAR(80);

나. DROP COLUMN

- 테이블에서 필요없는 칼럼을 삭제할 수 있으며 데이터가 없거나 있거나 모두 삭제 가능하다.

- 한 번에 하나의 칼럼만 삭제 가능하며 칼럼 삭제 후 최소 하나 이상의 칼럼이 데이터에 존재해야한다.

- 한 번 삭제된 칼럼은 복구가 불가능하다.

ALTER TABLE 테이블명

DROP COLUMN 컬럼명;

다. MODIFY COLUMN 

- ALTER TABLE 명령어를 통해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대해 변경을 포함할 수 있다.

- 칼럼에 대한 데이터 정의를 변경하는 명령어

ALTER TABLE 테이블명

MODIFY ( 칼럼명1 데이터유형 [DEFAULT] [NOTNULL] ,

                 칼럼명2 데이터유형 .... ) ;

* 칼럼 변경 시 고려할 사항.

- 해당 칼럼의 크기는 늘릴 수 있지만 줄일 수는 없다. (기존 데이터의 훼손 방지)

- 해당 칼럼이 NULL값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

- 해당 칼럼이 NULL값만 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 칼럼의 DEFAULT 값을 바꾸면 변경작업 이후 발생하는 행 삽입에만 영향을 미친다.

- 해당 칼럼에 NULL값이 없을 경우에만 NOT NULL제약조건을 추가할 수 있다.

ex) TEAM 테이블에 ORIG_YYYY컬럼의 데이터 유형을 CHAR(4) -> VARCHAR2(8) 형식으로 변경하고 향후 입력되는 데이터의 DEFAULT 값으로 20090826 적용하고 모든 행의 ORIG_YYYY컬럼에 널이 없으므로 제약조건을 NOT NULL로 변경한다

- 오라클

ALTER TABLE TEAM

MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20150826' NOT NULL) ;

-SQL SERVER
ALTER TABLE TEAM

ALTER CLOUMN ORIG_YYYY VARCHAR(8) DEFAULT '20150826' NOT NULL;

ALTER TABLE TEAM

ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '201508226' FOR ORIG_YYYY;

- RENAME COLUMN

칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우.

ALTER TABLE 테이블명

RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 칼럼명;

ex) 

- 오라클

ALTER TABLE PLAYER

RENAME COLUMN PLAYER_ID TO TEMP_ID;

ALTER TABLE PLAYER 

RENAME COLUMN TEMP_ID TO PLAYER_ID;

- SQL server

sp_rename 프로시져를 이용하여 변경한다.

sp_rename 변경해야 할 컬럼명, 새로운 칼럼명, 'COLUMN';

ex) sp_rename 'dbo.TEAM_TEMP.TEAMID', 'TEAM_TEMP_ID', 'COLUMN';

라. DROP CONSTRAINT

- 테이블 생성 시 부여했던 제약조건을 삭제하는 명령

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

ex) 

- 오라클 / SQL server

ALTER TABLE PLAYER

DROP CONSTRAINT PLAYER_FK;

마. ADD CONSTRAINT

- 테이블 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블

ADD CONSTRAINT 제약조건명 제약조건 (칼럼명) ;

ex)

ALTER TABLE PLAYER

ADD CONSTRAINT PLAYER_FK

FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);

* 참조 제약조건을 추가하면 참조 무결성 옵션에 따라 테이블의 데이터를 삭제하려 할 경우 외부에서 참조하고 있기 때문에 삭제가 불가능하게 제약할 수 있다.

=> FK를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지할 수있다.

4. RENAME TABLE

- RENAME 명령어를 사용하여 테이블 이름을 변경할 수 있다.

오라클: RENAME 변경전 테이블명 TO 변경후 테이블명;

SQL SERVER : sp_rename 변경전 테이블명, 변경후 테이블명; 

ex)

오라클

RENAME TEAM TO TEMP_TEAM;

SQL server

sp_rename 'dbo.team','temp_team';

5. DROP TABLE

- 테이블을 잘못 만들거나 더이상 필요없을 경우 삭제하는 명령어

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

* DROP 명령어를 사용하면 테이블의 데이터 및 구조를 삭제한다.

* CASCADE CONSTRAINT 옵션을 사용하면 테이블과 관계가 있었던 참조제약조건에 대해서도 삭제한다.

 (SQL server에서는 CASCADE옵션이 존재하지 않으며 테이블 삭제하기 전에는 FOREIGN KEY 제약조건 또는 참조하는 테이블을 먼저 삭제해야한다.)

ex) 

- 오라클 

DROP TABLE TEMP_EMP;

- SQL server

DROP TABLE TEMP_EMP;

[SQLD] 1과목 데이터 모델링의 이해 - 2장 6절:분산 데이터베이스와 성능

1. 분산 데이터베이스의 개요.

- 여러 곳으로 분산되어있는 데이터베이스를 하나의 가상 시스템으로 사용할 수 있도록 한 데이터베이스

- 논리적으로 동일한 시스템에 속하지만, 컴퓨터 네트워크를 통해 물리적으로 분산되어있는 데이터들의 모임, 물리적 SITE 분산, 논리적으로 사용자 통합 공유.

=> 데이터베이스를 연결하는 빠른 네트워크 환경을 이용하여 데이터베이스를 여러 지역 여러 노드로 위치시켜 사용성/성능을 극대화 시킨 데이터베이스.

2. 분산데이터베이스의 투명성.

- 분산데이터베이스가 되기 위해서는 6가지 투명성을 만족시켜야 한다.

1) 분할투명성(단편화) : 하나의 논리적인 Relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 site에 저장.

2) 위치 투명성 : 사용하려는 데이터의 저장 장소 명시 불필요, 위치정보가 system catalog에 유지되어야함.

3) 지역사상 투명성 : 지역 dbms와 물리적 db 사이의 mapping 보장. 각 지역시스템 이름과 무관한 이름 사용가능.

4) 중복 투명성 : DB객체가 여러 site에 중복되어있는지 알 필요가 없는 성질.

5) 장애 투명성 : 구성요소(DBMS, Computer)의 장애에 무관한 Transaction의 원자성 유지

6) 병행 투명성 : 다수 Transaction 동시 수행시 결과의 일관성 유지. Time stamp, 분산2단계 Locking을 이용해서 구성.

3. 분산데이터베이스의 적용방법 및 장단점.

가. 분산데이터베이스의 적용방법

- 업무의 흐름을 보고 업무 구성에 따른 아키텍쳐 특징에 따라 데이터베이스를 구성.

나. 분산 데이터베이스의 장단점

4. 분산 데이터베이스의 활용 방향성

- 위치 중심의 분산설계

- 업무필요에 의한 분산설계

5. 데이터베이스 분산구성의 가치

- 통합데이터베이스에서 제공할 수 없는 빠른 성능을 제공.

- 원거리 또는 다른 서버에 접속하여 처리하므로 인해 발생되는 네트워크 부하 및 트랜잭션 집중에 따른 성능저하이 원인을 분산데이터베이스 환경을 구축하므로 빠른 성능을 제공하는것이 가능해진다.

6. 분산데이터베이스의 적용기법.

가. 테이블 위치 분산

- 테이블 위치분산은 테이블의 구조는 변하지 않는다.

- 다른 데이터베이스에 중복되어 생성되지도 않는다

- 테이블별 위치 분산은 정보를 이용하는 형태가 각 위치별로 차이가 있을 경우에 이용한다.

- 테이블의 위치가 위치별로 다르므로 테이블의 위치를 파악할 수 있는 도식화된 데이터베이스 문서가 필요하다.

나. 테이블 분할 분산

- 테이블 분할 분산은 위치만 다른곳에 놓는것이 아니라 각각의 테이블을 쪼개어 분산하는 방법이다.

- 테이블을 분할하여 분산하는 방법은 테이블을 나누는 기준에 따라 두 가지로 구분된다.

1) 수평분할

- 테이블의 로우단위로 분할.

- 칼럼은 분리하지 않는다.

- 모든데이터가 각 지사별로 분리되어 있는 형태를 가진다.

- 각 지사에 있는 데이터와 다른지사에 있는 데이터와 항상 배타적으로 존재하며 한군데 집합시켜도 Primary key에 의해 중복이 발생하지않는다.

- 수평분할을 이용하는 경우는 각 지사별로 사용하는 Row가 다를 때 사용한다.

- 데이터를 수정할 때는 타 지사에 있는 데이터를 원칙적으로 수정하지 않고 자신의 데이터에 대해서 수정하도록한다.

- 각지사에 존재하는 테이블에 대해서 통합처리를 해야하는 경우 조인이 발생하게 되므로 성능저하가 예상된다. 그러므로 통합처리 프로세스가 많은지 검토 후 많지않은 경우에 수평분할을 한다.

2)수직분할

- 지사에 따라 칼럼을 기준으로 칼럼을 분리한다. 로우단위로는 분리되지 않는다.

- 모든데이터가 각 지사별로 분리되어 있는 형태를 가지고 있다.

- 칼럼을 기준으로 분할 하였기 때문에 각각의 테이블에는 동일한 Primary key 구조와 값을 가지고 있어야한다.

- 통합하여 처리해야하는 프로세스가 많을 경우 이용하지않는다.

다. 테이블 복제 분산

- 테이블 복제 분산은 동일한 테이블을 다른 지역이나 서버에 동시에 생성하여 관리하는 유형이다.

- 마스터데이터베이스에서 테이블의 일부의 내용만 다른 지역이나 서버에 위치시키는 부분복제가 있고, 마스터 데이터베이스의 내용을 각 지역이나 서버에 존재시키는 광역복제가 있다.

1) 부분복제

- 통합된 테이블을 한군데에 가지고 있으면서 각 지사별로는 지사에 해당된 로우를 가지고 있는 형태이다.

- 지사에 존재하는 데이터는 반드시 본사에 존재하게된다.

- 지사에서 데이터 처리가 용이할 뿐만 아니라 전체 테이터에 대한 통합처리도 본사에 있는 통합테이블을 이용하게 되므로 조인이 발생하지 않는 빠른 작업수행이 가능하다.

- 지사간에는 데이터 중복이 발생되지 않으나, 지사와 본사 간에는 데이터 중복이 항상 발생하게된다.

- 야간에 배치작업에 의해 데이터 복제를 한다.

- 본사와 지사 양쪽 모두 데이터를 수정하여 전송하는 경우 데이터 정합성을 일치시키는게 어렵기 때문에 가능하면 지사에서 데이터 수정이 발생하여 본사로 복제하도록 한다.

2) 광역복제

- 통합된 테이블을 본사에서 가지고 있으면서 각 지사에도 본사와 동일한 데이터를 모두 가지고 있는 형태이다.

- 지사에 존재하는 데이터는 반드시 본사에 존재하게 된다.

- 모든 지사의 데이터양과 본사에 있는 데이터 양은 동일하다.

- 부분복제의 경우 지사에서 데이터에 대한 입력,수정,삭제가 발생하여 본사에 이용하는 방식이 많은 반면에 광역복제의 경우에는 본사에서 데이터가 입력 수정 삭제가 발생되어 지사에서 이용하는 형태가 많다.

- 데이터를 복제하는데 많은 시간이 소요되므로 배치에 의해 복제되도록 한다.

라. 테이블 요약 분산

- 테이블 요약 분산은 지역간에 또는 서버간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우가 있다.

요약 방식에 따라 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식의 분산석요약과 분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식의 통합요약이 있다.

1) 분석요약

- 각 지사별로 존재하는 요약정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법이다.

- 통합 통계데이터에 대한 정보제공에 용이한 분산방법이다.

2)통합요약

- 각 지사별로 존재하는 다른 내용의 정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법이다.

- 통합요약은 단지 지사에서 산출한 요약정보를 한군데 취합하여 보여주는 형태이다. 분석요약은 지사에 있는 데이터를 이용하여 본사에서 통합하여 요약데이터를 산정하지만 통합요약에서는 자사에서 요약한 정보를 취합하여 각 지사별로 데이터를 비교하기위해 있는것이다.

* 각종 통계데이터를 산정할 경우에 모든 지사의 데이터를 조인하여 처리하면 성능이 저하되고 각 지사 서버에 부하를 주기 때문에 장애가 발생할 수 있다.  본사에 통합 요약된 테이블을 생성하고 데이터는 야간에 수행하여 생성하는것이 일반적인 적용방법이다.

7. 분산 데이터베이스를 적용하여 성능이 향상된 사례

- 복제분산의 원리를 이용하면 성능을 향상시켜 설계할 수 있다.

- 트랜잭션 개별적으로 원격지 조인 -> 트랜잭션 내부조인(배치이동)

*분산데이터베이스 설계는 다음경우에 이용하면 효과적이다.

1) 성능이 중요한 사이트에 적용.

2) 공통코드, 기준정보, 마스터데이터 등에 대해 분산환경을 구성하면 성능이 좋아진다.

3) 실시간 동기화가 요구되지 않을 때 좋다.

4) 특정 서버에 부하가 집중될 때 부하를 분산할 때도 좋다.

5) 백업 사이트를 구성할 때 간단하게 분산기능을 적용하여 구성할 수 있다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능

1. 슈퍼타입/ 서브타입 모델의 성능고려 방법.

가. 슈퍼/서브타입 데이터 모델의 개요.

- Extended ER모델이라고 부르는 슈파타입/서브타입 데이터 모델은 최근에 데이터 모델링을 할 때 자주 쓰이는 모델링 방법이다.

- 업무를 구성하는 데이터의 특징을 공통점과 차이점의 특징을 고려하여 효과적으로 표현할 수 있기 때문.

- 공통의 부분을 슈퍼타입으로 모델링하고 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분하여 업무의 모습을 정확하게 표현하면서 물리적인 데이터 모델로 변환을 할 때 선택의 폭을 넓힐 수 있는 장점이 있다.

- 슈퍼/서브타입의 데이터 모델은 논리적 데이터 모델에서 이용되는 형태이며 분석/설계단계를 구분하자면 분석단계에서 많이 쓰이는 모델이다.

- 물리적인 데이터모델을 설계하는 단계에서는 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환해야한다.

- 물리적인 데이터모델이 성능을 고려한 데이터 모델이 되어야한다는 점을 고려하면 이렇게 막연하게 슈퍼/서브타입을 아무런 기준없이 변환하는것 자체가 성능이 저하될 수 있음을 기억해야한다.

나. 슈퍼/서브타입 데이터 모델의 변환

- 슈퍼 서브타입에 대한 변환을 잘못하여 성능이 저하되는 경우.

1) 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 UNION 연산에 의해 성능이 저하될 수 있다.

2) 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우가 있다.

3) 트랜잭션은 항상 슈퍼+서브타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우가 있다.

* 해당 테이블에 발생되는 성능이 중요한 트랜잭션이 빈번하게 처리되는 기준에 따라 테이블을 설계해야 성능저하 현상을 예방할 수 있다.

* 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터 양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라 결정된다.

* 데이터의 양은 소량일 경우 데이터 처리의 유연성을 고려하여 가급적 1:1관계를 유지하는것이 바랍직하다.

* 데이터의 양이 많을 경우 그리고 해당 업무적인 특징이 성능에 민감한 경우에는 트랜잭션이 해당 테이블에 어떻게 발생되는지에 따라 3가지 변환방법을 참조하여 변환해야한다.

다. 슈퍼/서브 타입 데이터 모델의 변환 기술.

- 논리적인 데이터 모델에서 설계한 슈퍼타입/서브타입 모델을 물리적인 데이터 모델로 전환할 때 주로 어떤 유형의 트랜잭션이 발생하는지 검증해야한다.

- 데이터양이 많이 존재하고 지속적으로 증가하는 양도 많다면 슈퍼타입/서브타입에 대해 물리적인 데이터모델로 변환하는 세 가지 유형에 대해 세심하게 적용해야한다.

1) 개별로 발생하는 트랜잭션에 대해서는 개별 테이블로 구성.

- 슈퍼타입과 서브타입 각각에 대해 독립적으로 트랜잭션이 발생이 되면 슈퍼타입에도 꼭 필요한 속성만 가지게 하고 서브타입에도 꼭 필요한 속성만 가지게 하기 위해서 모두 분리하여 1:1 관계를 갖도록 한다.

- 실전프로젝트에서는 데이터양이 대용량으로 존재하는 경우에 공통으로 이용하는 슈퍼타입의 속성의 수가 너무 많아져 디스크 I/O가 많아지는 것을 방지하기 위해 각각을 1:1 관계로 가져가는 경우도 있다.

2) 슈퍼타입+ 서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성

-  만약 대리인이 10만건 매수인 500만건 이해관계인 500만건의 데이터가 존재할 때, 슈퍼타입과 서브타입이 모두 하나의 테이블로 구성되어있을 경우 매수인 이해관계인에 대한 정보는 배제하고 10만건인 대리엔이데핸 데이터만 처리할 경우 다른테이블과 같이 1010만건 데이터가 저장되어있는 곳에서 처리하므로 불필요한 성능저하가 발생하게된다.

=>  이처럼 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무특징을 가지고 있을 때에는 슈퍼타입+서브타입을 각각 하나로 묶어 별도의 테이블로 구성하는것이 효율적이다.

3) 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성.

- 대리인이 10만건, 매수인이 500만건, 이해관계인 500만건의 데이터가 존재한다고 하더라도 데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리한다고 하면 테이블을 개별로 분리하면  불필요한 조인을 유발하거나 불필요한 UNION ALL과 같은 SQL 구문이 작성되어 성능이 저하된다. 비록 슈퍼타입과 서브타입의 테이블을 하나로 묶었을 때 각각의 속성별로 제약사항을 정확하기 지정하지 못할 지라도 대용량이고 성능향상에 필요하다면 하나의 테이블로 묶어서 만들어준다.

라. 슈퍼/서브타입 데이터 모델의 변환타입 비교.

2. 인덱스 특성을 고려한 PK/FK 데이터베이스 향상.

가. PK/FK 칼럼순서와 성능개요.

- 데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근경로를 제공하는 오브젝트가 인덱스이다.

- 일반적으로 데이터베이스 테이블에서는 균형잡힌 B*Tree 구조를 많이 사용한다.

- 테이블에 발생되는 트랜잭션의 조회패턴에 따라 PK/FK 칼럼의 순서를 조정해야한다.

- PK는 해당 테이블의 데이터에 접근할 때 가장 빈번하게 사용되는 유일한 인덱스를 모두 자동생성한다.

- PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK순서를 지정해야한다.

* 여러개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 낼 수 있다.

* 앞쪽에 위치한 속성값이 가급적 '=' 아니면 최소한 범위 BEETWEEN, < > 가 들어와야 인덱스를 이용할 수 있다.

* 데이터 모델링 때 결정한 PK 순서와는 다르게 DDL 문장을 통해 PK 순서를 다르게 생성할 수 있다.

(하지만 보통 데이터 모델의 PK 순서에 따라 그대로 PK를 생성한다.)

* FK라고 하더라도 데이터를 조회할 때 조인의 경로를 제공하는 역할을 수행하므로 FK에 대해서는 반드시 인덱스를 생성하도록 하며 인덱스 칼럼의 순서도 조회의 조건을 고려하여 접근이 가장 효율적인 칼럼 순서대로 인덱스를 생성하도록 주의한다.

나. PK칼럼의 순서를 조정하지 않으면 성능이 저하되는 이유.

- 먼저 데이터 모델링에서 엔터티를 설계하면 그에 때라 DDL이 생성이 되고 생성된 DDL에 따라 인덱스가 생성된다.

- PK순서에 따라 DDL이 그대로 생성되고, 테이블의 주문번호가 가장먼저 정렬되고, 주문일자가 정렬되고 주문번호코드가 정렬이된다.

 1) 맨앞에 있는 인덱스 칼럼에 조회조건이 들어올 경우

SELECT 주문단가

FROM 주문목록

WHERE 주문번호 = '1002';

=> 인덱스에 정렬된 첫 번째 칼럼에 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다.

2) 맨 앞에 있는 칼럼이 제외된 상태에서 데이터를 조회할 경우 데이터 비교버무이가 넓어지게되어 성능저하를 유발한다.

SELECT 주문단가

FROM 주문목록

WHERE 주문일자='2015.08.23';

=> 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 다 읽은 후 원하는 데이터를 찾게된다. 이러한 이유로 인덱스를 읽고 데이터 블록에서 읽어 처리하는데 IO가 많이 발생하므로 옵티마이저는 차라리 테이블 전체를 읽는 방식으로 처리한다.

* PK의 순서를 인덱스 특징에 맞게 고려하지 않게 생성하게되면 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있으므로 인덱스의 범위를 넓게 이용하거나 FULL SCAN을 유발하게 되어 성능이 저하된다고 할 수 있다.

다. PK순서를 잘못 지정하여 성능이 저하된 경우. - 간단한 오류

- 입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터의 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어있는 복합식별자 구조의 테이블이다.

- 입시마스터에는 200만 건의 데이터가 들어가있고 학사는 4학기로 구성되어 있고 데이터는 5년간 보관되어 한학기당 평균 2만 건의 데이터가 있을 때.

- 성능저하쿼리

=> 이 경우 입시마스터_I01인덱스가 수험번호+년도+학기 중 수험번호에 해당하는 값이 WHERE절에 들어오지 않으므로 FULL TABLE SCAN이 발생하게된다.

 * 입시마스터 테이블 조회시 년도와 학기가 빈번하게 들어오므로 PK순서를 변경함으로써 인덱스를 사용할 수 있도록 할 수 있다.

- PK 순서 변경 

라. PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류.

- 현금출급기의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어있는데, 대부분 SQL 문장에서 조회를 할 때 사무소코드가 '='로 들어오고 거래일자에 대해서는 BETWEEN 조회를 하고있다.

이 때 SQL은 정상적으로 인덱스를 사용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당한다.

=> 거래일자 + 사무소코드로 구성된 인덱스 이기 때문에 거래일자 범위때문에 조회범위가 넓어져서 성능저하가 발생한다.

 인덱스 구성을 사무소코드 + 거래일자로 구성하면 '='비교를 사용하므로써 범위가 좁혀질 수 있다.

* A+B 형식의 조회와 B+A 형식의 조회도 빈번하게 일어날 경우에는 좀 더 자주 이용되는 조회형태로 PK를 구성하고 순서를 바꾼 인덱스를 추가로 생성하는것이 좋다.

3. 물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하.

- 물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 의해 상속받은 FK 속성들은 SQL WHERE절에서 조인이 이용되는 경우가 많이 있으므로 FK 인덱스를 생성해야 성능이 좋은 경우가 빈번하다.

- 학사기준과 수강신청에 대한 데이터모델에서 물리적인 테이블에는 두 테이블 사이에 FK 참조무결성 관계가 걸려있지 않을 때 학사기준 데이터에는 5만건의 데이터가있고 수강신청에 데이터가 500만건이 있다고 가정하면.

=> FK 인덱스 미생성으로 FULL TABLE스캔이 발생하여 성능 저하가 생긴다.

- 학사기준번호가 SQL절에 비교자로 들어오지는 않지만 수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 인해 학사기준과 수강신청 테이블이 조인이 되면서 500만건의 수강신청 테이블이 FULL SCAN이 발생하며 성능이 저하되었다.

- 이때는 수강신청 테이블에 FK인덱스를 생성하여 성능을 개선할 수 있다.

CREATE INDEX 수강신청_FK01 ON 수강신청(학사기준번호)

* 물리적으로 학사기준과 수강신청이 연결되어 있지 않다고 하더라도 학사기준으로부터 상속받은 FK에 대해 FK인덱스를 생성함으로써 SQL문장이 조인이 발생할 때 성능저하를 예방할 수 있다.

* 물리적인 테이블에 FK제약을 걸었을 때는 반드시 FK 인덱스를 생성하도록 하고 FK 제약이 걸리지 않았을 경우에는 FK인덱스를 생성하는것을 기본 정책으로 하되 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때에만 FK 인덱스를 지우는 방법으로 하는것이 적절한 방법이다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 3절:반정규화와 성능

1.반정규화를 통한 성능향상 전략.

가. 반정규화의 정의

- 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터모델링의 기법을 말한다.

- 협의의 반정규화는 데이터를 중복하여 성능을 향상시키기 위한 기법이라고 정의할 수 있고 좀 더 넓은 의미의 반정규화는 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리등을 수행하는 모든 과정을 말한다.

- 데이터를 중복하여 반정규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산하여 읽을 때 성닝이 저하될 것이 예상되는 경우 반정규화를 수행한다.

- 프로젝트에서는 설계 단계에서 반정규화를 적용하게 된다.

* 반정규화를 기술적으로 수행하지 않는 경우

1) 성능이 저하된 데이터베이스가 생성될 수 있다.

2) 구축단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 더 많이 들게된다.

나. 반정규화의 적용방법.

-  개발을 하다가 SQL문장 작성이 복잡해지고 그에 따라  SQL 단위 성능 저하가 예상되어 다른 테이블에서 조인하여 가져와야 할 칼럼을 기준이 되는 테이블에 중복하여 SQL문장을 단순하게 처리하도록 하기위해 요청하는 경우가 많다. 

 (무분별하게 칼럼의 반정규화를 많이 하게되는것은 데이터에 대한 무결성을 깨뜨리는 결정적인 역할을 하는 경우가 많다.)

- 반정규화에 대한 필요성이 결정되면 칼럼의 반정규화 뿐만아니라, 테이블의 반정규화와 관계의 반정규화를 종합적으로 고려하여 적용해야 한다. 또한 막연하게 중복을 유도하는 것만을 수행하기 보다는 성능을 향상시킬 수 있는 다른 방법들을 고려하고 그 이후에 반정규화를 적용하도록 해야 한다.

- 반정규화를 적용할 때는 기본적으로 데이터 무결성이 깨질 가능성이 많기 때문에 반드시 데이터 무결성을 보장할 수 있는 방법을 고려한 후 반정규화를 적용하도록 해야한다.

* 반정규화 절차

1) 반정규화의 대상을 조사한다.

- 데이터의 양을 조사하고 그 데이터가 해당 프로세스를 처리할 때 성능저하가 나타날 수 있는지 검증해야한다.

* 자주 사용되는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우 반정규화를 검토한다.

* 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없을 경우에 반정규화를 검토한다.

* 통계성 프로세스에 의해 통계정보를 필요로 할 때 별도의 통계테이블을 생성한다.

* 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 반정규화를 검토한다.

2) 반정규화의 대상에 대해 다른 방법으로 처리할 수 있는지 검토한다

- 가급적이면 데이터를 중복하여 데이터 무결성을 깨뜨릴 위험을 제어하기 위하여 반정규화를 결정하기 전에 성능을 향상시킬 수 있는 다른 방법을 모색하도록 한다.

* 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰를 사용하면 해결할 수도 있다.

(성능을 고려한 뷰를 생성하여 개발자가 뷰를 통해 접근함으로 써 성능저하의 위험을 예방할 수 있다)

* 대량의 데이터 처리나 부분처리에 의해 성능이 저하되는 경우 클러스터링을 적용하거나 인덱스를 조정함으로써 성능을 향상시킬 수 있다.

( 클러스터링을 적용하는 방법은 대량의 데이터를 특정 클러스터링 팩트에 의해 저장방식을 다르게 하는 방법이다.

이 방법의 경우 데이터를 입력,수정,삭제 하는 경우 성능이 많이 저하되므로 조회중심의 테이블이 아니라면 생성하면 안된다. )

* 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있다. 즉 파티셔닝 기법이 적용되어 성능저하를 방지할 수 있다.

* 응용애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있다.

(응용메모리에 데이터를 처리하기 위한 값을 캐쉬한다든지 중간 클래스 영역에 데이터를 캐쉬하여 공유하게 하여 성능을 향상 시키는 것도 성능을 향상시키는 방법이 될 수 있다.)

3) 반정규화를 적용한다.

- 반정규화를 적용하기 이전에 충분히 성능에 대한 고려가 이루어져 판단이 들었다면 세가지 규칙을 고려하여 반정규화를 적용한다

- 반정규화를하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가할 수도 있고 분할할 수도 있으며 제거할 수도 있다.

2. 반정규화의 기법.

가. 테이블 반정규화

나. 칼럼 반정규화

다. 관계 반정규화.

=> 테이블과 칼럼의 반정규화는 데이터 무결성에 영향을 미치게 되나 관계의 반정규화는 데이터 무결성을 깨뜨릴 위험을 갖지 않고서도 데이터 처리의 능력을 향상시킬 수 있는 반정규화의 기법이 된다.

3. 정규화가 잘 정의된 데이터 모델에서 성능이 저하될 수 있는 경우.

- 공급자라고 하는 엔터티가 마스터 이고 전화번호와 메일주소 위치가 각각 변경되는 내용이 이력형태로 관리되는 데이터 모델의 경우, 반정규화 하여 마스터 엔터티에 최신 이력의 전화번호와 메일주소 위치를 저장할 경우  간단하게 조회할 수 있다.

- 데이터베이스서버가 분리되어 분산데이터베이스가 구성되어 있을 때 속성 반정규화를 통해 성능을 향상시킬 수 있다.

* 반정규화를 적용할 때 중요한 것은 데이터를 입력, 수정, 삭제할 때는 성능이 떨어지는 점을 기억해야하며, 데이터 무결성 유지에 주의해야한다.