NL 조인

NL(Nested Loop) 조인

Nested Loop Join 의 개념

   NL 조인의 영문의 뜻 : 중첩된(Nested) + 반복(Loop) + 연결(Join)

   * 프로그래밍 언어에서 For 문장 처럼 반복(Loop) 처리 

1. 두개의 테이블에서, 하나의 집합(테이블)을 기준으로 순차적으로 상대방 테이블의 row 를 결합하여 원하는 결과를 추출하는 테이블 연결 방식
2. 결합하기 위해 기준이 되는 테이블(선행테이블=드라이빙(driving) 테이블=outer테이블) : 드라이빙(driving) 테이블( OUTER 테이블, 즉 바깥쪽 테이블)
3. 결합되어지는 테이블(후행=드리븐(driven) 테이블=inner테이블) : 드리븐(driven) 테이블(INNER 테이블, 즉 안쪽 테이블)
4. NL 조인에서는 드라이빙 테이블의 각 row 에 대하여 loop 방식으로 조인이 되는데 드라이빙 테이블의 집합을 줄여주는 조건이 (where에서 사용된 컬럼에 인덱스가 있는가?) NL 조인의 성능을 결정함.

• 예시) USE_NL(각각 테이블에 어떤 컬럼에 인덱스를 이용 할것 인가? )

SELECT 고객.* ,주문.*
  FROM 고객 -- 1) [고객]테이블은 어떤 컬럼에 인덱스가 있으면 좋은가?
  JOIN 주문 -- 2) [주문]테이블은 어떤 컬럼에 인덱스가 있으면 좋은가?
    ON 주문.고객번호 = 고객.고객번호
 WHERE 고객.고객명='홍길동'
   AND 주문.주문일자='201909';

• 고객(outer,드라이빙테이블,선행 테이블)은 WHERE절의 '=' 조건(고객명) 인덱스 여부,
• 주문(inner,드리븐테이블,후행 테이블)은 고객번호 컬럼(조인되는컬럼)의 인덱스 여부가 N/L조인의 성능을 결정.

NL조인 시 인덱스 의 중요성

   * 인덱스의 중요성

   :# outer(선행,driving ) 테이블이 한 row 씩 반복해 가면서 inner(후행,driven ) 테이블로  조인이 이루어짐

1. inner(후행) 테이블의 컬럼은 outer(선행) 테이블의 컬럼을 받아서 데이터를 빨리 찾기하기 위해서는 인덱스가 반드시 있어야함.(성능 향상 포인트)
2. inner 테이블의 크기가 적다면 테이블 전체를 메모리에 읽어서 반복적으로 검색하는 것이 빠름
3. 조인되는 값들의 카디널리티(cardinality) 가 높을 수록, 한 번 스캔되어 조인된 자료가 다음 row 에서 조인에 사용될 확률이 낮아지기 때문에 스캔에 의한 조인 효율은 저하

• 원하는 값이 존재하는 지 빠르게 확인하기 위한 목적과 그 값에 대한 데이터를 빠르게 읽어 내기 위해서 인덱스 오브젝트는 N/L 조인에서 (특히 inner 테이블의 액세스 시) 반드시 필요

인덱스 조건

   * NL조인에서 인덱스의 중요성 

1. 선행(드라이빙,outer) 테이블은 where 절에 사용된 컬럼의 인덱스 여부가 중요
2. 후행(드리븐,inner) 테이블은 조인조건 컬럼의 인덱스가 중요

   * 

1. outer 테이블 조회 후 1건씩 순차적(sequential)으로 inner 테이블에 접근

• INDEX 구성
• EMP.IX_EMP_01 ( DEPTNO)
• DEPT.IX_DEPT_01 ( DEPTNO))

SELECT /*+ USE_NL(B) LEADING(A) */ ENAME,JOB,B.DNAME   
  FROM EMP A   
  JOIN DEPT B     
    ON A.DEPTNO = B.DEPTNO
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |       |    17 (100)|          |     14 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS                |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      11 |
|   2 |   NESTED LOOPS               |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL         | EMP        |      1 |     14 |   364 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | IX_DEPT_01 |     14 |      1 |       |     0   (0)|          |     14 |00:00:00.01 |       3 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT       |     14 |      1 |    22 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------

LEADING 이나 ORDERED 힌트와 같이 사용 추천

• INDEX 구성
• EMP.IX_EMP_01 ( DEPTNO)
• DEPT.IX_DEPT_01 ( DEPTNO))

