Nested Loop Join 중첩 루프 조인(NLJ)

WHERE절에 있는 조건에 따라 두 테이블에서 하나의 테이블을 기준으로 삼아 다른 하나의 테이블에 있는 데이터를 읽는 방식으로

조인을 수행한다. 여기서 기준이 되는 테이블을 드라이빙 테이블(Driving Table) 혹은 Outer 테이블이라고 부르며, 나머지 하나의 

테이블을 Inner 테이블이라고 한다.

Ex)

SELECT emp.employee_id, emp.department_id, dep.department_name

FROM employees emp,

           departments dep

WHERE emp.department_id = dep.department_id;

드라이빙테이블 : employees

옵티마이저는 맨먼저 employee 테이블의 한 로우를 읽어 해당 로우의 department_id 값을 찾고, 이값을 기준으로 departments 테이블에서 

이와같은 값을 가진 로우를 검색 한다. 검색이 완료되면 다시 employees 테이블의 두 번째 로우를 읽고 departments 테이블에서 데이터를 

검색하며, 이러한 방식으로 조인을 처리하는 것을 NLJ라 한다.

- 첫 번째 로우를 받는 시간은 빠르지만, 전체 결과를 받기까지는 시간이 걸림.

-메모리가 필요없음

-드라이빙 테이블의 선택이 중요함

-inner 테이블 검색시 인덱스를 사용하므로 inner 테이블의 인덱스 효율이 좋아야함

-드라이빙 테이블 검색에는 full table scan 이나 index scan 방식을 사용

-쿼리 수행결과가 전체 로우의 15%일때, 즉 적은 수의 로우를 검색 할 때 사용  

-조인조건에 " = " 연산자가 사용될 경우 옵티마이저는 NLJ를 선택 

Sort Merge Join 정렬 병합 조인(SMJ)

두 테이블을  각각 정렬 후 조인 조건에 맞는 건을 찾아 합치는(merge)방식의 조인 방법

-NLJ와 다르게 드라이빙 테이블 없음, 두 테이블 모두 동등 레벨

-첫 번째 로우를 받는 시간은 느리지만, 전체 로우가 반환되는 시간은 빠름

 (각 두테이블을 정렬 뒤 병합하므로, 정렬 작업이 끝날 때 까지 어떤 로우도 반환하지 않기 때문)

-정렬작업위해 추가메모리 사용. SORT_AREA_SIZE 파라미터에 명시. 명시된 값이상의 메모리 사용시 디스크 I/O 발생

-NLJ보다 많은 양의 데이터 처리시 유리. 

-메모리만 이용해 정렬 작업 할 경우 넓은 범위의 값을 검색하는데 유리

-정렬 작업이 전체 성능에 많은 영향을 미치므로 SELECT 절에서 불필요한 컬럼은 제거해서 정렬 작업시 부하를 줄여 주어야함

 (SELECT 절에서 꼭 필요한 것만 선택 할 것)

-조인 조건에서 >, >=, <, <= 와 같은 비동등 연산자가 사용 될 경우 옵티마이저는 SMJ를 선택  

Hash Join 해쉬 조인(HJ)

두 테이블 중 WHERE 조건에 의해 필터링 된 로우수가 적은 테이블을 대상으로 해쉬 테이블을 만든 후, 조인 조건에 따라 다른 

하나의 테이블의 데이터를 검색하는 방법.

-해쉬 테이블을 만든다는 것을 제외하면 NLJ와 비슷

-해쉬 테이블이 드라이빙 테이블이 되어 NLJ를 수행 한다고 보면 됨

-HASH_AREA_SIZE 에 지정된 메모리 값 내에서 해쉬 테이블 생성되므로 해쉬대상 테이블의 크기가 이 값 내에 있을 경우 유리

 (일반적으로  HASH_AREA_SIZE 는 SORT_AREA_SIZE의 두배)

-해쉬 대상의 테이블을 드라이빙 테이블(driving table) 또는 빌드 테이블(build table) 이라함

-조인 조건에서 동등조건(=)이 사용되었을 때만 사용가능

-반환되는 로우수가 적은 테이블(이 테이블을 대상으로 해서 해쉬테이블 생성)과 이에 반해 반환되는 로우수가 상당히 많은 

 테이블을 조인시 유리

출처 : 뇌를 자극하는 오라클 프로그래밍 P785