[친절한SQL] 조인 튜닝 (NL조인)

Coastby·2024년 8월 1일
NL조인친절한SQL
0

친절한 SQL

목록 보기
1/3

4.1 NL 조인 (Nested Loop Join)

  • NL 조인은 인덱스를 이용한 조인
  • 소트 머지 조인과 해시 조인도 프로세싱 과정은 NL 조인과 사용하는 자료 구조 (Sort Area, Hash Area)만 다르다.

4.1.1 기본 메커니즘

  • 건건이 일치하는 레코드는 찾는 것 → 중첩 루프문 (Nested Loop)
SELECT E.사원명, C. 고객명, C.전화번호
FROM 사원 E, 고객 C
WHERE E.입사일자 >= '19960101'
	AND C.관리사원번호 = E.사원번호;
  • 일반적으로 NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용
    • Outer 테이블 (사원)의 사이즈가 크지 않으면 인덱스를 사용 x : Table full scan 하더라도 한 번만 하기 때문에
    • Inner 테이블 (고객)은 인덱스를 사용 : 인덱스를 사용하지 않으면 매번 (outer table 건수만큼) table full scan을 하기 때문에

1) 사원_X1 인덱스에서 입사일자 ≥’19960101’ 인 첫 번째 레코드를 찾는다.
2) 인덱스에서 읽은 ROWID로 사원 테이블 레코드를 찾아간다.
3) 사원 테이블에서 읽은 사원번호 ‘0006’으로 고객_X1 인덱스를 탐색한다.
4) 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아간다.

사원_X1 인덱스에서 입사일자 조건인 모든 레코드에 대해 같은 과정을 반복한다.

4.1.2 NL 조인 실행계획 제어

  • NL 조인 실행 계획 예시 → 사원 테이블 기준으로 고객 테이블과 NL 조인한다
  • 힌트 사용
    • /*+ ordered us_nl(c) */
      • ordered : FROM 절에 기술한 순서대로 조인
      • us_nl : NL 방식으로 조인
    • 예시 1)
      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와 조인할 때는 해시 방식으로 조인
    • 예시 2)
      select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */ *
from A, B, C, D
where ...
      leading() : FROM 절을 수정하지 않아도 제어할 수 있다는 장점

4.1.3 NL 조인 수행 과정 분석

select /*+ ordered use_nl(c) index(e) index(c)*/ 
	  e.사원번호, e.사원명, e.입사일자,
      c.고객번호, c.고객명, c.전화번호, c.최종주문번호
from 사원 e, 고객 c
where c.관리사원번호  = e.사원번호   -- 1️⃣
and   e.입사일자    >= '19960101'   -- 2️⃣ 
and   e.부서코드     = 'Z123'       -- 3️⃣
and   c.최종주문금액 >= 20000        -- 4️⃣
사원_PK : 사원번호
사원_X1 : 입사일자
고객_PK : 고객번호
고객_X1 : 관리사원번호
고객_X2 : 최종주문금액
  1. 조건절 비교 순서?
  2. 위의 인덱스 중 어떤 것이 사용될까?
  • 설명
    • 2️⃣ : 입사일자에 만족하는 레코드를 찾으려고 사원_X1 index range scan
    • 3️⃣ : 사원_X1에서 읽은 ROWID로 사원 테이블에 엑세스하여 부서코드에 조건이 만족하는지 확인
    • 1️⃣ : 사원 테이블의 사원번호 값으로 조인 조건을 만족하는 고객 쪽 레코드를 찾으려고 고객_X1 index range scan
    • 4️⃣ : 고객_X1에서 읽은 ROWID로 고객 테이블에 엑세스하여 최종주문금액 조건을 만족하는지 확인

4.1.4 NL 조인 튜닝 포인트

  1. 사원_X1 인덱스를 읽고 사원 테이블에 엑세스 하는 부분
    • 단일 컬럼 인덱스를 ‘≥’ 조건으로 스캔 → 해당되는 만큼만 table random access 발생
    • 부서코드에서 필터링되는 비율이 높다면 사원_X1 인덱스에 부서코드 컬럼을 추가 고려
  2. 고객_X1 인덱스 탐색하는 부분
    • 인덱스를 탐색하는 횟수 (조인 액세스 횟수)가 많을수록 성능이 느려진다.
    • 조인 액세스 횟수는 Outer 테이블 (사원)을 읽고 필터링한 결과 건수에 의해 결정된다.
    • 만약 조건을 만족하는 레코드가 10만 건이고 고객_X1 인덱스 Depth가 3이라면 인덱스 수직적 탐색 과정에서만 30만개 블록을 읽어야 하고, 리프 블록을 수평적으로 스캔하는 과정에서 추가적인 블록 I/O가 더해진다.
  3. 고객_X1 인덱스를 읽고 고객 테이블을 액세스하는 부분
    • 1.과 마찬가지로 필터링되는 비율이 높다면 컬럼 추가 고려
  4. 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다.
    • 사원_X1 인덱스로 추출한 레코드 증가 → 사원 테이블로 랜덤 액세스 횟수 증가 → 고객_X1 인덱스 탐색 횟수 증가 → 고객 테이블로 랜덤 액세스 횟수 증가

올바른 조인 메소드 선택

  • 온라인 트랜잭션 처리 (OLTP) 시스템에서 튜닝할 때 일차적으로 NL 조인부터 고려하는 것이 올바르다
  • 성능이 느리다면
    • 각 단계의 수행 일량을 분석해서 과도한 랜덤 액세스가 발생하는 지점 파악
    • 조인 순서 변경하여 랜덤 액세스 발생량을 줄일 수 있는지
    • 효과적인 인덱스가 있는지 고려
    • NL 조인으로 결코 성능이 안 나온다면 소트 머지 조인 / 해시 조인 검토

