본사 주문 조회 화면이 매장을 골라도 일주일 치를 읽고, 메뉴로 찾으면 3만 블록 가까이 읽는다

실전DDL버거60 / 700

상황

버거 본사 「주문 조회」 화면은 기간(필수, 최대 7일)에 매장, 메뉴, 시간대를 옵션으로 더해 주문을 찾는다. SQL은 한 문장이고 옵션마다 (:v IS NULL OR ...)를 달았다. 메뉴 조건은 주문상품에 그 메뉴가 있는지 EXISTS로 확인한다. 강도현이 한 달 치 호출 로그를 세어 보니 네 조합이 거의 전부였다. C1 기간 + 매장(하루 약 1,200번), C2 기간 + 매장 + 시간대(약 500번), C3 기간 + 메뉴(약 200번), C4 기간 + 시간대(약 100번)다. 박팀장은 "조합마다 SQL을 어떻게 나누고 인덱스를 몇 개 둘지, POS 주문 적재가 얼마나 무거워지는지까지 적어 와라"고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO(PK) · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO(매장 200곳) · 주문 일자 순으로 쌓였다 · 하루 평균 1,368건, 7일 약 9,576건, 매장 하나의 7일 약 48건
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_NO · ITEM_ID(메뉴 92종) · ORD_ITEM_QTY · ORD_DT · ORD_HMS(주문의 값을 그대로 복사해 둔 컬럼) · 주문 하나에 평균 2.5줄 · 메뉴 하나의 7일 약 260줄
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 모두 BLEVEL 2 · 통계 최신
POS는 하루 약 1,400건의 주문과 3,400줄의 주문상품을 넣는다

원본 SQL

sql

SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO
  FROM ORD o
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
   AND (:v_shop IS NULL OR o.SHOP_NO = :v_shop)
   AND (:v_hms_from IS NULL OR o.ORD_HMS BETWEEN :v_hms_from AND :v_hms_to)
   AND (:v_item IS NULL OR EXISTS (SELECT 1
                                     FROM ORD_ITEM i
                                    WHERE i.ORD_NO  = o.ORD_NO
                                      AND i.ITEM_ID = :v_item))
 ORDER BY o.ORD_DT, o.ORD_HMS

실행계획 학습용 예시

xplan

