[친절한 SQL] NL 조인

 

■ 기본 메커니즘

- 아래 사원과 고객 테이블이있다. 이 두 테이블에서 1996년 1월 1일 이후 입사한 사원이 관리하는 고객 테이터를 추출하는 프로그램을 작성해보자

- 일반적으로 NL조인은 Outer(선행)와 Inner(후행) 양쪽 테이블 모두 인덱스를 이용한다. Outer 테이블은 사이즈가 크지 않으면 인덱스를 사용하지않고 Table Full Scan을 하기도한다. Table Full Scan을 하더라도 한번에 그치기 때문이다. 반면 Inner 쪽 테이블은 인덱스를 사용해야 한다. Inner 루프에서는 관리사원번호 INDEX를 읽어야한다. 그렇지 않을시 Outer루프에서 읽은 건수만큼의 Table Full Scan을 반복하게된다.

 

1. 사원_X1 인덱스에서 입사일자 >= '19960101' 인 첫번째 레코드 검색

2. 인덱스에서 읽은 ROWID로 사원 테이블을 찾아간다.

3. 사원 테이블에서 읽은 사원번호 '0006'으로 고객_X1 인덱스를 찾아간다.

4. 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아간다.

 

 

■ NL 조인 실행계획 제어

위의 그림을 해석해보면 A->B->C->D순으로 조인하되, B,C는 NL 조인으로  D는 Hash 조인으로 조인하라는 뜻이다.

위의 그림은 ordered 대신 leading 힌트를 사용한 SQL문이다. leading 안의 Table Alias 순서대로 조인이 진행된다.

 

 

■ NL 조인 특징 요약

- NL 조인의 첫 번째 특징은 랜덤 액세스 위주의 조인 방식이다.  레코드 하나를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다. 

* 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유다.

 

- NL 조인의 두 번째 특징은 조인을 한 레코드씩 순차적으로 진행한다는 점이다.  이 두번째 특징으로 부분범위 처리가 가능한 상황에서 아무리 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있다. 

 

 

■ NL 조인 확장 메커니즘

- 버전이 올라가면서 오라클은 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입했다.

- "테이블 Prefetch" = 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다.

- "배치 I/O" = 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다.

- 두 기능 모두, 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안되었다.

 

1) 테이블 Prefetch

 

2) 배치 I/O 실행계획

 

 

 

참조: 친절한 SQL 튜닝(개발자를 위한 SQL 튜닝 입문서)