판매 모니터 「최근 판매 20건」이 열 때마다 15만 블록을 읽고, 맨 윗줄은 오전 9시대 판매다

실전SELECT유통323 / 700

상황

본사 판매 모니터는 최근 한 달 판매 가운데 가장 최근 20건을 보여 주고, 줄마다 매장명, 판매상품 수, 판매 금액을 붙인다. 영업지원팀 15명이 수시로 새로 고치는데 열 때마다 0.7초가 걸리고, 매장들에서는 "맨 윗줄이 늘 오전 9시대 판매"라는 민원이 들어온다. 개발자는 화면에 붙는 값을 모두 스칼라 서브쿼리로 붙였고, 정렬은 SLE_DT DESC, SLE_HMS DESC로 했다. SLE_HMS 데이터 교정(앞자리 0 채움)은 승인됐지만 다음 달 정기 점검 때 적용된다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO '20231231-0612'(판매일 + 그날 일련번호) · SLE_DT VARCHAR2(8) · SLE_HMS VARCHAR2(6) · 33,241건은 앞자리 0이 빠진 5자리(오전 8 ~ 9시대, '95503') · 하루 약 685건 · CF10_PK (SLE_NO): CF 9,340
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 판매 한 건에 상품 평균 2.2줄
CF07 매장: 6행 · 4블록 · PK CF07_PK (SHP_NO) · 판매가 있는 매장은 5곳
교재 09화에서 잰 전제: 판매번호 앞 8자리 = 판매일(500,000 / 500,000), 하루 안에서 판매번호 순서 = 시각 순서(역전 0), 판매번호는 유일(PK)
인덱스 IX_CF10_SLE_DT (SLE_DT)가 있다 · 통계 최신

원본 SQL

sql

