Tip
2011.03.21 23:08

NL JOIN에 대하여...

조회 수 13163 추천 수 0 댓글 1


선행 테이블 = 외측 집합, driving, Outer
후행 테이블 = 내측 집합, Inner
집합 = table rows


1. Nested loop Join
-> 먼저 처리되는 어떤 범위의 집합(table rows)의 각 로우에 대하여 "연결고리"를 통해
   반복적(loop)으로 다른 집합(table rows)의 대응되는 로우를 탐침한다.
  
   핵심 : -먼저 수행되는 집합의 처리범위가 전체의 일량을 좌우한다는 것
          -나중에 반복 수행되는 연결작업이 랜덤 액세스로 발생한다는 점
          그러므로 소량의 범위를 연결할 때는 매우 유용하지만 대량의 범위는 커다란 부하를
          가져올 수 이다. (다른 조인 방식의 조인방식을 검토 (hash join?))
         
   힌트 : /*+ USE_NL(table1, table2) */
  
   수행 순서 : 1. 옵티마이져는 먼저 driving 집합(table)을 결정한다.
                   2. 선행 집합과 후행 집합을 연결을 한다.
                   3. 선행 집합이 액세스되면 그들의 "모든 컬럼"은 상수값을 가지게 되며, 이미 존재하던 상수값을
                       가진 "체크 조건"까지 감안해서 나머지 집합들 중에서 다음 수행할 내측 집합을 선택한다.
                   4. 만약 이 방법으로 조인될 집합이 더 있다면 위의 방법으로 나머지 순서도 결정한다.
                      연결고리의 인덱스가 중요함 (FK 인덱스 걸어주기...)
                   5. 실제로 외측집합 각각의 로우에 대해 내측집합의 대응되는 모든 로우가 액세스된다.
              
    NESTED LOOPS
      outer_loop
      inner_loop
     
예>
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT
    240    NESTED LOOPS
   
    540     TABLE ACCESS (CLUSTER) OF 'ORD_MST'                  <======== 외측조인
     63      INDEX (RANGE SCAN) OF 'ORD_MST_CLX' (CLUSTER)
    
    806     TABLE ACCESS (BY ROWID) OF 'ORD_DETAIL'              <======= 내측조인
   1346      IDEX (RANGE SCAN) OF 'ORD_DETAIL_IDX2' (NON-UNIQUE)


- 위의 실행계획은 외측 루프는 클러스터 키를 범위 스캔하면서 수행되며 단위 클러스터에는 약 8개의
  로우들이 존재하고 있음을 알 수 있다.

- 외측 루프에서 액세스한 로우 수보다 내측 루프에서 액세스양이 많은 것은 "RANGE SCAN"을 한 것을 보면
  내측 루프 액세스는 'M'쪽 집합인 것을 알 수 있다. 내측 루프 인덱스를 경유하여 테이블을 액세스한 것이
  806건 인 것은 앞서 인덱스 스캔에서 540건이 걸러졌다는 것도 알 수 있다.
 
- 이렇게 완료된 결과가 240건이라는 것은 내측조인에서 테이블 액세스까지 완료한 후 다시 "체크 조건"에 의해
  걸려 졌기 때문이다.
 
1-1 중첩된 Nested Loops : 두 개 이상의 테이블이 모두 Nested Loops 조인으로 수행 가장 안쪽에 있는 조인이
                          먼저 수행되고 성공한 것들만 다음 조인을 수행
                         
