JOIN-085

힌트 유효기간 대장의 「조사필요」 세 줄을 정리하다가, 힌트를 뗀 판으로 돌린 영수증 출력이 뒤죽박죽이 됐다

실전SELECT유통235 / 700

상황

본사 레거시 유통의 힌트 유효기간 대장에는 2009 ~ 2010년에 붙인 힌트 세 줄이 「조사필요」로 남아 있다. 근거 문서를 찾지 못했고 그때의 규모도 「불명」이다. 이번 정기 점검에서 이 세 줄을 닫기로 했다. 조민우가 시험 DB(운영 복제)에서 힌트가 있는 판과 뗀 판을 돌려 아래 표를 만들고 "셋 다 떼면 됩니다"라고 보고했다. 그런데 R2를 뗀 판으로 밤 배치를 시험해 보니 출력 파일의 영수증이 거의 한 줄에 한 장씩 찍혀 나왔다. 박팀장은 세 줄 모두 판정과 근거, 다음 재검토 조건을 숫자로 적어 오라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 날짜순 적재 · 하루 약 685건 · 2023년 6월 20,953건
SLE_NO는 'YYYYMMDD-NNNN' 모양이라 영수증 번호 순서가 곧 날짜와 발행 순서다
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증당 평균 2.19줄 · 2015년 테이블 재구성 때 ITM_NO 순서로 다시 적재했다(물리 순서가 상품 순)
영수증 출력 프로그램은 결과를 위에서부터 읽다가 SLE_NO가 바뀌면 새 영수증을 시작한다
통계 최신 · SLE_DT 범위는 실제의 4분의 1 안팎으로, R2의 SLE_NO 범위(하루 685건)는 약 2,700건으로 추정된다

원본 SQL

sql

-- [R1] PKG_SLE_RPT.Q3 「월 상품 판매 집계」 · 2009-11 · 근거 못 찾음
SELECT /*+ LEADING(a b) USE_NL(b) */
       b.ITM_NO, COUNT(*) AS line_cnt, SUM(b.SLE_QTY) AS qty
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO;

-- [R2] PKG_SLE_PRT.P1 「영수증 일괄 재출력」 (매일 밤 전날 영수증을 출력 파일로) · 2010-03 · 근거 못 찾음
SELECT /*+ ORDERED USE_NL(b) INDEX(a CF10_PK) */
       a.SLE_NO, a.SLE_DT, a.SLE_HMS, a.SHP_NO, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_NO BETWEEN :d || '-0000' AND :d || '-9999'
   AND b.SLE_NO = a.SLE_NO;

-- [R3] PKG_SLE_RPT.Q5 「매장 시간대 영수증」 · 2010-08 · 근거 못 찾음
SELECT /*+ LEADING(a b) USE_NL(b) */
       a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :d
   AND a.SHP_NO = :shp
   AND a.SLE_HMS BETWEEN :h1 AND :h2
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_HMS, a.SLE_NO, b.ITM_NO;

실행계획 학습용 예시

xplan

[재측정 · 시험 DB · Buffers (조민우)]
 줄   힌트                                  힌트 있는 판                            힌트 뗀 판
 R1   LEADING(a b) USE_NL(b)                109K · NL (CF11_PK Starts 20,953)       8,682 · HASH (CF11 FULL 한 번)
 R2   ORDERED USE_NL(b) INDEX(a CF10_PK)    3,569 · NL (CF10_PK 범위 → CF11_PK)     8,215 · HASH (CF10 하루가 build, CF11 FULL이 probe)
 R3   LEADING(a b) USE_NL(b)                122 · NL                                122 · NL (plan hash value 같음)

