조인 JOIN 방식

 

 

□Join의 종류는 5가지가 있습니다.

• INNER Join
• OUTER Join
• CROSS Join
• FULL OUTER Join
• SELF Join

Join의 종류는 논리적 Join이라고 합니다.

 

□Join의 방식은 3가지가 있습니다.

• Nested Loop Join - 중첩반복
• Merge Join - 정렬병합
• Hash Join - 해시매치

Join의 방식은 물리적 Join이라고 합니다.

 

JOIN의 방식에 대해서 알아보겠습니다.

 

◆중첩반복(Nested Loops) 조인

 

 

바깥 테이블의 처리 범위를 하나씩 액세스하면서 그 추출된 값으로 안쪽 테이블을 조인하는 방식

 

순차적으로 처리된다.

바깥 테이블과 일치하는 값을 안쪽 테이블에서 찾아야 하므로 안쪽 테이블의 해당 열에 인덱스가 필요하다.

메모리 사용량은 가장 적다.

바깥 테이블과 안쪽 테이블의 크기는 성능과 관련이 없다.

순차적인 진행

 

첫 테이블 필터링 -> 두테이블간의 연결 -> 최종운반 단위 산출가지 반복적, 순차적으로 진행됩니다.

 

선행적

 

선행 테이블의 처리 범위가 전체 일의 양을 결정합니다.

후행 테이블의 필터링 조건은 선행 테이블에서 나온 결과를 한번 더 걸러주는 체크 조건 역할을 할 뿐 전체 처리량을 좌우하지 않습니다.

 

종속적

 

후행 테이블은 선행 테이블의 결과값을 받아 처리됩니다.

선행 테이블의 결과에 종속적입니다.

하지만 후행 테이블의 인덱스를 전체 일의 양을 줄여줄 수 있는 필터링 조건으로 사용 못합니다. (체크조건으로만 사용)

 

랜덤 액세스

 

선행 테이블의 결과를 통해 후행 테이블을 액세스 할대 랜덤 I/O가 발생합니다.

선행 테이블은 최초 ROW만 액세스가 발생하고 이후에는 스캔방식으로 진행합니다.

 

연결 고리의 중요성

 

SELECT *

FROM TAB1 A

     INNER JOIN TAB2 B ON A.KEY = B.KEY

A.KEY = B.KEY에서 보듯이 TAB1에서 처리한 row를 가지고 TAB2의 인덱스 페이지를 액세스하기때문에 TAB2의 인덱스 유무가 중요합니다.

만약 TAB2에 인덱스가 없다면 옵티마이저는 TAB2를 후행 테이블로 선택하지 않습니다.

그럴경우 인덱스가 있는 TAB1이 후행 테이블로써 성능면에서 유리하기 때문이다

 

언제사용?

 

처리량이 적을 때

처리의 방향성이 필요

부분 범위 처리

 

단점

 

두 테이블을 연결할 때의 랜덤 i/o가 가장큰 부담

 

 

 

◆정렬병합(Sort Merge) 조인

 

 

양쪽 테이블의 처리 범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하며, 연결고리 조건으로 Merge하는 방식을 말합니다.

이 방식은 경우에따라 Loop Join 보다 훨씬 빨라지는 경우도 많이 있으며, 랜덤 액세스가 줄어들어 시스템 부하를 감소 시킵니다.

하지만 일반적으로 Loop Join보다는 사용빈도가 적습니다.

 

동시적 처리

 

각 키에 의해 정렬된 양쪽 행들을 순차적으로 병합하여 조인을 수행합니다.

 

인덱스가 필요

 

양 테이블의 모두 조인키에 의해 정렬되어있어야 합니다.

 

전체 범위 처리

 

선행 테이블, 후행 테이블 크기는 성능과 관련이 없습니다.

그러나 선행 테이블에 중복행이 존재하지 않을때 메모리 사용량이 적으며 권장하고 있습니다.

부분 범위처리를 할 수가 없으며 항상 전체 범위를 처리합니다.

 

스캔방식

 

주로 스캔방식으로 처리합니다.

자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 액세스이며 나머지작업은 스캔방식입니다.

 

조인의 방향과는 무관

 

테이블 스캔수는 한번

 

언제사용?

 

처리량이 많을때 성능상 이점이 있다.

중첩반복(Nested Loops)은 연결고리의 상태가 굉장히 중요하다. 한쪽 연결고리에 이상이 발생하면 중첩반복은 심히 고려해야한다. 이때 연결고리에 영향을 받지않는 Sort Merge를 쓰면 좋다.

 

단점

 

정렬에 따른 부담 (메모리 사용 증가)

