88줄짜리 「상품별 마지막 판매」 리포트가 열 때마다 TEMP를 38MB 쓴다

중급SELECT유통366 / 700

상황

본사 상품팀은 단종 후보를 고를 때 「상품별 마지막 판매」(상품마다 마지막으로 팔린 판매번호, 수량, 단가)를 본다. 오전 회의 전에 상품팀 8명이 몰아서 여는데 한 번에 2.3초가 걸리고, DBA에게는 TEMP 사용 경보가 온다. SQL 위에는 2021년에 남긴 메모가 있다. "GROUP BY로 MAX를 구해 다시 조인하면 판매상품을 두 번 읽는다. 분석 함수로 한 번만 읽게 고쳤다."

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_NO VARCHAR2(15) '20231231-0612'(판매일 + 그날 일련번호, 판매 순서대로 매겨진다) · ITM_NO · SLE_QTY · SLE_UPRC · 판매번호 순서대로 적재
CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 다른 인덱스는 없다 · 통계 최신
팔린 상품은 88종이다. 한 판매에 같은 상품은 한 줄뿐이라(PK) 상품 하나 안에서 SLE_NO는 겹치지 않는다
오전에는 다른 배치와 겹쳐 work area 하나의 상한이 약 20MB다

원본 SQL

sql

SELECT ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC
  FROM (SELECT d.ITM_NO, d.SLE_NO, d.SLE_QTY, d.SLE_UPRC,
               MAX(d.SLE_NO) OVER (PARTITION BY d.ITM_NO) AS LAST_NO
          FROM CF11 d)
 WHERE SLE_NO = LAST_NO
 ORDER BY ITM_NO

실행계획 학습용 예시 · 메모리 크기는 교재 09화 규모

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     88 |00:00:02.31 |    8204 |       |          |         |
|*  1 |  VIEW               |      |      1 |   1096K|     88 |00:00:02.31 |    8204 |       |          |         |
|   2 |   WINDOW SORT       |      |      1 |   1096K|   1096K|00:00:02.12 |    8204 |    47M|   20M (1)|      38M|
|   3 |    TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.13 |    8204 |       |          |         |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SLE_NO"="LAST_NO")

문제

88줄을 내는 SQL이 TEMP를 38MB 쓰는 까닭을 실행계획 2번 줄과 MAX() OVER의 성질로 설명하시오.
ROW_NUMBER로 고친 안과, GROUP BY로 상품별 마지막 판매번호를 구해 다시 찾는 안의 일량을 어림해 비교하고, 하나를 골라 SQL로 쓰시오.
2021년 메모의 "두 번 읽는다"는 판단이 이 테이블에서 맞지 않는 까닭을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품별 마지막 판매번호는 PK 인덱스만으로 구하고 (ITM_NO, SLE_NO 가 다 들어 있다), 88 줄만 PK 로 다시 찾는다
SELECT d.ITM_NO, d.SLE_NO, d.SLE_QTY, d.SLE_UPRC
  FROM (SELECT ITM_NO, MAX(SLE_NO) AS LAST_NO
          FROM CF11
         GROUP BY ITM_NO) m,
       CF11 d
 WHERE d.SLE_NO = m.LAST_NO
   AND d.ITM_NO = m.ITM_NO
 ORDER BY d.ITM_NO;

-- 같은 답 · 테이블을 한 번 읽되 상품마다 1 줄만 남기며 정렬 (TEMP 없음 · 8,204 블록)
-- SELECT ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC
--   FROM (SELECT d.ITM_NO, d.SLE_NO, d.SLE_QTY, d.SLE_UPRC,
--                ROW_NUMBER() OVER (PARTITION BY d.ITM_NO ORDER BY d.SLE_NO DESC) AS rn
--           FROM CF11 d)
--  WHERE rn = 1
--  ORDER BY ITM_NO;

개선 후 실행계획 학습용 예시 · 메모리 크기는 교재 09화 규모

xplan

[고른 안 · GROUP BY MAX + PK 로 다시 찾기]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     88 |00:00:00.24 |    4664 |          |
|   1 |  SORT ORDER BY                |         |      1 |     88 |     88 |00:00:00.24 |    4664 | 6144  (0)|
|   2 |   NESTED LOOPS                |         |      1 |     88 |     88 |00:00:00.24 |    4664 |          |
|   3 |    NESTED LOOPS               |         |      1 |     88 |     88 |00:00:00.24 |    4576 |          |
|   4 |     VIEW                      |         |      1 |     88 |     88 |00:00:00.23 |    4312 |          |
|   5 |      HASH GROUP BY            |         |      1 |     88 |     88 |00:00:00.23 |    4312 | 1043K (0)|
|   6 |       INDEX FAST FULL SCAN    | CF11_PK |      1 |   1096K|   1096K|00:00:00.08 |    4312 |          |
|*  7 |     INDEX UNIQUE SCAN         | CF11_PK |     88 |      1 |     88 |00:00:00.01 |     264 |          |
|   8 |    TABLE ACCESS BY INDEX ROWID| CF11    |     88 |      1 |     88 |00:00:00.01 |      88 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("D"."SLE_NO"="M"."LAST_NO" AND "D"."ITM_NO"="M"."ITM_NO")

