SQL 레벨업 - 6장

18강 기능적 관점으로 구분하는 결합의 종류

1. 크로스 결합 - 모든 결합의 모체

- 크로스 결합의 작동

SELECT *FROM Employees CROSS JOIN Departments;

 

2개의 테이블의 레코드에서 가능한 모든 조합을 구하는 연산

(사원 테이블 레코드) * (부서 테이블 레코드) 만큼의 레코드 결과 생성

 

- 크로스 결합이 실무에서 사용되지 않는 이유

  1. 이러한 결과가 필요한 경우가 없다

  2. 비용이 매우 많이 드는 연산이다

 

-결합 조건이 없으면 DBMS 는 어쩔 수 없이 두 개의 테이블에서 나올 수 있는 모든 조합을 만들어 버린다. 

 

2. 내부 결합 - '데카르트 곱의 부분 집합' 이라는 의미

- 내부 결합의 작동

SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
	FROM Employees E INNER JOIN Departments D
	ON E.dept_id = D.dept_id;

 

- 내부 결합의 결과는 크로스 결합의 일부(부분 집합)

- 내부 결합과 같은 기능을 하는 상관 서브 쿼리

SELECT E.emp_id, E.emp_name, E.dept_id,
       (SELECT D.dept_name FROM Departments D
       WHERE E.dept_id=D.dept_id) AS dept_name
FROM Employees E;

    dept_id는 부서 테이블의 기본 키; 레코드가 반드시 하나일 것임

    기본 키를 사용하면 상관 서브쿼리를 스칼라 서브쿼리로 사용 가능

   상관 서브쿼리를 스칼라 서브쿼리로 사용하면 결과 레코드 수만큼 상관 서브 쿼리를 실행해 비용이 높아짐

 

3.외부 결합 - '데카르트 곱의 부분 집합이 아니다' 라는 의미

- 왼쪽 외부 결합: 마스터가 되는 테이블을 왼쪽에 적은 것

- 오른쪽 외부 결합: 마스터가 되는 테이블을 오른쪽에 적은 것

- 완전 외부 결합 

마스터 테이블쪽에만 존재하는 키가 있을 경우 제거하지 않고 결과로 보존

키를 모두 가진 레이아웃의 리포트를 만들 때 자주 사용

-- 왼쪽 외부 결합(왼쪽 테이블이 마스터)
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
	FROM Departments D LEFT OUTER JOIN Employees E
	ON E.dept_id = D.dept_id;

-- 오른쪽 외부 결합(오른쪽 테이블이 마스터)
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
	FROM Employees E RIGHT OUTER JOIN Departments D
	ON E.dept_id = D.dept_id;

 

4. 외부 결합과 내부 결합의 차이

- 외부 결합 결과는 마스터 테이블의 정보를 모두 보존하고자 NULL값이 든 레코드를 생성

따라서 외부 결합은 크로스 결합의 결과의 부분집합이 아님. 

- 크로스 결합과 내부결합은 NULL을 생성하지 않음.

 

5. 자기 결합 - 자기 자신과 결합하는 연산( 같은 테이블을 사용해 결합하는 것)

-'자기 결합 + 크로스 결합'

-'자기결합 + 외부 결합'

 

19강 결합 알고리즘과 성능

1. nested loops

- 중첩 반복을 사용하는 알고리즘

- 한 번에 두 개의 테이블만 결합하므로 본질적으로 이중 반복과 같은 의미

Nested Loops의 특징

1. Table_A, Table_B 의 결합 대상 레코드를 R(A), R(B)라고 하면 접근하는 레코드 수는 R(A) * R(B) 가 된다. 그러므로 실행 시간은 레코드 수에 비례하게 된다.
2. 한 번의 단계에서 처리하는 레코드 수가 적으므로 Hash 또는 Sort Merge에 비해 메모리 소비가 적다.
3. 모든 DBMS에서 지원한다.

구동 테이블이 작을수록 nested loops의 성능은 좋아짐.

- 구동테이블의 중요성

   구동 테이블이 어떤 테이블이 되더라도 결과적으로 접근하는 레코드 수는 R(A) * R(B). 구동 테이블이 작건 크건 결합 비용에는 차이가 없어 보이지만, ‘구동 테이블을 작게’라는 격언에는 암묵적인 전제가 포함 -> 내부 테이블의 결합 키 필드에 인덱스가 존재

-- 내부 테이블의 결합 키 인덱스 사용(department_pkey, PK_DEP)
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
	FROM Employees E INNER JOIN Departments D
		ON E.dept_id = D.dept_id;
