2.3.3. Nested Loops 조인과 Sort Merge 조인의 비교

2.3.3 Nested Loops 조인과 Sort Merge 조인의 비교

 SELECT a.FLD1, ........., b.COL1 ...........
 FROM  TAB1 a, TAB2 b
 WHERE a.KEY1 = b.KEY2  
 AND   a.FLD1 = '111'
 AND   a.FLD2 LIKE 'AB%'
 AND   b.COL1 = '10' <=== 삭제
 

[VLDB: 그림 2-2-17  Nested Loops Join]

[VLDB: 그림 2-2-18  Sort Merge Join]

 b.COL1 = '10' 조건이 빠짐으로써 발생한 차이점
Nested Loops 조인의 경우
  최종적으로 체크하는 역항을 하는 것으로 전체적인 일량의 차이는 없으나,
  성공률(체크 조건이 삭제를 통한) 증가로 보다 빨리 운반단위를 채우는 것이 가능하다.
Sort Merge 조인의 경우
 TAB2의 처리범위를 줄여 주는 중요한 역할을 했었지만 이 조건이 없어짐으로써
  이제 TAB2는 전체 테이블을 모두 스캔하게 되었다. 뿐만 아니라 부담이 되는
  정렬의 범위가 크게 늘어 났으며 머지할 양도 크게 증가했다.
이와 같이 한쪽에 조건을 삭제해 보았더니 Nested Loops 조인은 거의 영향을
받지 않았으나, Sort Merge 조인은 엄청난 일의 증가를 가져왔다.
이런 경우는 당연히 Nested Loops 조인이 유리하다. 그것은 선행 테이블인 TAB1에서
상수값을 받았을 때에 TAB2의 처리범위보다 받지 않았을 때의 처림 범위가
훨씬 많아졌기 때문이다.

 이번에는 좀더 확실한 차이를 알아보기 위해 양쪽 모두에 조건이 없는, 즉 전체 테이블
모두를 조인하는 예를 한번 더 들어보자, 여기서는 전체범위 처리를 하는 경우를
만들어보자

SELECT a.FLD1, ....., b.COL1 ....
FROM   TAB1 a, TAB2 B 
WHERE  a.KEY1 = b.KEY2
ORDER BY a.FLD5, b.COL5 ;

ORDER BY 를 통한 전체 범위 SQL 처리 문장 이다.

[VLDB:그림 2-2-19]

Nested Loops Join 

조인되는 양쪽에 모두 조건이 없으므로 어느 한쪽(여기서는 TAB1로 가정함)이 먼저
전체 테이블을 스탠한다. 읽혀진 KEY1의 상수값에 대응되는 로우를 KEY2 인덱스에서
찾아 ROWID 로 TAB2 의 해당 로우를 액세스한다. 이 처리방식은 선행 테이블이
전체 테이블을 스캔하므로 전체 테이블을 대상으로 랜덤 액세스가 발생한다.

만약 ORADER BY 절이 없고, 부분 범위 처리 SQL 이라면, 비록 전체 테이블에
대해 Nested Loops 방식으로 조인이 수행되었더라도 부분범위처리 방식으로
처리되면 아주 빠른 수행속도를 보장받을 수 있다.

[VLDB:그림 2-2-20 ]

Sort Merge Join

조인되는 양쪽에 모두 조건이 없으므로 각각의 테이블마다 전체 테이블을 스캔하여
연결고리가 되는 컬럼으로 정렬한 후 머지한다. 어차피 전체 테이블을 조인해야 한다면
랜덤으로 전체 테이블을 액세스하는 것보다는 스캔방식으로 전체 테이블을 액세스하는
것이 당연히 유리하다. 이와 같이 전체 테이블에 대한 Sort Merge 조인에서는 대량의
랜덤 액세스를 피할 수 없으므로 Nested Loops 조인에 비해 훨씬 더 유리해진다.

Sort Merge 조인에서 가장 중요한 것은 정렬작업이 얼마나 최적화 되었느냐에 있다.
정렬 작업이 수행속도에 가장 많은 영향을 미치는 것은 역시 메모리 내에서 얼마나
많은 정렬작업을 수행하는가에 관련이 있다.

문서에 대하여

이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^