시간대 분석 화면 세 개 가운데 둘이 주문상품을 21만 번씩 찾아간다

실전SELECT버거615 / 700

상황

버거 본사 마케팅팀이 시간대 분석 화면 세 개를 사내에서 새로 만들었다. 점심 피크 메뉴(R1), 12시대 매장별 주문 수(R2), 저녁 피크 메뉴(R3)다. 개발자는 "시각 문자열의 앞 두 자리만 잘라 비교하면 간단하다"고 했고, R3은 다른 개발자가 숫자로 바꿔 비교하게 썼다. R1과 R3은 3초 가까이 걸리고, R2는 0.2초다. 다음 주 메뉴 개편 회의 전까지 하루 수십 번씩 돌린다. 선배는 셋을 같이 보고 한 번에 고칠 길을 고르라고 했다. 세 SQL은 고칠 수 있다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_HMS VARCHAR2(6) 'HH24MISS'(늘 6자리, 영업시간 09:00:00 ~ 22:59:59) · 시간대마다 고르게 쌓였다(한 시간에 약 71,429건)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID): BLEVEL 2 · 주문당 평균 2.47줄 · 주문번호 순으로 쌓였다 · 주문의 ORD_DT와 ORD_HMS를 그대로 복사해 둔 열이 있다
ITEM 상품: 92행 · 5블록
통계 최신 · 히스토그램 없음 · 확장 통계 없음
11 ~ 13시 주문 214,286건(21.4%) · 12시대 71,429건 · 18 ~ 20시 214,286건

원본 SQL

sql

