조인 수행원리

조인이란 2개 이상의 테이블을 하나의 집합으로 만드는 연산이다. FROM 절에 2개 이상의 테이블 혹은 집합이 존재할 경우 조인이 수행된다.

조인은 3개 이상의 테이블을 조인한다고 하더라도 특정 시점에는 2개의 테이블 단위로 조인된다. A, B, C 테이블을 조인한다고 하면 A, B를 조인한 후 해당 결과로 나온 집합을 C와 조인하는 방식이다. 각각의 조인 단계에서는 서로 다른 조인 기법이 사용될 수 있다.

조인 기법

조인 기법의 종류에는 NL 조인, 소트 머지 조인, 해시 조인이 있다.

NL 조인 (Nested Loops Join)

NL 조인은 첫 번째 집합의 대상 건수만큼 반복하면서 두 번째 집합을 조회하여 매칭되는 행을 리턴하는 조인 기법입니다. 한 레코드(행)씩 순차적으로 진행하기 때문에 처리 범위가 넓은 경우에는 비효율적이다.

begin
  for outer in (select deptno, empno, rpad(ename, 10) ename from emp)
  loop    -- outer 루프
    for inner in (select dname from dept where deptno = outer.deptno)
    loop  -- inner 루프
      dbms_output.put_line(outer.empno||' : '||outer.ename||' : '||inner.dname);
    end loop;
  end loop;
end;

위는 NL조인 과정을 표현한 PL/SQL문이다. 2중 for문을 사용한 모습이다.

NL 조인은 특성상 인덱스 유무, 인덱스 컬럼 구성에 따라 성능이 좌우된다. 하지만 Random 액세스 위주의 조인방식이기 떄문에 인덱스 구성이 완벽하더라도 대량의 데이터 조인시에는 비효율적이다.

NL 조인은 부분 범위 처리가 가능한 OLTP(Online Transaction Processing) 환경에 적합하다. 조인을 한 레코드씩 순차적으로 진행하기 때문에 데이터의 양이 많지않은 OLTP 환경에선 매우 극적인 응답속도를 낼 수 있다.

소트 머지 조인 (SortMerge Join)

소트 머지 조인은 2개의 테이블(집합)을 조인 컬럼 기준으로 모두 정렬한 후 두 집합을 병합하면서 결과 집합을 도출한다.

소트 머지 조인은 인덱스 유무에 영향을 받지 않는다. 어차피 2개의 집합을 몯두 정렬하기 때문이다. 단, 필요한 인덱스가 이미 있는 경우에는 해당 인덱스를 이용해서 정렬 작업을 생략하기도 한다.

스캔 위주의 액세스 방식이기 떄문에 양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업은 인덱스틑 이용하여 랜덤 액세스 방식으로 처리할 수 있다. 랜덤 액세스가 많아지는 경우에는 소트 머지 조인의 이점이 사라진다.

소트머지 조인은 Outer루프와 Inner루프가 Sort Area에 미리 정렬해둔 데이터를 이용할 뿐, 실제 조인과정은 NL조인과 동일하다. 하지만, Sort Area가 PGA영역에 할당되어 래치획득과정이 없기 때문에, SGA를 경유하는 것보다 훨씬 빠르다.

소트 머지 조인은 다음과 같은 경우 사용할 수 있다.

• First 테이블에 소트연산을 대체할 인덱스가 있을 때
• 조인할 First 집합이 이미 정렬되어 있을 때
• 조인 조건식이 등치(=)조건이 아닐 때

해시 조인 (Hash Join)

해시 조인은 첫 번째 테이블(집합)을 기준으로 해시 테이블을 생성하고 두 번째 집합을 조회하면서 해시 테이블과의 해시 FUNCTION 비교를 통해 매칭되는 건들을 결과집합에 포함한다.

해시 조인은 첫 번째 집합의 용량이 PGA에 할당되는 HACK AREA 메모리 공간에 전부 다 담길 수 있을 정도의 크기라면 성능상 매우 유리해진다.

해시 조인은 소트 머지 조인처럼 정렬 부하가 있지도 않고, NL 조인처럼 랜덤 엑세스에 대한 부하도 존재하지 않는다. 하지만 해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 바로 소멸하는 자료구조이므로, 수행빈도가 높은 쿼리에서 사용하면 CPU와 메모리 사용률을 크게 증가시키고, 래치 경합이 발생하여 시스템 동시성을 떨어뜨린다.

그러므로 해시조인은 수행빈도가 낮고, 쿼리의 수행시간이 오래 걸리는 대용량 테이블을 조인할 때 주로 사용해야 한다. 배치 프로그램, DW, OLAP설 쿼리 등에서 유리한 조인 기법이다.

정리

• 대량의 테이블에서 소량의 범위만 부분 범위처리로 가져올 때, 혹은 대량의 테이블에서 극소량의 데이터를 가져올 때는 NL 조인이 유리하고, 대량의 배치 프로그램 처리를 할 때는 해시 조인이 유리하다.
• 소트 머지 조인은 NL조인보다 성능이 좋긴 하지만, 해시조인보다는 부족하기 때문에 잘 쓰이지 않는다.