PART-014

결제일 하루치 정산이 주문 테이블을 통째로 읽는다

실전SELECT한빛유통389 / 700

상황

결제팀의 「결제일 기준 일 정산」은 결제일 하루를 골라 결제수단별 건수와 금액을 낸다. 결제팀 화면과 정산 대사 배치에서 하루 약 1,500번 호출된다. 지난달 T_ORD를 주문일 기준 월별 파티션 T_ORD_P로 옮긴 뒤 다른 화면은 빨라졌는데 이 조회만 그대로다. 결제팀 규정상 주문 후 72시간 안에 결제되지 않은 주문은 자동 취소 배치가 CANCEL로 바꾸고, 그 뒤에는 결제를 받지 않는다.

테이블과 인덱스

T_ORD_P 주문(파티션 복사본, 문제 설정): T_ORD 100,000행을 PARTITION BY RANGE (ORD_DT) 월별 24칸(P202301 ~ P202412)과 빈 PMAX로 나눠 CTAS로 지었다(STORAGE (INITIAL 64K)). 칸 하나는 약 4,170행이고 FULL로 약 38블록, 24칸 전체는 약 912블록이다.
ORD_DT는 시분초가 있다. PAY_DT는 결제 시각이고 결제 전이면 NULL이다. 실측한 MAX(PAY_DT - ORD_DT)는 2.97일이다. 결제일 하루는 약 137건이다.
PAY_TP는 결제수단 4종, PAY_AMT는 결제 금액이다.
인덱스는 LOCAL PK_T_ORD_P (ORD_SEQ, ORD_DT)뿐이다. 통계는 최신이다.
매달 1일 보관 기간(24개월)이 지난 달을 DROP PARTITION으로 정리한다.

원본 SQL

sql

SELECT PAY_TP, COUNT(*) AS pay_cnt, SUM(PAY_AMT) AS pay_amt
  FROM T_ORD_P
 WHERE PAY_DT >= TO_DATE(:v_pay_dt, 'YYYYMMDD')
   AND PAY_DT <  TO_DATE(:v_pay_dt, 'YYYYMMDD') + 1
 GROUP BY PAY_TP

실행계획 학습용 예시

xplan

[:v_pay_dt = '20240612']
--------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |      4 | 00:00:00.02 |     912 |        |       |
|   1 |  HASH GROUP BY         |         |      1 |      4 |      4 | 00:00:00.02 |     912 |        |       |
|*  2 |   FILTER               |         |      1 |        |    137 | 00:00:00.02 |     912 |        |       |
|   3 |    PARTITION RANGE ALL |         |      1 |    250 |    137 | 00:00:00.02 |     912 |      1 |    25 |
|*  4 |     TABLE ACCESS FULL  | T_ORD_P |     24 |    250 |    137 | 00:00:00.02 |     912 |      1 |    25 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_PAY_DT,'YYYYMMDD')<TO_DATE(:V_PAY_DT,'YYYYMMDD')+1)
   4 - filter(("PAY_DT">=TO_DATE(:V_PAY_DT,'YYYYMMDD') AND "PAY_DT"<TO_DATE(:V_PAY_DT,'YYYYMMDD')+1))

문제

이 조회가 칸을 전부 여는 까닭을 실행계획과 파티션 키로 설명하시오.
세 가지 안(주문일 조건을 더한다, PAY_DT에 LOCAL 인덱스를 만든다, PAY_DT에 GLOBAL 인덱스를 만든다)을 SQL과 DDL로 쓰고, 읽는 블록 수와 부작용을 비교하시오.
권하는 안을 고르고, 그 안이 결과를 바꾸지 않으려면 무엇이 지켜져야 하며 그것을 어떻게 확인하고 지킬지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 안 A (권함) : 결제는 주문 후 72시간 안에만 들어온다 → 결제일 D 의 주문일은 D-3 이상, D+1 미만
SELECT PAY_TP, COUNT(*) AS pay_cnt, SUM(PAY_AMT) AS pay_amt
  FROM T_ORD_P
 WHERE PAY_DT >= TO_DATE(:v_pay_dt, 'YYYYMMDD')
   AND PAY_DT <  TO_DATE(:v_pay_dt, 'YYYYMMDD') + 1
   AND ORD_DT >= TO_DATE(:v_pay_dt, 'YYYYMMDD') - 3
   AND ORD_DT <  TO_DATE(:v_pay_dt, 'YYYYMMDD') + 1
 GROUP BY PAY_TP;

-- 안 A 의 전제를 DB 가 지키게 한다 (규칙을 어기는 결제는 들어오지 못한다)
ALTER TABLE T_ORD_P ADD CONSTRAINT CK_T_ORD_P_PAY
  CHECK (PAY_DT IS NULL OR PAY_DT < ORD_DT + 3);

-- 안 B : CREATE INDEX IX_T_ORD_P_PAY   ON T_ORD_P (PAY_DT) LOCAL;
-- 안 C : CREATE INDEX IX_T_ORD_P_PAY_G ON T_ORD_P (PAY_DT);        -- 월 정리에 UPDATE GLOBAL INDEXES

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

xplan

