03화에서 판매 헤더를 고쳤는데 월 매출 집계는 아직 8,552블록을 읽는다

모범 답안 SQL ​

-- CF11 에도 자기 조건을 준다: SLE_NO 앞 8자리가 판매일자다 (지금 데이터 위반 0건, 아래 제약으로 보장)
SELECT COUNT(*), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE a.SLE_NO = b.SLE_NO
   AND a.SLE_DT BETWEEN '20230601' AND '20230630'
   AND b.SLE_NO >= '20230601'
   AND b.SLE_NO <  '20230701';

-- 규칙을 데이터가 아니라 제약이 지키게 한다 (위반 0건을 확인한 뒤)
ALTER TABLE CF10 ADD CONSTRAINT CF10_CK_SLE_NO CHECK (SUBSTR(SLE_NO, 1, 8) = SLE_DT);

개선 후 실행계획 실측 · 교재 03화 (개선안은 학습용 예시) ​

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |      1 |00:00:00.04 |    1366 |           |
|   1 |  SORT AGGREGATE                        |                |      1 |      1 |      1 |00:00:00.04 |    1366 |           |
|*  2 |   HASH JOIN                            |                |      1 |   2861 |  45935 |00:00:00.04 |    1366 | 2061K (0) |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |   2673 |  20953 |00:00:00.01 |     478 |           |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |   2673 |  20953 |00:00:00.01 |      62 |           |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF11           |      1 |  45980 |  45935 |00:00:00.02 |     888 |           |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |      1 |  45980 |  45935 |00:00:00.01 |     179 |           |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."SLE_NO"="B"."SLE_NO")
   4 - access("A"."SLE_DT">='20230601' AND "A"."SLE_DT"<='20230630')
   6 - access("B"."SLE_NO">='20230601' AND "B"."SLE_NO"<'20230701')

모범 답안 서술 ​

NL로 바꾸면: 6월 판매 20,953건마다 CF11_PK로 판매상품을 찾아간다(Starts 20,953). 한 번에 인덱스 2블록과 테이블 1 ~ 2블록이면 약 6만 ~ 9만 블록이다. 지금 해시 조인이 CF11을 FULL로 한 번 읽는 비용(8,074블록)보다 훨씬 비싸다. 한 달은 판매의 4%지만 건수로는 2만 건이 넘어서, 왕복 횟수가 곱해지는 NL은 맞지 않는다. 줄여야 할 것은 조인 방식이 아니라 probe 쪽에서 읽는 범위다. 개선안: 해시 조인의 probe 쪽 CF11에는 조건이 없어서 110만 줄을 다 읽는다. 그런데 SLE_NO는 앞 8자리가 판매일자(SLE_DT)인 번호다. 그래서 b.SLE_NO >= '20230601' AND b.SLE_NO < '20230701'을 더하면 CF11_PK 범위 스캔으로 6월 줄만 읽는다(인덱스 179블록과 테이블 709블록). Buffers는 8,552 → 1,366이고, 건수 45,935와 금액 377,266,500원은 그대로다. 근거와 그 근거가 깨질 때: 결과가 같다는 근거는 '모든 판매의 앞 8자리가 SLE_DT와 같다'는 데이터의 성질이다. 지금은 위반이 0건이지만 이를 지키는 제약이 없다. 자정을 넘겨 마감한 판매처럼 번호의 날짜와 판매일자가 다른 행이 한 건이라도 들어오면, 고친 SQL은 에러 없이 그 줄을 빼고 금액을 틀리게 낸다. 그래서 CHECK (SUBSTR(SLE_NO, 1, 8) = SLE_DT) 제약을 걸어 규칙을 강제하는 것까지가 개선안이다. 제약을 걸 때 50만 행을 한 번 검사하고, 그 뒤로 규칙을 어기는 적재는 들어올 때 바로 실패한다. CF10 조인을 없애는 안: 집계에 쓰는 컬럼은 모두 CF11에 있으므로 CF11만 범위로 읽으면 888블록이다. 하지만 판매 헤더가 없는 판매상품이 0건이라는 것도 FK가 보장하지 않는다. 조인을 없애면 헤더 없는 줄이 생기는 날 그 금액까지 더해진다. FK를 만들어 검증하기 전에는 권하지 않는다. 커버링 인덱스안: CF11 (SLE_NO, SLE_QTY, SLE_UPRC)를 만들면 CF11 쪽이 인덱스만 약 250블록으로 끝나 전체 약 730블록이다. 개선안보다 약 640블록 더 줄지만, 가장 큰 테이블(110만 행)에 인덱스를 하나 더 두면 적재할 때마다 갱신 비용과 redo가 붙는다. 이득에 비해 비용이 커서 권하지 않는다. 권고: CF11에 SLE_NO 범위 조건을 더하고 CHECK 제약으로 그 근거를 보장한다. 8,552 → 1,366블록이다.

해설 ​

• 옵티마이저가 못 하는 일: 옵티마이저는 조인 조건과 a.SLE_DT의 조건만 보고서는 b.SLE_NO의 범위를 알 수 없다. SLE_NO에 날짜가 들어 있다는 것은 업무 규칙이라 사람이 조건으로 적어 줘야 한다. 같은 범위를 a.SLE_NO에 적어도 된다. 그러면 옵티마이저가 조인 조건을 따라 b.SLE_NO에도 같은 범위를 붙인다.
• NL이 답이 아닌 이유: NL은 바깥 집합의 건수만큼 왕복한다. 한 달 2만 건처럼 바깥이 크면 inner에 인덱스가 있어도 해시 조인이 낫다(교재 06화 §5 손익분기).
• 정합성 회귀: 고친 뒤 건수 45,935와 금액 377,266,500원이 원본과 같은지 확인한다(교재 08화 §10). 테스트를 통과해도 규칙을 깨는 데이터가 들어오면 결과가 조용히 틀리므로, 제약으로 규칙을 강제해야 한다.
• b.SLE_NO LIKE '202306%'로 써도 같은 범위 스캔이 된다(교재 03화 §2.1).

관련 교재 ​

• 03화 · 이 화의 측정값 전부 (CF10 × CF11)
• 06화 · 손익분기점을 직접 재는 법
• 08화 · 정합성 회귀 검증