정산팀 카드 결제 대사 화면이 주문상세를 주문 수만큼 1,370번 찾아간다

기초SELECT한빛유통613 / 700

상황

정산팀의 「카드 결제 대사」는 월을 고르면 그 달에 주문하고 그 달에 결제된 카드 주문을 주문별 상세 금액 합계와 함께 보여 준다. PG사 정산서와 맞춰 보는 화면이라 마감 주간에는 정산팀 12명이 하루 수백 번 누른다. 화면은 월을 리터럴로 넣는다(아래는 2024년 6월). 개발자의 설명은 이렇다. "주문일도 결제일도 그 달이어야 해서 조건을 둘 다 걸었어요."

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT DATE(시분초 있음, 2023-01-01 ~ 2024-12-31, 하루 약 137건) · PAY_DT DATE(결제일 0시) · PAY_TP CARD · CASH · POINT가 1/3씩
이 테이블의 결제는 모두 주문 당일에 일어났다(PAY_DT = TRUNC(ORD_DT)). 결제일이 주문일보다 앞선 행은 없다.
2024년 6월 주문 4,110건 · 그중 카드 결제 1,370건
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO): BLEVEL 1 · 주문당 1 ~ 3건(평균 2건)
통계 최신 · 히스토그램 없음 · 확장 통계 없음 · ORD_DT NUM_DISTINCT 약 10만(시분초) · PAY_DT NUM_DISTINCT 730

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.PAY_DT, o.PAY_AMT, SUM(d.ORD_QTY * d.UNT_PRC) AS det_amt
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'
   AND o.PAY_DT >= DATE '2024-06-01' AND o.PAY_DT < DATE '2024-07-01'
   AND o.PAY_TP = 'CARD'
   AND d.ORD_SEQ = o.ORD_SEQ
 GROUP BY o.ORD_SEQ, o.ORD_DT, o.PAY_DT, o.PAY_AMT

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |   1370 |00:00:00.03 |    5006 |
|   1 |  HASH GROUP BY               |              |      1 |     58 |   1370 |00:00:00.03 |    5006 |
|   2 |   NESTED LOOPS               |              |      1 |    116 |   2740 |00:00:00.03 |    5006 |
|   3 |    NESTED LOOPS              |              |      1 |    116 |   2740 |00:00:00.02 |    3636 |
|*  4 |     TABLE ACCESS FULL        | T_ORD        |      1 |     58 |   1370 |00:00:00.01 |     896 |
|*  5 |     INDEX RANGE SCAN         | PK_T_ORD_DET |   1370 |      2 |   2740 |00:00:00.01 |    2740 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T_ORD_DET   |   2740 |      1 |   2740 |00:00:00.01 |    1370 |
--------------------------------------------------------------------------------------------------
   4 - filter(("O"."PAY_TP"='CARD' AND
              "O"."ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."PAY_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."PAY_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - access("D"."ORD_SEQ"="O"."ORD_SEQ")

문제

4번 줄의 E-Rows 58을 손으로 계산하고, 실제 1,370과 왜 다른지 데이터로 설명하시오. 그 58 때문에 무엇을 골랐는지도 쓰시오.
결과가 바뀌지 않게 SQL을 고쳐 추정을 바로잡으시오. 두 열에 컬럼 그룹 통계를 만들면 고쳐지는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 결제일은 주문일보다 앞설 수 없다 → 'PAY_DT >= 6월 1일' 은 'ORD_DT >= 6월 1일' 에 이미 들어 있다
-- 상한(PAY_DT < 7월 1일)은 남긴다 : 앞으로 늦게 결제된 건은 여기서 걸러진다
SELECT o.ORD_SEQ, o.ORD_DT, o.PAY_DT, o.PAY_AMT, SUM(d.ORD_QTY * d.UNT_PRC) AS det_amt
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'
   AND o.PAY_DT <  DATE '2024-07-01'
   AND o.PAY_TP = 'CARD'
   AND d.ORD_SEQ = o.ORD_SEQ
 GROUP BY o.ORD_SEQ, o.ORD_DT, o.PAY_DT, o.PAY_AMT;

-- 규칙을 데이터베이스에 적어 둔다 (지금 데이터는 위반 0행)
ALTER TABLE T_ORD ADD CONSTRAINT CK_T_ORD_PAY_DT CHECK (PAY_DT >= TRUNC(ORD_DT));

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |   1370 |00:00:00.02 |    1792 |          |
|   1 |  HASH GROUP BY      |           |      1 |   1026 |   1370 |00:00:00.02 |    1792 | 1180K (0)|
|*  2 |   HASH JOIN         |           |      1 |   2052 |   2740 |00:00:00.02 |    1792 | 1336K (0)|
|*  3 |    TABLE ACCESS FULL| T_ORD     |      1 |   1026 |   1370 |00:00:00.01 |     896 |          |
|   4 |    TABLE ACCESS FULL| T_ORD_DET |      1 |    200K|    200K|00:00:00.01 |     896 |          |
---------------------------------------------------------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   3 - filter(("O"."PAY_TP"='CARD' AND
              "O"."ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."PAY_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

손으로 계산하면 이렇다. 6월 한 달은 주문일 범위(약 731일)의 30/731 = 0.0410이고, 결제일 범위(729일)에서는 30/729 + 1/730 = 0.0425다. 카드는 1/3이다. 옵티마이저는 세 조건이 서로 상관없다고 보고(독립 가정) 곱해서 100,000 × 0.0410 × 0.0425 × 1/3 = 58로 봤다. 그러나 이 테이블의 결제는 모두 주문 당일이라 6월에 주문한 건은 결제일도 6월이다. 결제일 조건은 아무것도 거르지 않는데, 옵티마이저는 같은 조건을 두 번 곱한 셈이다. 실제는 4,110 × 1/3 = 1,370이다. 58건이라고 믿은 옵티마이저는 주문마다 상세를 찾아가는 NL을 골랐다. 5번 줄의 Starts가 1,370이고, 상세 인덱스와 테이블에서만 4,110블록을 읽었다. 두 테이블을 한 번씩 읽는 해시 조인이면 1,792블록이다. 결제일은 주문일보다 앞설 수 없으므로 PAY_DT >= 6월 1일은 ORD_DT >= 6월 1일에 이미 들어 있다. 이 하한을 지우고 상한(PAY_DT < 7월 1일)만 남기면 결과는 같다(앞으로 늦게 결제되는 건은 상한이 거른다). 남은 상한의 선택도는 0.75라 E-Rows가 1,026이 되고 해시 조인을 고른다. Buffers는 5,006 → 1,792다. 지운 조건이 안전하다는 근거는 규칙이므로 CHECK (PAY_DT >= TRUNC(ORD_DT))로 데이터베이스에도 적어 둔다. 컬럼 그룹 통계((ORD_DT, PAY_DT))는 이 경우를 고치지 못한다. 컬럼 그룹은 등치 조건과 IN 목록에만 쓰이고, 이 SQL처럼 두 열에 범위 조건이 걸리면 쓰이지 않는다. 그래서 만들어도 E-Rows는 58 그대로다.

해설

SQL을 고칠 수 없다면 /*+ DYNAMIC_SAMPLING(o 4) */ 힌트가 답이 된다. 수준 4는 한 테이블에 열이 둘 이상인 조건이 있으면 표본을 떠서 그 조건을 통째로 센다. 대가는 하드 파싱마다 표본을 읽는 시간과, 표본이라 실행마다 조금씩 다른 숫자다.
카드 조건까지 지우면 안 된다. 결제 방법은 주문일과 상관이 없어서(1/3씩) 독립 가정이 맞는 조건이다. 상관이 있는 조건만 골라 정리한다.
히스토그램도 고치지 못한다. 히스토그램은 열 하나의 분포라 열과 열 사이의 관계를 모른다(교재 10화 §8).
CHECK 제약을 걸 때는 지금 데이터가 규칙을 지키는지 먼저 센다. 결제 연동이 바뀌어 주문 전에 결제일이 찍히는 건이 생기면 제약이 그 INSERT를 막는다. 그것이 이 제약을 거는 이유이기도 하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황