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

[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번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행