SQL튜닝 - 소트머지조인을 활용한 조인튜닝 (소트머지조인의 메커니즘, 특징, 이점)

SQL튜닝 - 소트머지조인을 활용한 조인튜닝 (소트머지조인의 메커니즘, 특징, 이점)

 

 

NL 조인은 INNER TABLE(CHECK TABLE)의 인덱스에 조인조건 칼럼이 포함돼 있는지가 중요합니다. 포함돼있지 않다면 OUTER TABLE의 결과 레코드 수 만큼 INNER TABLE 전체를 스캔하기 때문입니다. 이때, 옵티마이저는 소트머지 조인 혹은 해시 조인을 고려합니다. 소트머지 조인은 이름이 의미하는 것 처럼 두 테이블을 각각 정렬할 다음 두 집합을 머지하는 방식입니다. 소트머지 조인은 크게 두 단계로 나눌 수 있습니다.

 

- 소트단계 : 양쪽 집합을 조인 칼럼 기준으로 정렬

- 머지단계 : 정렬된 양쪽 집합을 서로 머징

 

조인 칼럼에 인덱스가 있으면 소트 단계를 거치지 않고 곧바로 조인할 수도 있습니다. ORACLE은 조인 연산자가 부등호이거나 조인 조건이 없어도 소트머지 조인으로 처리할 수 있습니다. SQL SERVER는 조인 연산자가 = 일 때만 소트머지조인을 수행할 수 있습니다.

 

소트머지조인의 메커니즘

 

아래 SQL은 USE_MERGE(CHECK TABLE)힌트를 통해 DEPT 테이블을 기준으로 EMP 테이블과 조인할 때, 소트머지조인을 유도합니다.

 

SELECT /*+ORDER USE_MERGE(E)*/
D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO

-- EXECUTION PLAN

SELECT STATEMENT
    MERGE JOIN
      SORT JOIN
          TABLE ACCESS BY INDEX ROWID OF DEPT
              INDEX FULL SCAN OF PK_DEPT
      SORT JOIN
          TABLE ACCESS FULL OF EMP

 

SQL SERVER에 경우, 다음과 같이 조인합니다.

 

select 
*
from 주문 O, 고객 C
where O.주문일자 >= TRUNC(SYSDATE)
AND C.고객번호 = O.고객번호
OPTION(FORCE ORDER, MERGE JOIN)

 

INNER TABLE은 정렬돼있기 때문에 조인에 실패하는 레코드를 만나는 순간 MERGING을 중단합니다. 또한, OUTER TABLE의 결과집합만큼 INNER TABLE을 반복해서 스캔하지 않아도 됩니다. 소트머지조인의 PSEUDO 코드는 다음과 같습니다.

 

-- OUTER 집합(정렬된 dept) 에서 첫 번째 로우 O를 가져온다.
-- INNER 집합 (정렬된 emp) 에서 첫 번째 로우 I를 가져온다.
LOOP
  양쪽 집합 중 어느 것이든 끝에 도달하면 Loop를 빠져나간다
  IF O = I
    조인에 성공한 로우를 리턴한다
    inner 집합에서 다음 로우 i 를 가져온다.
  ELSE IF O < I
    outer 집합에서 다음 로우 o를 가져온다.
  ELSE
    INNER 집합에서 다음 로우 I를 가져온다
  END IF
END LOOP

 

소트머지조인의 특징

 

 

소트머지조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없습니다. 정렬 시, PGA 영역을 활용하기 때문에 래치없이 빠른 정렬이 가능합니다. 따라서 소트 부하만 감수한다면, 건건이 버퍼 캐시를 경유하는 NL 조인보다 빠릅니다. PGA에 INNER TABLE은 반드시 저장해 정렬하고 OUTER TABLE의 경우 인덱스가 있다면 저장하지 않고 머징할 수 있습니다. NL 조인은 머징과정에서 인덱스를 사용하지 않기 때문에 조인 칼럼에 대한 인덱스가 없는 상황에서 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을때 유리합니다.

 

• 조인 전 양쪽테이블의 결과집합을 정렬합니다.

NL조인은 정렬없이 OUTER 집합을 한 건씩 차례로 조인합니다. 반면, 소트머지조인은 양쪽 집합을 조인 칼럼을 기준으로 정렬할 후 조인을 시작합니다. 대량집합을 NL 조인할 경우, 과도한 TABLE RANDOM ACCESS로 인해 비효율이 발생합니다. 만약, 정렬해야할 결과집합이 초대용량이라면 정렬 자체가 큰 비용을 수반하기 때문에 성능 개선 효과를 얻지 못할 수 있습니다. 하지만 일반 인덱스나 클러스터형 인덱스 처럼 미리 정렬된 오브젝트를 이용하면 바로 조인을 수행할 수 있습니다.

 

• 부분적인 부분범위처리가 가능합니다.

 

소트 머지 조인은 양쪽 집합을 정렬하기 때문에 부분처리가 불가능할것 같지만 부분적으로 가능합니다. 일부 레코드에 대해서 FETCH 하다가 멈추면 OUTER 집합은 끝까지 일지 않아도 되기 때문입니다.

 

• 테이블별 검색 조건에 의해 전체 일량이 결정됩니다.

 

NL 조인은 OUTER 테이블의 결과 집합에 의해 전체 일량이 좌우됩니다. 반면, 소트머지 조인은 두 집합을 각각 정렬 후 조인하기 때문에 테이블별 검색 조건에 의해 일량이 좌우됩니다.

 

• 스캔 위주의 조인방식입니다.

NL 조인이 랜덤 엑세스 위주의 조인방식이라면 소트머지조인은 스캔위주의 조인방식입니다. INNER TABLE을 반복해서 엑세스하지 않기때문에 머지 과정에서 랜덤 엑세스가 발생하지 않습니다. 테이블의 검색 조건에 따라 결과집합을 찾을 때는 인덱스가 사용되지만 머지 과정에서 인덱스가 개입하지 않습니다. 조건 검색시 랜덤 엑세스가 많다면 소트머지 조인의 이점이 사라질 수 있습니다.