회계 감사용 포인트 원장 내려받기의 2024년 적립 합계가 18,500포인트 모자란다

실전SELECT이커머스304 / 700

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

상황

회계 감사에서 「포인트 원장」 내려받기의 2024년 적립 합계가 point_transactions를 직접 더한 값보다 18,500포인트 적다는 지적이 나왔다. 원인을 찾던 DBA는 SQL의 UNION을 보고 "UNION이 들어간 SQL을 전부 UNION ALL로 바꾸자. 정렬 비용도 준다"라고 제안했다. 지금 UNION을 쓰는 SQL은 아래 셋이다. 박팀장은 SQL마다 바꿔도 되는지, 바꾸면 무엇이 달라지는지 정리해 달라고 했다.

테이블과 인덱스

point_transactions: 45,000행 · 210블록 · tx_id(PK) · customer_id · amount · tx_type(EARN · USE · EXPIRE) · created_at · 2024년 9,120건(EARN 5,880 · USE 2,760 · EXPIRE 480) · 이벤트 적립은 배치가 같은 초에 같은 금액(500)으로 한꺼번에 넣는다
customers: 5,230행 · 51블록 · reviews: 15,000행 · 775블록 · orders: 34,908행 · 193블록 · status 6가지(PENDING · PAID · SHIPPED · DELIVERED · CANCELLED · RETURNED) · 모두 PK만 있다
2024년 11월: 리뷰 268건(고객 231명) · 주문 720건(고객 652명) · 리뷰와 주문이 다 있는 고객 118명 · 취소 29건 · 반품 14건
통계 최신

원본 SQL

sql

-- S1 「11월 활동 고객」 마케팅 문자 대상 (리뷰를 썼거나 주문한 고객)
SELECT c.customer_id, c.name, c.email
  FROM customers c, reviews r
 WHERE r.customer_id = c.customer_id
   AND r.created_at >= DATE '2024-11-01' AND r.created_at < DATE '2024-12-01'
UNION
SELECT c.customer_id, c.name, c.email
  FROM customers c, orders o
 WHERE o.customer_id = c.customer_id
   AND o.order_date >= DATE '2024-11-01' AND o.order_date < DATE '2024-12-01';

-- S2 「포인트 원장」 2024년 내려받기 (회계 감사 자료)
SELECT customer_id, created_at, '적립' AS tp, amount FROM point_transactions
 WHERE tx_type = 'EARN'   AND created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01'
UNION
SELECT customer_id, created_at, '사용', amount FROM point_transactions
 WHERE tx_type = 'USE'    AND created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01'
UNION
SELECT customer_id, created_at, '소멸', amount FROM point_transactions
 WHERE tx_type = 'EXPIRE' AND created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01'
 ORDER BY 1, 2;

-- S3 「취소 · 반품 주문」 월 목록
SELECT order_id, customer_id, order_date, status, total_amount FROM orders
 WHERE status = 'CANCELLED' AND order_date >= :d1 AND order_date < :d2
UNION
SELECT order_id, customer_id, order_date, status, total_amount FROM orders
 WHERE status = 'RETURNED'  AND order_date >= :d1 AND order_date < :d2;

실행계획 학습용 예시

xplan

[S1]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    765 | 00:00:00.02 |    1070 |
|   1 |  SORT UNIQUE           |           |      1 |    950 |    765 | 00:00:00.02 |    1070 |
|   2 |   UNION-ALL            |           |      1 |        |    988 | 00:00:00.01 |    1070 |
|*  3 |    HASH JOIN           |           |      1 |    250 |    268 | 00:00:00.01 |     826 |
|*  4 |     TABLE ACCESS FULL  | REVIEWS   |      1 |    250 |    268 | 00:00:00.01 |     775 |
|   5 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |   5230 |   5230 | 00:00:00.01 |      51 |
|*  6 |    HASH JOIN           |           |      1 |    700 |    720 | 00:00:00.01 |     244 |
|*  7 |     TABLE ACCESS FULL  | ORDERS    |      1 |    700 |    720 | 00:00:00.01 |     193 |
|   8 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |   5230 |   5230 | 00:00:00.01 |      51 |
-----------------------------------------------------------------------------------------------

[S2]
-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |      1 |        |   9083 | 00:00:00.03 |     630 |
|   1 |  SORT UNIQUE          |                    |      1 |   9100 |   9083 | 00:00:00.03 |     630 |
|   2 |   UNION-ALL           |                    |      1 |        |   9120 | 00:00:00.01 |     630 |
|*  3 |    TABLE ACCESS FULL  | POINT_TRANSACTIONS |      1 |   5900 |   5880 | 00:00:00.01 |     210 |
|*  4 |    TABLE ACCESS FULL  | POINT_TRANSACTIONS |      1 |   2700 |   2760 | 00:00:00.01 |     210 |
|*  5 |    TABLE ACCESS FULL  | POINT_TRANSACTIONS |      1 |    500 |    480 | 00:00:00.01 |     210 |
-------------------------------------------------------------------------------------------------------

