[Oracle] 오라클 Nested Loops JOIN, NL 조인

기본 메커니즘

< C, JAVA >

for(i=0; i<100; i++){          -- outer loop

for(j=0; j<100; j++){    -- inner loop

// Do Anything ...

}

}

SELECT /* ordered use_nl(d) */ E.EMP_NO, E.ENAME, D.DNAME

  FROM EMP E, DEPT D

WHERE D.DEPT_NO = E.DEPT_NO;

▼

begin

for outer in ( SELECT DEPT_NO, EMP_NO, RPAD(ENAME, 10) ENAME FROM EMP )

loop        -- outer loop

for inner in ( SELECT DNAME FROM DEPT WHERE DEPT_NO = outer.DEPT_NO )

loop    -- inner loop

dbms_output.put_line(outer.EMP_NO ||' : '|| outer.ENAME ||' : '|| inner.DNAME);

end loop;

end loop;

end;

* Outer Table : Driving Table

* Inner Table : Driven Table

=> Outer Table을 스캔하면서 Inner Table을 탐색하는 메커니즘

NL JOIN 수행 과정 분석

SELECT /*+ ordered use_nl(e) */

   E.EMP_NO, E.ENAME, D.DNAME, E.JOP, E.SAL

  FROM DEPT D, EMP E

WHERE E.DEPT_NO = D.DEPT_NO  --- ①

    AND D.LOC = 'SEOUL'                --- ②

    AND D.GB = '2'                         --- ③

    AND E.SAL >= 1500                  --- ④

ORDER BY SAL DESC;                   Answer :: ② -> ③ -> ① -> ④

* pk_dept             : dept.dept_no

* dept_loc_idx        : dept.loc

* pk_emp             : emp.emp_no

* emp_deptno_idx   : emp.dept_no

* emp_sal_idx        : emp.sal

Execution Plan 

------------------------------------------------------------------------------

0         SELECT STATEMENT

1    0         SORT ORDER BY

2    1             NESTED LOOPS

3    2                 TABLE ACCESS BY INDEX ROWID DEPT

4    3                     INDEX RANGE SCAN DEPT_LOC_IDX

5    2                 TABLE ACCESS BY INDEX ROWID EMP

6    5                     INDEX RANGE SCAN EMP_DEPTNO_IDX

- 먼저 Access되는 테이블의 처리 범위(dept_loc_idx 인덱스를 스캔하는 양)에 의해 전체 일량이 좌우됨

- Random Access 위주의 JOIN 방식

=> 대량의 데이터 JOIN 시 매우 비효율적

- 한 레코드씩 순차적으로 진행

=> 부분범위처리가 가능한 상황에서는 대량의 테이블에서도 극적인 응답 속도를 낼 수 있음

- JOIN 컬럼을 선두로 갖는 인덱스가 없으면 Outer 테이블에서 읽히는 건마다 Inner 테이블 Full Scan

=> 인덱스 구성 전략이 아주 중요

* 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 OLTP 환경에 적합한 JOIN 방식