「이 상품을 산 주문」 목록에서 같은 날 같은 금액으로 두 번 산 주문이 한 줄로 보인다

중급SELECT이커머스343 / 700

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

상황

관리자 화면의 상품 상세에는 「이 상품을 산 주문」 목록(주문일, 고객 이름, 주문 금액, 상태)이 최근 주문부터 나온다. 고객센터에 이런 문의가 들어왔다. "선물용으로 같은 날 같은 상품을 두 번 따로 주문했는데 관리자 화면에는 한 줄만 있다." 주문 테이블에는 두 주문이 다 있다. 개발자는 한 주문에 같은 상품이 옵션별로 두 줄 담기면 목록에 같은 주문이 두 번 나와서 DISTINCT를 붙였다고 한다.

테이블과 인덱스

order_items: 73,498행 · 290블록 · order_item_id(PK) · order_id · product_id · quantity · unit_price
orders: 34,908행 · 193블록 · order_id(PK) · customer_id · order_date DATE(시분초 있음) · status · total_amount
customers: 5,230행 · 51블록 · customer_id(PK) · name
인덱스는 세 테이블 모두 PK뿐이다. 상품 105가 담긴 주문상품 줄은 266줄이고 주문은 259건이다(7건은 같은 상품이 두 줄). 통계는 최신이다.

원본 SQL

sql

SELECT DISTINCT TRUNC(o.order_date) AS order_day, c.name, o.total_amount, o.status
  FROM order_items i, orders o, customers c
 WHERE i.product_id = :prod
   AND o.order_id = i.order_id
   AND c.customer_id = o.customer_id
 ORDER BY order_day DESC

실행계획 학습용 예시

xplan

[상품 105]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |    256 |00:00:00.02 |     534 |       |       |          |
|   1 |  SORT UNIQUE        |             |      1 |    262 |    256 |00:00:00.02 |     534 | 30720 | 30720 |27648  (0)|
|*  2 |   HASH JOIN         |             |      1 |    262 |    266 |00:00:00.02 |     534 |  1250K|  1250K| 1340K (0)|
|*  3 |    HASH JOIN        |             |      1 |    262 |    266 |00:00:00.01 |     483 |  1316K|  1316K| 1428K (0)|
|*  4 |     TABLE ACCESS FULL| ORDER_ITEMS |      1 |    262 |    266 |00:00:00.01 |     290 |       |       |          |
|   5 |     TABLE ACCESS FULL| ORDERS      |      1 |  34908 |  34908 |00:00:00.01 |     193 |       |       |          |
|   6 |    TABLE ACCESS FULL | CUSTOMERS   |      1 |   5230 |   5230 |00:00:00.01 |      51 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
   3 - access("O"."ORDER_ID"="I"."ORDER_ID")
   4 - filter("I"."PRODUCT_ID"=:PROD)

[대조 · 상품 105]  주문상품 266줄 · 주문 259건 · 화면 256줄

문제

주문상품 266줄, 주문 259건, 화면 256줄의 차이를 각각 무엇이 만들었는지 SQL과 실행계획으로 설명하시오.
주문이 빠지지도 겹치지도 않도록 SQL을 고치고, 고친 SQL에서 중복 제거(SORT UNIQUE)가 왜 필요 없는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품이 '담겨 있는' 주문만 고른다 (세미 조인) · 주문번호를 함께 내보내고 정렬 키 끝에도 붙인다
SELECT o.order_id, TRUNC(o.order_date) AS order_day, c.name, o.total_amount, o.status
  FROM orders o, customers c
 WHERE c.customer_id = o.customer_id
   AND EXISTS (SELECT 1
                 FROM order_items i
                WHERE i.order_id = o.order_id
                  AND i.product_id = :prod)
 ORDER BY o.order_date DESC, o.order_id DESC

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

