NL조인 기반 인덱스 설계

 

 

https://product.kyobobook.co.kr/detail/S000001975837

친절한 SQL 튜닝 | 조시형 - 교보문고

 

요즘 SQL 튜닝에 대해 관심이 생겨 관련된 내용을 공부하는 중이다.

그러던 중 '친절한 SQL 튜닝' 이라는 책에서 아래와 같은 내용을보게 되었다.

 

 

---

 

(책 내용 중...)

개발 업무를 하다 보면 다음과 같이 인덱스 생성을 요청받는 경우가 종종 있다.

 

PRA_HST_STC_N1 : SALE_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT

 

해당 인덱스의 타당성을 검토하기 위해 SQL을 요청했더니, 다음과 같은 쿼리를 전달받았다.

SELECT * 
FROM PRA_HST_STC a, ODM_TRMS b
WHERE a.SALE_ORG_ID = :sale_org_id
  AND a.STRD_GRP_ID = b.STRD_GRP_ID
  AND a.STRD_ID = b.STRD_ID
ORDER BY a.STC_DT DESC;

 

겉보기엔 크게 문제 없어 보이지만, 실제로는 이 인덱스 신청이 비효율적인 경우다. 특히 NL 조인(Nested Loop Join)의 원리를 고려한다면 더욱 그렇다.

---

 

 

이 문제는 답이 있는 문제는 아니다.

그래서 내 생각과 그 이유에 대해 남겨보려고 한다.

 

SALE_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT 그대로가 제일 효율적일 것 같다.

 

이렇게 생각하는 이유는 다음과 같다.

• 인덱스 첫 번째 컬럼으로 SALE_ORG_ID가 위치하면 바인드 변수 조건에 의해 선행 필터링이 가능므로 해당 조직에 해당하는 데이터만 빠르게 추출할 수 있음.
• 조인키에 대응해서 STRD_GRP_ID, STRD_ID 를 인덱스 컬럼에 추가한다면,
  NL 조인에서 외부 테이블(PRA_HST_STC)이 먼저 스캔되기 때문에 그 안에서 STRD_GRP_ID, STRD_ID가 정렬된 상태로 준비되어 내부 테이블(ODM_TRMS)에서 조인 시 빠른 탐색이 가능할 것이다.
• 인덱스 끝에 STC_DT가 포함되어 있고, DESC 정렬이 지정돼 있다면 정렬 연산 없이 Index Scan만으로 결과 정렬 가능할 것이므로 SORT ORDER BY 비용을 없앨 수 있다.

 

 

다른 사람들의 의견은 어떤지 궁금하여 검색을 해보았는데 다음과 같은 의견도 발견했다.

• 추측 1. Outer는 PRA_HST_STC 테이블이고, Inner는 ODM_TRMS이다.
• 추측 2. 따라서 조건절 중, 'AND a.STRD_GR_ID = b.STRD_GRP_ID AND a.STRD_ID = b.STRD_ID'은 INNER 쪽을 찾을 때 사용하는 조건절이다.
• 추측 3. 따라서 인덱스 컬럼 중 두 번째(STRD_GRP_ID)와 세 번째 (STRD_ID)는 인덱스에 의미가 없다.
• 추측 4. STC_DT는 ORDER BY를 위해 필요하므로 사용하는 것이 좋다.
• 즉, SALE_ORG_ID + STC_DT 만 존재하면 될 것으로 판단.

 

서로의 의견을 비교해 보면 아래와 같이 정리할 수 있다.

	다른 의견	내 의견
조인 방향	PRA_HST_STC → ODM_TRMS	PRA_HST_STC → ODM_TRMS
인덱스 컬럼 구성	SALE_ORG_ID, STC_DT 만 필요	STRD_GRP_ID, STRD_ID, SALE_ORG_ID, STC_DT  모두 필요
조인 키는 생략?	O (INNER에 사용되므로)	X (OUTER에도 있어야 효율적)
ORDER BY 고려	STC_DT만 필요	정렬도 포함, 인덱스 커버리지를 최대화해야 함
주된 논리	INNER 테이블 탐색만 인덱스 의미 있음	OUTER도 튜닝 대상. 조인 전에 pruning + 커버리지가 핵심

 

 

그래서 내 의견에 대한 근거를 아래와 같이 더 들어보았다.

 

1. OUTER 테이블에도 인덱스가 매우 중요

• Nested Loop Join의 OUTER는 여러 번 액세스되므로 효율이 중요.
• PRA_HST_STC는 바인딩 필터와 조인 조건을 모두 가지므로 인덱스를 통해
  • 필터링 (SALE_ORG_ID)
  • 조인 키 준비 (STRD_GRP_ID, STRD_ID)
  • 정렬 (STC_DT) → 모두 한 번에 처리 가능.

2. 인덱스 커버리지

• STRD_GRP_ID, STRD_ID, SALE_ORG_ID, STC_DT  인덱스는 위 내용을 전부 커버 가능할 수 있다.
  • 인덱스만으로 데이터를 다 가져오면 TABLE ACCESS 자체가 생략됨.
  • 특히 NL 조인에서 OUTER는 반복 접근되므로 커버링 인덱스가 있는게 좋아 보인다.