[S3 · 2024년 11월]
-------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |     43 | 00:00:00.01 |     386 |
|   1 |  SORT UNIQUE          |        |      1 |     44 |     43 | 00:00:00.01 |     386 |
|   2 |   UNION-ALL           |        |      1 |        |     43 | 00:00:00.01 |     386 |
|*  3 |    TABLE ACCESS FULL  | ORDERS |      1 |     29 |     29 | 00:00:00.01 |     193 |
|*  4 |    TABLE ACCESS FULL  | ORDERS |      1 |     15 |     14 | 00:00:00.01 |     193 |
-------------------------------------------------------------------------------------------

[S2 대조]  적립 합계: 내려받기 2,921,500 · point_transactions 2,940,000 (차이 18,500)

문제

2024년 적립 합계가 18,500포인트 모자란 이유를 S2의 실행계획으로 설명하시오.
DBA의 제안대로 세 SQL의 UNION을 UNION ALL로 바꾸면 SQL마다 결과가 어떻게 달라지는지 판단하시오.
세 SQL을 각각 맞는 결과를 내도록 고치고, 고친 뒤 결과가 맞는지 무엇으로 대조할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- S1 : 중복 제거가 요구다 (고객 목록). 넓은 행을 정렬하지 말고, 고객번호만 모아 세미 조인이 걸러 주게 한다
SELECT c.customer_id, c.name, c.email
  FROM customers c
 WHERE c.customer_id IN (SELECT r.customer_id FROM reviews r
                          WHERE r.created_at >= DATE '2024-11-01' AND r.created_at < DATE '2024-12-01'
                         UNION ALL
                         SELECT o.customer_id FROM orders o
                          WHERE o.order_date >= DATE '2024-11-01' AND o.order_date < DATE '2024-12-01');

-- S2 : 같은 값의 행이 정상인 원장. 한 번 읽고 구분은 값으로, 행을 구별하는 tx_id 를 함께 낸다
SELECT p.customer_id, p.tx_id, p.created_at,
       DECODE(p.tx_type, 'EARN', '적립', 'USE', '사용', 'EXPIRE', '소멸') AS tp,
       p.amount
  FROM point_transactions p
 WHERE p.tx_type IN ('EARN', 'USE', 'EXPIRE')
   AND p.created_at >= DATE '2024-01-01' AND p.created_at < DATE '2025-01-01'
 ORDER BY p.customer_id, p.created_at, p.tx_id;

-- S3 : 겹치지 않는 두 상태 (UNION ALL 로 바꿔도 같다) → 한 번 읽기
SELECT order_id, customer_id, order_date, status, total_amount
  FROM orders
 WHERE status IN ('CANCELLED', 'RETURNED')
   AND order_date >= :d1 AND order_date < :d2;

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

xplan

[S1 고친 뒤]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    765 | 00:00:00.01 |    1019 |
|*  1 |  HASH JOIN RIGHT SEMI  |           |      1 |    950 |    765 | 00:00:00.01 |    1019 |
|   2 |   VIEW                 | VW_NSO_1  |      1 |    950 |    988 | 00:00:00.01 |     968 |
|   3 |    UNION-ALL           |           |      1 |        |    988 | 00:00:00.01 |     968 |
|*  4 |     TABLE ACCESS FULL  | REVIEWS   |      1 |    250 |    268 | 00:00:00.01 |     775 |
|*  5 |     TABLE ACCESS FULL  | ORDERS    |      1 |    700 |    720 | 00:00:00.01 |     193 |
|   6 |   TABLE ACCESS FULL    | CUSTOMERS |      1 |   5230 |   5230 | 00:00:00.01 |      51 |
-----------------------------------------------------------------------------------------------

[S2 고친 뒤]
------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Starts | E-Rows | A-Rows |      A-Time | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |      1 |        |   9120 | 00:00:00.01 |     210 |
|   1 |  SORT ORDER BY       |                    |      1 |   9100 |   9120 | 00:00:00.01 |     210 |
|*  2 |   TABLE ACCESS FULL  | POINT_TRANSACTIONS |      1 |   9100 |   9120 | 00:00:00.01 |     210 |
------------------------------------------------------------------------------------------------------

