[DB] Nested Loop Join

부분 범위처리의 핵심 조인

2중 반복문으로 구현이 가능한 Nested Loop Join이다.
이 조인 방법은 초대량 테이블에서 아주 나쁜 성능을 보이지만 반대로 극적인 결과를 낼수있다.
바로 부분 범위처리에 특화된 특징을 가지기 때문이다.

부분 범위처리란?

소트 머지조인이나 해시조인같은 경우 PGA에서 해시영역등을 할당받고 테이블을 생성하는등의 부가적 비용이 발생한다. 이들은 한번 영역을 할당받고 준비가 끝나면 최종 작업까지 극적인 성능을 보이지만, Nested Loop 조인은 이러한 부가적인 비용이 필요없다.
왜냐하면 하나의 레코드 단위로 Join을 시도하며 결과를 도출하기 때문이다.
즉 쉽게말해 그냥 무식하게 조인하므로 부가적 비용이 들지않고, 레코드 단위로 작업하므로 사용자가 원하는 범위안에서만 조인이 가능하기때문에 초대량 데이터라도 부분범위처리를 활용하면 극적인 성능을 보인다.

NL조인의 특징

• 하나의 레코드 단위로 조인하므로 특정 범위만 검색하기 용이하다.
• Random 액세스 위주의 조인방식 이므로 초대량 테이블의 조인은 매우 비효율적이다.
• 인덱스 전략 구성이 매우 중요하다.

NL조인의 내부 원리

SELECT /*+ ordered use_nl(e) */
       E.EMP_NO, E.ENAME, D.DNAME, E.JOP, E.SAL
FROM DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO     --- ①
    AND D.LOC = 'SEOUL'         --- ②
    AND D.GB = '2'              --- ③
    AND E.SAL >= 1500           --- ④
ORDER BY SAL DESC;           

Answer :: ② -> ③ -> ① -> ④

위 쿼리를 진행할때 NL 조인의 내부 동작방식이다.

먼저 NL조인은 레코드 마다 조인을 시도하고 결과를 도출 하므로 첫번쨰 조건을 만족하는 레코드가 진행된다.

• dept 테이블에서 Loc 인덱스를 경유해 키값이 'SEOUL' 인 레코드를 검색한다
• 해당 인덱스 레코드는 GB = '2'를 필터링 하기위해 dept테이블로 Random 액세스 한다.
• GB = '2'에 필터링된 레코드는 다시 e.deptno = d.deptno를 만족하는 인덱스를 찾는다
• 앞선 결과에서 emp테이블이 sal >= 1500을 위해 Random 액세스 하고, 최종 결과집합에 포함 시킨다.

이처럼 먼저 액세스 되는 레코드의 범위가 NL조인의 전체 일량을 좌우하며, 하나의 레코드씩 조인하므로 일정 부분까지 처리하기가 용이하다.
OLTP 환경에서 적합한 방식이다.

부하 지점

첫 번쨰 Loc 인덱스를 검색하는 지점에서 = 조건으로 스캔했으므로 인덱스 스캔에는 비효율이 없다.
그후 gb = '2' 를 필터링하는 Random 액세스 지점에서 첫번째 부하가 발생한다.

• 만약 gb = '2'를 필터링하는 시점에서 필터링 되는 비율이 높다면 인덱스 구성을 변경하는 방안을 고려한다.

두번째 부하지점은 emp 인덱스를 탐색하는 부분이며 Outer테이블인 dept를 읽고나서 얼마나 조인 액세스가 발생하냐에 따라 일량이 결정된다.
이역시 Random 액세스에 해당되며 만약 emp인덱스의 높이가 3 이라면 매건마다 그만큼의 부하가 발생한다.

세번쨰 부하지점은 emp 테이블을 랜덤액세스 하는 부분이며 역시 sal >= 1500 조건에 의해 필터링 되는 레코드가 많다면 튜닝을 고려해야 한다.

이후 최적의 조건을 갖춘 NL조인을 부분처리할때, OLTP환경에서는 최적의 퍼포먼스를 보여줄 수 있다.