본문 바로가기

04번. IT 힌트얻기/▶ DB/ SQL

ORACLE NESTED LOOP JOIN

ORACLE NESTED LOOP JOIN #1

 

from 새로쓴 대용량 데이터베이스 솔루션 | 이화식

1.    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 의 개략적인 흐름

 

위의 그림을 간단히 이야기 하면 폭이 좁은 푸른빛의 두 직사각형이 인덱스를 의미하며

빨간색은 테이블을 의미한다.

좌측의 둘 우측의 둘이 각기 한 세트이다.

선행테이블에 대한 연산은 좌측 두개를 생각하면 된다.



< 추가 설명 >
데이터를 Access하는 방식

1. Full Table Scan 

: 모든 행을 Scan하기 위하여 테이블에 관련된 모든 블록을 차례로 읽고, 각 레코드마다 where 조건을 만족하는지를 확인한다.  

2. Random Access

인덱스를 이용하여 where 조건의 일부를 만족하는 레코드를 선택 테이블에 관련된 블록을 랜덤하게 읽고, 나머지 where 조건을 만족하는 레코드를 출력한다.



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

 

(hint)에 대한 추가 설명

ORDERED : FROM 절에 명시된 테이블의 순서대로 DRIVING

USE_NL(TABLE1, TABLE2) : NESTED LOOP JOIN

-> 옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다. 먼저 특정 테이블의 ROW에 액세스하고 그 값에 해당하는 다른 TABLE의 ROW를 찾는 작업을 해당범위까지 실행하는 조인

 

위에서 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건이 줄었다.

이 한건 한건이 인덱스와 조인고리를 확인하고 만족하면 테이블의 랜덤엑세스를 하여

체크조건을 확인하는 일량을 가진다는 것을 생각하면 큰 차이이다.

출처 : http://h391106.tistory.com/126

'04번. IT 힌트얻기 > ▶ DB/ SQL ' 카테고리의 다른 글

대용량 데이터베이스를 위한 어드바이스  (0) 2011.09.29
HASH JOIN  (0) 2011.09.27
SORT MERGE JOIN  (0) 2011.09.27
SUM(DECODE...)  (0) 2011.09.09
1. Function의 종류  (0) 2011.09.01