Nested Loops Join & Sort Merger Join

조인 종류별 특징 및 활용 방안

조인은 크게 두가지 분류로 나눌수 있다.

• 어떤 집합을 먼저 액세스하여 상수 값을 만들고 그것을 이용해 대응되는 집합을 찾아가는것(Nested Loops)
• 서로 연결하기 좋도록 미리 소정의 선행작업을 수행한 다음 서로를 대응 시켜 가는 방법(Sort Merge)

1. Nested Loops Join

전통적인 조인 방법이며, 먼저 액세스한 결과를 다음의 액세스에 상수 값으로 제공해준다.

Nested Loops Join의 기본 개념

Nested Loops Join의 특징

• 순차적: Driving TABLE의 처리 범위에 있는 각각의 Row와 테이블 간의 연결 모두 순차적
• 선행적: 초기 액세스 테이블의 처리 범위에 의해 처리량이 결정
• 종속적: 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스
• 랜덤 액세스: 선행 테이블은 첫 번째 경우만 랜덤, 연결 테이블은 모두 랜덤 액세스로 수행
• 선택적: 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라짐
• 방향성, 연결고리 상태: 연결 고리의 인덱스 유무에 따라 액세스 방향 및 수행 속도에 많은 차이 발생
• 부분범위 처리 가능: 부분 범위 처리 조건을 만족하면 운반 단위가 채워지는 순간에 우선 멈출 수 있음
• 체크 조건의 영향력: 부분 범위의 경우 조건이 넓으면 운반 단위를 빨리 채우므로 빨리 수행, 전체 범위 처리일 경우 가공할 대상이 많아지므로 수행속도가 나빠짐

Nested Loops 조인의 적용 기준

• 부분 범위 처리 
• 조인되는 한 쪽이 상대방 테이블의 결과를 제공받아야만 처리 범위를 줄일 수 있는 상황일 경우
• 처리량이 적은 경우(많다면 부분처리)
• 연결 고리에 이상이 없는 경우
• 먼저 수행한 집합의 처리 범위의 크기와 얼마나 많은 처리 범위를 미리 줄여 줄 수 있느냐가 수행속도에 영햘을 미침
• 운반 단위의 크기가 수행 속도에 상당한 영향을 미침
• 대용량 처리일 경우 메모리 사용에 대한 부담이 될 경우 유리(Sort 조인이나 해쉬조인은 과도하게 메모리 사용 요구)

Nested Loops 조인의 순서결정

어떤 순서로 수행 되느냐에 따라 조인의 효율성이 달라진다.

SELECT ...

  FROM TAB x, TAB2 y, TAB3 z

 WHERE x.A1 = y.B1

   AND z.C1 = y.B2

   AND x.A2 = y.B2

   AND x.A2 = '10'

   AND y.B2 LIKE 'AB%';

TAB1 - TAB2 - TAB3

순서	Access Path	설명
TAB1 x  TAB2 y  TAB3 z	A2='10'  B1=A1 and B2 LIKE 'AB%'  C1=B2	x.A1=y.B1 은 y가 아직 액세스 되지않았음  z.C1=y.B2 의경우 z가 아직 액세스 되지 않았음  이미 x 와 y가 액스세 되어있으므로 선택

TAB2 - TAB3 - TAB1

순서	Access Path	설명
TAB2 y  TAB3 z  TAB1 x	B2 LIKE 'AB%'  C1=B2  A1=B1 and A2='10'	y 중 상수값을 가진 값만 선택됨  y 가 이미 액세스 되었으므로 선택  이미 y와 z가 액세스 되어있으므로 x와 조인된 모든것 선택

TAB3 - TAB2 - TAB1

순서	Access Path	설명
TAB3 z  TAB2 y  TAB1 x	FULL TABLE SCAN  B2=C1 and B2 LIKE 'AB%'  A1=B1 and A2='10'	z 의 유일한 조건인 z.c=y.b2 처리 불가능 z를 전체다 읽음  z 가 이미 액세스 되므로 z와 조인과 y의 상수대입 조건 수행  x 와 관련된 조건 수행

