Nested Loops 조인

(1) 기본 매커니즘

 

Nested Loops 조인을 이해하기 위한 중첩 루프

 

중첩 루프문

 

<C, JAVA>

for(i=0; i<100; i++){

   for(j=0; j<100; j++) {

     // Do Anything

   }

}

 

begin

  for outer in (select deptno, empno, rpad(ename, 10) ename from emp)

    for inner in (select dname from dept where depeno = outer.deptno)

    loop

       dbms_output.put_line(outer.empno||' : '||outer.ename||' : '||inner.dname);

    end loop;

  end loop;

end

 

위 PL/SQL문은 아래 쿼리와 100% 같은 순서로 데이터를 엑세스하고, 데이터 출력 순서도 같다. 내부적으로 쿼리를 반복 수행하지 않는다는 점만 다르다.

 

select /*+ ordered use_nl(d) */ e.empno, e.ename, d.dname

from emp e, dept d

where d.deptno = e.deptno;

 

 

(2) 힌트를 이용해 NL 조인을 제어하는 방법

 

select /*+ ordered use_nl(e) */ *

   from dept d, emp e

 where d.deptno = d.deptno;

 

ordered 힌트는 from 절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용하고,

use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용한다. 위에서는 ordered와 use_nl(e) 힌트를 같이 사용했으므로 dept 테이블을 기준으로 emp테이블과 조인할 때 NL방식으로 조인하라는 뜻이다.

 

 

참고로 세개 이상의 테이블을 nl 조인으로 유도할 때 쓰는 힌트 예제

 

select /*+ ordered use_nl(B) use_nl(C) use_nl(D) */ *

from A, B, C, D

where ....

 

 

(3) NL 조인 튜닝 실습

 

- 테이블 및 인덱스 정보

 

테이블 : jobs, employees

인덱스 : 

pk_jobs : jobs.job_id

jobs_max_sal_ix : jobs.max_salary

pk_employees : employees.employee_id

emp_job_ix : employees.job_id

emp_hiredate_ix : employees.hire_date

 

 

튜닝 대상 쿼리

 

select /*+ ordered use_nl(e) index(j) index(e) */

         j.job_titile, e.first_time, e.last_time,

        e.hire_date, e.salary, e.email.e.phone_number

from jobs j, employees e

where e.job_id = j.job_id        ------------  ①

and j.max_salary >= 1500     ------------- ②

and j.job_type = 'A'                ------------- ③ 

and e.hire_date >= to_date('19960101','yyyymmdd');  ----------- ④

 

② -> ③ -> ① -> ④ 

 

 

참고 : 

오라클 성능 고도와 원리와 해법 Ⅱ(p. 210 ~ p.214)