[ORACLE] 실행 계획에 의한 조인의 종류

실행 계획이란?

: 옵티마이저가 SQL문을 어떻게 실행할 것인지를 미리 판단할 수 있도록 시뮬레이션 해 보는 것

 

1. Nested Loop Join

: 한 테이블의 Row를 읽고 다른 테이블에서 조인 조건에 해당되는 행들을 찾아가는 방식

 

  1) Driving 테이블을 선택, (*Driving 테이블: 테이블을 조인할 때 첫 번째로 읽게 되는 테이블 <-> Inner 테이블)

  2) Driving 테이블의 각 행들에 대해 Inner table에서 조건에 만족하는 행들을 검색

  3) 조인된 데이터를 결합하여 리턴

 

  Driving 테이블로부터 순차적으로 조건에 맞는 데이터를 검색하기 때문에 성능의 영향을 많이 받음

  또, 테이블을 조인할 때 조인되는 테이블의 연결고리에 따라서도 성능에 영향을 미침

  Equi-Join 보다 NonEqui-Join이나 Outer Join이 조회 성능이 떨어짐

 

  Hint ex) SELECT /*+ use_nl(a b) */ a1, b1 FROM TMP1 a, TMP2 b WHERE a.A = b.B ...;

  ㄴ 이 때, a가 Driving, b가 Inner가 되고, b의 경우 인덱스가 있어야 함.

          만약 b에 인덱스가 없으면 Table Full Scan을 하게 됨

 

 

2. Sort Merge Join

: 각각의 테이블을 검색 조건에 의하여 정렬한 후 조인 조건에 의하여 값을 조인하는 방식

 

  1) 먼저 조인되는 각각의 테이블을 조인되는 컬럼 기준으로 정렬, 만약 사전에 정렬되어 있었다면 다시 정렬되지는 않음

  2) 정렬된 데이터를 각각 비교하여 조인조건에 맞는 데이터를 검색

  3) 조인된 테이블의 데이터를 결합하여 리턴

 

  테이블의 양에 관계 없이 무조건 테이블들을 정렬함

  대용량의 테이블을 Sort Merge하면 성능이 떨어지고 DBMS에 많은 부하를 줌

  따라서 테이블을 정렬하기 전에 조건절에서 범위를 많이 줄여야 함

 

  Equi_join에서만 사용 가능하며 다량의 데이터를 배치 처리하거나 통계 조회에 많이 쓰임

  Nested Loop와 달리 테이블의 연결고리가 성능에 영향을 미치지 않음

 

  Hint ex) SELECT /*+ use_merge(a b) */ a1, b1 FROM TMP1 a, TMP2 b WHERE a.A = b.B and a.AA between 10 and 1000 ...;

  ㄴ 최대한 처리범위를 좁히면 Sort되는 데이터 범위가 줄게 되므로 성능이 더 좋아짐

 

 

3. Hash Join

: 각각의 테이블을 Full 검색하여 해시 테이블에 저장한 후 값을 비교하여 조인

 

  1) 조인되는 각각의 테이블을 Full Scan하여 조인 컬럼에 대한 파티션 생성, (*파티션: 동일한 해시 값을 갖는 묶음)

  2) 각 파티션으로부터 작은 파티션을 메모리에 로드하여 해시 테이블을 만듬, 이 때 메모리가 부족하면 디스크 상에 해시 테이블을 저장

  3) 다른 파티션의 Row를 읽음, 이 때 메모리 내의 대응되는 Row가 있는지 검증하기 위해 해시 테이블을 이용

  4) 2), 3) 작업을 계속하여 조인을 수행

 

  Sort Merge와 마찬가지로 대용량의 배치업무 처리에 적합

  조인되는 테이블 간의 연결고리 상태에 영향을 받지 않음

  Equi_Join의 경우만 사용 가능하며 대용량 데이터를 처리하는 경우 Hash Join이 더 유리함

 

  Hint ex) SELECT /*+ use_hash(a b) */ a1, b1 FROM TMP1 a, TMP2 b WHERE a.A = b.B ...;

  

 

Nested Loop	Sort Merge	Hash
테이블을 순차적으로 스캔	테이블을 동시에 스캔	순차적 스캔 or 동시 스캔
조인 순서에 영향 받음	각각 독립적으로 스캔하여 조인 순서에는 영향 받지 않으나 처리 범위가 좁을수록 유리	처리 범위와 조인 순서 둘 다 영향 받음
연결고리 상태에 따라 영향 받음	연결고리 상태가 영향 없음	연결고리 상태가 영향 없음
좁은 범위 처리에 유리	넓은 범위 처리에 유리	대용량 데이터 처리에 유리

 

출처

DBMS의 이론과 실무 활용