각각의 조건은 주어진 환경에 따라 성능이 달라진다. 자신의 처리 범위를 줄여 줄 수있는 다른 집합들이 액세스 된 후에나 자신이 먼저 처리범위를 줄여 줄 수 있다면 그들보다 먼저 수행 되도록 하는것이 가능하면 좋다.

2. Sort Merge Join

조인 할 집합들을 정렬한 후 서로 조인을 수행한다.

Sort Merge Join의 특징

• 동시적: 상대 집합의 처리 결과와 상관없이 자신이 보유한 처리 조건만 가지고 액세스하여 정렬을 완료한 후 머지 수행
• 독립적: 자신에게 주어진 상사값에 의해서만 범위를 줄임
• 전체범위 처리: 정렬이 완료된 후에 조인을 시작할 수 있음
• 스캔방식: 스캔방식으로 처리
• 선택적: 연결 고리가 되는 컬럼은 인덱스를 전혀 사용하지 않음
• 무방향성: 조인의 방향과는 거의 무관
• 자신의 처리범위를 줄이기 위해 인덱스는 한 가지만 사용되고 나머지는 머지 작업 대상을 줄여주기 때문에 중요함

Sort Merge Join의 적용기준

• 전체 처리로만 가능한 형태일 경우 검토
• 상대 테이블에서 값을 받지 않고도 처리범위를 줄일 수 있다면 효과가 큼
• 스캔방식의 처리로 많은 양의 랜덤 액세스를 줄일 수 있다.
• 연결 고리 이상 상태에 영향을 받지 않음
• 자신의 처리범위를 줄여줄 수 있는 인덱스 구성이 중요
• 운반 단위의 크기가 수행속도에 영향을 미치지 않음
• 온라인 상태에서는 NL이 유리
• 옵티마이저가 'ALL_ROWS'일 경우 'SORT MERGE Join' 이나 'Hash Join'의 실행계획이 자주 수립됨
• 충분한 메모리 활용이 가능하고 병렬처리를 통해 빠르게 정렬작업이 가능한 경우

3. Nested Loops Join과 Sort Merge Join의 비교

조건처리

SELECT a.FLD1, ...

     , b.COL1 ...

  FROM TAB1 a

     , TBA2 b

 WHERE a.KEY1 = b.KEY2

   AND a.FLD1 = '111'

   AND a.FLD2 LIKE 'AB%'

   AND b.COL1 = '10'  ---> 삭제 

Nested Loops Join

• 삭제된 조건은 최종적으로 체크하는 역할을 수행하므로 일의 양에는 큰 차이 없음
• 부분 처리의 경우 운반 단위가 빨리 채워져서 유리

Sort Merge Join

• TAB2의 범위를 줄여 주는 역할이었지만 삭제되었으므로 TAB2를 FULL SCAN 한 후 정렬
• 머지 양 증가

전체 범위 처리의 경우

SELECT a.Fld1, ...

     , b.COL1, ...

  FROM TAB1 a

     , TAB2 b

 WHERE a.KEY1 = b.KEY2

 ORDER BY a.FLD5, b.COL5;

Nested Loops Join

• TAB1(어느 한 쪽도 될 수 있음)이 먼제 전체 테이블을 스캔
• KEY1에 따라 KEY2 인덱스에서 찾아 TAB2의 해당 로우를 액세스
• 전체 테이블을 대상으로 랜덤 액세스 발생(대량의 랜덤액세스)
• ORDER BY가 없다면 운반 단위 처리가 가능해져 수행속도 빨라짐

Sort Merge Join

• 각각의 테이블마다 전체 테이블 스캔이 발생하여 연결고리가 되는 컬럼으로 정렬 후 머지
• 대량의 랜덤 액세스 발생이 없음(NL 조인보다 유리)
• Sort Area Size의 크기가 적으면 정렬 작업에 대한 부하로 인해 NL 보다 늦어 질 수도 있음

출처: 두리누리