NL 조인
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
예시)
SELECT *
FROM 고객, 주문
WHERE 고객.고객번호 = 주문.고객번호
AND 고객.고객명='홍길동'
AND 주문.주문일자='201909';
1 인덱스 조건[편집]
- 드라이빙 테이블은 필터링 조건이 중요
- 세컨드 테이블은 조인조건의 인덱스가 중요
- 드라이빙 테이블 조회 후 1건씩 순차적으로 세컨트 테이블에 접근
- 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 |
-------------------------------------------------------------------------------------------------------------------------------------
2 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 |
-------------------------------------------------------------------------------------------------------------------------------------
3 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 |
------------------------------------------------------------------------------------------------------------------------------------