IN으로 바꿨을 뿐인데 평가 상품 TOP 5가 13배 느려졌다

중급SELECT한빛유통237 / 700

상황

상품팀의 「평가가 있는 상품의 판매 TOP 5」는 08화의 Q3 rev01(EXISTS, 839블록)을 다른 팀이 가져다 쓴 것이다. 그 팀은 IN으로 바꾸면서 사내 공통 가이드의 한 줄, "IN 서브쿼리에는 NO_UNNEST를 붙인다 (2019.03 해시 세미 조인 TEMP 장애 뒤)"를 따랐다. 그 뒤로 답은 같은데 Buffers가 839 → 11,001로 늘었다.

테이블과 인덱스

T_ORD_DET 주문상세: 200,000행 · 전체 스캔 807블록 · 상품 100종류 × 2,000줄 · 주문번호 순으로 쌓여 있다
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) (인덱스 32블록) · 상품마다 50건 · 100개 상품 전부에 평가가 있다
통계 최신 · 히스토그램 없음

원본 SQL

sql

SELECT d.ITM_ID, SUM(d.ORD_QTY * d.UNT_PRC) AS amt, COUNT(*) AS cnt
  FROM T_ORD_DET d
 WHERE d.ITM_ID IN (SELECT /*+ NO_UNNEST */ e.ITM_ID FROM T_ITM_EVL e)
 GROUP BY d.ITM_ID
 ORDER BY amt DESC
 FETCH FIRST 5 ROWS ONLY

실행계획 학습용 예시 · 수치는 교재 08화 실측

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |      1 |        |      5 |00:00:00.09 |   11001 |
|*  1 |  VIEW                     |              |      1 |      5 |      5 |00:00:00.09 |   11001 |
|*  2 |   WINDOW SORT PUSHED RANK |              |      1 |    100 |      5 |00:00:00.09 |   11001 |
|   3 |    HASH GROUP BY          |              |      1 |    100 |    100 |00:00:00.09 |   11001 |
|*  4 |     FILTER                |              |      1 |        |    200K|00:00:00.08 |   11001 |
|   5 |      TABLE ACCESS FULL    | T_ORD_DET    |      1 |    200K|    200K|00:00:00.01 |     807 |
|*  6 |      INDEX RANGE SCAN     | PK_T_ITM_EVL |   5097 |      1 |   5097 |00:00:00.03 |   10194 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY SUM("D"."ORD_QTY"*"D"."UNT_PRC") DESC )<=5)
   4 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_ITM_EVL" "E" WHERE "E"."ITM_ID"=:B1))
   6 - access("E"."ITM_ID"=:B1)

문제

4번과 6번 줄을 읽고 이 계획이 서브쿼리를 어떻게 실행했는지 설명하시오. Starts가 주문상세 행 수(20만)가 아니라 5,097인 이유도 설명하시오.
옵티마이저가 서브쿼리를 펼치도록 SQL을 고치시오. 같은 뜻을 조인으로 쓰려면 무엇을 조심해야 하는지(조인과 DISTINCT의 위치)도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- NO_UNNEST 를 뗀다 — 옵티마이저가 IN 을 세미 조인으로 펼친다 (힌트 없이)
SELECT d.ITM_ID, SUM(d.ORD_QTY * d.UNT_PRC) AS amt, COUNT(*) AS cnt
  FROM T_ORD_DET d
 WHERE d.ITM_ID IN (SELECT e.ITM_ID FROM T_ITM_EVL e)
 GROUP BY d.ITM_ID
 ORDER BY amt DESC
 FETCH FIRST 5 ROWS ONLY;
-- EXISTS (SELECT 1 FROM T_ITM_EVL e WHERE e.ITM_ID = d.ITM_ID) 로 써도 같은 계획이다 (08화 Q3 rev01)

개선 후 실행계획 학습용 예시 · 수치는 교재 08화 실측

xplan

Plan hash value: 2231864090

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |      1 |        |      5 |00:00:00.03 |     839 |
|*  1 |  VIEW                     |              |      1 |      5 |      5 |00:00:00.03 |     839 |
|*  2 |   WINDOW SORT PUSHED RANK |              |      1 |    100 |      5 |00:00:00.03 |     839 |
|   3 |    HASH GROUP BY          |              |      1 |    100 |    100 |00:00:00.03 |     839 |
|*  4 |     HASH JOIN RIGHT SEMI  |              |      1 |    200K|    200K|00:00:00.02 |     839 |
|   5 |      INDEX FAST FULL SCAN | PK_T_ITM_EVL |      1 |   5000 |   5000 |00:00:00.01 |      32 |
|   6 |      TABLE ACCESS FULL    | T_ORD_DET    |      1 |    200K|    200K|00:00:00.01 |     807 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY SUM("D"."ORD_QTY"*"D"."UNT_PRC") DESC )<=5)
   4 - access("E"."ITM_ID"="D"."ITM_ID")