[R2 · 힌트 뗀 판의 출력 첫 6줄 (2023-12-24)]
 SLE_NO          SLE_HMS  ITM_NO  SLE_QTY
 20231224-0388   183512   1001    1
 20231224-0021   101204   1001    2
 20231224-0517   201733   1001    1
 20231224-0102   120945   1003    1
 20231224-0388   183512   1003    1
 20231224-0233   144410   1003    2
 → 출력 프로그램은 685장이어야 할 영수증을 1,497장으로 찍었다 (결과 1,503줄 · 힌트 있는 판은 685장)

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
[R1 · 힌트 있는 판 · 2023년 6월]
|   0 | SELECT STATEMENT                       |                |      1 |        |    109 |00:00:00.62 |    109K |          |
|   1 |  HASH GROUP BY                         |                |      1 |    109 |    109 |00:00:00.62 |    109K | 1210K (0)|
|   2 |   NESTED LOOPS                         |                |      1 |  11337 |  45887 |00:00:00.60 |    109K |          |
|   3 |    NESTED LOOPS                        |                |      1 |  11337 |  45887 |00:00:00.30 |   63337 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   5177 |  20953 |00:00:00.02 |     478 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   5177 |  20953 |00:00:00.01 |      60 |          |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |  20953 |      2 |  45887 |00:00:00.21 |   62859 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID         | CF11           |  45887 |      2 |  45887 |00:00:00.27 |   45885 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)
   6 - access("B"."SLE_NO"="A"."SLE_NO")

문제

세 줄을 각각 판정(폐기, 유지, 재검토, 조사필요)하고 그 근거를 재측정값으로 쓰시오.
R2를 그냥 폐기하면 무엇이 깨지는지 출력 결과로 설명하고, 순서와 성능을 모두 지키도록 SQL을 고치시오.
세 줄의 다음 재검토 조건을 이 조인의 손익분기점을 근거로 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- [R1] 폐기: 힌트를 뗀다 (한 달은 해시가 맞다)
SELECT b.ITM_NO, COUNT(*) AS line_cnt, SUM(b.SLE_QTY) AS qty
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO;

-- [R2] 순서는 ORDER BY 로 보장하고, NL 힌트는 성능 근거로만 남긴다 (ORDERED 대신 LEADING)
SELECT /*+ LEADING(a b) USE_NL(b) INDEX(a CF10_PK) */
       a.SLE_NO, a.SLE_DT, a.SLE_HMS, a.SHP_NO, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_NO BETWEEN :d || '-0000' AND :d || '-9999'
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_NO, b.ITM_NO;

-- [R3] 폐기: 힌트를 뗀다 (뗀 판도 같은 plan hash value)
SELECT a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :d
   AND a.SHP_NO = :shp
   AND a.SLE_HMS BETWEEN :h1 AND :h2
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_HMS, a.SLE_NO, b.ITM_NO

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
[R2 · 고친 뒤 · 2023-12-24]
|   0 | SELECT STATEMENT                       |                |      1 |        |   1503 |00:00:00.03 |    3569 |          |
|   1 |  SORT ORDER BY                         |                |      1 |   5913 |   1503 |00:00:00.03 |    3569 |  124K (0)|
|   2 |   NESTED LOOPS                         |                |      1 |   5913 |   1503 |00:00:00.03 |    3569 |          |
|   3 |    NESTED LOOPS                        |                |      1 |   5913 |   1503 |00:00:00.02 |    2066 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   2700 |    685 |00:00:00.01 |      11 |          |
|*  5 |      INDEX RANGE SCAN                  | CF10_PK        |      1 |   2700 |    685 |00:00:00.01 |       4 |          |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |    685 |      2 |   1503 |00:00:00.01 |    2055 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID         | CF11           |   1503 |      2 |   1503 |00:00:00.01 |    1503 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("A"."SLE_NO">=:D||'-0000' AND "A"."SLE_NO"<=:D||'-9999')
   6 - access("B"."SLE_NO"="A"."SLE_NO")

모범 답안 서술