SELECT *
  FROM (SELECT a.SLE_NO, a.SLE_DT, a.SLE_HMS,
               (SELECT c.SHP_NM FROM CF07 c WHERE c.SHP_NO = a.SHP_NO) AS SHP_NM,
               (SELECT COUNT(*) FROM CF11 b WHERE b.SLE_NO = a.SLE_NO) AS ITM_CNT,
               (SELECT SUM(b.SLE_QTY * b.SLE_UPRC) FROM CF11 b WHERE b.SLE_NO = a.SLE_NO) AS SLE_AMT
          FROM CF10 a
         WHERE a.SLE_DT >= :d_from
         ORDER BY a.SLE_DT DESC, a.SLE_HMS DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시 · 데이터 분포는 교재 03화, 09화 실측

xplan

[:d_from = '20231201' · 한 달 21,235건]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |     20 |00:00:00.71 |    149K |          |
|*  1 |  COUNT STOPKEY                         |                |      1 |        |     20 |00:00:00.71 |    149K |          |
|   2 |   VIEW                                 |                |      1 |  21235 |     20 |00:00:00.71 |    149K |          |
|   3 |    TABLE ACCESS BY INDEX ROWID         | CF07           |      5 |      1 |      5 |00:00:00.01 |      10 |          |
|*  4 |     INDEX UNIQUE SCAN                  | CF07_PK        |      5 |      1 |      5 |00:00:00.01 |       5 |          |
|   5 |    SORT AGGREGATE                      |                |  21235 |      1 |  21235 |00:00:00.18 |   63705 |          |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |  21235 |      2 |  46717 |00:00:00.12 |   63705 |          |
|   7 |    SORT AGGREGATE                      |                |  21235 |      1 |  21235 |00:00:00.27 |   84940 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF11           |  21235 |      2 |  46717 |00:00:00.21 |   84940 |          |
|*  9 |      INDEX RANGE SCAN                  | CF11_PK        |  21235 |      2 |  46717 |00:00:00.11 |   63705 |          |
|* 10 |    SORT ORDER BY STOPKEY               |                |      1 |  21235 |     20 |00:00:00.70 |    149K | 4096  (0)|
|  11 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  21235 |  21235 |00:00:00.02 |     258 |          |
|* 12 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  21235 |  21235 |00:00:00.01 |      63 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   4 - access("C"."SHP_NO"=:B1)
   6 - access("B"."SLE_NO"=:B1)
   9 - access("B"."SLE_NO"=:B1)
  10 - filter(ROWNUM<=20)
  12 - access("A"."SLE_DT">=:D_FROM)

[화면 맨 윗줄]  20231231-0037 · 20231231 · 95503 (오전 9:55)   그날 마지막 판매는 20231231-0612 · 225914

문제

20건을 보여 주는 화면이 15만 블록을 읽는 까닭을 실행계획의 Starts로 설명하고, 맨 윗줄이 오전 9시대 판매인 까닭도 설명하시오.
㉠ 스칼라 서브쿼리를 Top-N 바깥으로 옮기는 안, ㉡ 정렬 키를 판매번호(SLE_NO DESC)로 바꿔 PK를 거꾸로 읽는 안, ㉢ 데이터 교정을 기다린 뒤 (SLE_DT, SLE_HMS) 순서의 인덱스를 만드는 안을 일량, 정합성, 전제와 부작용으로 비교하시오.
지금 적용할 SQL을 쓰고, 그 SQL이 기대는 전제를 언제 다시 확인할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ㉡ + ㉠ : 판매번호(PK) 를 거꾸로 읽어 20 건에서 멈추고, 화면 값은 그 20 건에만 붙인다
SELECT x.SLE_NO, x.SLE_DT, x.SLE_HMS,
       (SELECT c.SHP_NM FROM CF07 c WHERE c.SHP_NO = x.SHP_NO) AS SHP_NM,
       (SELECT COUNT(*) FROM CF11 b WHERE b.SLE_NO = x.SLE_NO) AS ITM_CNT,
       (SELECT SUM(b.SLE_QTY * b.SLE_UPRC) FROM CF11 b WHERE b.SLE_NO = x.SLE_NO) AS SLE_AMT
  FROM (SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
          FROM (SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
                  FROM CF10
                 WHERE SLE_DT >= :d_from
                 ORDER BY SLE_NO DESC)
         WHERE ROWNUM <= 20) x
 ORDER BY x.SLE_NO DESC

개선 후 실행계획 학습용 예시 · 데이터 분포는 교재 03화, 09화 실측

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |     20 |00:00:00.01 |     157 |
|   1 |  TABLE ACCESS BY INDEX ROWID          | CF07    |      5 |      1 |      5 |00:00:00.01 |      10 |
|*  2 |   INDEX UNIQUE SCAN                   | CF07_PK |      5 |      1 |      5 |00:00:00.01 |       5 |
|   3 |  SORT AGGREGATE                       |         |     20 |      1 |     20 |00:00:00.01 |      60 |
|*  4 |   INDEX RANGE SCAN                    | CF11_PK |     20 |      2 |     44 |00:00:00.01 |      60 |
|   5 |  SORT AGGREGATE                       |         |     20 |      1 |     20 |00:00:00.01 |      80 |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED | CF11    |     20 |      2 |     44 |00:00:00.01 |      80 |
|*  7 |    INDEX RANGE SCAN                   | CF11_PK |     20 |      2 |     44 |00:00:00.01 |      60 |
|   8 |  SORT ORDER BY                        |         |      1 |     20 |     20 |00:00:00.01 |     157 |
|   9 |   VIEW                                |         |      1 |     20 |     20 |00:00:00.01 |       7 |
|* 10 |    COUNT STOPKEY                      |         |      1 |        |     20 |00:00:00.01 |       7 |
|  11 |     VIEW                              |         |      1 |  21235 |     20 |00:00:00.01 |       7 |
|* 12 |      TABLE ACCESS BY INDEX ROWID      | CF10    |      1 |  21235 |     20 |00:00:00.01 |       7 |
|  13 |       INDEX FULL SCAN DESCENDING      | CF10_PK |      1 |     20 |     20 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."SHP_NO"=:B1)
   4 - access("B"."SLE_NO"=:B1)
   7 - access("B"."SLE_NO"=:B1)
  10 - filter(ROWNUM<=20)
  12 - filter("SLE_DT">=:D_FROM)

[화면 맨 윗줄]  20231231-0612 · 20231231 · 225914

모범 답안 서술

