재구매 감사 쿠폰이 고객 한 명에게 두세 장씩 나갔다

기초INSERT이커머스261 / 700

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

상황

마케팅팀은 11월에 주문한 고객에게 감사 쿠폰을 한 장씩 주기로 했다. 개발자는 처음에 IN 서브쿼리로 짰다가 "조인이 더 빠르다"는 말을 듣고 조인으로 바꿔 배포했다. 발급 다음 날 고객센터에 "쿠폰이 세 장 왔다"는 문의가 들어왔다.

테이블과 인덱스

customers 고객: 5,230행 · 51블록 · PK(customer_id)만 있다
orders 주문: 34,908행 · 193블록 · PK(order_id)만 있고 customer_id에는 인덱스가 없다 · 2024년 11월 주문 582건, 주문한 고객 523명(두 번 주문 53명, 세 번 주문 3명)
coupon_issue 쿠폰 발급: 이번 행사를 위해 만든 테이블 (customer_id, coupon_cd, issued_at) · 제약과 인덱스 없음
통계 최신

원본 SQL

sql

INSERT INTO coupon_issue (customer_id, coupon_cd, issued_at)
SELECT c.customer_id, 'THX2411', SYSDATE
  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'

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |        |      0 |00:00:00.02 |     822 |
|   1 |  LOAD TABLE CONVENTIONAL | COUPON_ISSUE |      1 |        |      0 |00:00:00.02 |     822 |
|*  2 |   HASH JOIN              |              |      1 |    582 |    582 |00:00:00.01 |     244 |
|*  3 |    TABLE ACCESS FULL     | ORDERS       |      1 |    582 |    582 |00:00:00.01 |     193 |
|   4 |    TABLE ACCESS FULL     | CUSTOMERS    |      1 |   5230 |   5230 |00:00:00.01 |      51 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
   3 - filter(("O"."ORDER_DATE">=TO_DATE(' 2024-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDER_DATE"<TO_DATE(' 2024-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

문제

고객 523명에게 쿠폰 582장이 나간 이유를 실행계획의 2번 줄로 설명하시오.
고객 한 명에 쿠폰 한 장만 들어가도록 SQL을 고치고, 같은 실수가 다시 나와도 중복 발급을 막을 장치를 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 한 명에 한 줄: '11월에 주문이 있는가'만 묻는 세미 조인
INSERT INTO coupon_issue (customer_id, coupon_cd, issued_at)
SELECT c.customer_id, 'THX2411', SYSDATE
  FROM customers c
 WHERE EXISTS (SELECT 1
                 FROM 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');

-- 같은 쿠폰이 두 번 들어가지 못하게 테이블이 막는다 (이미 나간 중복 59장을 정리한 뒤)
ALTER TABLE coupon_issue ADD CONSTRAINT coupon_issue_uk UNIQUE (customer_id, coupon_cd);

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

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |        |      0 |00:00:00.01 |     763 |
|   1 |  LOAD TABLE CONVENTIONAL | COUPON_ISSUE |      1 |        |      0 |00:00:00.01 |     763 |
|*  2 |   HASH JOIN RIGHT SEMI   |              |      1 |    523 |    523 |00:00:00.01 |     244 |
|*  3 |    TABLE ACCESS FULL     | ORDERS       |      1 |    582 |    582 |00:00:00.01 |     193 |
|   4 |    TABLE ACCESS FULL     | CUSTOMERS    |      1 |   5230 |   5230 |00:00:00.01 |      51 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
   3 - filter(("O"."ORDER_DATE">=TO_DATE(' 2024-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDER_DATE"<TO_DATE(' 2024-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

2번 줄은 주문과 고객의 해시 조인이다. 조인은 짝이 맞는 행마다 한 줄을 만들므로, 11월에 두 번 주문한 고객 53명은 두 줄, 세 번 주문한 3명은 세 줄이 되었다. 그래서 고객 523명에게 쿠폰 582장이 나갔고 59장이 중복이다. IN을 조인으로 바꿀 때 '한 번만'이라는 뜻이 사라진 것이다. IN이나 EXISTS는 '11월 주문이 있는가'만 묻는다. 옵티마이저는 이것을 세미 조인(HASH JOIN RIGHT SEMI)으로 푼다. 11월 주문 582건을 해시 테이블에 올리고 고객을 한 번 훑으면서, 짝이 하나라도 있으면 그 고객을 한 번만 내보낸다. 읽는 블록은 조인과 같고(244블록), 결과는 582 → 523행이 된다. "조인이 더 빠르다"는 말은 이 SQL에 맞지 않는다. 세미 조인도 해시로 돌아 일량이 같다. 조인으로 두고 SELECT DISTINCT를 붙여도 결과는 맞는다. 하지만 582줄을 만든 뒤 다시 중복을 지우는 일이 더 들고, 고객 쪽 컬럼을 더 꺼내면 비교할 컬럼도 늘어난다. 질문이 '있는가'이면 SQL도 세미 조인으로 쓴다. 같은 실수가 다시 나와도 쿠폰이 두 번 나가지 않도록 coupon_issue에 (고객, 쿠폰) UNIQUE 제약을 건다. 그러면 중복 행이 생기는 순간 ORA-00001로 문장 전체가 실패해 고객에게 나가기 전에 드러난다. 이미 나간 중복 59장은 정리한 뒤에 건다.

해설

"조인이 더 빠르다"의 근거: IN 서브쿼리가 FILTER로 돌던 오래된 버전의 이야기다. 지금은 IN과 EXISTS를 세미 조인으로 펼치므로 조인으로 바꿔 얻는 것이 없고, 결과의 뜻만 바뀔 수 있다(교재 08화 §4).
조인이 맞는 경우: 서브쿼리 쪽 컬럼(예: 11월의 마지막 주문일)을 쿠폰에 같이 적어야 한다면 조인이 필요하다. 그때는 주문을 고객별로 먼저 묶어 한 줄로 만든 뒤 붙인다(GROUP BY customer_id와 MAX(order_date)).
건수 대조: 발급 배치는 넣은 행 수를 대상 고객 수(COUNT(DISTINCT customer_id))와 대조하고 끝낸다. 582와 523이 다르다는 것을 배포 전에 알 수 있었다.
같은 함정이 모든 '있는가' 질문에 있다. 상품 평가를 남긴 고객, 반품한 적 있는 주문처럼 많은 쪽을 조건으로 쓰는 질문은 세미 조인으로 쓴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황