품질팀의 클레임 역추적 조회가 그날 주문 37건을 찾는 데 6초 걸린다

중급SELECT버거260 / 700

상황

본사 품질팀은 매장에서 상품 클레임이 들어오면 「클레임 역추적」 화면에 날짜와 상품을 넣어, 그날 그 상품이 팔린 주문(매장, 시각)을 뽑는다. 클레임이 몰리는 날은 수십 번 돌리는데 한 번에 6초가 걸린다. 서브쿼리의 NO_UNNEST 옆에는 -- 서브쿼리 먼저 풀지 말 것 (2014.02 오과장)이라는 주석이 있다. 2014년에 이 SQL은 바깥에도 o.ORD_DT = :dt가 있었고, ORD_ITEM에는 PK 말고 인덱스가 없었다. 2019년 리팩터링에서 "날짜는 서브쿼리에 있으니 중복"이라며 바깥의 날짜 조건을 지웠다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) (높이 3) · ORD_X01 (ORD_DT, ORD_HMS) · 하루 주문 약 1,370건
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) (높이 3) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) (2016년 추가, 하루치가 약 76블록에 모여 있다) · ORD_DT는 주문 헤더의 날짜를 그대로 복사한 값이다
상품 하나는 전 기간에 약 2만 7천 주문에 들어 있다 · 2013-05-14 주문상품 3,387줄 · 그중 상품 4(와일드쉬림프버거)가 든 주문 37건
통계 최신

원본 SQL

sql

SELECT o.ORD_NO, o.ORD_HMS, o.SHOP_NO
  FROM ORD o
 WHERE o.ORD_NO IN (SELECT /*+ NO_UNNEST */ i.ORD_NO     -- 서브쿼리 먼저 풀지 말 것 (2014.02 오과장)
                      FROM ORD_ITEM i
                     WHERE i.ORD_DT  = :dt
                       AND i.ITEM_ID = :item)
 ORDER BY o.ORD_HMS

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |     37 |00:00:06.12 | 3049299 |
|   1 |  SORT ORDER BY                |             |      1 |      1 |     37 |00:00:06.12 | 3049299 |
|*  2 |   FILTER                      |             |      1 |        |     37 |00:00:06.12 | 3049299 |
|   3 |    TABLE ACCESS FULL          | ORD         |      1 |   1000K|   1000K|00:00:00.31 |   22422 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| ORD_ITEM    |   1000K|      1 |     37 |00:00:05.21 | 3026877 |
|*  5 |     INDEX UNIQUE SCAN         | ORD_ITEM_PK |   1000K|      1 |  26877 |00:00:04.02 | 3000000 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "ORD_ITEM" "I" WHERE "I"."ORD_NO"=:B1
              AND "I"."ITEM_ID"=:ITEM AND "I"."ORD_DT"=:DT))
   4 - filter("I"."ORD_DT"=:DT)
   5 - access("I"."ORD_NO"=:B1 AND "I"."ITEM_ID"=:ITEM)

문제

4번과 5번 줄의 Starts가 1000K인 이유를 설명하시오. 2014년에는 이 힌트가 왜 문제가 되지 않았는지도 설명하시오.
SQL을 고치시오. 고친 계획에서 서브쿼리를 먼저 읽을 때 HASH UNIQUE가 왜 붙는지, 이 SQL을 조인으로 직접 쓴다면 무엇을 조심해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 힌트를 뗀다: 서브쿼리를 먼저 풀어 그날 그 상품이 든 주문 37건부터 읽는다
SELECT o.ORD_NO, o.ORD_HMS, o.SHOP_NO
  FROM ORD o
 WHERE o.ORD_NO IN (SELECT i.ORD_NO
                      FROM ORD_ITEM i
                     WHERE i.ORD_DT  = :dt
                       AND i.ITEM_ID = :item)
 ORDER BY o.ORD_HMS;
-- 손으로 쓴 같은 모양: FROM (SELECT DISTINCT ORD_NO FROM ORD_ITEM WHERE ORD_DT = :dt AND ITEM_ID = :item) i, ORD o
--                       WHERE o.ORD_NO = i.ORD_NO

