조인힌트

조인 힌트는 두 테이블의 조인 방법과 조인순서(테이블사용순서)를  지정한다.
LOOP, MERGE,HASH 중에서 오직 하나의 조인 방법만 선택할 수 있다. 그리고 조인힌트는 두 테이블에 대해서만 영향력을 미치며, 조인에 적용되는 테이블들의 순서도 강제화하므로 반드시 두 테이블을 원하는 순서대로 나열해야 한다.
조인에서 먼저 사용되는 테이블을 바깥테이블(OUTTER) 이라고 하고 나중에 사용되는 테이블을 안쪽테이블(INNER)라고 한다.


[LOOP JOIN : 중첩루프조인]

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

1. 특징
1) 순차적으로 처리된다.
2) 바깥 테이블과 일치하는 값을 안쪽 테이블에서 찾아야 하므로 안쪽 테이블의 해당 열에 인덱스가 필요하다.
3) 메모리 사용량은 가장 적다
4) 바깥 테이블과 안쪽 테이블의 크기는 성능과 관련이 없다.
    - 필요하다면 바깥 테이블을 먼저 정렬한 후 조인한다. 순서대로 하는것이 더 효율적이다.

예)

SELECT *

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (LOOP join);

[Merge Join: 병합조인]

양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 Merge 하는 방식을 말한다. 이 방식은 경우에 따라  Loop Join보다 훨씬 빨라지는 경우도 많이 있으며 랜덤 액세스가 줄어들어 시스템의 부하를 감소시키지만 일반적으로 Loop Join 보다는 사용되는 빈도가 적은 편이다.
이 방식의 가장 큰 특징은 상대방에게 아무런 값도 받지 않고 자신이 가지고 있는 조건만으로 처리범위가 정해지며, 랜덤 액세스를 줄일 수는 있으나 항상 전체범위처리를 한다는 것이다.
병합조인의 전체 조건은 양 테이블이 모두 조인 키에 의해 정렬되어야 한다는 것이다.

1. 특징
1) 동시적으로 처리된다. 각 키에 의해 정렬된 양쪽 행들을 순차적으로 병합하여 조인을 수행한다
2) 인덱스가 필요하다. 양 테이블의 모두 조인 키에 의해 정렬되어 있어야 한다.
3) 바깥 테이블과 안쪽 테이블의 크기는 성능과 관련이 없다.
    그러나 안쪽 테이블에 중복행이 존재하지 않을때 메모리 사용량이 적으며, 권장한다.
3) 결코 부분범위처리를 할 수가 없으며, 항상 전체범위처리를 한다.
4) 주로 스캔방식으로 처리된다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 액세스이고 머지작업은 스캔방식이다.
6) 조인의 방향과는 전혀 무관하다.
4) 테이블 스캔수가 한번이다.

2. 주의점
1) 뒤에 쓰이는 테이블이 조인키에 의해 중복된 값이 없어야 조인의 성능이 좋아진다.
2) 주로 다:1의 관계에서 주로 사용한다.

예)

SELECT *

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (MERGE join, FORCE ORDER);

 

SELECT *

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (MERGE join);

  

SELECT *

FROM Sales.CustomerAddress AS ca

INNER JOIN Sales.Customer AS c

ON c.CustomerID = ca.CustomerID

OPTION (MERGE join);

[Hash Join]
해시 조인에서는 바깥 테이블이 해시 버킷을 만드는데 사용된다. 이것을 빌드입력이라고 부른다. 안쪽 테이블은 검색이라고 부른다.

1. 특징
1) 단계별로 처리된다. 먼저 전체 빌드 입력을 스캔하거나 계산한 다음 해시 테이블을 메모리에 작성한다
    (바깥 테이블 -> 안쪽 테이블 순서)
2) 바깥 테이블은 안쪽 테이블이 처리 되기 전에 완전히 읽혀져야 하므로 응답시간이 조인들 중에서 가장늦다
3) 해시 조인의 결과에는 순서가 없다, 그 이유는 해시 버킷의 순서대로 결과가 출력되기 때문이다.
    따라서 순서가 중요하다면 반드시 ORDER BY 절을 사용해야 한다.
4)  인덱스는 사용되지 않는다.  따라서 임의성 쿼리에 탁월한 성능을 발휘한다
5) 해시 방법을 사용하면 즉시 해시 테이블이 생성되고 실시간 인덱스처럼 등장한다. 
   그러나 중요한것은 메모리에서 바로 삭제된다.  똑같은 쿼리를 반복 수행하는 경우는 매번 새 테이블을 
   생성해야 하므로 인덱스를 만들어 두는것이 효과적이다.

2.주의점
해시 조인에서는 작은 테이블이 바깥 테이블로 사용되어야 한다. 큰 테이블을 사용하면 메모리 사용량이 늘어나기 때문이다.

예)
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
OPTION (hash join, FORCE ORDER);

 

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
OPTION (hash join);

[비교]

입력키정렬	LOOP	반드시 필요한것은 아니지만 더 나은 성능을 위해 바깥 테이블이 정렬될수도 있다
	MERGE	양테이블 반드시 조인 키에 의해 정렬되어야 한다.
	HASH	전혀 필요업다.
테이블구별	LOOP	안쪽 테이블에 인덱스가 있거나 테이블 크기가 작아야 한다.
	MERGE	중요하지 않다
	HASH	작은 테이블이 바깥 테이블이 되어야 한다.
메모리 사용량	LOOP	추가 메모리 필요없다
	MERGE	백 루프를 위해 필요
	HASH	해쉬 버킷을 위해 필요

정리하면 적은 행에 대해, 인덱스가 있는 테이블에 대해서는 중접 루프조인이 사용될 가능성이 높다. 하지만 용량이 커지고, 행수가 많아지면 중첩루프 조인은 행수 만큼의 테이블 스캔이 발생하므로 매우 느린 방법이다. 이때는 해시 조인이 발생된다. 해시 조인은 인덱스가 아예 없는 조인에 흔히 사용된다.
병합조인은 빠른 조인이기는 하지만 이를 충족시키기 위해서는 양쪽 테아블이 키 값에 의 해 정렬되어 있어야 하고 1:다의 조건을 만족해야 한다.