-
NL 조인은 조인 컬럼을 선두로 갖는 인덱스가 있는지가 매우 중요하다.
만약 조인 컬럼을 선두로 갖는 인덱스가 없으면 Outer 테이블에서 읽히는 건마다 Innder 테이블 전체를 스캔하기 때문이다. 그럴 때 옵티마이저는 소트 머지 조인이나 해시 조인을 고려한다.
소트 머지 조인은 이름이 의미하는 것처럼 두 테이블을 각각 정렬한 다음에 두 집합을 머지(merge) 하면서 조인을 수행한다. 소트 머지 조인은 아래 두 단계로 진행된다.
1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
2. 머지 단계 : 정렬된 양쪽 집합을 서로 merge 한다.
만약 조인 컬럼에 인덱스가 있으면(오라클의 경우 outer 테이블에만 해당), 1번 소트 단계를 거치지 않고 곧바로 조인할 수도 있다.
오라클은 조인 연산자가 부등호이거나 아예 조인 조건이 없어도 소트 머지 조인으로 처리할 수 있지만, SQL 서버는 조인 연산자가 '=' 일 때만 소트 머지 조인을 수행한다는 사실도 유념해야 한다.
1. 기본 매커니즘
아래 SQL 은 dept 테이블을 기준으로 emp 테이블과 조인할 때 소트 머지 조인 방식을 사용하라고 힌트를 사용하고 있다.
select /*+ordered use_merger(e)*/ d.deptno, d.dname, e.empno, e.ename from dept d, emp e where d.deptno = e.deptno ------------------------------- Execution Plan ------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=654 Bytes=35K) 1 0 MERGE JOIN (Cost=11 Card=654 Bytes=35K) 2 1 SORT(JOIN) (Cost=11 Card=654 Bytes=14K) 3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=654 Bytes=14K) 4 1 SORT(JOIN) (Cost=5 Card=327 Bytes=11K) 5 4 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=327 Bytes=11K)
위 그림에서 주목할 점은, Inner 집합인 emp 테이블이 정렬돼 있기 때문에 조인에 실패하는 레코드를 만나는 순간 멈출 수 있다는 사실이다. 예를 들어 deptno=10 인 레코드를 찾기 위해 1번 스캔을 진행하다가 20을 만나는 순간 멈춘다.
또 한가지는 정렬된 emp 에서 스캔 시작점을 찾으려고 매번 탐색하지 않아도 된다는 점이다. 예를 들어 deptno=20 인 레코드를 찾는 2번 스캔은 1번에서 스캔하다가 멈춘 지점을 기억했다가 거기서부터 시작하면 된다.
Outer 집합인 dept 테이블도 같은 순서로 정렬돼 있으므로 가능한 일이다.
다음은 소트 머지 조인이 머지하는 방식을 pseudo 코드로 작성한 것이다.
Outer 집합(정렬된 dept)에서 첫 번째 로우 o 를 가져온다. Inner 집합(정렬된 emp)에서 첫 번째 로우 i 를 가져온다. loop 양쪽 집합 중 어느 것이든 끝에 도달하면 loop 를 빠져나간다. if o=i 이면 조인에 성공한 로우를 리턴한다. inner 집합에서 다음 로우 i 를 가져온다. else if o < i 이면 outer 집합에서 다음 로우 o 를 가져온다. else (즉 o > i 이면) inner 집합에서 다음 로우 i 를 가져온다. end if end loop실제 조인 과정이 NL 조인과 크게 다르지 않다. outer 집합과 inner 집합을 미리 정렬해 둔다는 점만 다르다.
양쪽 집합을 미리 정렬해두었기 때문에 위와 같은 처리가 가능한 것이다.
2. 소트 머지 조인의 특징
가. 조인을 하기 전에 양쪽 집합을 정렬한다.
NL 조인은 정렬없이 Outer 집합을 한 건씩 차례로 조인을 진행하지만, 소트 머지 조인은 양쪽 집합을 조인 컬럼을 기준으로 정렬한 후에 조인을 시작한다.
대량 집합 조인은 랜덤 액세스 위주의 NL 조인의 경우 비효율이 있다. 이 비효율을 줄이고자 나온 조인방식이 소트 머지 조인이다. 만약 정렬해야할 집합이 초 대용량 테이블이면 정렬 자체가 큰 비용을 수반하기 떄문에 성능 개선 효과를 얻지 못할 수도 있다. 하지만 일반 인덱스나 클러스터형 인덱스처럼 미리 정렬된 오브젝트를 이용하면 정렬작업을 하지 않고 바로 조인을 수행할 수 있어 소트 머지 조인이 대안이 될 수 있다.
나. 부분적으로 부분범위 처리가 가능하다.
소트 머지 조인은 양쪽을 정렬해야함으로 부분범위처리가 불가능할 것 같지만, 부분적으로는 가능하다. Outer 집합이 조인 컬럼 순으로 미리 정렬된 사태에서 사용자가 일부 로우만 Fetch 하다가 멈춘다면 Outer 집합은 끝까지 읽지 않아도 되기 때문이다.
다. 테이블별 검색 조건에 의해 전체 일량이 좌우된다.
NL 조인은 Outer 집합의 건마다 Inner 집합을 탐색한다. 이 때문에 Outer 집합에서 조인 대상이 되는 건수에 의해 전체 일량이 좌우된다. 그러나 소트 머지 조인은 두 집합을 각각 정렬한 후에 조인함으로 각 집합의 크기, 즉 테이블별 검색 조건에 의해 전체 일량이 좌우된다.
라. 스캔 위주의 조인방식이다.
NL 조인이 랜덤 액세스 위주의 조인 방식이라면 소트 머지 조인은 스캔 위주의 조인 방식이다. Inner 테이블을 반복 액세스하지 않으므로 머지 과정에서 랜덤 액세스가 발생하지 않는다. 하지만 랜덤 액세스가 전혀 없는 것은 아니다.
각 테이블 검색 조건에 해당하는 대상 집합을 찾을 때 인덱스를 이용한 랜덤 액세스 방식으로 처리될 수 있고, 이때 발생하는 랜덤 액세스량이 많다면 소트 머지 조인의 이점이 사라질 수 있다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형