ORACLE NESTED LOOP JOIN #1
from 새로쓴 대용량 데이터베이스 솔루션 | 이화식
090721_nested_loop_script.txt1. NESTED LOOP JOIN
JOIN의 한가지 방법이다.
NESTED LOOP 란 단어는 DRIVING(먼저 읽어들이는 테이블) 데이터를 가지고
루프를 돌리기 때문이다.
Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS (cr=48 r=0 w=0 time=1081 us)
14 TABLE ACCESS FULL EMP (cr=18 r=0 w=0 time=394 us)
14 TABLE ACCESS BY INDEX ROWID DEPT (cr=30 r=0 w=0 time=529 us)
14 INDEX UNIQUE SCAN DEPT_DEPTNO_PK (cr=16 r=0 w=0 time=325 us)(object id 7095)
위에서 DRIVING 되는 테이블은 EMP 이다. EMP 데이터를 가지고 연결을 시도하게 된다.
2. NL JOIN 의 개략적인 흐름
위의 그림을 간단히 이야기 하면 폭이 좁은 푸른빛의 두 직사각형이 인덱스를 의미하며
빨간색은 테이블을 의미한다.
좌측의 둘 우측의 둘이 각기 한 세트이다.
선행테이블에 대한 연산은 좌측 두개를 생각하면 된다.
A. 선행테이블이 조건절을 가지고 있고 조건절에 사용되는 컬럼이 인덱스를 가지고
있는 경우 INDEX SCAN을 한다.
B. 위 과정에서 조건을 만족하는 ROW를 발견하면 테이블에 ROWID를 가지고 RANDOM ACCESS 를 한다. 이는 SCAN에 비해 무거운 작업이다.
C. 테이블에 엑세스 한 후엔 해당 테이블의 또다른 조건절에 대해 check 과정을
거친다. 해당 조건절의 컬럼은 A과정에서 처리범위를 줄이는데 사용되지 못한
조건들로 인덱스를 가지고 있을 수도 없을 수도 있다.
D. 테이블에서 CHECK 과정을 성공적으로 마치면 다음 과정으로 이행된다. 그렇지
않은 경우엔 해당 프로세스를 버리고 A로 돌아가 다음 행을 검사한다.
E. 다음 과정에선 연결고리(조인조건)를 확인하게 된다. 여기서의 전제는 조인조건으로
사용되는 컬럼에 인덱스가 생성되어 있다는 것이다. 여기서 실패하면 A로
돌아가게 된다.
F. 이 과정에서 실패하는 요인은 단순히 조인조건을 만족하는 경우가 없거나 조인
조건에 사용된 인덱스가 결합인덱스이고 후행 인덱스의 컬럼이 조건절에 사용
되었다면 여기서 필터 될 수도 있다.
G. 이 과정을 마치면 마지막으로 ROWID 를 가지고 테이블에 엑세스한다. 이후의
과정은 C와 동일하다. 여기서 남은 조건절에 대해 CHECK 과정이 끝나면 이는
JOIN 에 성공해 최종적으로 FETCH 될 ROW 가 된다.
3. 환경구성
내용을 진행하기 전에 SCOTT SCHEMA 의 EMP, DEPT 를 가지고 환경을 구성하겠다.
(세부내용은 첨부한 스크립트를 참조)
스크립트 수행의 결과는 아래와 같다.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL CHAR(1000)
ENAME NOT NULL VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
DEPTNO CHAR(1000)
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL CHAR(1000)
DNAME VARCHAR2(14)
TABLE_NAME INDEX_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
DEPT DEPT_DEPTNO_PK DEPTNO
EMP EMP_EMPNO_PK EMPNO
EMP EMP_DEPTNO_IDX DEPTNO
테이블 두개 생성 후 원본데이터를 insert 받게 되고 PK 로 지정한 EMPNO DEPTNO에 INDEX가 자동생성된다. EMP_DEPTNO_IDX 는 수동으로 만든 인덱스다.
이 후 각 테이블과 인덱스에 대해 통계수집이 완료된 상태이다.
특이사항으로 EMP 의 EMPNO, DEPTNO, DEPT 의 DEPTNO 의 데이터형이 CHAR(1000)
으로 되어있다. 이는 인덱스 블록을 늘려주기 위해 사용하였다.
(CHAR 타입의 컬럼에 인덱스가 만들어지면 인덱스 블록의 VALUE 값에 데이터길이만큼
공백문자로 채워지게 된다.)
4. NESTED LOOP JOIN 의 예제
A. JOIN 조건이 있는 경우
select /*+ordered use_nl(e d)*/ e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
위에서 emp 가 driving table 이 된다. 이는 ordered 로 고정하였고 join 은
NL JOIN 으로 풀린다. (use_nl 로 고정)
위에서 연결고리는 실패하지 않는다.
그 이유는 emp의 deptno 은 dept 의 deptno 의 자식키이기 때문이다.
index 의 블록수 table의 블록수를 검색한 결과는 다음과 같다.
INDEX_NAME INDEX_TYPE UNIQUENES PCT_FREE BLEVEL LEAF_BLOCKS DISTINCT_KEYS
-------------------- --------------------------- --------- ---------- ---------- ----------- -------------
DEPT_DEPTNO_PK NORMAL UNIQUE 10 1 2 4
EMP_DEPTNO_IDX NORMAL NONUNIQUE 10 1 5 3
EMP_EMPNO_PK NORMAL UNIQUE 10 1 5 14
EMP의 EMPNO은 PRIMARY KEY로 DISTINCT KEYS 의 값은 곧 ROW수를 의미한다.
블록이 다섯개 사용된 것은 EMPNO 의 데이터 타입이 CHAR(1000) 이므로
한 블록에 세개의 ROW가 들어간다. 총 행이 14개 이기 때문에 다섯개의 블록이
필요하다.
EMP.DEPTNO 역시 같은 데이터 타입을 가지고 있고 14개 있기에 다섯개의 블록을
사용하며 DEPT.DEPTNO 의 경우 는 두개의 블록을 사용한다.
select /*+ordered use_nl(e d)*/ e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 0 48 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 48 0 14
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS (cr=48 r=0 w=0 time=1955 us) - (1)
14 TABLE ACCESS FULL EMP (cr=18 r=0 w=0 time=676 us) - (2)
14 TABLE ACCESS BY INDEX ROWID DEPT (cr=30 r=0 w=0 time=1002 us) - (3)
14 INDEX UNIQUE SCAN DEPT_DEPTNO_PK (cr=16 r=0 w=0 time=459 us)(object id 7095) -(4)
SQL 수행을 TRACE 한 화면이다.
앞서 이야기 한 NL JOIN 의 이야기대로 이야기를 전개하고자 한다.
a. (2) 에서 조건을 만족하는 시작 row를 얻는다.
b. (4) 에서 연결고리를 확인한다. 위 경우 참조무결성 조건이 있기 때문에 연결은
항상 성공한다.
c. (3) 에서 성공한 조인(한 row)에 대해 테이블에 엑세스 해 필요한 select list
value 를 얻었다.
d. 이 작업을 DRIVING TABLE 의 처리범위가 바닥날때까지 반복한다.(4)
B. JOIN 조건이 있고 선행테이블에 대해 CHECK 조건이 있는 경우
select list의 sal 컬럼에 조건절을 추가했다. emp 테이블만 보았을 때 해당조건을
만족하는 row는 6건이다.
select /*+ordered use_nl(e d)*/ e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
and sal > 2000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 32 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 32 0 6
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS (cr=32 r=0 w=0 time=842 us) – (1)
6 TABLE ACCESS FULL EMP (cr=18 r=0 w=0 time=395 us) –(2)
6 TABLE ACCESS BY INDEX ROWID DEPT (cr=14 r=0 w=0 time=362 us) – (3)
6 INDEX UNIQUE SCAN DEPT_DEPTNO_PK (cr=8 r=0 w=0 time=244 us)(object id 7095) – (4)
여기서 주의할 점은 (2) -> (4) -> (3) 의 과정 중 (2) 에서 check 조건이 발생했다는
것이다. sal 에 대한 조건절로 인해 결과는 6건이 나왔고 시작 row를 발견하며
(2) -> (4) -> (3) 의 반복처리가 아까보았던 계획에 비해 8건이 줄었다.
이 한건 한건이 인덱스와 조인고리를 확인하고 만족하면 테이블의 랜덤엑세스를 하여
체크조건을 확인하는 일량을 가진다는 것을 생각하면 큰 차이이다.