정렬은 tempdb를 사용한다. 정렬양이 극도로 많아 tempdb의 임계치를 넘었을때 순간 전체 데이터베이스에 페이지잠금이 발생하는등 DB성능에 심각한 영향을 줄 수 있다.

물론 가공없이 Clustered Index를 그대로 사용하게 되면 정렬은 안해도 되니 이때만큼은 정렬의 부담에서 해방된다.

참고

 

MSSQL에서는 양쪽 테이블에서 필터링 되어 나온 값이 각 테이블에서 유니크(Unique)할때만 이 조인방식을 사용하려는 경향이 있다고한다.

연결고리로 사용할 키값에 중복이 심하면 잘 선택하지 않으려고 한다고 하니 알아두자

 

 

 

◆해시매치(Hash Match) 조인, 해시(Hash) 조인

 

 

[오라클 조인]해시조인(Hash Join)이란?, Driving/Inner Table, Oracle Hash Join, 해시조인이란 

 

실무개발자가 진행하는 자바, 스프링, SQL 강좌

오라클자바커뮤니티 교육센터(www.ojcedu.com)

Hash Join

해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식의 조인이다.
RDBMS에 서 비용이 가장 많이 들어가는 Join 방법으로 주로 작은 Table과 큰 Table 의 Join 시 사용되어 지며 , Driving 조건과 상관없이 좋은 성능을 발휘할 수 있다.


1. 작은 테이블(Build Input)을 읽어 Hash Area에 해시 테이블 생성한다.
(해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인에 엔트리를 연결)
2. 큰테이블 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다.
(해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾는다)


[특징]

- 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합하다.
- NL조인 과 달리 Random 액세스 부하가 없다.(단, 양쪽집합을 읽는 과정에서 인덱스를 이용한다면 Random 액세스 발생)
- NL조인 과 달리 Hash Area에 미리 생성해 둔 해시 테이블(또는 해시 맵)을 이용한다.
(해시테이블을 만드는 단계는 전체범위처리 불가피, Probe Input을 스캔하는 단계는 NL조인처럼 부분범위처리가능)
- 소트머지조인과 달리 조인 전에 미리 양쪽 집합을 정렬하는 부담이 없으며 NL조인 과 달리 래치획득 과정없이 PGA에서 빠르게 데이터 탐색한다.
- 해시 테이블을 생성하는 비용이 수반됨으로 Build Input이 작을때 효과적이며 PGA(or SGA) 메모리에 할당되는 Hash Area에 담길 정도로 충분히 작아야 한다. 해시키 값으로 사용되는 컬럼에 중복값이 거의 없을 때 효과적이다.
- SQL 문장에서 옵티마이저는 해쉬 조인으로 수행하기 위해 작은 테이블을 메모리에 로드 한 후 큰 테이블을 여러 Partition으로 분리하여 메모리에 로드가 되어 있는 작은 테이블을 해쉬 알고리즘에 의하여 탐색하게 되고 여러 Partition 으로 나뉘어 지는 테이블은 HASH_AREA_SIZE 에 명시된 메모리에 상주되며 메모리가 충분치 않아 메모리에 모두 상주 시킬수 있는 상황이라면 디스크에 위치하게 된다. (충분한 hash_area_size 제공필요)
- 해시조인을 사용하기 위해서는 USE_HASH hint를 사용 한다.


해시 조인 사용기준

- 기준
1. 한쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야함.
2. Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함.

- 조건
1. 조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율적일때
2. 조인 컬럼에 인덱스가 있더라고 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로서의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
3. 소트 머지 조인하기에는 두 테이블이 너무 커 소트 부하가 심할때
4. 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할때

- 결과
1. 해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조이다.
2. 수행빈도가 낮고, 쿼리수행 시간이 오래 걸리는, 대용량테이블을 조인할 때

 

사용방법

 

mssql 기준 아래와 같이 사용합니다.

 

Nested Loop Join

 

SELECT *

FROM Sales.Customer AS c

     INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID

OPTION (LOOP join);

Merge Join

 

SELECT *

FROM Sales.Customer AS c

     INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID

OPTION (MERGE join);

SELECT *

FROM Sales.Customer AS c

     INNER MERGE JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID

Hash Join

 

SELECT *

FROM Sales.Customer AS c

     INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID

OPTION (LOOP join);

참고

 

Gravity DB Team

양군’s 블로그 - [MS-SQL] 조인 방식 (Join Method)

DevNote - [MSSQL] 조인의 종류 (inner join,left(right) outer join, cross join, ….)

 

 

출처 및 참조)

 

 http://sonim1.tistory.com/108

 http://blog.naver.com/ssayagain/90036001354

 http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=127