[C1 · 매장 SH0042 · 2013-03-11 ~ 2013-03-17]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows | A-Time      | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |     47 | 00:00:00.03 |     251 |
|*  1 |  FILTER                       |             |      1 |        |     47 | 00:00:00.03 |     251 |
|*  2 |   TABLE ACCESS BY INDEX ROWID | ORD         |      1 |     48 |     47 | 00:00:00.03 |     251 |
|*  3 |    INDEX RANGE SCAN           | ORD_X01     |      1 |   9576 |   9576 | 00:00:00.01 |      36 |
|*  4 |   INDEX UNIQUE SCAN           | ORD_ITEM_PK |      0 |      1 |      0 | 00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:V_ITEM IS NULL OR  IS NOT NULL))
   2 - filter((:V_SHOP IS NULL OR "O"."SHOP_NO"=:V_SHOP))
   3 - access("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<=:V_TO)
       filter((:V_HMS_FROM IS NULL OR ("O"."ORD_HMS">=:V_HMS_FROM AND "O"."ORD_HMS"<=:V_HMS_TO)))
   4 - access("I"."ORD_NO"=:B1 AND "I"."ITEM_ID"=:V_ITEM)

[C3 · 메뉴 23 · 같은 7일 · 같은 커서]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows | A-Time      | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |    261 | 00:00:00.21 |   28979 |
|*  1 |  FILTER                       |             |      1 |        |    261 | 00:00:00.21 |   28979 |
|*  2 |   TABLE ACCESS BY INDEX ROWID | ORD         |      1 |     48 |   9576 | 00:00:00.03 |     251 |
|*  3 |    INDEX RANGE SCAN           | ORD_X01     |      1 |   9576 |   9576 | 00:00:00.01 |      36 |
|*  4 |   INDEX UNIQUE SCAN           | ORD_ITEM_PK |   9576 |      1 |    261 | 00:00:00.17 |   28728 |
--------------------------------------------------------------------------------------------------------

문제

지금 SQL로 C1과 C3를 실행할 때 각각 무엇을 읽는지 설명하고, C3가 3만 블록 가까이 읽는 이유를 Starts로 설명하시오.
조합에 맞게 SQL을 나누고, 꼭 필요한 인덱스만 DDL로 작성하시오. 네 조합이 각각 어느 인덱스를 쓰는지도 밝히시오.
새 인덱스가 POS 주문 적재에 주는 부담을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 인덱스 둘 : 매장으로 시작하는 ORD 인덱스, 메뉴로 시작하는 ORD_ITEM 인덱스 (ORD_NO까지 담아 주문상품 테이블에는 가지 않는다)
CREATE INDEX ORD_X02 ON ORD (SHOP_NO, ORD_DT, ORD_HMS) INVISIBLE;
CREATE INDEX ORD_ITEM_X02 ON ORD_ITEM (ITEM_ID, ORD_DT, ORD_NO) INVISIBLE;
-- 세션에서 OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE로 재고, 적재 시간을 잰 뒤 VISIBLE

-- S_A : 매장이 있을 때 (C1 · C2 · 매장 + 메뉴) → ORD_X02
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO
  FROM ORD o
 WHERE o.SHOP_NO = :v_shop
   AND o.ORD_DT BETWEEN :v_from AND :v_to
   AND (:v_hms_from IS NULL OR o.ORD_HMS BETWEEN :v_hms_from AND :v_hms_to)
   AND (:v_item IS NULL OR EXISTS (SELECT 1 FROM ORD_ITEM i
                                    WHERE i.ORD_NO = o.ORD_NO AND i.ITEM_ID = :v_item))
 ORDER BY o.ORD_DT, o.ORD_HMS;

-- S_B : 매장 없이 메뉴가 있을 때 (C3) → ORD_ITEM_X02에서 출발해 ORD_PK로
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO
  FROM ORD_ITEM i, ORD o
 WHERE i.ITEM_ID = :v_item
   AND i.ORD_DT BETWEEN :v_from AND :v_to
   AND o.ORD_NO  = i.ORD_NO
   AND (:v_hms_from IS NULL OR o.ORD_HMS BETWEEN :v_hms_from AND :v_hms_to)
 ORDER BY o.ORD_DT, o.ORD_HMS;

-- S_C : 매장도 메뉴도 없을 때 (C4) → ORD_X01 그대로
SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS, o.SHOP_NO
  FROM ORD o
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
   AND (:v_hms_from IS NULL OR o.ORD_HMS BETWEEN :v_hms_from AND :v_hms_to)
 ORDER BY o.ORD_DT, o.ORD_HMS;
-- 화면은 :v_shop → :v_item 순서로 보고 셋 가운데 하나를 부른다

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

xplan

[S_A · C1 · 매장 SH0042 · 같은 7일]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows | A-Time      | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |     47 | 00:00:00.01 |      50 |
|*  1 |  FILTER                       |             |      1 |        |     47 | 00:00:00.01 |      50 |
|   2 |   TABLE ACCESS BY INDEX ROWID | ORD         |      1 |     48 |     47 | 00:00:00.01 |      50 |
|*  3 |    INDEX RANGE SCAN           | ORD_X02     |      1 |     48 |     47 | 00:00:00.01 |       3 |
|*  4 |   INDEX UNIQUE SCAN           | ORD_ITEM_PK |      0 |      1 |      0 | 00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------
   1 - filter((:V_ITEM IS NULL OR  IS NOT NULL))
   3 - access("O"."SHOP_NO"=:V_SHOP AND "O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<=:V_TO)
       filter((:V_HMS_FROM IS NULL OR ("O"."ORD_HMS">=:V_HMS_FROM AND "O"."ORD_HMS"<=:V_HMS_TO)))
   4 - access("I"."ORD_NO"=:B1 AND "I"."ITEM_ID"=:V_ITEM)

[S_B · C3 · 메뉴 23 · 같은 7일]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows | A-Time      | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |    261 | 00:00:00.02 |    1053 |
|   1 |  SORT ORDER BY                 |              |      1 |    257 |    261 | 00:00:00.02 |    1053 |
|   2 |   NESTED LOOPS                 |              |      1 |    257 |    261 | 00:00:00.02 |    1053 |
|   3 |    NESTED LOOPS                |              |      1 |    257 |    261 | 00:00:00.01 |     788 |
|*  4 |     INDEX RANGE SCAN           | ORD_ITEM_X02 |      1 |    257 |    261 | 00:00:00.01 |       5 |
|*  5 |     INDEX UNIQUE SCAN          | ORD_PK       |    261 |      1 |    261 | 00:00:00.01 |     783 |
|*  6 |    TABLE ACCESS BY INDEX ROWID | ORD          |    261 |      1 |    261 | 00:00:00.01 |     265 |
----------------------------------------------------------------------------------------------------------
   4 - access("I"."ITEM_ID"=:V_ITEM AND "I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)
   5 - access("O"."ORD_NO"="I"."ORD_NO")
   6 - filter((:V_HMS_FROM IS NULL OR ("O"."ORD_HMS">=:V_HMS_FROM AND "O"."ORD_HMS"<=:V_HMS_TO)))

[나머지] C2(S_A + 시간대) ORD_X02에서 시각을 인덱스 안에서 걸러 10건 · Buffers 251 → 13
         C4(S_C) ORD_X01 7일 범위 36블록에서 시각을 걸러 약 400건 · Buffers 약 66(지금과 같다)

모범 답안 서술

지금 SQL에서 옵션 조건 셋은 모두 (:v IS NULL OR ...) 모양이라 access가 되지 못하고 filter로만 쓰인다. access를 맡는 것은 필수인 기간뿐이라, 어느 조합이든 ORD_X01로 일주일 치 9,576항목(36블록)을 읽고 테이블 9,576행(215블록 안팎)을 찾아간다. C1은 그 가운데 2번 줄 filter로 매장 하나의 47건만 남긴다(Buffers 251). C3는 더 나쁘다. 1번 FILTER가 주문 9,576건마다 EXISTS 서브쿼리를 돌려 4번 줄이 Starts 9,576번 ORD_ITEM_PK를 찾고(28,728블록), 메뉴가 든 주문 261건만 남긴다(Buffers 28,979). 조합에 따라 SQL을 셋으로 나눈다. 매장이 있으면 S_A, 매장 없이 메뉴가 있으면 S_B, 둘 다 없으면 S_C를 부른다. 인덱스는 둘만 더한다. 첫째 ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS)는 S_A가 매장 =, 기간 범위로 그 매장의 일주일 치만 찾게 한다. C1은 Buffers 251 → 50이다. C2는 같은 인덱스에서 시각을 인덱스 안에서 걸러 10건만 테이블로 가므로 251 → 13이다. 둘째 ORD_ITEM_X02 (ITEM_ID, ORD_DT, ORD_NO)는 S_B가 메뉴 =, 기간 범위로 주문상품 261줄을 찾게 한다. 인덱스에 ORD_NO가 있어 주문상품 테이블에는 가지 않고 ORD_PK로 주문을 찾아간다. C3는 Buffers 28,979 → 1,053이다. C4(기간 + 시간대)는 지금처럼 ORD_X01로 일주일 범위를 읽으며 시각을 인덱스에서 거르면 Buffers 약 66이라 S_C로 그대로 둔다. 인덱스를 조합 수만큼 만들지 않는다. C2는 C1의 인덱스 뒤에 ORD_HMS를 붙여 함께 받고, 매장과 메뉴를 함께 넣는 드문 조합은 S_A의 EXISTS로 그 매장의 48건만 확인하면 된다. 대가는 POS 적재다. 주문 한 건이 들어올 때 ORD에 한 행, ORD_ITEM에 평균 2.5줄이 들어가므로, 새 인덱스 둘 때문에 인덱스 항목이 주문당 3.5개 더 생긴다. 하루 약 1,400건이면 약 4,900항목이고 그만큼 인덱스 블록 변경과 redo가 는다. 주문이 매장 순서나 메뉴 순서로 들어오지 않으므로 두 인덱스는 여러 리프에 흩어져 고쳐진다. 지금 주문량에서는 감당할 만하지만, INVISIBLE로 만들어 적재 시간을 잰 뒤에 연다.

해설

함정: 옵션 조건마다 단일 컬럼 인덱스(SHOP_NO, ITEM_ID, ORD_HMS)를 만들어도 SQL이 한 문장으로 남아 있는 한 아무것도 쓰이지 않는다. 옵션 조건은 SQL을 나눈 뒤에야 access가 될 수 있다.
메뉴 갈래를 조인으로 써도 주문이 두 번 나오지 않는 것은 ORD_ITEM_PK가 (ORD_NO, ITEM_ID)라서 한 주문에 같은 메뉴 줄이 하나뿐이기 때문이다. 그런 보장이 없다면 IN (SELECT ...)(세미 조인)으로 써야 한다.
C4를 더 줄이고 싶다면 기간을 날짜 IN 목록(7개)으로 바꿔 날짜마다 (ORD_DT =, ORD_HMS 범위)로 찾게 할 수 있다(66 → 약 30). 하루 100번 남짓 호출되는 조합이라 이번에는 하지 않는다.
SQL 셋을 화면이 고르는 대신 UNION ALL에 바인드 조건을 붙여 한 문장으로 써도 계획은 같다. 조합이 더 늘면 들어온 조건만 붙이는 동적 SQL로 넘어간다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황