오라클힌트-조인 방법 변경(USE_NL )
구로디지털
오엔제이프로그래밍실무교육센터
테이블을
조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장
입니다. 중첩 루프 조인은 중첩 반복이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 로우를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법
입니다.
즉 조인
입력 한 개를 외부 입력 테이블로 사용하고, 한 개는 내부(최하위) 입력 테이블로 사용하고 외부 루프는 외부 입력 테이블을 행
단위로 사용하고 각 외부 행에 대해 실행되는 내부 루프는 내부 입력 테이블에서 일치되는 행을 검색 하는거죠… 이것을 원시 중첩 루프 조인이라고 하는데 검색에서 인덱스를 사용하는
경우에는 인덱스 중첩 루프 조인이라고 합니다.
예를
들어 EMP 테이블과 DEPT 테이블을 조인하는
경우 dept 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에
대응하는 emp 테이블의 데이터를 추출 하는 경우라면 중첩 루프 조인에 해당 합니다. 이때 emp 테이블의 경우 건수가 많다고 가정을 하면 대부분
인덱스를 이용하도록 하여 emp 테이블의 외래키인 deptno
컬럼은 대부분 인덱스를 걸게 되죠^^
중첩
루프 조인은 테이블 중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때
연관되는 방식으로 이 중첩 루프 조인에서 테이블 중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL
SCAN이 일어나게 됩니다. 이 테이블이 드라이빙 테이블이 되는데… 이 테이블의 데이터 건마다 나머지
테이블에서 원하는 데이터를 추출하기 위해 대부분 인덱스를 사용하게 되는 겁니다.
보통 USE_NL 힌트
구문은 ORDERED 힌트 구문과 같이 사용되는데
USE_NL이 취하는 인자는 FROM절에서 두번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해
주어야 합니다. 안수로 사용되지 않은 첫 번째
테이블은 outer/driving
table이 되는 것입니다.
[형식]
[예]
아래는 Oracle 11g에서 테스트 한 결과 입니다.
analyze table emp compute statistics
analyze table dept compute statistics
아래는 Oracle 11g에서 테스트 한 결과 입니다.
analyze table emp compute statistics
analyze table dept compute statistics
EMP 테이블의 deptno는 인덱스가
있다.
SQL> select
2 e.ename,
3 d.dname
4 from
dept d, emp
e
5 where
e.deptno = d.deptno ;
ENAME
DNAME
---------- --------------
CLARK
ACCOUNTING
KING
ACCOUNTING
MILLER
ACCOUNTING
SMITH
RESEARCH
JONES
RESEARCH
SCOTT
RESEARCH
ADAMS
RESEARCH
FORD
RESEARCH
ALLEN
SALES
WARD
SALES
MARTIN
SALES
BLAKE
SALES
TURNER
SALES
JAMES SALES
14 개의 행이 선택되었습니다.
경
과: 00:00:00.03
-----------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 14 |
252 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | |
| | |
| 2 | NESTED LOOPS | | 14 |
252 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 |
44 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP_DEPTNO | 5
| | 0
(0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 4 |
28 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SQL> select
2 e.ename,
3 d.dname
4 from
emp e, dept
d
5 where
e.deptno = d.deptno ;
ENAME
DNAME
---------- --------------
SMITH
RESEARCH
ALLEN
SALES
WARD
SALES
JONES
RESEARCH
MARTIN
SALES
BLAKE
SALES
CLARK
ACCOUNTING
SCOTT
RESEARCH
KING
ACCOUNTING
TURNER
SALES
ADAMS
RESEARCH
JAMES
SALES
FORD
RESEARCH
MILLER
ACCOUNTING
14 개의 행이 선택되었습니다.
경
과: 00:00:00.03
----------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT |
| 14 | 252 |
17 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | |
| |
|
| 2 | NESTED LOOPS | |
14 | 252 | 17
(0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP |
14 | 98 | 3
(0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
| 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT |
1 | 11 | 1
(0)| 00:00:01 |
----------------------------------------------------------------------------------------
이번에는 USE_NL만 힌트를 주었는데 중첩 루프 조인이 되지 않는다. ORDERED와 같이 써야 한다.
SQL> select
2 e.ename,
3 d.dname
4 from
dept d, emp
e
5
where e.deptno =
d.deptno
;
-----------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 14 |
252 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 |
252 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 |
98 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_EMP_DEPTNO | 14 |
| 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 |
44 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT |
4 | 44 |
3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
힌트를
사용 안 했을 때와 같다.
SQL> select
2 e.ename,
3 d.dname
4 from
dept d, emp
e
5 where
e.deptno = d.deptno;
-----------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 14 |
252 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 |
252 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 |
98 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_EMP_DEPTNO | 14 |
| 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 |
44 | 4 (25)| 00:00:01 |
| 5 |
TABLE ACCESS FULL |
DEPT | 4 |
44 | 3 (0)| 00:00:01 |
[실습]
- 실습을 위한 예제 테이블 및
데이터는 아래 링크에서 확인 바랍니다.
myemp1 :
1000만건
myemp1_old :
100만건
mydept :
5건
테스트환경 : oracle
11g
우선 인덱스를 만들자.
SQL>
create index
idx_myemp1_deptno on
myemp1(deptno)
SQL>
set autotrace on
explain
SQL>
set autotrace
traceonly
SQL>alter system
optimizer_mode = all_rows --
CBO로 동작
아무런 힌트도 주지
않으니 HASH JOIN을 한다.
SQL>
select e.ename,
2 d.dname
3 from mydept1 d, myemp1 e
4 where
e.deptno = d.deptno
5 ;
20000000
개의 행이 선택되었습니다.
경 과:
00:01:42.55 -- 시간이 꽤 걸린다.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 20M| 1525M| 17043 (2)| 00:03:25 |
|* 1 |
HASH JOIN | |
20M| 1525M| 17043 (2)| 00:03:25 |
| 2 | TABLE ACCESS FULL| MYDEPT1 | 10 |
650 | 3 (0)| 00:00:01 |
| 3 |
TABLE ACCESS FULL| MYEMP1
| 10M|
143M| 16941 (1)| 00:03:24 |
------------------------------------------------------------------------------
이번에는 중첩 루프 조인을
걸어보자.
SQL>
select
2 e.ename,
3 d.dname
4 from mydept1 d, myemp1 e
5 where
e.deptno = d.deptno
;
20000000
개의 행이 선택되었습니다.
경 과:
00:01:57.85
Execution
Plan
----------------------------------------------------------
Plan
hash value:
1400616069
------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 20M| 1525M|
169K (1)| 00:33:53 |
| 1 |
NESTED LOOPS | |
20M| 1525M| 169K
(1)| 00:33:53 |
| 2 |
TABLE ACCESS FULL| MYDEPT1 | 10
| 650 | 3
(0)| 00:00:01 |
|* 3 |
TABLE ACCESS FULL| MYEMP1 | 2000K|
28M| 16939 (1)| 00:03:24 |
------------------------------------------------------------------------------
이번에는
인덱스를 이용하기 위해 추가로 인덱스 힌트를 넣었자. 어마어마하게 늦다.
(4분)
SQL> select
2 e.ename,
3 d.dname
4 from
mydept1 d, myemp1
e
5 where
e.deptno = d.deptno ;
20000000 개의 행이 선택되었습니다.
경
과: 00:04:17.13
--------------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 20M|
1525M| 659K (1)| 02:11:59 |
| 1 | NESTED LOOPS | | |
| |
|
| 2 | NESTED LOOPS | | 20M|
1525M| 659K (1)| 02:11:59 |
| 3 | TABLE ACCESS FULL | MYDEPT1 |
10 | 650 | 3
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO | 2000K|
| 3865 (1)| 00:00:47 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYEMP1 |
2000K| 28M| 65985 (1)| 00:13:12 |
--------------------------------------------------------------------------------------------------
이번에는
드라이빙 테이블을 바구어 보자.
MYEMP1(1000만건)이
드라이빙 테이블이 되니 좀 느리다. 그래도 mydept1이
드라이빙 테이블이 되고 myemp1을 인덱스 범위 검색하는 것 보다는 빠르다.
SQL> select
2 e.ename,
3 d.dname
4 from
myemp1 e, mydept1
d
5 where
e.deptno = d.deptno
;
20000000 개의 행이 선택되었습니다.
경
과: 00:03:16.12
------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
20M| 1525M| 13M
(1)| 45:15:25 |
| 1 | NESTED LOOPS |
| 20M| 1525M|
13M (1)| 45:15:25 |
| 2 | TABLE ACCESS FULL|
MYEMP1 | 10M|
143M| 16941 (1)| 00:03:24 |
|* 3 |
TABLE ACCESS FULL| MYDEPT1 | 2
| 130 | 1
(0)| 00:00:01
|
댓글 없음:
댓글 쓰기