(→인덱스 조건) |
(→인덱스 조건) |
||
| 35번째 줄: | 35번째 줄: | ||
==== 인덱스 조건 ==== | ==== 인덱스 조건 ==== | ||
{{틀:서브 | {{틀:서브 | ||
|제목= NL조인에서 인덱스의 중요성 | |제목= * NL조인에서 인덱스의 중요성 | ||
# 선행(드라이빙,outer) 테이블은 where 절에 사용된 컬럼의 인덱스 여부가 중요 | # 선행(드라이빙,outer) 테이블은 where 절에 사용된 컬럼의 인덱스 여부가 중요 | ||
# 후행(드리븐,inner) 테이블은 조인조건 컬럼의 인덱스가 중요 | # 후행(드리븐,inner) 테이블은 조인조건 컬럼의 인덱스가 중요 | ||
2025년 8월 8일 (금) 10:56 판
NL(Nested Loop) 조인
Nested Loop Join 의 개념
NL 조인의 영문의 뜻 : 중첩된(Nested) + 반복(Loop) + 연결(Join)
* 프로그래밍 언어에서 For 문장 처럼 반복(Loop) 처리
- 두개의 테이블에서, 하나의 집합(테이블)을 기준으로 순차적으로 상대방 테이블의 row 를 결합하여 원하는 결과를 추출하는 테이블 연결 방식
- 결합하기 위해 기준이 되는 테이블(선행테이블=드라이빙(driving) 테이블=outer테이블) : 드라이빙(driving) 테이블( OUTER 테이블, 즉 바깥쪽 테이블)
- 결합되어지는 테이블(후행=드리븐(driven) 테이블=inner테이블) : 드리븐(driven) 테이블(INNER 테이블, 즉 안쪽 테이블)
- 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 ) 테이블로 조인이 이루어짐
- inner(후행) 테이블의 컬럼은 outer(선행) 테이블의 컬럼을 받아서 데이터를 빨리 찾기하기 위해서는 인덱스가 반드시 있어야함.(성능 향상 포인트)
- inner 테이블의 크기가 적다면 테이블 전체를 메모리에 읽어서 반복적으로 검색하는 것이 빠름
- 조인되는 값들의 카디널리티(cardinality) 가 높을 수록, 한 번 스캔되어 조인된 자료가 다음 row 에서 조인에 사용될 확률이 낮아지기 때문에 스캔에 의한 조인 효율은 저하
- 원하는 값이 존재하는 지 빠르게 확인하기 위한 목적과 그 값에 대한 데이터를 빠르게 읽어 내기 위해서 인덱스 오브젝트는 N/L 조인에서 (특히 inner 테이블의 액세스 시) 반드시 필요
인덱스 조건
* NL조인에서 인덱스의 중요성
- 선행(드라이빙,outer) 테이블은 where 절에 사용된 컬럼의 인덱스 여부가 중요
- 후행(드리븐,inner) 테이블은 조인조건 컬럼의 인덱스가 중요
# 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조인 - 파이썬으로 구현한 예제
- NL 조인은 두 개의 테이블을 조인할 때, 한 테이블(외부 테이블)을 먼저 순차적으로 스캔하고, 스캔한 각 행마다 다른 테이블(내부 테이블)을 반복적으로 탐색하여 조인하는 방식
- 이중 for 루프와 같아서 이름이 '중첩 루프(Nested Loop)' 조인
- 외부 테이블(Outer Table): 바깥쪽 루프를 도는 테이블. 주로 크기가 작거나 인덱스가 있는 테이블이 선택됨.
- 내부 테이블(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 조인의 특징
- 성능: 외부 테이블의 크기(건수) 와 내부 테이블의 조인되는 컬럼의 인덱스 존재 여부에 따라 성능이 크게 차이남.
- 외부 테이블의 크기: 외부 테이블의 행 수가 적을수록 내부 루프를 도는 횟수가 줄어들어 효율적임.
- 내부 테이블의 인덱스: 내부 테이블의 조인되는 컬럼에 인덱스가 있으면, 내부 루프가 전체 테이블을 스캔하는 대신 인덱스 스캔을 통해 필요한 행만 빠르게 찾으므로 성능이 매우 좋아짐.
- 활용: 주로 외부 테이블이 작고, 내부 테이블의 조인 키에 인덱스가 잘 구성되어 있을 때 효율적입니다.
- 대용량 테이블을 조인할 때는 대부분 비효율적입니다.