R1 (폐기): 힌트 있는 판은 NL로 6월 영수증 20,953건마다 CF11_PK를 찾아가(6번 줄 Starts 20,953) 109K블록을 읽는다. 뗀 판은 해시로 CF11을 한 번 읽어 8,682블록이다. 이 조인의 손익분기점은 CF11 8,204블록 ÷ 영수증 한 장에 드는 약 5.2블록(인덱스 3블록과 상품 줄 2.19줄) ≈ 1,580장이므로, 한 달 2만 장은 해시 구간이다. 2009년 개점 초기에는 한 달 영수증이 분기점보다 적었을 수도 있지만 그때 규모는 「불명」이다. 근거가 성능뿐이고 결과(상품별 합계)는 순서와 상관없으므로 폐기하고, 「힌트 없음(해시)」을 새 줄로 올린다. R2 (조사필요에서 정합성 근거 확인, 고쳐서 유지): 성능만 보면 힌트 있는 판(3,569블록)이 뗀 판(8,215블록)보다 싸다. 그런데 더 중요한 근거는 순서다. 이 SQL에는 ORDER BY가 없다. 힌트가 CF10_PK를 범위로 읽게 하고(SLE_NO 순) 영수증마다 CF11_PK로 상품 줄을 붙이게 해서(ITM_NO 순), 결과가 우연히 영수증 순서로 나왔다. 출력 프로그램은 그 순서에 기대어 SLE_NO가 바뀔 때 새 영수증을 시작한다. 힌트를 떼자 해시 조인이 되면서 결과가 probe 쪽인 CF11의 물리 순서(2015년 재구성 뒤로 상품 순)를 따랐고, 같은 영수증의 줄이 흩어져 685장이 1,497장으로 찍혔다. 2010년의 힌트는 성능이 아니라 출력 순서를 지키고 있었던 셈이다. 그래서 그냥 폐기하지 않는다. 먼저 ORDER BY a.SLE_NO, b.ITM_NO를 넣어 순서를 SQL이 보장하게 하고, 그다음 힌트는 성능 근거로만 판단한다. 하루 685장은 분기점 약 1,580장보다 적어 NL이 맞으므로 ORDERED를 LEADING(a b)로 바꿔 유지한다. 정렬이 더해져도 1,503줄이라 124K 메모리로 끝나고 Buffers는 3,569 그대로다. R3 (폐기): 힌트를 뗀 판도 같은 plan hash value, 같은 122블록이다. 매장 하나의 두 시간(영수증 약 20장)이면 옵티마이저도 NL을 고르므로 이 힌트는 아무 일도 하지 않는다. 결과 순서는 ORDER BY가 이미 보장한다. 쓸모없는 힌트는 SQL이 바뀔 때 해로운 힌트가 될 수 있으므로 떼고, 「힌트 없음」을 대장에 올린다. 대장과 재검토 조건: R1과 R3의 옛 줄은 「폐기」로 닫고, R2의 옛 줄은 「조사필요」에서 「정합성 근거 확인(출력 순서), ORDER BY 추가 후 유지」로 바꾼다. 다음 재검토 조건은 손익분기점 약 1,580장을 기준으로 적는다. R1(힌트 없음, 해시)은 「조회 범위가 이틀 이하로 줄 때(약 1,400장으로 분기점 아래), 또는 6개월」이다. R2(NL 유지)는 「하루 영수증이 1,200장을 넘을 때, CF11이 아카이빙 등으로 4,000블록 아래로 줄 때(분기점이 약 770장으로 당겨진다), 또는 6개월」이다. R3(힌트 없음)은 「화면이 한 번에 1,000장 넘게 보도록 바뀔 때, 또는 6개월」이다.

해설

「조사필요」를 그냥 「폐기」로 넘기지 않는다: 근거를 못 찾은 힌트는 이유 없이 붙은 힌트가 아니라 이유를 아직 못 찾은 힌트다. R2처럼 그 이유가 성능이 아니라 정합성일 수 있다. 힌트를 떼는 시험에는 Buffers뿐 아니라 결과(건수, 합계, 순서)의 비교를 꼭 넣는다 (교재 06화 §8).
순서를 계획에 맡기는 SQL: ORDER BY 없이 나오는 순서는 계획이 바뀌면 아무 예고 없이 바뀐다. 힌트, 인덱스, 병렬 처리, 테이블 재구성 가운데 하나만 바뀌어도 깨진다. 순서가 필요한 SQL은 반드시 ORDER BY로 적는다.
R2에 힌트를 남기는 대가: NL 판은 하루 영수증이 분기점에 가까워질수록 손해가 커진다. 그래서 재검토 조건에 하루 영수증 수와 CF11 블록 수를 함께 적었다. 분기점은 "안쪽을 한 번 읽는 값 ÷ 바깥 한 건의 일"이다 (교재 06화 §5).
대장의 줄은 힌트 하나당 한 줄이다. R2는 LEADING(a b), USE_NL(b), INDEX(a CF10_PK)를 각각 한 줄로 올리고 같은 근거를 적는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황