[JOIN] NL Join (Nested Loops 조인)

 

NL 조인 ( Nested Loops 조인)

 

NL 조인은 두 테이블이 조인을 할 때, 드라이빙 테이블( Outer 테이블)에서 결합 조건에 일치하는 레코드를 내부 테이블(Inner Table)에서 조인하는 방식이다.

 

• Outer Join 에서 만족하는 레코드가 적을수록 NL 조인에서 효율이 좋다.
• 조인 조건에 해당하는 컬럼들은 인덱스를 가지고 있어야 한다.

 

NL 조인의 예시를 보자.

 

아래와 같은 SQL문이 있다고 하자.

 

SELECT   /*+ USE_NL (B) */
        A.*
      , B.*
FROM    ITEM    A
,UITEM B
WHERE A.ITEM_ID=B.ITEM_ID --- 1
AND A.ITEM_TYPE_CD = '100100' --- 2
AND A.SALE_YN = 'Y'  --- 3
AND B.SALE_YN = 'Y'  --- 4

ITEM_X01 -> ITEM_TYPE_CD
UITEM_PK -> ITEM_ID + UITEM_ID

 

힌트를 사용하여 NL 조인을 하도록 유도한다.

 

해당 SQL문에서 동작 순서는 어떻게 될까?

 

정답은 2 -> 3 -> 1 -> 4 이다.

 

그 이유는 아래의 그림을 통해  좀 더 쉽게 설명할 수 있다. 

기준 테이블 ( Driving Table) : ITEM , Inner 테이블 : UITEM

 

 

 

실행 순서

1. 인덱스 ITEM_X01를 통해 ITEM_TYPE_CD = 100100인 것을 스캔힌다.
2. 인덱스 ITEM_X01에서 읽은 rowid를 가지고  A 테이블에 액세스해 SALE_YN = 'Y' 인 필터 조건을 만족하는 레코드를 찾는다.
3. A 테이블에서 읽은 ITEM_ID와 같은 값을 가진 B 테이블의 ITEM_ID를 찾기 위해 UITEM_PK를 스캔한다.
4. B 테이블에 액세스하여 SALE_YN = 'Y' 인 레코드들을 찾는다.

 

 

그렇다면 해당 SQL 문에서 최적의 인덱스는 무엇일까?

 

 

ITEM_X01 -> ITEM_TYPE_CD + SALE_YN
UITEM_PK -> ITEM_ID + UITEM_ID + SALE_YN

 

각각 인덱스에 SALE_YN 칼럼 값도 추가하는 것이다.

그렇다면 랜덤 액세스로 테이블로 접근할 일이 없어지기 때문에 훨씬 성능이 좋아질 수 있다.

 

 

 

 

NL 조인의 특징

 

• Random 액세스 위주의 조인 방식이다. 따라서 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다.
• 조인을 한 레코드씩 순차적으로 진행한다는 점이다. 
• 인덱스 구성 전략이 특히 중요하다. 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다. 
• 이런 여러가지 특징을 종합할 때, NL 조인은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합한 조인 방식이라고 할 수 있다.

 

MySQL 에서는 오랫동안 NL 조인만 지원하고 있었다. ( 최근  MySQL 8.0.18 버전부터는 해시 조인도 지원한다.)

 

 

 

힌트 쓰는 방법

 

결합 명칭	힌트 방법
Nested Loops	SELECT /*+ USE_NL ( TABLE_NAME, TABLE_NAME2 ... ) */ COLUMN ..
Sort Merge	SELECT /*+ USE_MERGE ( TABLE_NAME, TABLE_NAME2 .. ) */ COLUMN ..
Hash Join	SELECT /*+ USE_HASH ( TABLE_NAME, TABGLE_NAME2 ... ) */ COLUMN ..

 

 

 

 

해시조인과 소트머지 조인은 다음 포스팅에서 설명하겠습니다.