-- 내부 테이블 인덱스가 사용되는 Nested Loops
Nested Loop(cost= ~)
	-> Seq Scan on employees e
	-> Index Scan using departments_pkey on departments d
		Index Cond:(dept_id = e.dept_id)
-- 내부 테이블의 인덱스가 사용되지 않은 Nested Loops
Nested Loop(cost ~)
	Join Filter: (e.dept_id = d.dept_id)
		-> Seq Scan on departments d (cost ~)
		-> Materialize (cost ~)
			-> Seq Scan on employees e (cst ~)

 

2. hash

- 작은 테이블을 스캔하고 결합 키에 해시 함수 적용해 해시값으로 변환 후 다른 테이블을 스캔해 결합 키가 해시값에 존재하는지 확인하며 결합 수행

Hash 의 특징

1. 결합 테이블로부터 해시 테이블을 만들어서 활용하므로, Nested Loops에 비해 메모리를 크게 소모한다.
2. 메모리가 부족하면 저장소를 사용하므로 지연이 발생한다.
3. 출력되는 해시값은 입력값의 순서를 알지 못하므로, 등치 결합에만 사용할 수 있다
   
   1. Nested Loops에서 적절한 구동 테이블이 존재하지 않는 경우
   2. 구동 테이블로 사용할만한 작은 테이블은 있지만, 내부 테이블에서 히트되는 레코드가 너무 많은 경우
   3. Nested Loop 내부 테이블에 인덱스가 존재하지 않는(또는 추가할 수 없는) 경우

3. Sort merge

Sort Merge는 결합 대상 테이블들을 각각 결합 키로 정렬하고, 일치하는 결합 키를 찾으면 결합

 

Sort Merge 의 특징

1. 대상 테이블을 모두 정렬해야 하므로 Nested Loops보다 많은 메모리를 소비한다. 메모리 부족으로 TEMP 탈락이 발생하게 되면 I/O 비용이 늘어나고 지연 발생 위험이 있다.(Hash와 마찬가지)
2. Hash와 다르게 동치 결합뿐만 아니라 부등호를 사용한 결합에도 사용 가능하다. (부정 조건은 안됨)
3. 원리적으로 테이블이 결합 키로 정렬되어 있다면 정렬을 생략 가능하다.
4. 테이블을 정렬하므로 한쪽 테이블을 모두 스캔한 지점에 결합을 완료할 수 있다.

4. 의도하지 않은 크로스 결합

의도하지 않게 크로스 결합이 나타나는 경우가 있다. ‘삼각 결합’이라 부르는 패턴이다.

 

4가지 형태의 실행 계획

• Table_A를 구동 테이블로 Table_B와 결합하고 그 결과를 Table_C 와 결합
• Table_A를 구동 테이블로 Table_C와 결합하고 그 결과를 Table_B 와 결합
• Table_B를 구동 테이블로 Table_A와 결합하고 그 결과를 Table_C 와 결합
• Table_C를 구동 테이블로 Table_A와 결합하고 그 결과를 Table_B 와 결합

20장 결합이 느리다면

1. 상황에 따른 최적의 결합 알고리즘

Nested Loops	1. (작은 구동테이블 + 내부 테이블의 인덱스) 라는 조건이 충족된다면 빠르다.  2. 메모리 또는 디스크 소비가 적으므로 OLTP에 적합 3. 비등가 결합에서도 사용 가능	1. 대규모 테이블들의 결합에는 부적합 2. 내부 테이블의 인덱스가 사용되지 않거나, 내부 테이블의 선택률이 높으면 느리다.
Hash	1. 대규모 테이블들을 결합할 때 적합	1. 메모리 소비량이 큰 OLTP에 부적합 2. 메모리 부족이 일어나면 TEMP 탈락 발생 3. 등가 결합에서만 사용 가능
Sort Merge	1. 대규모 테이블들을 결합할 때 적합 2. 비등가 결합에서도 사용 가능	1. 메모리 소비량이 큰 OLTP에 부적합 2. 메모리 부족이 일어나면 TEMP 탈락 발생 3. 데이터가 정렬되어 있지 않다면 비효율적

2. 실행 계획 제어

- DBMS별 실행 계획 제어

 

3. 흔들리는 실행 계획

사용자가 명시적으로 실행 계획을 제어할 경우, 실행 계획이 더 이상 최적이 아닌 사용자의 실패.

'옵티마이저의 실패' :  데이터 양의 증가 등에 따라 통계 정보가 변했을 떄 일정한 역치를 넘으면 옵티 마이저가 실행 계획을 변화시키면서 발생