[S3 고친 뒤]
-----------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |      1 |        |     43 | 00:00:00.01 |     193 |
|*  1 |  TABLE ACCESS FULL  | ORDERS |      1 |     44 |     43 | 00:00:00.01 |     193 |
-----------------------------------------------------------------------------------------

[대조]  S1 765명 = 765명 · S2 9,120행 · 적립 합계 2,940,000 = point_transactions 2,940,000 · S3 43건 = 43건

모범 답안 서술

S2는 적립, 사용, 소멸을 갈래로 나눠 point_transactions를 세 번 읽고(210블록씩), 이어 붙인 9,120행을 1번 줄 SORT UNIQUE로 정렬해 9,083행을 냈다. UNION은 선택한 칸(고객, 시각, 구분, 금액)이 모두 같은 행을 하나로 친다. 이벤트 적립은 배치가 같은 초에 같은 금액으로 넣으므로, 한 고객이 같은 초에 이벤트 두 개로 500포인트씩 받으면 두 행이 한 행이 된다. 2024년에 그렇게 사라진 적립이 37건이라 37 × 500 = 18,500포인트가 모자랐다. 같은 값의 행이 여러 번 있는 것이 정상인 원장에 UNION을 쓴 것이 원인이다. DBA의 제안을 SQL마다 보면 결과가 셋으로 갈린다. S2는 UNION ALL로 바꾸면 37건이 돌아와 맞는 결과가 된다. 바꾸는 것이 맞다. S1은 바꾸면 틀린다. 11월에 리뷰도 쓰고 주문도 한 고객 118명은 두 갈래에 다 나오고, 리뷰를 두 번 쓰거나 주문을 여러 번 한 고객은 한 갈래 안에서도 여러 번 나온다. UNION이 이것을 765명으로 줄이고 있었는데, UNION ALL이면 988행이 되어 한 고객에게 문자가 여러 통 간다. 여기서는 중복 제거가 요구 그 자체다. S3는 바꿔도 결과가 같다. 두 갈래는 상태(취소, 반품)로 갈려 같은 주문이 나올 수 없고, order_id가 PK라 한 갈래 안에서도 같은 행이 없다. 바꾸면 할 필요가 없던 정렬만 사라진다. 고친 SQL은 이렇다. S1은 고객을 한 번 읽고 IN (리뷰 고객번호 UNION ALL 주문 고객번호)로 세미 조인한다. 고객번호만 이어 붙이고 중복은 세미 조인이 걸러 주므로, 이름과 이메일까지 담은 넓은 행을 정렬하지 않는다(1,070 → 1,019블록, 765명 그대로). S2는 갈래를 없애고 한 번 읽은 뒤 구분을 DECODE(tx_type, …)로 붙이고, 행마다 다른 tx_id를 함께 내보낸다(630 → 210블록, 9,120행). S3도 갈래를 없애고 status IN ('CANCELLED', 'RETURNED')로 한 번 읽는다(386 → 193블록, 43건). 대조는 건수와 합계로 한다. S2는 구분별 건수와 합계를 point_transactions를 직접 센 값과 맞추고(적립 차이 18,500 → 0), S1과 S3는 원래 SQL과 행 수를 맞춘다. MINUS는 쓰지 않는다. MINUS도 집합 연산이라 중복을 보지 못해서, S2의 원래 SQL과 고친 SQL(같은 네 칸)을 MINUS로 비교하면 양쪽 모두 0행이 나와 통과해 버린다.

해설

UNION이 맞는 경우는 '행'이 아니라 '대상'을 셀 때다: 고객 목록, 상품 목록처럼 중복 제거가 요구인 SQL에서는 UNION이 맞다. 다만 넓은 행 전체를 정렬하지 않도록, 키만 모아 중복을 걸러 주는 IN이나 EXISTS로 바꾸면 같은 결과를 더 싸게 낸다.
일괄 치환의 위험: 'UNION을 전부 UNION ALL로'는 정렬 비용만 보고 세운 규칙이다. 세 SQL 가운데 하나는 맞아지고, 하나는 그대로이며, 하나는 틀린다. 집합 연산을 바꿀 때는 SQL마다 '갈래가 겹칠 수 있는가', '한 갈래 안에 같은 행이 정상인가'를 따로 묻는다.
S2에 tx_id를 넣는 이유: 같은 초, 같은 금액의 두 적립이 화면에서 한 줄처럼 보이면 사용자가 중복으로 오해한다. 행을 구별하는 칸(거래번호)을 함께 보여 주면 두 건임이 드러난다.
교재 08화의 SUM(DISTINCT)와 MINUS의 맹점이 같은 원리다. 값이 같으면 하나로 치는 연산은 '같은 값의 서로 다른 행'을 지운다(교재 08화 §2, §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황