SELECT /*+ USE_NL(A,B) LEADING(A) */ ENAME,JOB,B.DNAME   
  FROM EMP A   JOIN DEPT B     ON A.DEPTNO = B.DEPTNO
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |       |    17 (100)|          |     14 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS                |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      11 |
|   2 |   NESTED LOOPS               |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL         | EMP        |      1 |     14 |   364 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | IX_DEPT_01 |     14 |      1 |       |     0   (0)|          |     14 |00:00:00.01 |       3 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT       |     14 |      1 |    22 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------

USE_NL 괄호 안의 테이블은 NL조인 적용 대상 테이블

SELECT /*+ USE_NL(A,B) LEADING(B) */ ENAME,JOB,B.DNAME   
  FROM EMP A   
  JOIN DEPT B     ON A.DEPTNO = B.DEPTNO
 

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      10 |
|   1 |  NESTED LOOPS                |           |      1 |     14 |   672 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |      10 |
|   2 |   NESTED LOOPS               |           |      1 |     20 |   672 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |       9 |
|   3 |    TABLE ACCESS FULL         | DEPT      |      1 |      4 |    88 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | IX_EMP_01 |      4 |      5 |       |     0   (0)|          |     14 |00:00:00.01 |       2 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP       |     14 |      4 |   104 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------

NL조인 - 파이썬으로 구현한 예제

1. NL 조인은 두 개의 테이블을 조인할 때, 한 테이블(외부 테이블)을 먼저 순차적으로 스캔하고, 스캔한 각 행마다 다른 테이블(내부 테이블)을 반복적으로 탐색하여 조인하는 방식
2. 이중 for 루프와 같아서 이름이 '중첩 루프(Nested Loop)' 조인
3. 외부 테이블(Outer Table): 바깥쪽 루프를 도는 테이블. 주로 크기가 작거나 인덱스가 있는 테이블이 선택됨.
4. 내부 테이블(Inner Table): 안쪽 루프를 도는 테이블. 외부 테이블의 행 수만큼 반복적으로 스캔. 내부 테이블에 조인되는 컬럼에 인덱스가 있으면 성능이 매우 향상됨

• 아래 코드는 employees 테이블과 departments 테이블을 NL 조인하는 상황을 가정
• 이 예제에서는 departments를 외부 테이블로, employees를 내부 테이블로 사용.

# 'departments' 테이블 (외부 테이블)
departments = [
    {'dept_id': 10, 'dept_name': '인사팀'},
    {'dept_id': 20, 'dept_name': '개발팀'},
    {'dept_id': 30, 'dept_name': '마케팅팀'},
]

# 'employees' 테이블 (내부 테이블)
employees = [
    {'emp_id': 1, 'name': '김철수', 'dept_id': 20},
    {'emp_id': 2, 'name': '박영희', 'dept_id': 10},
    {'emp_id': 3, 'name': '이민호', 'dept_id': 20},
    {'emp_id': 4, 'name': '최민수', 'dept_id': 30},
    {'emp_id': 5, 'name': '정수미', 'dept_id': 40},  # 조인되지 않는 데이터
]

# 조인된 결과 
joined_results = []

# 외부 루프: departments 테이블을 순회
for dept in departments:
    # 내부 루프: employees 테이블을 순회
    for emp in employees:
        # 조인 조건: 두 테이블의 dept_id가 같은지 비교
        if dept['dept_id'] == emp['dept_id']:
            # 조건이 일치하면 조인 결과를 추가
            joined_row = {
                'emp_id': emp['emp_id'],
                'name': emp['name'],
                'dept_name': dept['dept_name']
            }
            joined_results.append(joined_row)

# 조인 결과 출력
for result in joined_results:
    print(result)

# 예상 출력:
# {'emp_id': 2, 'name': '박영희', 'dept_name': '인사팀'}
# {'emp_id': 1, 'name': '김철수', 'dept_name': '개발팀'}
# {'emp_id': 3, 'name': '이민호', 'dept_name': '개발팀'}
# {'emp_id': 4, 'name': '최민수', 'dept_name': '마케팅팀'}

NL 조인의 특징

• 성능: 외부 테이블의 크기(건수) 와 내부 테이블의 조인되는 컬럼의 인덱스 존재 여부에 따라 성능이 크게 차이남.
• 외부 테이블의 크기: 외부 테이블의 행 수가 적을수록 내부 루프를 도는 횟수가 줄어들어 효율적임.
• 내부 테이블의 인덱스: 내부 테이블의 조인되는 컬럼에 인덱스가 있으면, 내부 루프가 전체 테이블을 스캔하는 대신 인덱스 스캔을 통해 필요한 행만 빠르게 찾으므로 성능이 매우 좋아짐.
• 활용: 주로 외부 테이블이 작고, 내부 테이블의 조인 키에 인덱스가 잘 구성되어 있을 때 효율적입니다.
• 대용량 테이블을 조인할 때는 대부분 비효율적입니다.