Nested Loops Join

#핵심 요약

Nested Loops 조인은 조인하는 데이터 양이 적을 때 적합함
힌트는 use_nl임

조인 순서는 leading(테이블 순서) 힌트로 정함
ordered는 FROM 절 순서대로 조인하는 옛 힌트이고, 실습에서는 leading을 권장함

조인 조건 외에 검색 조건이 없으면 작은 테이블을 드라이빙 테이블로 함
검색 조건이 있으면 조건으로 걸러지는 건수가 적은 쪽을 먼저 읽음

드라이빙 테이블에서 인덱스로 많이 읽고 테이블에서 대부분 버린다면, 그 필터 컬럼을 인덱스에 추가함

11g 이상에서는 NL 조인에 Batch I/O가 기본 적용되어 ORDER BY가 없으면 결과 순서가 보장되지 않음

#개념과 동작 원리

설명(세 테이블 조인): leading(d, s, e)처럼 연결 조건이 없는 두 테이블(dept, salgrade)을 먼저 NL로 묶으면 실제로는 카테시안 곱이 됨
MERGE JOIN CARTESIAN과 달리 NL은 실행계획에 CARTESIAN이라고 표시되지 않으므로 Starts와 A-Rows로 역산해야 함(실습 기록: DEPT 4건 × SALGRADE 5건 = 20건, 이를 드라이빙으로 EMP를 20번 Full Scan, Buffers 152)

#필수 명령어 또는 SQL

#조인 순서와 버퍼

SELECT /*+ leading(e d) use_nl(d) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno;               -- 실습 92

SELECT /*+ leading(d e) use_nl(e) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno;               -- 실습 32

SELECT /*+ leading(d e) use_nl(e) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SCOTT';  -- 실습 32

SELECT /*+ leading(e d) use_nl(d) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SCOTT';  -- 실습 14

결과 해석: 인덱스가 없을 때 NL 조인은 드라이빙 테이블 한 건마다 이너 테이블을 다시 읽으므로 작은 쪽, 또는 조건으로 적게 걸러지는 쪽을 먼저 둠
실습에서는 emp의 deptno에 인덱스를 만든 뒤 index(e emp_deptno)를 더해 dept → emp 순서를 12버퍼로 줄였고, dept에 PK를 건 뒤 emp → dept 순서는 25버퍼였음

#드라이빙 테이블의 필터 컬럼을 인덱스에

실습 환경: 사원 2000건(부서 50개, 부서당 40명), 고객 14만 건(사원 1명당 평균 70명)
인덱스 사원_X1(입사일자), 고객_X1(관리사원번호)

SELECT /*+ leading(e c) use_nl(c) index(e 사원_X1) */
       e.사원번호, e.사원명, e.입사일자, c.고객번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE e.사원번호 = c.관리사원번호
   AND e.입사일자 >= '19960101'
   AND e.부서코드 = 'Z123';

결과 해석: 입사일자 조건만으로 1373건을 읽고 테이블에서 부서코드로 걸러 27건만 남겼으므로, 부서코드를 인덱스에 넣어 인덱스 단계에서 거르게 했음
실습에서는 이어서 인덱스를 부서코드 + 입사일자로 바꿔 보게 했고(등치 조건 컬럼을 앞에, 스캔 범위와 필터 조건), 이너 쪽 고객_X1을 관리사원번호 + 최종주문금액으로 바꿔 2003버퍼를 1928버퍼로 줄였음

실습 조건 e.입사일자 >= '19960101'은 DATE 컬럼과 문자열 비교라 세션 날짜 형식에 따라 결과가 달라질 수 있음(인덱스 사용을 방해하는 조건)
실습 11번 문제는 "20000 이상"을 묻는데 답은 c.최종주문금액 >= 2000임

#세 테이블 조인 순서

SELECT /*+ leading(s, e, d) use_nl(e) use_nl(d) */ e.ename, e.sal, d.loc, s.grade
  FROM emp e, dept d, salgrade s
 WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;   -- 실습 123

SELECT /*+ leading(d, e, s) use_nl(e) use_nl(s) */ e.ename, e.sal, d.loc, s.grade
  FROM emp e, dept d, salgrade s
 WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;   -- 실습 116

결과 해석:

use_nl은 조인되는 쪽(이너) 테이블마다 따로 적음

#Nested Loops로 바꿔 정렬 없애기

