점주 앱 세 화면에 같은 힌트를 넣었는데 하나만 빨라졌고 하나는 상품에 따라 몇 초씩 걸린다

실전SELECT버거633 / 700

상황

버거 개발팀은 무한 스크롤 목록 화면을 FIRST_ROWS(20) 힌트로 고친 사례(OPT-035)를 보고 "목록 화면에는 FIRST_ROWS(20)을 넣는다"는 규칙을 만들어 점주 앱의 세 화면에 같은 힌트를 넣었다. 결과는 제각각이었다. A 「주문 목록」은 빨라졌다. B 「상품별 최근 판매」는 잘 팔리는 상품을 고르면 빠른데, 단종 상품을 고르면 3초씩 걸린다. C 「이번 달 판매 상위 10개 상품」(본사 화면)은 힌트를 넣기 전과 똑같이 0.6초다. 박팀장은 규칙을 다시 쓰라고 했다. "어떤 화면에 그 힌트가 통하는지 기준을 주세요."

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS) · 한 달 약 41,700건
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) 리프 9,150블록 · 날짜순 적재(클러스터링 팩터 약 55,000) · 대부분 버퍼 캐시에 없다
상품 92종 · 상품 하나에 평균 26,877줄(전체의 1.1%) · 단종 상품 하나는 2012년 6월까지만 팔렸다(4,100줄)
POS는 하루 주문 약 1,370건, 주문상품 약 3,390줄을 넣는다. ORD_ITEM.ORD_DT는 주문의 ORD_DT를 그대로 복사한 컬럼이다.

원본 SQL

sql

-- A 「주문 목록」 (매장, 최신순 · 무한 스크롤)
SELECT /*+ FIRST_ROWS(20) */ ORD_NO, ORD_DT, ORD_HMS
  FROM ORD
 WHERE SHOP_NO = :shop
 ORDER BY ORD_DT DESC, ORD_HMS DESC;

-- B 「상품별 최근 판매」 (상품을 골라 최신순 · 무한 스크롤)
SELECT /*+ FIRST_ROWS(20) */ ORD_NO, ORD_DT, ORD_HMS, ORD_ITEM_QTY
  FROM ORD_ITEM
 WHERE ITEM_ID = :item
 ORDER BY ORD_DT DESC, ORD_HMS DESC;

-- C 「이번 달 판매 상위 10개 상품」 (전 매장)
SELECT /*+ FIRST_ROWS(20) */ i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN :d1 AND :d2
   AND i.ORD_NO = o.ORD_NO
 GROUP BY i.ITEM_ID
 ORDER BY qty DESC
 FETCH FIRST 10 ROWS ONLY;

실행계획 학습용 예시

xplan

[요약 — 첫 화면 기준]
 A   INDEX RANGE SCAN DESCENDING ORD_X02 + 테이블 20행 · 23블록 · 0.01초
 B   잘 팔리는 상품 : INDEX FULL SCAN DESCENDING ORD_ITEM_X01 · 인덱스 1,840줄을 읽고 20행 · 52블록
     단종 상품     : 같은 계획 · 아래
 C   힌트 전과 같은 계획 : HASH GROUP BY ← HASH JOIN (ORD 한 달 · ORD_ITEM FULL) · 56,336블록 · 0.6초

[B — 단종 상품 · 첫 화면 20행]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |     20 |00:00:03.12 |   48330 | 41870 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ORD_ITEM     |      1 |     20 |     20 |00:00:03.12 |   48330 | 41870 |
|   2 |   INDEX FULL SCAN DESCENDING| ORD_ITEM_X01 |      1 |   1840 |   1854K|00:00:00.58 |    6870 |  6020 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ITEM_ID"=:ITEM)

문제

세 화면에서 같은 힌트가 다르게 작동한 이유를 각각 설명하시오. B가 상품에 따라 달라지는 이유는 옵티마이저가 어떤 가정을 했기 때문인지 쓰시오.
세 화면이 어떤 값에서도 빠르도록 인덱스와 SQL을 고치시오. 인덱스를 더하는 대가도 쓰시오.
"목록 화면에는 FIRST_ROWS(20)"을 대신할 규칙을 한두 문장으로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- B : 조건(ITEM_ID)과 정렬(ORD_DT, ORD_HMS)을 한 인덱스에 담는다 → 어느 상품이든 그 상품의 끝에서 20행
CREATE INDEX ORD_ITEM_X02 ON ORD_ITEM (ITEM_ID, ORD_DT, ORD_HMS);

SELECT /*+ FIRST_ROWS(20) */ ORD_NO, ORD_DT, ORD_HMS, ORD_ITEM_QTY
  FROM ORD_ITEM
 WHERE ITEM_ID = :item
 ORDER BY ORD_DT DESC, ORD_HMS DESC;

-- C : 앞의 20행이 아니라 '읽는 양'의 문제 — ORD 를 거치지 않고 ORD_ITEM 의 일자로 한 달 치만 읽는다
--     (먼저 확인 : SELECT COUNT(*) FROM ORD o, ORD_ITEM i WHERE i.ORD_NO = o.ORD_NO AND i.ORD_DT <> o.ORD_DT → 0)
SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :d1 AND :d2
 GROUP BY ITEM_ID
 ORDER BY qty DESC
 FETCH FIRST 10 ROWS ONLY;

-- A : 그대로 (ORD_X02 가 조건과 정렬을 함께 담고 있다)

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

