포인트 내역에서 사용과 적립이 같은 초에 찍힌 두 줄은 잔액이 똑같이 나온다

중급SELECT이커머스358 / 700

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

상황

고객센터 「포인트 내역」 화면은 고객 한 명의 포인트 거래를 최신순으로 보여 주고, 줄마다 그 거래 직후의 잔액을 붙인다. 상담원 40명이 하루 약 3만 번 연다. 상담원이 캡처를 보내왔다. "주문할 때 포인트를 쓰고 같은 주문으로 적립도 받은 고객인데, 사용 줄과 적립 줄의 잔액이 똑같아요. 사용 직후 잔액이 안 보여서 설명을 못 했어요." 주문을 확정하는 프로시저가 사용과 적립을 같은 초에 넣는다.

테이블과 인덱스

point_transactions: 45,000행 · 210블록 · tx_id(시퀀스로 넣는 순서대로 매긴다) · customer_id · amount(늘 양수) · tx_type(EARN · USE · EXPIRE) · created_at DATE(초까지)
고객 5,230명, 고객당 평균 8.6건이고 많은 고객은 200건이 넘는다. 같은 고객의 같은 초 거래가 3,108쌍 있다.
잔액은 EARN은 더하고 USE와 EXPIRE는 뺀 누적이다. 화면의 잔액은 고객의 첫 거래부터 계산한다.
인덱스는 PK(tx_id)뿐이다. 통계는 최신이다.

원본 SQL

sql

SELECT tx_id, tx_type, amount, created_at,
       SUM(CASE tx_type WHEN 'EARN' THEN amount ELSE -amount END)
         OVER (ORDER BY created_at) AS balance
  FROM point_transactions
 WHERE customer_id = :cus
 ORDER BY created_at DESC

실행계획 학습용 예시

xplan

[고객 4417 · 거래 12건]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |      1 |        |     12 |00:00:00.01 |     210 |          |
|   1 |  SORT ORDER BY      |                    |      1 |      9 |     12 |00:00:00.01 |     210 | 2048 (0) |
|   2 |   WINDOW SORT       |                    |      1 |      9 |     12 |00:00:00.01 |     210 | 2048 (0) |
|*  3 |    TABLE ACCESS FULL| POINT_TRANSACTIONS |      1 |      9 |     12 |00:00:00.01 |     210 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUSTOMER_ID"=:CUS)

[화면 · 위가 최신]
  TX_ID   TX_TYPE  AMOUNT  CREATED_AT            BALANCE
  41207   EARN        320  2024-06-14 13:02:11     1,870
  41206   USE       1,000  2024-06-14 13:02:11     1,870   ← 사용 직후 잔액은 1,550이어야 한다
  38810   EARN        450  2024-05-30 19:44:02     2,550
  …

문제

사용 줄과 적립 줄의 잔액이 같게 나오는 까닭을 분석 함수의 창(window)으로 설명하시오.
줄마다 그 거래 직후의 잔액이 나오도록 SQL을 고치고, 화면을 열 때마다 테이블 전체를 읽고 정렬하지 않도록 인덱스를 설계하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 한 명의 거래가 시각, 거래번호 순으로 줄 선 인덱스 : 읽는 순서가 곧 누적 순서
CREATE INDEX IX_PT_CUS_DT ON point_transactions (customer_id, created_at, tx_id);

SELECT tx_id, tx_type, amount, created_at,
       SUM(CASE tx_type WHEN 'EARN' THEN amount ELSE -amount END)
         OVER (ORDER BY created_at, tx_id
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
  FROM point_transactions
 WHERE customer_id = :cus
 ORDER BY created_at DESC, tx_id DESC

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |      1 |        |     12 |00:00:00.01 |      14 |          |
|   1 |  SORT ORDER BY                |                    |      1 |      9 |     12 |00:00:00.01 |      14 | 2048 (0) |
|   2 |   WINDOW BUFFER               |                    |      1 |      9 |     12 |00:00:00.01 |      14 | 2048 (0) |
|   3 |    TABLE ACCESS BY INDEX ROWID| POINT_TRANSACTIONS |      1 |      9 |     12 |00:00:00.01 |      14 |          |
|*  4 |     INDEX RANGE SCAN          | IX_PT_CUS_DT       |      1 |      9 |     12 |00:00:00.01 |       2 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("CUSTOMER_ID"=:CUS)

[화면]  41207 EARN 320 → 1,870 · 41206 USE 1,000 → 1,550 · 38810 EARN 450 → 2,550

모범 답안 서술

분석 함수에 ORDER BY만 주고 창을 적지 않으면 창은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 된다. RANGE는 정렬 값이 같은 줄을 한 묶음(동료 줄)으로 보므로, 같은 초에 찍힌 사용(41206)과 적립(41207)은 서로를 함께 더한 누적값을 받는다. 그래서 두 줄 모두 적립까지 끝난 잔액 1,870이 나오고, 사용 직후의 1,550은 어디에도 나오지 않는다. 같은 고객의 같은 초 거래 3,108쌍이 모두 이렇게 보인다. 고친 SQL은 정렬 키에 tx_id를 붙여 순서를 유일하게 하고 창을 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 적는다. 줄 단위로 위에서부터 더하므로 41206은 1,550, 41207은 1,870이 된다. 같은 초 안의 순서는 넣은 순서대로 매기는 tx_id가 정한다. 화면의 최신순 정렬도 created_at DESC, tx_id DESC로 맞춘다. 성능 쪽에서는 customer_id에 인덱스가 없어 화면을 열 때마다 테이블 전체(210블록)를 읽고 고객 한 명의 거래를 골라 정렬했다. (customer_id, created_at, tx_id) 인덱스를 만들면 고객 범위를 시각과 거래번호 순서 그대로 읽으므로 누적을 계산할 때 정렬이 필요 없다(WINDOW BUFFER). 최신순으로 뒤집는 정렬만 고객의 거래 수만큼 남는다. Buffers는 210 → 14이고, 하루 3만 번이면 약 630만 블록이 약 42만 블록으로 준다.

해설

ORDER BY에 tx_id만 붙여도 된다: 정렬 키가 유일하면 RANGE의 동료 줄이 자기 자신뿐이라 ROWS와 같은 결과가 나온다. 그래도 창을 ROWS로 적어 두면 뜻이 코드에 남는다.
PARTITION BY: 지금은 WHERE 절이 고객 한 명으로 거르므로 PARTITION BY가 없어도 된다. 여러 고객을 한 번에 내는 SQL(예: 잔액 대사 배치)로 옮기면 PARTITION BY customer_id가 꼭 있어야 한다.
최근 20건만 보여 주는 화면이라면: 잔액은 첫 거래부터 더해야 하므로 누적은 고객의 전체 거래로 계산하고 바깥에서 20건을 자른다. 먼저 20건을 자르면 잔액이 0부터 다시 시작한다.
인덱스에 tx_type과 amount까지 넣으면 테이블을 읽지 않고 2 ~ 3블록으로 끝나지만, 거래가 들어올 때마다 고칠 인덱스 항목이 커진다. 하루 3만 번이면 14블록으로도 충분하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황