[펌] NESTED LOOP JOIN, SORT MERGE JOIN, HASH JOIN 정리

[출처] http://zoonoo.egloos.com/2272248

1. NESTED LOOP JOIN

NESTED LOOP JOIN이란 먼저 선행테이블(Driving Table)의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다.
따라서 선행테이블의 결정과 처리범위에 따라 성능이 좌우된다.

HINT: /*+ use_nl (테이블) */

[특징]
- 순차적으로 처리된다. 선행테이블의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐만 아니라 테이블간의 연결도 순차적이다.
- 먼저 액세스되는 테이블(Driving Table)의 처리범위에 의해 처리량이 결정된다
- 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스된다. 즉, 자신에게 주어진 상수값에 의해 스스로 범위를 줄이는 것이 아니라 값을 받아서 처리범위가 정해진다.
- 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라질 수 있다.
- 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 발생된다.

[사용기준]
- 부분범위처리를 하는 경우에 주로 유리해진다.
- 조인되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리해진다.
- 주로 처리량이 적은 경우(많더라도 부분범위처리가 가능한 경우)에 유리해진다. 그것은 처리방식이 주로 랜덤 액세스방식이므로 많은 양의 랜덤 액세스가 발생한다면 수행속도가 당연히 나빠지기 때문이다.
- 순차적으로 처리되기 때문에 어떤 테이블이 먼저 액세스되느냐에 따라 수행속도에 많은 영향을 미치므로 최적의 액세스 순서가 되도록 적절한 조치가 요구된다.
- 선행테이블의 처리 범위가 많거나 연결 테이블의 랜덤 액세스의 양이 아주 많다면 SORT MERGE JOIN보다 불리해지는 경우가 많다.
- 일반적으로 온라인 어플리케이션에 유리하다.


2. SORT MERGE JOIN

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

HINT: /*+ use_merge(테이블) */

[특징]
- 동시적으로 처리된다. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다.
- 결코 부분범위처리를 할 수가 없으며, 항상 전체범위처리를 한다.
- 주로 스캔방식으로 처리된다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 액세스이고 머지작업은 스캔방식이다.
- 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않는다. 
- 조인의 방향과는 전혀 무관하다.
- 스스로 자신의 처리범위를 줄이기 위해 사용되는 인덱스는 대개 가장 유리한 한가지만 사용되어진다. 그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 작업대상을 줄여 주기 때문에 중요한 의미를 가진다. 

[사용기준] 
- 전체범위처리를 하는 경우에 주로 유리해진다.
- 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태인 경우 주로 유리해 질 수 있다. 상수값을 받아 처리(NESTED LOOP JOIN)한 범위의 크기와 처리범위를 줄여 처리(SORT MERGE JOIN)한 범위의 크기를 대비해보아 상수값을 받아 줄여진 범위가 약 30% 이상이라면 SORT MERGE JOIN이 일반적으로 유리해진다. 그러나 부분범위처리가 되는 경우라면 전혀 달라질 수 있다. 이런 경우는 처리할 전체범위를 비교하지 말고 첫번째 운반단위에 도달하기 위해 액세스하는 범위애 대해서 판단해야 한다.
- 주로 처리량이 많은 경우 (항상 전체범위처리를 해야 하는 경우)에 유리해진다. 그것은 처리방식이 주로 스캔방식이므로 많은 양의 랜덤 액세스를 줄일 수가 있기 때문이다.
- 스스로 자신의 처리범위를 어떻게 줄일 수 있느냐가 수행속도에 많은 영향을 미치므로 보다 효율적으로 액세스할 수 잇는 인덱스 구성이 중요한다.
- 처리할 데이터량이 적은 온라인 애플리케이션에서는 NESTED LOOP JOIN이 유리한 경우가 많으므로 함부로 SORT MERGE JOIN을 사용하지 말아야 한다.
- 일반적으로 배치작업시 유리하다.


3. HASH JOIN

해시값을이용하여 테이블을 조인하는 방식이다.

HINT: /*+ use_hash(테이블) */

- 적은테이블과 큰테이블의 조인시에 유리하다.
- Equal 조인에서만 가능하다.
- 선행테이블에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
- 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 스켄이 동시에 일어남.