모범 답안 서술

4번 줄이 FILTER이고, 그 아래 6번 줄(서브쿼리)의 Starts가 5,097이다. 서브쿼리가 조인으로 펼쳐지지 않아서, 주문상세 20만 행을 읽어 가며 '이 상품에 평가가 있는가'를 행마다 따로 물었다(4번 Predicate의 EXISTS (… WHERE "E"."ITM_ID"=:B1)). 20만 번이 아니라 5,097번인 것은 FILTER에도 스칼라 서브쿼리와 같은 메모판이 붙고, 바로 앞 줄과 같은 상품이면 서브쿼리를 다시 열지 않기 때문이다(5,097 = 97 × 1 + 1,666 + 1,667 + 1,667, 08화). 그래도 한 번에 2블록씩 읽어 10,194블록이 됐다. 원인은 서브쿼리의 NO_UNNEST 힌트다. 이 힌트가 쿼리 변환(Unnesting)을 막아 서브쿼리를 조인으로 펼치지 못하게 했다. 힌트를 떼면 옵티마이저가 IN을 세미 조인(HASH JOIN RIGHT SEMI)으로 펼친다. 평가 인덱스를 한 번(32블록) 올리고 주문상세를 한 번(807블록) 훑어서, Buffers가 11,001 → 839로 준다. 2019년의 NO_UNNEST는 그때 그 SQL에서 난 TEMP 장애에 대한 처방이었다. 가이드 한 줄을 근거로 모든 IN에 붙이면 유효기간이 지난 힌트가 된다. 세미 조인은 짝이 '있는가'만 본다. 평가가 50건이든 1건이든 주문상세 한 줄은 한 번만 남는다. 그냥 조인으로 바꾸면 상품 하나에 평가 50건이 매달린 M : 1 : M이 되어 매출이 50배로 곱해진다 (08화 Q3 rev00). DISTINCT는 위치가 답을 가른다. 조인한 뒤에 SELECT DISTINCT나 SUM(DISTINCT 금액)을 쓰면 금액이 같은 서로 다른 주문상세 줄까지 하나로 합쳐져 답이 틀린다(08화: ITM00071의 2,000줄 중 서로 다른 금액은 1,276개라 1위가 바뀐다). 조인으로 쓰려면 평가 쪽을 먼저 상품당 한 줄로 만들고(인라인 뷰 SELECT DISTINCT ITM_ID FROM T_ITM_EVL) 붙여야 같은 답이 나온다.

해설

실측: FILTER 계획의 숫자는 교재 08화 실습/02의 [A] 실험에서 EXISTS (SELECT /*+ NO_UNNEST */ …)를 잰 값이다. IN은 FILTER 안에서 EXISTS (… = :B1)로 바뀌어 돌기 때문에 계획 모양과 Starts가 같다. 힌트를 뗀 계획은 IN과 EXISTS 모두 계획 해시 2231864090, 839블록이다.
Starts 5,097의 분해: 상품 100종류 중 97개는 메모판에 올라 한 번씩만 열렸고, 3개는 자리를 못 얻어 '값이 바뀌어 들어올 때마다' 다시 열렸다. 캐싱 효과는 입력 NDV에 따라서도, 값이 들어오는 순서에 따라서도 달라진다. 같은 데이터라도 적재 순서가 다르면 이 숫자는 바뀐다.
다른 정답: EXISTS로 쓰거나, /*+ UNNEST */를 주거나, 평가를 상품별로 먼저 묶어 1 : 1로 조인해도 된다(08화 표의 5번, 화면에 평가 수를 같이 보여 줘야 할 때). 모두 839블록이다.
변환을 막는 다른 모양: 힌트가 없는데 FILTER가 보이면 서브쿼리의 모양부터 본다. 서브쿼리 안의 ROWNUM 같은 것도 Unnesting을 막는다 (08화 §4 변환 도감, +OUTLINE의 UNNEST 유무로 확인).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 수치는 교재 08화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 수치는 교재 08화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황