"NL 조인"의 두 판 사이의 차이
DB CAFE
| 2번째 줄: | 2번째 줄: | ||
[[파일:Use nl.jpg|섬네일|가운데|USE_NL 조인]] | [[파일:Use nl.jpg|섬네일|가운데|USE_NL 조인]] | ||
| + | |||
| + | == 인덱스 조건 == | ||
| + | 드라이빙 테이블은 필터링 조건이 중요 | ||
| + | 세컨드 테이블은 조인조건의 인덱스가 중요 | ||
| + | |||
| + | |||
| + | SQL_ID 20wdrnayv7n31, child number 0 | ||
| + | ------------------------------------- | ||
| + | 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 힌트와 같이 사용 추천 == | ||
| + | SQL_ID b5ay1xamrg4jp, child number 0 | ||
| + | ------------------------------------- | ||
| + | SELECT /*+ USE_NL(A,B) LEADING(A) */ ENAME,JOB,B.DNAME FROM | ||
| + | EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO | ||
| + | |||
| + | Plan hash value: 1550058730 | ||
| + | |||
| + | ------------------------------------------------------------------------------------------------------------------------------------- | ||
| + | | 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조인 적용 대상 테이블 == | ||
| + | SQL_ID gsbsggrdrmaz8, child number 0 | ||
| + | ------------------------------------- | ||
| + | SELECT /*+ USE_NL(A,B) LEADING(B) */ ENAME,JOB,B.DNAME FROM | ||
| + | EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO | ||
| + | |||
| + | Plan hash value: 1016187388 | ||
| + | |||
| + | ------------------------------------------------------------------------------------------------------------------------------------ | ||
| + | | 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 | | ||
| + | ------------------------------------------------------------------------------------------------------------------------------------ | ||
| + | |||
| + | |||
| + | |||
[[Category:oracle]] | [[Category:oracle]] | ||
2020년 1월 10일 (금) 20:57 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 인덱스 조건[편집]
드라이빙 테이블은 필터링 조건이 중요 세컨드 테이블은 조인조건의 인덱스가 중요
SQL_ID 20wdrnayv7n31, child number 0
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 힌트와 같이 사용 추천[편집]
SQL_ID b5ay1xamrg4jp, child number 0
SELECT /*+ USE_NL(A,B) LEADING(A) */ ENAME,JOB,B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO
Plan hash value: 1550058730
| 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조인 적용 대상 테이블[편집]
SQL_ID gsbsggrdrmaz8, child number 0
SELECT /*+ USE_NL(A,B) LEADING(B) */ ENAME,JOB,B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO
Plan hash value: 1016187388
| 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 |