1-2 진보된 내포 조인(Advanced Nested loops Join)
   
    SELECT e.*, d.DNAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
      AND d.LOC ='SEOUL'
      AND e.JOB = 'CLERK';
     
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
                 INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) 
    
 DEPTNO 라는 걸로 봐서는 외측 루프가 '1' 내측 루프가 'M'쪽 집합이라는 것을 발견
 
 1) 'DEPT_LOC_IDX' 인덱스를 이용하여 LOC='SEOUL'인 DEPT 테이블의 첫번째 로우를 액세스한다.
 2) 액세스한 DEPT 테이블의 DEPTNO 를 이용하여 EMP 테이블의 인덱스를 범위 스캔하며
    DEPT테이블의 DNAME과 결합한 결과의 집합이 만들어 진다.
 3) 이제 ROWID를 이용하여 EMP테이블의 블록을 액세스하여 PGA버퍼에 저장한다.
 4) 앞서 2)에서 만들어진 집합에 있는 해당 블록을 가진 로우와 3)에서 액세스해 둔 PGA 버퍼를
    이용하여 대응되는 로우를 찾아서 JOB='CLERK' 조건을 체크하고, 성공하면 운반단위로 보낸다.
    바로 이 단계가 이 방식의 핵이다. 각각의 ROWID마다 테이블 액세스르 시도하는 것이 아니라
    한 번 액세스한 "블록"에서 계속 연결을 시도한다는 것에 유의하기 바란다.
 5) PGA 버퍼와의 연결이 완료되면 다시 2)에서 만들어진 집합의 ROWID에서 다음 블록을 찾아 EMP
    테이블의 블록을 액세스하는 3)의 작업을 수행한다.
 6) 이러한 방식으로 계속해서 수행하다가 'DEPT_LOC_IDX' 인덱스의 처리 범위가 끝나면 쿼리를 종료한다.
   
즉 : 3,4 에서 처럼 PGA영역에 블록으로 저장을 하기 때문에 클러스터링 팩터가 양호하다면 보다 많은
     부분을 한 번의 블록 액세스에서 연결할 수 있기 때문에 물리적,논리적 블록 액세스 량이 크게 감소함으로써
     효율성이 크게 높아지게 되었다.
    
     1:M 의 NL 조인에서 내측루프가 'M'일때 위에서 같은 실행계획이 나타난다.
     만약 'M'쪽 집합이 내측 루프에서 수행될 때만 적용되는 이유는 같은 블록에 조인 대상이 여러 개 존재해야만
     가치가 있기 때문.


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84902
60 Tip Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] 담벼락 2011.03.23 17481
» Tip NL JOIN에 대하여... 1 고구마 2011.03.21 13163
58 Tip Outer Join의 정확한 이해 file 고구마 2011.03.21 13028
57 Tip 쿼리 튜닝 사례 고구마 2011.03.21 14449
56 Tip windows os 에서 운영중 listener.log 삭제하기 2 file dbkill 2011.02.21 22245
55 Tip partition table에 index 생성시 perfstat 2011.01.28 18165
54 Tip DB계정에 profile설정 perfstat 2011.01.05 14588
53 Tip em 실행시 에러 발생 조치 1 dbkill 2010.12.15 27305
52 Tip NetCA 실행시 VM 관련 에러 대처법 2 송기성 2010.12.09 18164
51 Tip Lock Check 승현짱 2010.05.30 11795
50 Tip 11g Newfeature 승현짱 2010.05.30 11477
49 Tip EXPORT/IMPORT 이용한 TABLE과 INDEX 분리하는 방법 고구마 2010.05.24 25453
48 Tip TABLE(INDEX) 다른 TABLESPACE로 옮기기(8i) 고구마 2010.05.24 23009
47 Tip reoder column order 1 유주환 2010.05.19 11394
46 Tip RECOVERY 2 고구마 2010.05.19 19916
45 Tip TEMP TABLESPACE 사용현황 조회하기 1 고구마 2010.05.19 43816
44 Tip 통계복원하기 고구마 2013.05.13 8046
43 Tip 유저별 Segment 개수 및 사이즈 조회 쿼리, n2bfree 2013.03.12 11017
42 Tip online reorg script 담벼락 2013.01.10 10630
41 Tip 오라클11g에서 SE1,SE,EE제품의 옵션비교입니다. file perfstat 2012.03.07 27011
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8