xplan

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |    259 |00:00:00.02 |     534 |       |       |          |
|   1 |  SORT ORDER BY         |             |      1 |    262 |    259 |00:00:00.02 |     534 | 31744 | 31744 |28672  (0)|
|*  2 |   HASH JOIN            |             |      1 |    262 |    259 |00:00:00.02 |     534 |  1250K|  1250K| 1338K (0)|
|*  3 |    HASH JOIN RIGHT SEMI|             |      1 |    262 |    259 |00:00:00.01 |     483 |  1316K|  1316K| 1422K (0)|
|*  4 |     TABLE ACCESS FULL  | ORDER_ITEMS |      1 |    262 |    266 |00:00:00.01 |     290 |       |       |          |
|   5 |     TABLE ACCESS FULL  | ORDERS      |      1 |  34908 |  34908 |00:00:00.01 |     193 |       |       |          |
|   6 |    TABLE ACCESS FULL   | CUSTOMERS   |      1 |   5230 |   5230 |00:00:00.01 |      51 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
   3 - access("I"."ORDER_ID"="O"."ORDER_ID")
   4 - filter("I"."PRODUCT_ID"=:PROD)

모범 답안 서술

266 → 259는 조인이 만든 중복이다. 한 주문에 같은 상품이 옵션별로 두 줄 담긴 주문 7건은 order_items와 조인하면 두 줄이 된다(3번 줄 A-Rows 266). 259 → 256은 DISTINCT가 만든 손실이다. DISTINCT는 '같은 주문'을 지우는 것이 아니라 SELECT 절에 고른 칸(주문일, 이름, 금액, 상태)의 값이 모두 같은 줄을 지운다. 이 SQL은 주문번호를 고르지 않았으므로, 같은 고객이 같은 날 같은 금액으로 따로 주문한 두 주문 3쌍이 한 줄씩으로 합쳐졌다. 1번 줄 SORT UNIQUE가 266줄을 정렬하며 중복 7줄과 서로 다른 주문 3건을 함께 지운 것이다. 고친 SQL은 상품이 '담겨 있는가'를 EXISTS로 묻는다. 세미 조인(HASH JOIN RIGHT SEMI)은 상품 105가 담긴 주문상품 줄을 해시 표에 올리고 주문마다 한 번만 짝을 확인하므로, 두 줄로 담긴 주문도 한 번씩만 나온다. 조인이 중복을 만들지 않으니 중복 제거(SORT UNIQUE)가 필요 없고, 남는 정렬은 화면 순서를 위한 SORT ORDER BY 259줄뿐이다. 주문번호를 함께 내보내고 정렬 끝에 order_id DESC를 붙여 같은 시각의 주문도 순서가 정해지게 했다. 화면은 256줄에서 259줄이 된다. Buffers는 534로 같다. 인덱스가 PK뿐이라 두 SQL 모두 세 테이블을 한 번씩 다 읽는다. 이 문제에서 고친 것은 일량보다 정답이고, 중복 제거라는 정렬 작업 하나를 덜어 낸 것이 덤이다.

해설

DISTINCT는 중복의 원인을 고치지 않는다: 1:M 조인이 늘린 줄을 DISTINCT로 지우면 고른 칸이 같다는 이유로 서로 다른 행까지 함께 지워진다. 고른 칸에 주문번호가 있었다면 이번 손실은 없었겠지만 중복 제거 정렬은 남는다. 늘어날 일을 처음부터 만들지 않는 EXISTS가 맞다(교재 08화의 1:M 곱하기).
GROUP BY o.order_id, …로 써도 한 주문에 한 줄이 되지만, 이것도 조인으로 늘린 뒤 묶는 모양이라 묶는 작업(HASH GROUP BY)이 남는다.
주문상품에 (product_id, order_id) 인덱스가 있으면 266줄을 인덱스로 찾아 NL 세미 조인으로 풀 수 있다. 조인 키에 인덱스를 둘지는 적재 비용과 함께 정한다.
정합성은 건수만 보지 말고 주문번호 집합으로 대조한다. 고친 SQL의 259건과 order_items에서 그 상품의 주문번호를 뽑은 259건이 양방향 MINUS로 0 / 0이어야 한다(교재 08화 정합성 회귀 검증).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황