by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1. Nested Loops 조인

Added by 오정희, last edited by study.gurubee on 8월 19, 2010



01. Nested Loops 조인

(1) 기본 메커니즘

  • Nested Loops조인은 아래와 같은 중첩 루프문과 동일한 원리이다.
    C,JAVA PL/SQL 
    for(i=0; i<100; i++){    -- outer loop  
  for(j=0; j<100; j++){  -- inner loop
    .....
  }
}

    for outer in 1..100 loop
  for inner in 1..100 loop
    dbms_output.put_line(outer || ':' || inner);
  end loop;
end loop;
  • 아래의 PL/SQL과 SQL문은 내부적으로(Recursive하게) 쿼리를 반복 수행하지 않는점 이외에 동일한 처리를 한다.
    PL/SQL SQL 
    begin
  for outer in (select deptno, empno, rpad(ename, 10) ename from emp)  
  loop    -- outer 루프
    for inner in (select dname from dept where deptno = outer.deptno) 
    loop  -- inner 루프
      dbms_output.put_line(outer.empno||' : '||outer.ename||' : '||inner.dname);
    end loop;
  end loop;
end;

    select	/*+ ordered use_nl(d) */ e.empno, e.ename, d.dname
from	emp e, dept d
where	d.deptno = e.deptno

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

  • 힌트 사용예1 
    select /*+ ordered use_nl(e) */ *
from dept d, emp e
where e.deptno = d.deptno
    • ordered : from절에 써있는 순서대로 테이블을 조인하세요
    • use_nl : NL방식으로 조인하세요.
      => 즉, dept테이블을 기준으로, emp테이블을 NL방식으로 조인하세요
Outer테이블, Inner테이블
  • 두개의 테이블을 조인할 때, 어떤게 Outer 테이블이고, 또 어떤게 Inner 테이블인거지?
    • NL조인 
      SQL> explain plan for
  2  select /*+ ordered use_nl(e)*/*
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4192419542

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   798 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |   798 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | => Outer/Driving
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   148 |     2   (0)| 00:00:01 | => Inner/Driven
---------------------------------------------------------------------------
    • 소트머지 조인 
      SQL> explain plan for
  2  select /*+ ordered full(d) use_merge(e)*/ *
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1407029907

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   798 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |    14 |   798 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    80 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | => Outer/First
|*  4 |   SORT JOIN         |      |    14 |   518 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 | => Inner/Second
    • 해시 조인 
      SQL> explain plan for
  2  select /*+ ordered use_hash(e)*/*
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   798 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   798 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | => Outer/Build Input
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 | => Inner/Probe Input
---------------------------------------------------------------------------
  NL 조인 소트머지 조인 해시 조인
실행계획상 위쪽 Outer(Driving)테이블 Outer(First)테이블 Build Input
실행계획상 아래쪽 Inner(Driven)테이블 Inner(Second)테이블 Probe Input
  • 힌트사용예2 
    select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *
from A, B, C, D
where ...

    => A->B->C->D 순으로 B와 조인할때, C와 조인할 때는 NL로, D와 조인할때는 Hash로..

  • 힌트사용예3
    ordered대신 leading힌트를 이용하여 조인순서제어가 가능(leading을 사용하면 테이블 순서를 일일이 바꿔줄 필요없이 제어가능) 
    select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */ *
from A, B, C, D
where ...

    =>C->A->D->B 순으로 A와 조인할때, D와 조인할때는 NL로 B와 조인할때는 Hash로..

  • 힌트사용예4 
    select /*+ use_nl(A,B,C,D)*/ *
from A, B, C, D
where ...

    => A,B,C,D를 조인할 때 모두 NL로.. 단, 특별히 조인순서를 지정안했으므로, 조인순서는 옵티마이저 판단에 맡김

(3) NL조인 수행과정 분석

SQL INDEX 
select /*+ ordered use_nl(e) */
       e.empno, e.ename, d.dname, e.job, e.sal
from   dept d, emp e
where  e.deptno = d.deptno  ...........(1)
and    d.loc = 'SEOUL'      ...........(2)
and    d.gb = '2'           ...........(3)
and    e.sal >= 1500        ...........(4)
order by sal desc
pk_dept : dept.deptno
dept_loc_idx : dept.loc
pk_emp : emp.empno
emp_deptno_idx : emp.deptno
emp_sal_idx : emp.sal
실행계획 
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    45 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                |     1 |    45 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | EMP            |     4 |   100 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                |     1 |    45 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT           |     1 |    20 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | DEPT_LOC_IDX   |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | EMP_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

1) 사용되는 인덱스는 ? dept_loc_idx, emp_deptno_idx
2) 조건비교순서는? (2) -> (3) -> (1) -> (4)
(실행계획 해석은, 위에서 아래로, 안쪽에서 바깥으로 읽기)


각 단계를 완료하고 나서 다음단계로 넘어가는게 아니라 한 레코드씩 순차적으로 진행한다.
단, order by는 전체집합을 대상으로 정렬해야 하므로 작업을 모두 완료한 후 다음 오퍼레이션을 진행한다.

  • NL조인의 수행절차
    그림 2-2 참조
    • dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우된다.
    • gb='2'조건에 의해 필터링 되는 비율이 높다면 dept_loc_idx인덱스에 gb컬럼을 추가하는 방안을 고려해볼것
    • sal >= 1500 조건에 의해 필터링 되는 비율이 높다면 emp_deptno_idx인덱스에 sal 컬럼 추가하는 방안고려


OLTP시스템에서 조인을 튜닝할 때는 우선적으로 NL조인부터 고려