xplan

[B — 새 인덱스 · 단종 상품이든 잘 팔리는 상품이든 첫 화면 20행]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |     20 |00:00:00.01 |      24 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ORD_ITEM     |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  2 |   INDEX RANGE SCAN DESCENDING| ORD_ITEM_X02 |      1 |     20 |     20 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
   2 - access("ITEM_ID"=:ITEM)

[C — 고친 SQL · 11월]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |     10 |00:00:00.09 |    2690 |
|*  1 |  VIEW                                   |              |      1 |     10 |     10 |00:00:00.09 |    2690 |
|*  2 |   WINDOW SORT PUSHED RANK               |              |      1 |     92 |     11 |00:00:00.09 |    2690 |
|   3 |    HASH GROUP BY                        |              |      1 |     92 |     92 |00:00:00.09 |    2690 |
|*  4 |     FILTER                              |              |      1 |        |    103K|00:00:00.06 |    2690 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |    103K|    103K|00:00:00.05 |    2690 |
|*  6 |       INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |    103K|    103K|00:00:00.01 |     384 |
--------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY SUM("ORD_ITEM_QTY") DESC )<=10)
   4 - filter(:D1<=:D2)
   6 - access("ORD_DT">=:D1 AND "ORD_DT"<=:D2)

모범 답안 서술

A: ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS)가 조건(SHOP_NO)과 정렬 순서를 함께 담고 있다. 인덱스를 거꾸로 읽으면 그 매장의 최신 주문이 차례로 나오므로 20행을 읽고 멈춘다(23블록). FIRST_ROWS(20)이 통하는 전형이다. B: 조건 컬럼 ITEM_ID가 ORD_ITEM_X01 (ORD_DT, ORD_HMS)에 없다. FIRST_ROWS(20)은 "정렬 순서대로 인덱스를 거꾸로 읽다가 테이블에서 상품을 걸러 20행이 차면 멈춘다"는 계획을 골랐다. 이때 옵티마이저는 그 상품의 줄이 날짜에 고르게 퍼져 있다고 가정한다. 상품 하나가 1.1%이므로 20 ÷ 1.1% ≒ 1,840줄만 읽으면 된다고 셈했다(E-Rows 1,840). 잘 팔리는 상품이면 그 가정이 맞아 52블록이다. 단종 상품은 2012년 6월까지만 팔렸으므로 최신 쪽에는 한 줄도 없다. 그래서 인덱스를 1,854K줄 거의 끝까지 읽고 줄마다 테이블을 확인해 48,330블록(대부분 디스크, 3.12초)을 읽었다. 값에 따라 일량이 1,000배 가까이 달라지는 계획이다. C: GROUP BY로 상품별 합계를 다 낸 뒤 합계로 정렬해야 첫 행이 나온다. 이런 블로킹 연산이 있는 문장에는 앞의 20행을 빨리 내는 계획이 따로 없으므로 FIRST_ROWS가 고를 것이 없고, 계획은 힌트 전과 같다(56,336블록). 문제는 첫 행이 아니라 읽는 양이다. 고친 것: B에는 조건과 정렬을 함께 담은 ORD_ITEM_X02 (ITEM_ID, ORD_DT, ORD_HMS)를 만든다. 어느 상품이든 그 상품의 가장 최근 줄부터 20행만 읽어 24블록이다(단종 상품 48,330 → 24). C는 ORD를 거치지 않고 ORD_ITEM.ORD_DT로 한 달 치만 ORD_ITEM_X01로 읽는다(56,336 → 2,690). ORD_ITEM.ORD_DT는 주문 일자를 복사한 컬럼이지만, 바꾸기 전에 두 테이블의 일자가 다른 줄이 0건인지 확인해야 한다. A는 그대로 둔다. 대가: 새 인덱스는 약 1만 블록(약 85MB)이고, POS가 주문상품을 넣을 때마다 한 번 더 고쳐야 한다. 하루 3,390줄이라 입력 비용은 작지만, 인덱스 목록과 이 화면을 함께 적어 두어야 나중에 "안 쓰는 인덱스"로 지워지지 않는다. 새 규칙: FIRST_ROWS(n)은 조건과 ORDER BY를 한 인덱스가 순서대로 담고 있고, 그 사이에 GROUP BY나 다른 정렬이 없을 때만 넣는다. 그렇지 않으면 힌트보다 인덱스나 SQL을 먼저 고친다.

해설

가장 위험한 쪽은 B다. 평균으로 재면 빠르고, 드문 값에서만 터진다. 리허설에서 인기 상품 하나로만 재면 통과한다. 값 분포의 양 끝(가장 많은 값, 가장 적거나 오래된 값)으로 재야 한다(17화 '평균은 거짓말을 한다'와 같은 이야기).
B를 힌트만 바꿔 막는 안: 힌트를 빼면 ALL_ROWS가 상품의 26,877줄을 다 읽고 정렬한다(약 2만 7천 블록). 단종 상품은 빨라지고 인기 상품은 느려진다. 인덱스가 없으면 어느 쪽이든 한쪽이 손해다.
C에 FETCH FIRST 10이 있으니 옵티마이저는 이미 앞의 행에 맞춰 셈한다. 힌트를 하나 더 붙여도 바뀌는 것이 없다.
인덱스를 더하는 결정은 DML 비용과 함께 적는다(04화 §9, 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황