PART-015

포인트 소멸 안내 배치의 실행계획에 Pstop 1048575가 찍힌다

기초INSERT이커머스390 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스의 적립 포인트는 적립일로부터 365일이 지나면 소멸한다. 「소멸 예정 안내」 배치는 매일 새벽 7일 뒤에 소멸될 적립 건을 골라 안내 대상 테이블에 넣는다. 포인트 거래가 빠르게 늘 것으로 보고 지난달 월별 INTERVAL 파티션 복사본으로 옮겼다. 배치 기준일은 DATE 바인드 :v_base(그날 0시)로 넘긴다. 아래는 :v_base = 2024-12-01의 실행이다.

테이블과 인덱스

point_transactions_p 포인트 거래(파티션 복사본, 문제 설정): point_transactions 45,000행을 PARTITION BY RANGE (created_at) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))로 나눠 CTAS로 지었다(STORAGE (INITIAL 64K)). 2020년 1월 ~ 2024년 12월의 60칸이 있고, 칸 하나는 약 750행이며 FULL로 약 4 ~ 5블록, 60칸 전체는 약 270블록이다.
created_at은 DATE이고 시분초가 있다. tx_type은 EARN, USE, EXPIRE 세 가지이고, 하루 적립(EARN)은 약 15건이다.
point_expire_notice 안내 대상(문제 설정): 배치가 매일 비우고 다시 넣는다. 인덱스는 없다.
point_transactions_p의 인덱스는 PK(tx_id)뿐이다. 통계는 최신이다.

원본 SQL

sql

INSERT INTO point_expire_notice (customer_id, tx_id, amount, expire_dt)
SELECT customer_id, tx_id, amount, :v_base + 7
  FROM point_transactions_p
 WHERE tx_type = 'EARN'
   AND TRUNC(created_at) + 365 = :v_base + 7

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart |   Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                      |      1 |        |      0 | 00:00:00.01 |     284 |        |         |
|   1 |  LOAD TABLE CONVENTIONAL | POINT_EXPIRE_NOTICE  |      1 |        |      0 | 00:00:00.01 |     284 |        |         |
|   2 |   PARTITION RANGE ALL    |                      |      1 |    150 |     16 | 00:00:00.01 |     270 |      1 | 1048575 |
|*  3 |    TABLE ACCESS FULL     | POINT_TRANSACTIONS_P |     60 |    150 |     16 | 00:00:00.01 |     270 |      1 | 1048575 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("TX_TYPE"='EARN' AND TRUNC(INTERNAL_FUNCTION("CREATED_AT"))+365=:V_BASE+7))

문제

Pstop의 1048575가 무엇을 뜻하는지 쓰고, 이 배치가 칸을 전부 여는 까닭을 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 적립일 하루가 든 칸만 읽게 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 365일 뒤가 :v_base + 7 인 적립일 D = :v_base - 358 → created_at 이 D 이상, D + 1 미만
INSERT INTO point_expire_notice (customer_id, tx_id, amount, expire_dt)
SELECT customer_id, tx_id, amount, :v_base + 7
  FROM point_transactions_p
 WHERE tx_type = 'EARN'
   AND created_at >= :v_base - 358
   AND created_at <  :v_base - 357

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

xplan

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT            |                      |      1 |        |      0 | 00:00:00.01 |      19 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL    | POINT_EXPIRE_NOTICE  |      1 |        |      0 | 00:00:00.01 |      19 |        |       |
|*  2 |   FILTER                    |                      |      1 |        |     16 | 00:00:00.01 |       5 |        |       |
|   3 |    PARTITION RANGE ITERATOR |                      |      1 |      8 |     16 | 00:00:00.01 |       5 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | POINT_TRANSACTIONS_P |      1 |      8 |     16 | 00:00:00.01 |       5 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_BASE-358<:V_BASE-357)
   4 - filter(("TX_TYPE"='EARN' AND "CREATED_AT">=:V_BASE-358 AND "CREATED_AT"<:V_BASE-357))

  (실행 때 고른 칸 — 2023년 12월 · :v_base = 2024-12-01 → 적립일 2023-12-09)

모범 답안 서술

2번 줄이 PARTITION RANGE ALL이고 Pstart 1, Pstop 1048575다. INTERVAL 파티션은 칸이 저절로 생기므로, 계획은 앞으로 생길 수 있는 칸 번호의 끝(1048575)까지 잡는다. RANGE ALL과 함께 찍힌 1048575는 '있는 칸을 전부 연다'는 뜻이다. 실제로 3번 줄의 Starts가 60이라서 지금 있는 60칸을 다 읽었다(270블록). 고를 적립일은 하루뿐이다. 까닭은 조건 TRUNC(created_at) + 365 = :v_base + 7이 파티션 키 created_at을 TRUNC로 감싸고 365를 더했다는 데 있다. 칸막이 경계는 created_at 값에 붙어 있으므로, 계산을 거친 값으로는 어느 칸인지 고르지 못한다. 계산을 바인드 쪽으로 옮긴다. TRUNC(created_at) = :v_base + 7 - 365 = :v_base - 358이고, TRUNC(x) = D는 x >= D AND x < D + 1과 같으므로 created_at >= :v_base - 358 AND created_at < :v_base - 357로 쓴다. 시분초가 있어서 상한은 다음 날 0시 미만으로 준다. 그러면 Pstart와 Pstop이 KEY로 찍히고 2023년 12월 칸 하나만 읽는다(Starts 1). 테이블을 읽는 몫은 270 → 5블록으로 준다.

해설

계산을 옮기는 규칙: 컬럼 + 상수 = 값은 컬럼 = 값 - 상수로 바꾼다. 날짜에 일 수를 더하고 빼는 계산은 정확히 옮겨진다. ADD_MONTHS(created_at, 12)처럼 월 단위 함수는 말일에서 거꾸로 정확히 풀리지 않으므로(1월 30일과 1월 31일 모두 한 달 뒤가 2월 말일이 된다) 옮길 때 경계를 따로 확인한다.
지금은 270블록이라 티가 나지 않는다. 하지만 INTERVAL 파티션은 달마다 칸이 하나씩 늘어 전부 여는 SQL의 일량도 함께 커진다. 키를 맨몸으로 두면 칸이 늘어도 하루치 일량은 그대로다.
판독(교재 14화 §6, §7.1): INTERVAL 테이블의 ALL은 1 / 1048575로 찍힌다. 숫자가 커서 놀라지만 실제로 연 칸 수는 Starts에서 읽는다.
컬럼 산술이 인덱스를 막는 것과 같은 원리다(교재 02화 §3).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황