(4) NL조인의 특징

  • Random 액세스 위주의 조인방식
    그러므로, 인덱스 구성이 완벽해도 대량의 데이터 조인시 비효율적
  • 조인을 한 레코드씩 순차적으로 진행
    아무리 대용량 집합이더라도 매우 극적인 응답속도를 낼 수 있으며, 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정


다른 조인방식보다 인덱스 구성 전략이 특히 중요하며, 소량의 데이터를 처리하거나 부분범위 처리가 가능한 OLTP성 환경에 적합한 조인방식이다.

(5) NL조인 튜닝실습

  • SQL트레이스 1 (p220 상단)
    테이블을 액세스 한 후에 필터링 되는 비율이 높다면 인덱스에 테이블 필터 조건 컬럼을 추가하는것을 고려
    (job_max_sal_ix : max_salary + job_type)
  • SQL트레이스 2 (p221 상단)
    job_max_sal_ix인덱스로부터 3건을 리턴하기 위해 인덱스 블록을 1000개 읽음. 인덱스 컬럼 순서를 조정
    (job_max_sal_ix : job_type + max_salary)
  • SQL트레이스 3(p221 하단)
    1278번 조인시도했으나 최종성공한 결과집합은 5건뿐
    조인순서 jobs -> employees에서 employees -> jobs로 변경고려
    조인순서를 바꾸어도 소득이 없다면 소트머지조인과 해시조인을 검토

(6) 테이블 Prefetch

  • 테이블 Prefetch란?
    인덱스를 경유하여 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야하는데, 그때 곧이어 읽을 가능성이 큰 블록들을 미리 적재해 두는 기능
    디스크 I/O에 의한 대기횟수 감소를 노림
  • 실행계획에 인덱스 rowid에의한 Inner테이블 액세스가 Nested Loops 위쪽에 표시되면, Prefetch기능이 활성화 됨을 의미
  • _talble_lookup_prefetch_size를 0으로 설정하면 전통적인 NL조인 실행계획으로 돌아감
  • 실행계획에 위와 같이 나타났다고 항상 테이블 Prefetch가 작동하는 것은 아니다. db_file_sequential_read 대기 이벤트 대신 db_file_parallel_reads대기이벤트가 나타나면 실제작동함을 의미
  • Prefetch기능이 나타나는 경우
    • Inner쪽 Non-Unique인덱스를 Range Scan할 때는 항상 나타남
    • Inner쪽 Unique인덱스를 Non-Unique 조건(모든 인덱스 구성컬럼이 '='조건이 아닐때)으로 Range Scan할 때도 항상 나타난다.
    • Inner쪽 Unique인덱스를 Unique조건(모든 인덱스 구성컬럼이 '='조건)으로 액세스할 때도 나타날 수 있다. 이대 인덱스는 Range Scan으로 액세스하며, 테이블 Prefetch실행계획이 안 타타날 때는 Unique Scan으로 엑세스한다.
  • 예) p224
    • 지분보고_PK : 회사코드 + 보고서구분코드 + 최초보고일자 + 보고서id + 보고일련번호
    • cardinality힌트를 사용하여 드라이빙 집합의 카디널리티를 변경하면서 이와 같은 실행계획이 나타남을 확인할수 있다.

(7) 배치 I/O

  • 오라클 11g에서 시작
  • Inner 쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘
  • 테이블 엑세스를 나중에 하지만 부분범위처리는 정상적으로 작동한다.
  • 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리
  • 배치 I/O방식
    • 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner쪽 인덱스와 조인하며 중간 결과집합을 만듬
    • 중간결과집합이 일정량 쌓이면 inner쪽 테이블 레코드를 액세스. 테이블 블록이 버퍼 캐시에 있으면 바로 최종 결과집합에 담고, ?찾으면 중간집합에 남겨둠
    • 위에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크에서 읽음.
    • 버퍼캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음
    • 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4를 반복
  • nlj_batching 힌트를 사용하면 됨
  • 이방식을 사용하면 데이터 정렬 순서가 달라질 수 있음

(8) 버퍼 Pinning 효과

8i에서 나타난 버퍼 Pinning효과

  • 페이블 블록에 대한 버퍼 Pinning기능이 작동
  • 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 다른 레코드를 읽기위해 Outer쪽으로 돌아오는 순간 Pin을 해제

9i에서 나타난 버퍼 Pinning효과

  • Inner쪽 인덱스 루트 블록에 대한 버퍼 Pinning효과가 나타나기시작
  • 9i부터 Inner쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL조건 위쪽으로 올라가므로 이때는 항상 버퍼 Pinning효과가 나타나는 셈

10g에서 나타난 버퍼 Pinning효과

select /*+ ordered use_nl(d) */ count(e.ename), count(d.dname)
from   scott.t_emp e, scott.dept d
where  d.deptno = e.deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     38.75      45.39       8730    1409213          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     38.78      45.43       8730    1409214          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1409213 pr=8730 pw=0 time=45394912 us)
1400000   NESTED LOOPS  (cr=1409213 pr=8730 pw=0 time=53215534 us)
1400000    TABLE ACCESS FULL T_EMP (cr=9211 pr=8730 pw=0 time=9815520 us)
1400000    TABLE ACCESS BY INDEX ROWID DEPT (cr=1400002 pr=0 pw=0 time=32299603 us)
1400000     INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=12912986 us)(object id 51250)
  • Inner쪽 테이블을 Index Range Scan을 거쳐 NL조인 위쪽에서 액세스 할 때는 , 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 Outer를 돌아오더라도 테이블 블록에 대한 Pinning상태을 유지

11g에서 나타난 버퍼 Pinning효과

  • User Rowid로 테이블 액세스할 때도 버퍼 Pinning효과가 나타남
  • NL조인에서 inner쪽 루트 아래 인덱스 블록들도 Pinning하기 시작

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.