월 감사 출력이 판매번호를 1,000개씩 나눠 스물한 번 보내고 8만 블록을 읽는다

중급SELECT유통147 / 700

상황

본사 감사팀은 매달 초 지난달 판매상품 원장을 출력한다. 출력 프로그램은 먼저 CF10에서 지난달 판매번호 목록을 받고, 그 번호를 1,000개씩 묶어 CF11에 IN 목록으로 보낸다. 2023년 12월은 판매가 20,864건이라 스물한 번 보냈다. 개발자는 이렇게 설명했다. "IN에는 1,000개까지만 넣을 수 있어서(ORA-01795) 나눴다. PK로 한 건씩 정확히 찾으니 가장 빠르다." 그런데 출력에 1분이 넘게 걸리고, 그동안 매장 POS의 판매 등록이 느려진다는 말이 나온다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록(블록당 약 134행) · 판매번호 순으로 쌓였다 · SLE_NO는 'YYYYMMDD-NNNN'(앞 8자리가 판매일) · 영수증 한 장에 평균 2.2줄 · 2023년 12월 46,322줄
CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록(리프 한 블록에 약 256항목) · CLUSTERING_FACTOR 16,735 · 통계 최신
CF10 판매: 2023년 12월 20,864건 · CF10_PK (SLE_NO)

원본 SQL

sql

-- 출력 프로그램 · 판매번호 1,000개씩 (2023년 12월은 21번)
SELECT SUBSTR(SLE_NO, 1, 8) AS sle_dt, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC,
       SLE_QTY * SLE_UPRC AS sle_amt
  FROM CF11
 WHERE SLE_NO IN (:b1, :b2, :b3, /* … */ :b1000)
 ORDER BY SLE_NO, ITM_NO

실행계획 학습용 예시 · 건수와 인덱스 규모는 적재 데이터(CF10 · CF11) 그대로

xplan

[한 번 · 판매번호 1,000개 · 2,212줄]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |   2212 |00:00:02.91 |    4058 |  1203 |
|   1 |  INLIST ITERATOR             |         |      1 |        |   2212 |00:00:02.91 |    4058 |  1203 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CF11    |   1000 |   2200 |   2212 |00:00:02.88 |    4058 |  1203 |
|*  3 |    INDEX RANGE SCAN          | CF11_PK |   1000 |   2200 |   2212 |00:00:00.41 |    3012 |   188 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("SLE_NO"=:B1 OR "SLE_NO"=:B2 OR … OR "SLE_NO"=:B1000))

[2023년 12월 · 스물한 번 합계]  Buffers 약 85,000 · Reads 약 25,000 · 경과 1분 3초

문제

한 번(판매번호 1,000개)에 약 4,000블록, 한 달 스물한 번에 약 8만 5천 블록이 되는 이유를 인덱스 높이와 키 수로 어림해 설명하시오.
같은 결과를 내도록 SQL을 고치고, 고친 SQL이 읽을 블록 수를 리프 블록 수와 CLUSTERING_FACTOR로 어림하시오.
IN 목록으로 키를 하나씩 찾는 편이 나은 경우를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매번호를 하나씩 찾지 않는다 — 앞 8자리가 판매일이라 한 달 치는 PK 안에서 한 구간이다
SELECT SUBSTR(SLE_NO, 1, 8) AS sle_dt, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC,
       SLE_QTY * SLE_UPRC AS sle_amt
  FROM CF11
 WHERE SLE_NO >= :v_ym || '01'           -- '20231201'
   AND SLE_NO <  :v_next_ym || '01'      -- '20240101'
 ORDER BY SLE_NO, ITM_NO;
-- 출력 프로그램은 CF10 에서 번호 목록을 받는 단계를 없애고 이 SQL 을 한 번만 보낸다

개선 후 실행계획 학습용 예시 · 건수와 인덱스 규모는 적재 데이터(CF10 · CF11) 그대로

xplan

[2023년 12월 · 한 번]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |  46322 |00:00:00.38 |     890 |   527 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF11    |      1 |  46310 |  46322 |00:00:00.38 |     890 |   527 |
|*  2 |   INDEX RANGE SCAN          | CF11_PK |      1 |  46310 |  46322 |00:00:00.05 |     183 |   181 |
-------------------------------------------------------------------------------------------------------
   2 - access("SLE_NO">=:V_YM||'01' AND "SLE_NO"<:V_NEXT_YM||'01')