선두 테이블 인덱스가 (조건 컬럼, 정렬 컬럼)이면 해시 조인 뒤 정렬하는 대신 Nested Loops로 인덱스를 거꾸로 읽어 SORT ORDER BY를 없앨 수 있음

-- payment_big 8만 건, 인덱스 idx_payment_big_bd (branchid, paydate), merchant_big PK
SELECT /*+ leading(pm) use_nl(m) index_desc(pm idx_payment_big_bd) */
       pm.branchid, pm.paymentno, pm.paydate, pm.payamt, m.mname, m.category
  FROM payment_big pm, merchant_big m
 WHERE pm.merchantcode = m.merchantcode
   AND pm.branchid = 'BR050'
 ORDER BY pm.paydate DESC;

결과 해석:

실습 기록: INDEX RANGE SCAN DESCENDING으로 정렬 없이 첫 50건을 Buffers 141에 가져왔음
연습 문제(reservation_big 5만 건, (hotelid, resdate) 인덱스)의 힌트 없는 SQL은 HASH JOIN 뒤 SORT ORDER BY(Buffers 200)였고, 같은 힌트로 SORT ORDER BY가 사라졌음(Buffers 140)

인덱스 컬럼 순서도 정렬 생략을 좌우함
job = 'SALESMAN' ... ORDER BY reg_dt DESC에서 (job, deptno, reg_dt) 인덱스는 중간 deptno에 조건이 없어 정렬이 남고, (job, reg_dt) 인덱스 + index_desc면 사라짐(실습 emp_big 28만 건)

결합 인덱스는 = 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둠
수업 자료 예: 입사일자 >= '19960101' 단일 인덱스로 1,373건을 읽고 부서코드 = 'Z123' 필터로 28건만 남겼다면 (부서코드, 입사일자)가 맞음

세 테이블을 leading(d, s, e)처럼 연결 조건 없는 순서로 이으면 Nested Loops 카테시안 조인이 되지만, 실행계획의 NESTED LOOPS에는 "CARTESIAN" 표기가 없을 수 있음
안쪽 테이블의 Starts가 바깥 건수만큼이고 필터가 없는지로 알아봄

#Prefetch와 Batch I/O

SELECT /*+ optimizer_features_enable('8.1.7') leading(e, d) use_nl(d) index(e emp_job)
           no_nlj_prefetch(e) no_nlj_prefetch(d) */ e.ename, d.loc
  FROM emp e, dept d
 WHERE e.deptno = d.deptno AND e.job = 'SALESMAN' AND d.loc = 'CHICAGO';

결과 해석: no_nlj_prefetch를 주면 DEPT의 TABLE ACCESS BY INDEX ROWID가 NESTED LOOPS 아래로 내려온 전통적인 모양이 됨
optimizer_features_enable('8.1.7')은 옛 버전 옵티마이저 동작을 흉내 내는 힌트임

실습의 Batch I/O 실습(게시판 20만 건, 인덱스 게시판유형 + 등록일시, 회원 1000건)은 Top-N 결과를 회원과 NL 조인할 때 순서를 지키는 방법을 비교했음

SELECT /*+ ordered use_nl(b) no_nlj_batching(b) */
       a.등록일시, a.번호, a.제목, b.회원명
  FROM (SELECT a.*, ROWNUM no
          FROM (SELECT 등록일시, 번호, 제목, 작성자번호
                  FROM 게시판
                 WHERE 게시판유형 = 'TYPE1'
                 ORDER BY 등록일시 DESC) a
         WHERE ROWNUM <= 30) a,
       회원 b
 WHERE a.작성자번호 = b.회원번호;

결과 해석:

설명: 순서를 보장하려면 no_nlj_batching으로 Batch I/O를 끄거나, 바깥에 ORDER BY a.등록일시 DESC를 명시함
안쪽 ORDER BY를 빼면 정렬이 깨지는 정도가 아니라 Top-N으로 뽑히는 행 자체가 달라짐
실습에서는 ORDER BY ... FETCH FIRST 30 ROWS ONLY로 안쪽을 쓰는 형태를 가장 이상적이라고 적었음
실습2 코드에는 힌트 밖에 no_nlj_prefetch(e) no_nlj_prefetch(d) 한 줄이 그대로 남아 있고, 게시판 생성문이 두 번 들어 있어 그대로 실행하면 오류가 남