3. 정렬까지 커버

• ORDER BY STC_DT DESC가 붙어 있는데, 인덱스에 STC_DT가 없다면 SORT 작업이 추가됨.
• 인덱스 컬럼 끝에 STC_DT가 있으면, Index Full Scan(Descending) 만으로 정렬비용도 줄일 수 있다.

 

 

NL 조인은 일반적으로 OUTER 테이블의 각 Row에 대해 INNER 테이블을 반복적으로 탐색하는 방식이다.

이때 성능 최적화를 위해서는 OUTER 테이블에서 필터 조건과 조인 키가 모두 포함된 인덱스를 활용해 최소한의 I/O로 조인 키를 준비해주는 것이 중요하다고 생각한다.

 

SALE_ORG_ID가 인덱스 앞에 오는 것은 필터 조건이기 때문에 적절하다고 생각되고,

그 다음 조인 키(STRD_GRP_ID, STRD_ID)를 순차적으로 포함시켜 Index Range Scan이 가능하게끔 설계하는 것이 이상적이라고 생각한다.

 

 

그래서 OUTER 테이블에서 조인 키 + 필터 조건 + 정렬 조건을 모두 커버하는 인덱스가 NL 조인에 가장 적합하지 않을까 생각한다.

 

 

 

테스트 해보기

실제로 두 인덱스간의 실행계획을 파악해보기 위해서 테이블을 생성한 후 임의의 데이터를 밀어넣었다.

조인방식은 USE_NL 힌트절을 사용하여 NL 조인을 강제하였다.

 

-- OUTER 테이블: PRA_HST_STC
CREATE TABLE PRA_HST_STC (
    SALE_ORG_ID   NUMBER,
    STRD_GRP_ID   NUMBER,
    STRD_ID       NUMBER,
    STC_DT        DATE,
    ETC_DATA      VARCHAR2(100)
);

-- INNER 테이블: ODM_TRMS
CREATE TABLE ODM_TRMS (
    STRD_GRP_ID   NUMBER,
    STRD_ID       NUMBER,
    DESC_TEXT     VARCHAR2(100)
);

 

테스트용 데이터를 넣는다

-- PRA_HST_STC를 10만 건
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO PRA_HST_STC (
      SALE_ORG_ID, STRD_GRP_ID, STRD_ID, STC_DT, ETC_DATA
    ) VALUES (
      MOD(i, 10),
      MOD(i, 100),
      MOD(i, 200),
      SYSDATE - MOD(i, 365),
      'Data ' || i
    );
    IF MOD(i, 10000) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END;
/

-- ODM_TRMS: 2000건
BEGIN
  FOR i IN 0..2000 LOOP
    INSERT INTO ODM_TRMS (
      STRD_GRP_ID, STRD_ID, DESC_TEXT
    ) VALUES (
      MOD(i, 100),
      i,
      'Description ' || i
    );
  END LOOP;
  COMMIT;
END;
/

 

 

 

1. (SALE_ORG_ID, STRD_GRP_ID, STRD_ID, STC_DT DESC) 인덱스를 생성하여 실행계획 확인

CREATE INDEX IDX_PRA_HST_STC_OPTIMAL
ON PRA_HST_STC (SALE_ORG_ID, STRD_GRP_ID, STRD_ID, STC_DT DESC);

 

-- 실행계획 확인

EXPLAIN plan FOR
SELECT /*+ USE_NL(a b) */ a.*
FROM PRA_HST_STC a
JOIN ODM_TRMS b
  ON a.STRD_GRP_ID = b.STRD_GRP_ID
 AND a.STRD_ID = b.STRD_ID
WHERE a.SALE_ORG_ID = 3
ORDER BY a.STC_DT DESC;

SELECT * FROM TABLE(dbms_xplan.display);

 

 

결과

 

 

 

2. (SALE_ORG_ID, STC_DT DESC) 인덱스를 생성하여 실행계획 확인

-- 1에서 생성한 인덱스를 삭제한 후
drop index IDX_PRA_HST_STC_OPTIMAL;

-- 새롭게 인덱스를 생성
CREATE INDEX IDX_PRA_HST_STC_WEAK
ON PRA_HST_STC (SALE_ORG_ID, STC_DT DESC);

 

-- 실행계획 확인

EXPLAIN plan FOR
SELECT /*+ USE_NL(a b) */ a.*
FROM PRA_HST_STC a
JOIN ODM_TRMS b
  ON a.STRD_GRP_ID = b.STRD_GRP_ID
 AND a.STRD_ID = b.STRD_ID
WHERE a.SALE_ORG_ID = 3
ORDER BY a.STC_DT DESC;

SELECT * FROM TABLE(dbms_xplan.display);

 

결과

 

 

IDX_PRA_HST_STC_OPTIMAL 인덱스 (SALE_ORG_ID, STRD_GRP_ID, STRD_ID, STC_DT DESC) 가

IDX_PRA_HST_STC_WEAK 인덱스 (SALE_ORG_ID, STC_DT DESC) 보다 대략 700배 정도 비용이 적게 들걸로 예상이 되는 것을 알 수 있다.