-- R1 점심 피크 메뉴 (11시 ~ 13시대)
SELECT i.ITEM_NM, SUM(oi.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM oi, ITEM i
 WHERE SUBSTR(o.ORD_HMS, 1, 2) BETWEEN '11' AND '13'
   AND oi.ORD_NO = o.ORD_NO
   AND i.ITEM_ID = oi.ITEM_ID
 GROUP BY i.ITEM_NM;

-- R2 12시대 매장별 주문 수
SELECT o.SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD o
 WHERE SUBSTR(o.ORD_HMS, 1, 2) = '12'
 GROUP BY o.SHOP_NO;

-- R3 저녁 피크 메뉴 (18시 ~ 20시대)
SELECT i.ITEM_NM, SUM(oi.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM oi, ITEM i
 WHERE TO_NUMBER(o.ORD_HMS) BETWEEN 180000 AND 205959
   AND oi.ORD_NO = o.ORD_NO
   AND i.ITEM_ID = oi.ITEM_ID
 GROUP BY i.ITEM_NM;

실행계획 학습용 예시

xplan

[R1]
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     92 |00:00:02.91 |    667K |
|   1 |  HASH GROUP BY                 |             |      1 |     92 |     92 |00:00:02.91 |    667K |
|*  2 |   HASH JOIN                    |             |      1 |   6175 |    529K|00:00:02.85 |    667K |
|   3 |    TABLE ACCESS FULL           | ITEM        |      1 |     92 |     92 |00:00:00.01 |       5 |
|   4 |    NESTED LOOPS                |             |      1 |   6175 |    529K|00:00:02.71 |    667K |
|   5 |     NESTED LOOPS               |             |      1 |   6175 |    529K|00:00:01.32 |    452K |
|*  6 |      TABLE ACCESS FULL         | ORD         |      1 |   2500 |    214K|00:00:00.21 |   22422 |
|*  7 |      INDEX RANGE SCAN          | ORD_ITEM_PK |    214K|      2 |    529K|00:00:00.98 |    430K |
|   8 |     TABLE ACCESS BY INDEX ROWID| ORD_ITEM    |    529K|      1 |    529K|00:00:01.21 |    215K |
----------------------------------------------------------------------------------------------------
   2 - access("I"."ITEM_ID"="OI"."ITEM_ID")
   6 - filter((SUBSTR("O"."ORD_HMS",1,2)>='11' AND SUBSTR("O"."ORD_HMS",1,2)<='13'))
   7 - access("OI"."ORD_NO"="O"."ORD_NO")

[R2]  TABLE ACCESS FULL ORD · filter(SUBSTR("O"."ORD_HMS",1,2)='12') · E-Rows 10,000 / A-Rows 71,429
      → HASH GROUP BY · Buffers 22,422 · 0.2초
[R3]  R1과 같은 모양(ORD FULL → NL ORD_ITEM_PK) · filter(TO_NUMBER("O"."ORD_HMS")>=180000 AND
      TO_NUMBER("O"."ORD_HMS")<=205959) · E-Rows 2,500 / A-Rows 214,286 · Buffers 667K · 2.9초

문제

세 SQL의 E-Rows(2,500 · 10,000 · 2,500)가 어디서 나왔는지 쓰고, 셋 다 틀렸는데 R2만 느리지 않은 이유를 설명하시오.
고치는 길(원래 열의 범위 조건으로 다시 쓰기, 표현식 통계, 가상 열)을 비교하고, 하나를 골라 세 SQL을 고쳐 쓰시오.
고른 길의 대가와 반영 전에 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 표준 : 시각 문자열은 잘라서 비교하지 않고 원래 값의 범위로 비교한다 (상한은 '< 다음 시각')
-- R1 · R3 : 주문상품에도 같은 주문 시각(ORD_HMS)이 있다 → ORD 조인을 뺀다

-- R1 점심 피크 메뉴
SELECT i.ITEM_NM, SUM(oi.ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM oi, ITEM i
 WHERE oi.ORD_HMS >= '110000' AND oi.ORD_HMS < '140000'
   AND i.ITEM_ID = oi.ITEM_ID
 GROUP BY i.ITEM_NM;

-- R2 12시대 매장별 주문 수
SELECT o.SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD o
 WHERE o.ORD_HMS >= '120000' AND o.ORD_HMS < '130000'
 GROUP BY o.SHOP_NO;

-- R3 저녁 피크 메뉴
SELECT i.ITEM_NM, SUM(oi.ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM oi, ITEM i
 WHERE oi.ORD_HMS >= '180000' AND oi.ORD_HMS < '210000'
   AND i.ITEM_ID = oi.ITEM_ID
 GROUP BY i.ITEM_NM;

-- 반영 전 한 번 (야간) : 복사본 열이 원본과 같은지 센다 → 0 이어야 한다
SELECT COUNT(*)
  FROM ORD o, ORD_ITEM oi
 WHERE oi.ORD_NO = o.ORD_NO
   AND (oi.ORD_HMS <> o.ORD_HMS OR oi.ORD_DT <> o.ORD_DT);

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

xplan

[R1 고친 뒤 · R3도 같은 모양]
-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |     92 |00:00:00.41 |   55270 |          |
|   1 |  HASH GROUP BY      |          |      1 |     92 |     92 |00:00:00.41 |   55270 |          |
|*  2 |   HASH JOIN         |          |      1 |    545K|    529K|00:00:00.33 |   55270 | 1340K (0)|
|   3 |    TABLE ACCESS FULL| ITEM     |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|*  4 |    TABLE ACCESS FULL| ORD_ITEM |      1 |    545K|    529K|00:00:00.22 |   55265 |          |
-------------------------------------------------------------------------------------------------
   2 - access("I"."ITEM_ID"="OI"."ITEM_ID")
   4 - filter(("OI"."ORD_HMS">='110000' AND "OI"."ORD_HMS"<'140000'))

[R2 고친 뒤]  E-Rows 73,5xx / A-Rows 71,429 · TABLE ACCESS FULL ORD · Buffers 22,422 (계획은 그대로)

모범 답안 서술

추정의 출처: 셋 다 ORD_HMS에 함수를 씌워서 열의 통계를 쓰지 못했다. 그래서 오라클은 고정 선택도를 쓴다. 양쪽 범위(R1의 SUBSTR … BETWEEN, R3의 TO_NUMBER … BETWEEN)는 5% × 5% = 0.25%라 1,000,000 × 0.25% = 2,500이고, 등치(R2)는 1%라 10,000이다. 실제는 214,286 · 71,429 · 214,286이다. R2만 멀쩡한 이유: R2는 시간대 조건 하나로 주문 테이블만 읽는다. 어느 날짜에나 퍼져 있는 시간대라 어차피 FULL이 맞고, 추정이 틀려도 바뀔 선택지가 없다(22,422블록). R1과 R3은 다르다. 2,500건이라고 믿은 옵티마이저가 주문마다 ORD_ITEM_PK를 찾아가는 NL을 골랐고, 7번 줄의 Starts가 214K라 인덱스와 테이블에서만 645K블록을 읽었다(전체 667K, 2.9초). 같은 오추정이라도 그 숫자로 고를 것이 있는 SQL만 다친다. 고치는 길 비교: ① 원래 열의 범위로 다시 쓰면 ORD_HMS의 통계(최솟값 090000, 최댓값 225959)로 계산한다. 11 ~ 13시는 (140000 − 110000) / 135,959 = 22%로 실제 21.4%에 가깝다. 시간대가 고르게 퍼져 있어서 숫자 폭으로 계산해도 맞는다. 인덱스와 상관없이 세 SQL 모두 고쳐지고 새 객체도 없다. ② 표현식 통계(SUBSTR(ORD_HMS, 1, 2))는 SQL을 고치지 않아도 되지만 식이 글자까지 같은 SQL에만 쓰인다. R1과 R2는 받지만 R3의 TO_NUMBER는 따로 만들어야 하고, 다음에 누가 또 다른 모양으로 쓰면 다시 0.25%다. ③ 가상 열(ORD_HH)을 두고 모두가 그 열을 쓰게 하면 통계가 하나로 모이지만, 테이블에 열이 하나 보이게 늘어 값만 쓰는 INSERT와 SELECT *에 영향을 준다. 고른 것: SQL을 고칠 수 있으므로 ①이다. 여기에 R1과 R3은 주문상품의 ORD_HMS(주문 시각 복사본)로 바로 거르면 주문 테이블 조인이 필요 없다. R1은 E-Rows 545K로 해시 조인을 골라 Buffers 667K → 55,270, 2.9초 → 0.4초다(주문을 남겨 해시 조인으로 두 테이블을 읽어도 77,692다). R2는 계획이 그대로지만 추정이 73,5xx로 맞아, 나중에 조인이 붙어도 같은 일이 생기지 않는다. 팀 코딩 표준에 "시각 문자열은 잘라 비교하지 않고 범위로 비교한다"를 넣는다. 대가와 확인: 주문 조인을 빼는 것은 복사본 열이 원본과 같다는 전제에 기댄다. 반영 전에 야간에 한 번 두 열이 다른 행을 세어 0인지 확인하고, 적재 프로그램이 두 열을 함께 채우는지 본다. 하나라도 다르면 주문 조인을 남기고 범위 조건만 고친다.

해설

함정: R3을 TO_NUMBER(ORD_HMS) BETWEEN …에서 ORD_HMS BETWEEN 180000 AND 205959(숫자)로 바꾸면 이번에는 문자 열 쪽이 숫자로 바뀌는 묵시적 형변환이라 다시 가공이 된다. 비교 값은 문자 '180000'으로 준다(IDX-002와 같은 규칙).
시간대가 고르지 않은 시스템(유통 CF10처럼 12시대에 판매가 몰리는 곳)에서는 범위로 바꿔도 숫자 폭 계산이 틀린다. 그때는 ORD_HMS에 히스토그램을 더하거나 표현식 통계에 히스토그램을 준다.
같은 0.25%라도 날짜 하나를 함께 거는 SQL(하루치 점심)은 실제가 수백 건이라 NL이 맞을 수 있다. 고정 선택도가 해로운지는 그 숫자로 무엇을 고르는지에 달렸다.
상한을 < '140000'으로 주면 13:59:59까지 빠짐없이 들어간다. <= '135959'도 결과는 같지만 시각 형식이 바뀌면(밀리초 등) 빠지는 행이 생긴다(IDX-001의 '다음 날 0시 미만'과 같은 원리).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황