[안 A — :v_pay_dt = '20240612' → 주문일 6월 9일 ~ 12일, 칸 하나]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      4 | 00:00:00.01 |      38 |        |       |
|   1 |  HASH GROUP BY              |         |      1 |      1 |      4 | 00:00:00.01 |      38 |        |       |
|*  2 |   FILTER                    |         |      1 |        |    137 | 00:00:00.01 |      38 |        |       |
|   3 |    PARTITION RANGE ITERATOR |         |      1 |      1 |    137 | 00:00:00.01 |      38 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_P |      1 |      1 |    137 | 00:00:00.01 |      38 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

   4 - filter(("PAY_DT">=TO_DATE(:V_PAY_DT,'YYYYMMDD') AND "PAY_DT"<TO_DATE(:V_PAY_DT,'YYYYMMDD')+1 AND
              "ORD_DT">=TO_DATE(:V_PAY_DT,'YYYYMMDD')-3 AND "ORD_DT"<TO_DATE(:V_PAY_DT,'YYYYMMDD')+1))

[세 안 어림 — 결제일 하루 137건 · 하루 1,500번]
  안   읽는 곳                                      블록/번      하루
  A    주문일 조건 · 칸 1개 (월초 사흘은 2개)         38 ~ 76     약 6.3만
  B    LOCAL (PAY_DT) · 인덱스 24번 + 테이블          약 65       약 9.8만
  C    GLOBAL (PAY_DT) · 인덱스 1번 + 테이블          약 18       약 2.7만
  지금 24칸 FULL                                     912        약 137만

모범 답안 서술

3번 줄이 PARTITION RANGE ALL(Pstart 1, Pstop 25)이고 4번 줄의 Starts가 24다. 파티션 키는 ORD_DT인데 조건은 PAY_DT뿐이라서 칸을 고를 단서가 없다. 결제일 하루 137건을 내려고 24칸 912블록을 모두 읽었고, 하루 1,500번이면 약 137만 블록이다. 안 A는 결제일 범위에서 주문일 범위를 끌어내 더하는 방법이다. 결제는 주문 후 72시간 안에만 들어오므로, 결제일이 D인 주문은 D - 3일 이상, D + 1일 미만에 주문되었다. ORD_DT >= D - 3 AND ORD_DT < D + 1을 더하면 Pstart와 Pstop이 KEY로 찍히고 칸 하나(월초 사흘은 두 칸)만 연다. 912 → 38블록(월초는 76블록)이고 하루 약 6.3만 블록이다. DDL이 없고 다른 작업에 주는 영향도 없다. 안 B는 PAY_DT에 LOCAL 인덱스를 만드는 방법이다. 규칙에 기대지 않지만, 파티션 키가 없는 조건이라(nonprefixed) 칸마다 인덱스를 한 번씩, 24번 찾아 약 65블록을 읽는다. 칸을 정리할 때 같이 사라져 관리는 쉽지만, 결제할 때마다(PAY_DT 갱신) 인덱스를 고친다. 안 C는 GLOBAL 인덱스다. 인덱스를 한 번만 찾아 약 18블록으로 가장 적게 읽는다. 하지만 매달 1일의 DROP PARTITION에 UPDATE GLOBAL INDEXES를 붙여야 하고, 빠뜨리면 인덱스가 UNUSABLE이 되어 이 조회가 에러 없이 912블록으로 돌아간다. 결제 갱신마다 인덱스를 고치는 비용도 든다. 권하는 안은 A다. 하루 약 137만 → 약 6.3만 블록으로 줄고 DDL이 없다. 대신 결과가 바뀌지 않으려면 '결제는 주문 후 72시간 안'이라는 규칙이 늘 지켜져야 한다. 규칙을 어긴 결제가 하나라도 있으면 정산에서 조용히 빠진다. 그래서 지금 데이터로 MAX(PAY_DT - ORD_DT)(2.97일)를 확인하고, CHECK (PAY_DT IS NULL OR PAY_DT < ORD_DT + 3) 제약으로 DB가 그 규칙을 지키게 한다. 규칙을 보장할 수 없다면 B를 쓴다.

해설

파티션 키가 없는 조건의 처방: 조건 컬럼과 파티션 키 사이에 수식이나 업무 규칙이 있으면 키 조건을 끌어내 더한다. 버거의 ORD_DT와 ORD_YM처럼 수식(SUBSTR(ORD_DT, 1, 6))으로 정해지면 위험이 없고, PAY_DT처럼 규칙으로 정해지면 규칙이 깨지는 순간 건이 빠진다. 돈을 세는 조회라서 규칙을 제약으로 박는다.
CHECK 제약의 대가: 자동 취소 직전에 늦게 들어온 결제는 ORA-02290으로 거부된다. 결제 연동 쪽과 '72시간이 지난 결제는 받지 않는다'를 먼저 합의한다. 규칙이 7일로 바뀌면 SQL의 - 3과 제약을 함께 고친다.
A와 B를 함께 쓰면(주문일 조건과 LOCAL 인덱스) 인덱스도 한두 칸에서만 찾아 약 20블록이 된다. 호출이 더 늘어나면 그때 검토한다.
PAY_DT를 파티션 키로 바꾸는 안은 맞지 않는다. 결제 전에는 NULL이고 결제할 때 값이 바뀌어 행이 칸을 옮겨 다닌다(ROW MOVEMENT). 다른 화면은 주문일로 조회한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황