(PK 순서 그대로 나오므로 SORT ORDER BY가 없다)

모범 답안 서술

판매번호 하나를 IN 목록으로 찾을 때마다 PK 인덱스를 루트에서 리프까지 따로 내려간다. BLEVEL이 2이므로 한 번에 루트 1 + 브랜치 1 + 리프 1 = 3블록이다. 3번 줄의 Starts 1,000이 그 횟수이고, 1,000 × 3 = 3,000에 영수증 줄이 리프 경계에 걸린 몫을 더해 3,012블록이다. 테이블은 영수증마다 한 번씩 찾아가 1,046블록, 합해서 한 번에 4,058블록이다. 한 달 20,864건을 1,000개씩 나눠 스물한 번 보내므로 약 8만 5천 블록이다. 이웃한 판매번호는 같은 리프, 같은 테이블 블록에 있는데도 번호마다 처음부터 다시 찾아가서 같은 블록을 몇 번씩 읽는다. 1,000개 상한(ORA-01795) 때문에 나눈 것은 SQL 수만 늘렸다. 판매번호 앞 8자리가 판매일이므로 한 달 치 판매번호는 PK 인덱스 안에서 '20231201'부터 '20240101' 앞까지 한 구간으로 이어져 있다. 그래서 SLE_NO >= '20231201' AND SLE_NO < '20240101'로 한 번만 내려가 그 구간을 이어 읽으면 된다. 어림하면 수직 탐색 2블록 + 리프 46,322 ÷ 256 ≈ 181블록이다. 테이블로 가는 몫은 CLUSTERING_FACTOR × 선택도 = 16,735 × (46,322 ÷ 1,096,708) ≈ 707번이다. 테이블이 판매번호 순으로 쌓여 있어 한 달 치가 약 350블록에 모여 있기 때문이다. 합해서 약 890블록이다. 한 달 출력은 약 8만 5천 → 890블록(약 96분의 1)이 되고, PK 순서 그대로 나오므로 정렬도 없다. CF10에서 번호 목록을 먼저 받는 단계도 필요 없어진다. IN 목록이 나은 경우도 있다. 찾을 키가 몇 개뿐이고 서로 멀리 흩어져 있을 때다. 예를 들어 감사인이 고른 영수증 10장이 여러 달에 흩어져 있다면 10 × 4 = 약 40블록이면 끝나고, 그 달들을 모두 범위로 읽는 것보다 훨씬 싸다. 키가 많고 한 구간에 몰려 있으면 범위로 한 번에 읽는다.

해설

목록 길이마다 다른 SQL: 마지막 묶음은 864개라 다른 문장이 되고, 바인드 변수 1,000개짜리 문장은 파싱도 무겁다. 목록 길이가 매번 달라지는 화면이라면 커서가 길이마다 따로 생긴다(PARSE 단원).
조인으로 써도 된다: 판매 헤더(CF10)의 매장이나 판매일이 함께 필요하면 CF10.SLE_DT BETWEEN …으로 CF10을 읽고 CF11과 조인한다. 번호를 프로그램으로 가져왔다가 다시 보내는 왕복이 없어진다(교재 06화).
판매번호 범위 조건의 전제: 판매번호 앞 8자리가 판매일이라는 규칙에 기댄다. 번호 체계가 바뀌면 조건도 바뀌어야 하므로 승인서와 SQL 주석에 그 전제를 적어 둔다. 판매일 컬럼이 없는 CF11의 한계다.
Reads도 준다: 묶음마다 같은 블록을 몇 번씩 찾아가던 읽기가 한 구간을 한 번 읽는 것으로 바뀌어, 캐시에 없던 블록을 디스크에서 읽는 횟수도 약 2만 5천 → 약 530으로 준다. POS 판매 등록과 다투던 I/O가 그만큼 사라진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 인덱스 규모는 적재 데이터(CF10 · CF11) 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 인덱스 규모는 적재 데이터(CF10 · CF11) 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황