5번과 7번 줄의 Starts가 21,235다. 10번 줄 SORT ORDER BY STOPKEY는 정렬할 행을 다 받아야 첫 줄을 내주는데, 그 행마다 SELECT 목록의 스칼라 서브쿼리 값이 계산되어 함께 들어간다. 그래서 한 달 판매 21,235건마다 CF11_PK를 두 번씩 찾아갔고(COUNT에 3블록, SUM에 4블록), 약 149K 가운데 148,645블록이 이 두 서브쿼리 몫이다. 화면에 보이는 것은 20건인데 계산은 21,235건에 대해 했다. CF07 서브쿼리는 Starts가 5다. 입력값(매장 번호)이 다섯 가지뿐이라 스칼라 서브쿼리 캐시가 나머지를 처리했으므로 문제가 아니다. 맨 윗줄이 틀린 까닭은 데이터에 있다. SLE_HMS 33,241건이 앞자리 0이 빠진 5자리라서, '95503'(9시 55분)과 '225914'(22시 59분)를 문자로 비교하면 첫 글자 9가 2보다 커서 '95503'이 더 늦은 시각으로 정렬된다. 오전 8 ~ 9시대 판매가 그날의 맨 위로 올라온다. 세 안을 비교한다. ㉠ 스칼라 서브쿼리를 Top-N 바깥으로 옮기면 정렬은 남지만 서브쿼리는 20건에만 실행된다. 일량은 약 149K → 약 410(한 달 범위 읽기 약 260, 서브쿼리 약 150)으로 가장 크게 준다. 하지만 맨 윗줄 오류는 그대로다. ㉡ 정렬 키를 SLE_NO DESC로 바꾸면 PK를 거꾸로(INDEX FULL SCAN DESCENDING) 읽다가 20건에서 멈추므로 정렬이 사라지고, 판매번호는 0 채움 문제와 상관없이 시각 순서라 맨 윗줄도 맞는다. 다만 판매번호가 판매 순서대로 매겨진다는 전제에 기댄다. 교재 09화에서 그 전제를 쟀고(앞 8자리 = 판매일 500,000 / 500,000, 역전 0), 판매번호는 PK라서 정렬 키도 유일하다. ㉢ 데이터 교정과 (SLE_DT, SLE_HMS) 인덱스는 다음 달까지 기다려야 하고, 인덱스 하나만큼 적재 비용이 들며, (SLE_DT, SLE_HMS)는 같은 초에 겹치는 쌍이 13,585개라 유일하지도 않다. 지금은 ㉡과 ㉠을 함께 적용한다. 판매번호로 20건을 먼저 정하고, 화면 값은 바깥에서 그 20건에만 붙인다. Buffers는 약 149K → 157로 준다(CF10 7, CF11 서브쿼리 140, CF07 10). 데이터 교정은 영업시간 집계가 맞으려면 여전히 필요하므로 예정대로 진행한다. ㉡의 전제는 대장에 적고, 판매번호 채번 방식이나 POS 프로그램이 바뀔 때, 그리고 데이터 교정을 적용한 뒤에 다시 잰다. 교정 뒤에는 (SLE_DT, SLE_HMS, SLE_NO) 순번과 SLE_NO 순번이 50만 행 전부에서 같은지(어긋난 행 0)를 확인한다.

해설

스칼라 서브쿼리는 정렬 앞에서 계산된다: 정렬이 있는 Top-N에서는 SELECT 목록의 값이 정렬 입력에 실려야 하므로 화면에 나가지 않을 행에도 계산된다. 인덱스가 순서를 주어 정렬이 없는 계획(COUNT STOPKEY)이라면 20건에서 멈추므로 서브쿼리도 20번만 돈다. 어느 쪽이든 Top-N 바깥에 두면 계획과 상관없이 20번이다.
COUNT와 SUM 서브쿼리 둘은 같은 행을 두 번 찾아간다. 20건뿐이라 지금은 140블록이지만, CROSS APPLY (SELECT COUNT(*), SUM(…) FROM CF11 …)로 합치면 한 번에 읽는다.
기간 조건이 과거(예를 들어 작년 3월)로 바뀌면 ㉡안은 PK를 거꾸로 훑으며 SLE_DT filter로 그 뒤의 판매를 다 지나가야 한다. 그런 화면이라면 SLE_NO BETWEEN :d1 || '-0000' AND :d2 || '-9999' 같은 판매번호 범위 조건을 더해 access로 바꾼다. 이것도 같은 전제에 기댄다(SORT-013).
교정 SQL은 UPDATE CF10 SET SLE_HMS = LPAD(SLE_HMS, 6, '0') WHERE LENGTH(SLE_HMS) < 6이고 redo가 약 8.9MB다. 다시 깨지지 않게 CHECK (LENGTH(SLE_HMS) = 6)를 함께 건다(교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 데이터 분포는 교재 03화, 09화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 데이터 분포는 교재 03화, 09화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황