[ROW_NUMBER 안 (주석의 SQL)]  2번 줄 WINDOW SORT PUSHED RANK · Used-Mem 6144 (0) · Used-Tmp 없음 · A-Time 0.42초 · Buffers 8,204

모범 답안 서술

2번 줄 WINDOW SORT는 110만 행 전부를 상품별로 정렬해 작업 공간에 담는다. MAX(SLE_NO) OVER (PARTITION BY ITM_NO)는 행마다 '내 상품의 최댓값'을 붙여야 하므로, 상품 하나의 행을 끝까지 보기 전에는 어느 행도 버릴 수 없다. 그래서 1번 줄이 SLE_NO = LAST_NO로 88줄을 고르기 전에 1,096,708행을 모두 정렬해 둔다. 필요한 메모리(OMem)는 47M인데 오전 상한이 약 20MB라 20M (1), 곧 one-pass로 돌았고 넘친 38M를 TEMP에 썼다가 다시 읽었다. 2.3초의 대부분이 이 정렬이다. ROW_NUMBER() OVER (PARTITION BY ITM_NO ORDER BY SLE_NO DESC)로 바꿔 rn = 1로 거르면 순위 조건이 정렬 단계로 내려가 WINDOW SORT PUSHED RANK가 된다. 상품마다 1줄만 남기며 정렬하므로 작업 공간은 6K 남짓이고 TEMP가 사라진다(0.42초). 하지만 수량과 단가가 테이블에만 있어서 테이블은 여전히 8,204블록을 다 읽는다. 마지막 판매번호를 구하는 데는 테이블이 필요 없다. ITM_NO와 SLE_NO는 둘 다 CF11_PK에 들어 있으므로, GROUP BY ITM_NO의 MAX(SLE_NO)는 PK 인덱스만 빠르게 훑어(INDEX FAST FULL SCAN, 4,312블록) 88줄로 묶는다. 그 88줄의 (SLE_NO, ITM_NO)는 PK 값 그 자체라서 다시 찾는 일은 INDEX UNIQUE SCAN 88번과 테이블 88블록, 352블록이다. 합계 4,664블록, 작업 공간 1M 남짓, 0.24초다. 상품 하나 안에서 SLE_NO가 겹치지 않으므로 MAX로 찾아도 상품마다 한 줄만 나온다. 2021년 메모의 '두 번 읽는다'는 판단은 이 테이블에서는 맞지 않는다. 첫 번째는 테이블이 아니라 그 절반 크기인 PK 인덱스를 읽고, 두 번째는 88번의 PK 찾기다. MAX냐 ROW_NUMBER냐는 문법의 문제가 아니라 무엇을 읽어야 답이 나오는지의 문제다. Buffers는 8,204 → 4,664, TEMP는 38MB → 0이다.

해설

KEEP으로 쓴다면: MAX(SLE_QTY) KEEP (DENSE_RANK LAST ORDER BY SLE_NO)처럼 칸마다 KEEP을 붙이면 GROUP BY ITM_NO 한 번으로 끝나고 작업 공간도 상품 88개만큼이다. 다만 수량과 단가가 테이블에만 있어 8,204블록을 읽는 것은 ROW_NUMBER와 같다.
더 줄이려면: (ITM_NO, SLE_NO) 인덱스가 있으면 상품마다 인덱스 끝 한 항목만 읽어(FIRST ROW) CF01 109개 × 약 4블록, 400블록 남짓으로 끝난다(SORT-015와 같은 방법). 판매 줄이 들어올 때마다 고칠 인덱스가 하나 느는 대가와 견줘 정한다. 아침 회의 전에 여덟 번 여는 리포트라면 4,664블록으로 충분하다.
MAX 방식이 늘 싼 것은 아니다: 다시 찾을 줄이 많거나(그룹이 수만 개) 묶음 키와 MAX 컬럼이 한 인덱스에 없으면 원장을 정말로 두 번 읽게 된다. 그때는 ROW_NUMBER로 한 번 읽는 편이 낫다. 같은 '최신 1건'이라도 답은 데이터와 인덱스가 정한다.
동점이 생길 수 있는 키(예: 판매일만으로 마지막을 정할 때)라면 MAX 방식은 동점 줄을 모두 돌려주고 ROW_NUMBER는 아무 하나를 고른다. 이 문제는 상품 안에서 SLE_NO가 유일해서 두 방식의 답이 같다(SORT-004).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 메모리 크기는 교재 09화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 메모리 크기는 교재 09화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황