JOIN-016

원재료 20종의 입출고 집계가 입출고상품 테이블을 스무 번 읽는다

중급SELECT유통166 / 700

상황

구매팀의 「원재료 입출고 집계」는 원재료 20종마다 입출고 줄 수와 수량 합계를 낸다. 하루 수십 번 돌린다. SQL은 「상품 하나의 입출고 이력」 화면의 SQL을 복사해 조건만 바꾼 것이라 힌트도 그대로 붙어 있다. 조민우는 실행계획을 보고 "CF15에 ITM_NO 인덱스를 만들면 해결된다"라며 인덱스 생성 요청서를 올렸다. 오과장은 요청서를 받아들일지 숫자로 판단해 달라고 했다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 원재료(ITM_CL_CD = '102') 20개
CF15 입출고상품: 51,497행 · 349블록 · PK (INOUT_NO, ITM_NO) · 보조 인덱스 없음
원재료 줄은 6,201줄(전체의 12%, 상품마다 211 ~ 1,249줄)이다. 입출고 번호 순으로 쌓여서 한 원재료의 줄은 테이블 전체에 흩어져 있다 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(p) USE_NL(i) */
       p.ITM_NO, p.ITM_NM, COUNT(*) AS io_lines, SUM(i.INOUT_QTY) AS io_qty
  FROM CF01 p, CF15 i
 WHERE p.ITM_CL_CD = '102'
   AND i.ITM_NO = p.ITM_NO
 GROUP BY p.ITM_NO, p.ITM_NM

실행계획 학습용 예시 · 행 수는 실데이터

xplan

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     20 |00:00:00.21 |    6985 |
|   1 |  HASH GROUP BY       |      |      1 |     36 |     20 |00:00:00.21 |    6985 |
|   2 |   NESTED LOOPS       |      |      1 |  27252 |   6201 |00:00:00.20 |    6985 |
|*  3 |    TABLE ACCESS FULL | CF01 |      1 |     36 |     20 |00:00:00.01 |       5 |
|*  4 |    TABLE ACCESS FULL | CF15 |     20 |    757 |   6201 |00:00:00.20 |    6980 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("P"."ITM_CL_CD"='102')
   4 - filter("I"."ITM_NO"="P"."ITM_NO")

문제

6,985블록이 어디서 나오는지 Starts로 설명하시오.
CF15 (ITM_NO) 인덱스를 만들어 NL로 처리할 때와 해시 조인으로 처리할 때의 일량을 비교해, 인덱스 생성 요청을 받아들일지 정하고 개선 SQL을 작성하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 인덱스 요청은 반려한다: 원재료 줄 6,201개를 하나씩 찾아가는 것보다 CF15 를 한 번 읽는 편이 싸다
SELECT /*+ LEADING(p i) USE_HASH(i) */
       p.ITM_NO, p.ITM_NM, COUNT(*) AS io_lines, SUM(i.INOUT_QTY) AS io_qty
  FROM CF01 p, CF15 i
 WHERE p.ITM_CL_CD = '102'
   AND i.ITM_NO = p.ITM_NO
 GROUP BY p.ITM_NO, p.ITM_NM

개선 후 실행계획 학습용 예시 · 행 수는 실데이터

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     20 |00:00:00.02 |     354 |           |
|   1 |  HASH GROUP BY       |      |      1 |     36 |     20 |00:00:00.02 |     354 | 1053K (0) |
|*  2 |   HASH JOIN          |      |      1 |  27252 |   6201 |00:00:00.02 |     354 | 1236K (0) |
|*  3 |    TABLE ACCESS FULL | CF01 |      1 |     36 |     20 |00:00:00.01 |       5 |           |
|   4 |    TABLE ACCESS FULL | CF15 |      1 |  51497 |  51497 |00:00:00.01 |     349 |           |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_NO"="P"."ITM_NO")
   3 - filter("P"."ITM_CL_CD"='102')

모범 답안 서술

4번 줄 TABLE ACCESS FULL CF15의 Starts가 20이다. 원재료 20개마다 CF15 349블록을 처음부터 끝까지 다시 읽었으므로 20 × 349 = 6,980블록이고, 전체 6,985블록의 거의 전부다. 원래 화면은 상품 하나를 조회해서 CF15를 한 번만 읽었지만, 조건을 '원재료 20개'로 바꾸면서 같은 힌트가 20배의 일을 시켰다. 요청대로 CF15 (ITM_NO) 인덱스를 만들면 FULL 스캔 20번은 사라진다. 대신 원재료 줄 6,201개를 인덱스로 하나씩 찾아가야 한다. 한 원재료의 줄은 테이블 전체에 흩어져 있어, 상품마다 흩어진 블록을 따라 읽으면 테이블 액세스만 약 3,100블록이고 인덱스까지 약 3,200블록이다. 원본의 절반이지만 해시 조인보다는 9배 비싸다. 해시 조인은 원재료 20개(CF01 5블록)로 해시 테이블을 만든 뒤 CF15를 한 번만 읽으며 맞춰 본다. Buffers는 6,985 → 354다. 가져올 행이 테이블의 12%나 되고 흩어져 있으면, 인덱스로 한 행씩 찾아가는 것보다 테이블을 한 번 읽는 편이 싸다. 손익분기점을 넘은 것이다. 그래서 인덱스 생성 요청은 반려하고 힌트를 해시 조인으로 바꾼다. 요청서에는 '해시 조인 354블록, 인덱스 NL 약 3,200블록'처럼 숫자로 답한다.

해설

JOIN-002와 무엇이 다른가: 하루 21건처럼 바깥 집합이 작고 inner에서 가져올 행도 적으면 조인 키 인덱스가 답이다. 여기서는 inner에서 가져올 행이 테이블의 12%(6,201줄)이고 테이블 전체에 흩어져 있어서, 인덱스로 찾아가는 비용이 테이블을 한 번 읽는 비용보다 크다(교재 04화 §3 손익분기).
복사해 온 힌트: 원래 화면에서는 NL로 CF15를 한 번 읽든 해시로 한 번 읽든 같았다. 힌트를 복사할 때는 그 힌트를 정한 행 수의 전제도 함께 확인한다(교재 06화 §8).
인덱스의 대가: CF15는 입출고가 생길 때마다 행이 들어오는 테이블이다. 쓰지도 않을 인덱스는 적재 비용과 저장 공간만 늘린다.
「상품 하나의 입출고 이력」 화면을 자주 쓴다면 그 화면만을 위해 (ITM_NO, INOUT_NO) 인덱스를 따로 검토할 수 있다. 그 화면의 호출 빈도와 Buffers를 재서 판단할 일이고, 이 집계와 묶어서 정할 일은 아니다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황