4.1.5 NL 조인 특징 요약

  1. 랜덤 액세스 위주
    • 랜덤 액세스 : 코드 하나를 읽기 위해 블록을 통째로 읽음
    • 인덱스 구성이 완벽해도 대량 데이터 조인 시에는 NL 조인이 불리하다
  2. 조인을 한 레코드씩 순차적으로 진행
    • 부분범위 처리가 가능한 상황에서 큰 테이블 조인 시 빠른 응답 속도
    • 순차적으로 진행하므로 먼저 액세스되는 테이블 처리 범위에 의해 전체 일량이 결정된다.
  3. 인덱스 구성 전략이 특히 중요
    • 조인 컬럼에 대한 인덱스 유무, 인덱스 컬럼 구성에 따라 조인 효율이 결정된다.

💡 NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리 시스템(OLTP)에 적합한 조인 방식이다.

4.1.6 NL 조인 튜닝 실습

select /*+ ordered use_nl(c) index(e) index(c)*/ 
	  e.사원번호, e.사원명, e.입사일자,
      c.고객번호, c.고객명, c.전화번호, c.최종주문번호
from 사원 e, 고객 c
where c.관리사원번호  = e.사원번호
and   e.입사일자    >= '19960101'
and   e.부서코드     = 'Z123'
and   c.최종주문금액 >= 20000

예시1) 

ROWS    ROW Source Operation
----    -------------------------------------------
5				NESTED LOOPS
3					TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
2780				INDEX(RANGE SCAN) OF '사원_X1' (INDEX)
5					TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
8						INDEX(RANGE SCAN) OF '고객_X1' (INDEX)
  • 튜닝 1 사원_X1 인덱스를 스캔 후 사원테이블에 액세스한 횟수가 2780번인데 부서코드 필터링 결과는 3건에 불과하다. → 불필요한 데이터를 많이 액세스하였으므로, 인덱스에 테이블 필터 조건 컬럼을 추가하는 것을 고려할 필요가 있음
  • 튜닝 2 사원_X1 인덱스에 [입사일자 + 부서코드]으로 수정했다. 조건절에서 입사일자는 부등호 조건으로 입자일자 조건에 해당하는 레코드가 아주 많다면 인덱스 블록을 스캔하면서도 부서코드를 필터링했을 것이다. → 사원_X1 인덱스 컬럼 순서를 [부서코드 + 입사일자] 순으로 구성하면 된다. (다른 쿼리에 미치는 ‘영향도 분석’이 선행되어야 한다)

예시 2)

ROWS    ROW Source Operation
----    -------------------------------------------
5				NESTED LOOPS (cr=2732 pr=386 pw=0..)
2780			TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE) (cr=166 pr=2 pw=0..)
2780				INDEX(RANGE SCAN) OF '사원_X1' (INDEX) (cr=4 pr=0 pw=0..)
5					TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (cr=2566 pr=384 pw=0..)
8						INDEX(RANGE SCAN) OF '고객_X1' (INDEX) (cr=2588 pr=383 pw=0..)

cr : 논리적인 블록 요청 횟수, pr : 디스크에서 읽은 블록 수, pw : 디스크에 쓴 블록 수

  • 튜닝 1 사원 테이블을 읽는 부분은 일량은 많지만 비효율은 없다. 사원 테이블을 읽은 후 고객 테이블과 조인하는 횟수가 문제이다. 2780번 조인 시도를 했지만 조인에 성공하고 필터링까지 마친 최종 결과 집합은 다섯 건뿐디ㅏ. → 조인 순서 변경을 고려해볼 수 있다. 최종주문금액 조건절에 부합하는 레코드가 별로 없다면 튜닝에 성공할 가능성이 있다. 하지만 최종주문금액 단독으로 조회하면 데이터량이 2780보다 훨씬 많을 수도 있다. 순서 변경을 하여도 튜닝에 실패한다면 소트 머지 조인, 해시 조인을 고려해 볼 수 있다.

4.1.7 NL 조인 확장 메커니즘 (이해x)

버전이 올라가며 오라큼에서 NL 조인 성능을 높이기 위해 아래 기능을 도입했다. 두 기능 모두 읽는 블록마다 건건이 I/O call 발생을 줄이기 위해 고안되었다.

  1. 테이블 Prefetch

    인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능

    Inner 테이블에 대한 디스크 I/O 과정에서 테이블 prefetch 기능이 작동할 수 있음. nlj_prefetch, no_nlj_prefetch 힌트를 이용할 수 있다.

  2. 배치 I/O

    디스트 I/O call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능

    inner 테이블에 대한 디스크 I/O 과정에서 배치 I/O 기능이 작동할 수 있음. nlj_batching, no_nlj_batching 힌트 사용 가능.

오라클 11g에서 모두 나올 수 있는데 inner 테이블을 모두 버퍼캐시에서 읽는다면 어떤 방식으로 수행하든 성능 차이x, 출력 순서 같음. 일부를 디스크에서 읽으면 정렬 차이가 생길 수 있으므로, ORDER BY 명시하면 좋음.

NL 조인 자가진단

-- 인덱스 PRA_HST_STC_N1 : SALE_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT
select *
	FROM PRA_HST_STC a, ODM_TRMS b
WHERE 
	a.SALE_ORG_ID = :sale_org_id
	AND a.STRD_GR_ID = b.STRD_GRP_ID
	AND a.STRD_ID = b.STRD_ID
	AND b.TRMS_DT = :trms_dt
ORDER BY a.STC_DT desc
profile
Coastby
훈이야 화이팅
다음 포스트

Online Transaction Processing (OLTP)

0개의 댓글

관련 채용 정보