개선 후 실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |              |      1 |        |     37 |00:00:00.01 |     238 |
|   1 |  SORT ORDER BY                            |              |      1 |     37 |     37 |00:00:00.01 |     238 |
|   2 |   NESTED LOOPS                            |              |      1 |     37 |     37 |00:00:00.01 |     238 |
|   3 |    NESTED LOOPS                           |              |      1 |     37 |     37 |00:00:00.01 |     201 |
|   4 |     VIEW                                  | VW_NSO_1     |      1 |     37 |     37 |00:00:00.01 |      90 |
|   5 |      HASH UNIQUE                          |              |      1 |     37 |     37 |00:00:00.01 |      90 |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |     37 |     37 |00:00:00.01 |      90 |
|*  7 |        INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |   3387 |   3387 |00:00:00.01 |      14 |
|*  8 |     INDEX UNIQUE SCAN                     | ORD_PK       |     37 |      1 |     37 |00:00:00.01 |     111 |
|   9 |    TABLE ACCESS BY INDEX ROWID            | ORD          |     37 |      1 |     37 |00:00:00.01 |      37 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("I"."ITEM_ID"=:ITEM)
   7 - access("I"."ORD_DT"=:DT)
   8 - access("O"."ORD_NO"="ORD_NO")

모범 답안 서술

2번 줄 FILTER가 3번 줄 ORD 전체 스캔(1000K행) 위에 있고, 서브쿼리(4번, 5번 줄)의 Starts가 1000K다. NO_UNNEST 때문에 IN 서브쿼리가 펼쳐지지 않아서, 주문 100만 건마다 ORD_ITEM_PK로 '이 주문에 그날 그 상품이 있는가'를 따로 물었다. 주문번호는 주문마다 다르므로 FILTER의 캐시도 소용이 없다. 인덱스만 300만 블록을 읽었고, 전체 3,049,299블록의 99%가 여기서 나온다. 2014년에는 바깥에 o.ORD_DT = :dt가 있어서 FILTER가 그날 주문 1,370건 정도에만 돌았다. ORD_ITEM에 날짜 인덱스가 없던 그때는 서브쿼리를 먼저 풀면 주문상품 247만 줄을 다 읽어야 했으므로 풀지 말라는 주석이 옳았다. 2019년 리팩터링이 바깥의 날짜 조건을 지우면서 힌트의 근거가 사라졌다. NO_UNNEST를 떼면 옵티마이저가 서브쿼리를 펼쳐 먼저 읽는다. ORD_ITEM_X01로 그날 주문상품 3,387줄(90블록)을 읽어 상품 4인 37줄을 고르고, 주문번호의 중복을 없앤 뒤(5번 줄 HASH UNIQUE, 4번 줄 VW_NSO_1) 주문 37건을 ORD_PK로 찾아간다. Buffers는 3,049,299 → 238로 준다. HASH UNIQUE가 붙는 것은 IN의 뜻을 지키기 위해서다. IN은 '주문번호가 목록에 있는가'만 묻는다. 서브쿼리를 먼저 읽어 바깥을 찾아가는 조인으로 바꾸면, 서브쿼리 결과에 같은 주문번호가 두 번 있을 때 주문이 두 번 나온다. 그래서 옵티마이저는 조인 전에 서브쿼리 쪽 중복을 없앤다. 이 SQL은 (ORD_NO, ITEM_ID)가 PK이고 상품이 하나로 정해져 있어 실제로는 중복이 없지만, 상품을 둘 이상 넣으면(ITEM_ID IN (4, 5)) 같은 주문번호가 두 번 나올 수 있다. 손으로 조인을 쓸 때도 같다. 서브쿼리 쪽을 먼저 DISTINCT로 주문번호당 한 줄씩 만든 뒤 붙여야 한다. 그냥 조인하면 두 상품을 다 산 주문이 두 번 나오고, 조인한 결과에 나중에 DISTINCT를 걸면 같은 값의 다른 행까지 합쳐질 수 있다.

해설

펼친 두 모양: 서브쿼리를 펼치면 옵티마이저는 바깥을 먼저 읽는 세미 조인과, 서브쿼리를 먼저 읽고 중복을 없앤 뒤 바깥을 찾아가는 조인 가운데 싼 쪽을 고른다. 이 SQL은 바깥에 조건이 없어 세미 조인이면 ORD를 통째로 읽어야 하므로(22,422블록) 서브쿼리를 먼저 읽는 쪽이 맞다.
바깥 날짜를 되살리는 답: o.ORD_DT = :dt를 다시 넣으면 힌트를 그대로 두어도 FILTER가 그날 주문 1,370건에만 돈다(약 4,150블록). 힌트를 뗀 238보다 비싸지만 3백만 블록에서는 크게 준다. 둘 다 하면 옵티마이저가 더 싼 쪽을 고른다.
리팩터링의 함정: "중복 조건"을 지우는 정리는 결과를 바꾸지 않아 테스트를 통과한다. 그런데 그 조건에 기대던 힌트가 있으면 계획이 무너진다. 힌트 옆에는 근거(바깥에 날짜 조건이 있다)까지 적어 두어야 한다(교재 06화).
계획의 VW_NSO_1은 옵티마이저가 IN 서브쿼리를 펼쳐 만든 뷰다. +OUTLINE의 UNNEST로 확인할 수 있다(교재 08화 §5).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황