JOIN-006

하루 4만 번 누르는 '최근 구매 상품' 칸이 한 번에 500블록을 읽는다

실전SELECT이커머스156 / 700

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

상황

고객센터 상담 화면의 「최근 구매 상품」 칸은 고객 번호를 넣으면 고객명과 등급, 최근 구매 상품 20줄(주문일 · 상품명 · 카테고리 · 수량 · 금액)을 보여 준다. 상담원 40명이 하루 4만 번 누른다. 18화 졸업 과제의 「주문 내역」(E1) 바로 옆 칸이다. 고객사는 "이 칸 하나를 처음부터 다시 봐 달라. 인덱스는 승인 절차(T2)를 거치면 만들 수 있다"라고 요청했다.

테이블과 인덱스

customers: 5,230행 · 51블록 · PK_CUSTOMERS (customer_id) · UQ(email)
orders: 34,908행 · 193블록 · PK만 있음 · 고객당 평균 6.7건 · 고객 390은 16건 · order_date 시분초 있음
order_items: 73,498행 · 290블록 · PK만 있음 · 주문당 평균 2.1줄 · 고객 390의 구매 줄 35개
products 280행 · 8블록, categories 53행 · 4블록 (parent_id 계층) · 둘 다 PK만 있음
FK 39개 전부 DISABLED NOT VALIDATED · 조인 키에 인덱스 0개 · 18화에서 shipping.address_id 고아 2,719행이 나왔다 · 통계 최신

원본 SQL

sql

SELECT c.name AS cust_name, c.grade, o.order_id, o.order_date,
       p.name AS product_name, g.name AS category_name,
       i.quantity, i.quantity * i.unit_price AS amount
  FROM customers c, orders o, order_items i, products p, categories g
 WHERE c.customer_id = :cust
   AND o.customer_id = c.customer_id
   AND i.order_id    = o.order_id
   AND p.product_id  = i.product_id
   AND g.category_id = p.category_id
 ORDER BY o.order_date DESC
 FETCH FIRST 20 ROWS ONLY

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |      1 |        |     20 |00:00:00.02 |     498 |
|*  1 |  VIEW                             |              |      1 |     14 |     20 |00:00:00.02 |     498 |
|*  2 |   WINDOW SORT PUSHED RANK         |              |      1 |     14 |     20 |00:00:00.02 |     498 |
|*  3 |    HASH JOIN                      |              |      1 |     14 |     35 |00:00:00.02 |     498 |
|*  4 |     HASH JOIN                     |              |      1 |     14 |     35 |00:00:00.02 |     494 |
|*  5 |      HASH JOIN                    |              |      1 |     14 |     35 |00:00:00.02 |     486 |
|   6 |       NESTED LOOPS                |              |      1 |      7 |     16 |00:00:00.01 |     196 |
|   7 |        TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  8 |         INDEX UNIQUE SCAN         | PK_CUSTOMERS |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  9 |        TABLE ACCESS FULL          | ORDERS       |      1 |      7 |     16 |00:00:00.01 |     193 |
|  10 |       TABLE ACCESS FULL           | ORDER_ITEMS  |      1 |  73498 |  73498 |00:00:00.01 |     290 |
|  11 |      TABLE ACCESS FULL            | PRODUCTS     |      1 |    280 |    280 |00:00:00.01 |       8 |
|  12 |     TABLE ACCESS FULL             | CATEGORIES   |      1 |     53 |     53 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORDER_DATE") DESC )<=20)
   3 - access("G"."CATEGORY_ID"="P"."CATEGORY_ID")
   4 - access("P"."PRODUCT_ID"="I"."PRODUCT_ID")
   5 - access("I"."ORDER_ID"="O"."ORDER_ID")
   8 - access("C"."CUSTOMER_ID"=:CUST)
   9 - filter("O"."CUSTOMER_ID"=:CUST)

문제

이 칸을 한 번 조회할 때의 일량이 어디서 나오는지 줄 번호와 Buffers로 설명하고, 하루 4만 번이면 얼마가 되는지 계산하시오.
조인 순서와 조인 방식, 필요한 인덱스를 정해 개선 SQL과 DDL을 작성하시오. 각 결정의 근거는 행 수로 제시하시오.
다섯 테이블의 조인마다 '1 쪽이 정말 하나인가'(유일성과 고아)를 점검하시오. FK가 꺼진 이 DB에서 내부 조인을 그대로 둬도 되는지 판단하고, 정렬 키와 인덱스의 대가까지 포함해 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- T2 ① 인덱스 둘 — INVISIBLE 로 만들고 세션에서만 켜서 잰 뒤 승인 (18화)
CREATE INDEX IX_ORDERS_CUST_DT  ON orders (customer_id, order_date) INVISIBLE;
CREATE INDEX IX_ORDER_ITEMS_ORD ON order_items (order_id) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

-- 1 쪽 점검 — 꺼진 FK 사이로 들어온 고아를 센다 (넷 다 0 이면 지금은 내부 조인과 같다)
SELECT (SELECT COUNT(*) FROM orders o
         WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id)) AS ord_orphans,
       (SELECT COUNT(*) FROM order_items i
         WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id = i.order_id))         AS item_orphans,
       (SELECT COUNT(*) FROM order_items i
         WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.product_id = i.product_id))   AS prod_orphans,
       (SELECT COUNT(*) FROM products p
         WHERE NOT EXISTS (SELECT 1 FROM categories g WHERE g.category_id = p.category_id)) AS ctg_orphans
  FROM dual;

-- 고객 한 명 → 주문 → 주문상세 (인덱스 NL) · 상품 · 카테고리는 작은 마스터 (외부 조인 — 고아가 생겨도 구매 줄은 남는다)
SELECT c.name AS cust_name, c.grade, o.order_id, o.order_date,
       NVL(p.name, '(상품 정보 없음)') AS product_name, g.name AS category_name,
       i.quantity, i.quantity * i.unit_price AS amount
  FROM customers c
  JOIN orders o          ON o.customer_id = c.customer_id
  JOIN order_items i     ON i.order_id    = o.order_id
  LEFT JOIN products p   ON p.product_id  = i.product_id
  LEFT JOIN categories g ON g.category_id = p.category_id
 WHERE c.customer_id = :cust
 ORDER BY o.order_date DESC, o.order_id DESC, i.order_item_id
 FETCH FIRST 20 ROWS ONLY;

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

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |      1 |        |     20 |00:00:00.01 |      82 |
|*  1 |  VIEW                                   |                    |      1 |     14 |     20 |00:00:00.01 |      82 |
|*  2 |   WINDOW SORT PUSHED RANK               |                    |      1 |     14 |     20 |00:00:00.01 |      82 |
|*  3 |    HASH JOIN OUTER                      |                    |      1 |     14 |     35 |00:00:00.01 |      82 |
|*  4 |     HASH JOIN OUTER                     |                    |      1 |     14 |     35 |00:00:00.01 |      78 |
|   5 |      NESTED LOOPS                       |                    |      1 |     14 |     35 |00:00:00.01 |      70 |
|   6 |       NESTED LOOPS                      |                    |      1 |     14 |     35 |00:00:00.01 |      53 |
|   7 |        NESTED LOOPS                     |                    |      1 |      7 |     16 |00:00:00.01 |      21 |
|   8 |         TABLE ACCESS BY INDEX ROWID     | CUSTOMERS          |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  9 |          INDEX UNIQUE SCAN              | PK_CUSTOMERS       |      1 |      1 |      1 |00:00:00.01 |       2 |
|  10 |         TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS          |      1 |      7 |     16 |00:00:00.01 |      18 |
|* 11 |          INDEX RANGE SCAN               | IX_ORDERS_CUST_DT  |      1 |      7 |     16 |00:00:00.01 |       2 |
|* 12 |        INDEX RANGE SCAN                 | IX_ORDER_ITEMS_ORD |     16 |      2 |     35 |00:00:00.01 |      32 |
|  13 |       TABLE ACCESS BY INDEX ROWID       | ORDER_ITEMS        |     35 |      2 |     35 |00:00:00.01 |      17 |
|  14 |      TABLE ACCESS FULL                  | PRODUCTS           |      1 |    280 |    280 |00:00:00.01 |       8 |
|  15 |     TABLE ACCESS FULL                   | CATEGORIES         |      1 |     53 |     53 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_008"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORDER_DATE") DESC ,INTERNAL_FUNCTION("O"."ORDER_ID") DESC ,"I"."ORDER_ITEM_ID")<=20)
   3 - access("G"."CATEGORY_ID"(+)="P"."CATEGORY_ID")
   4 - access("P"."PRODUCT_ID"(+)="I"."PRODUCT_ID")
   9 - access("C"."CUSTOMER_ID"=:CUST)
  11 - access("O"."CUSTOMER_ID"=:CUST)
  12 - access("I"."ORDER_ID"="O"."ORDER_ID")

모범 답안 서술

일량: 498블록 중 9번 줄 ORDERS FULL 193블록과 10번 줄 ORDER_ITEMS FULL 290블록을 합한 483블록(97%)이 대부분이다. 고객 한 명의 주문 16건을 찾으려고 주문 34,908건을 다 읽고, 그 16건의 상품 35줄을 찾으려고 주문상세 73,498줄을 모두 해시 조인에 대어 본다. 하루 4만 번이면 약 1,992만 블록이다. 순서: 고객 한 명(PK, 1행) → 주문 16건 → 주문상세 35줄 → 상품 → 카테고리 순으로 조인한다. 가장 많이 거르는 조건은 고객 번호다(주문 34,908 → 16). 이 조건에서 출발해 조인 키를 따라 내려가면 단계마다 행 수가 16, 35로 작게 유지된다. 방식과 인덱스: 주문과 주문상세는 인덱스 NL로 조인한다. outer가 16건, 35건뿐이라 inner를 한 번 열 때 2~3블록이면 FULL 한 번(193블록, 290블록)보다 싸다 (06화 손익분기). 그러려면 이 DB에 없는 인덱스 두 개가 필요하다. orders(customer_id, order_date)는 고객 조건과 정렬 순서를 함께 담고, order_items(order_id)는 조인 키를 담는다. 상품(8블록)과 카테고리(4블록)는 작은 마스터라 해시 조인으로 FULL 12블록이면 끝나므로, PK로 35번 찾아가는 NL(약 70블록)보다 싸다. 옵티마이저가 이 계획을 스스로 고르므로 힌트는 쓰지 않았다. Buffers는 498 → 82로 준다(하루 약 1,992만 → 328만). 1 쪽 점검: 고객, 주문, 상품, 카테고리는 PK가 있어서 '하나뿐'임이 증명된다. 그래서 조인이 줄을 불리지 않는다. 그러나 FK 39개가 전부 DISABLED NOT VALIDATED여서 '반드시 있다'는 증명은 없으므로, 고아를 NOT EXISTS로 센다. 18화 E1에서는 배송 주소 고아 2,719행 때문에 내부 조인이 고객의 주문을 지웠다. 넷이 모두 0이면 지금은 내부 조인과 결과가 같지만, 화면에서는 상품과 카테고리를 외부 조인으로 둔다. 마스터가 지워지거나 고아가 들어와도 '산 줄'이 남아야 상담원이 고객에게 틀린 답을 하지 않는다. 주문과 주문상세 사이는 내부 조인이 맞다 (상세가 없는 주문에는 구매 줄도 없다). 정렬: ORDER BY o.order_date DESC만으로는 같은 주문 안에서 상품 줄의 순서가 정해지지 않아, 20줄 경계에서 누를 때마다 다른 줄이 나올 수 있다. order_id DESC와 order_item_id까지 붙여 정렬 키를 유일하게 한다 (09화). 대가: 주문과 주문상세를 적재할 때마다 두 인덱스도 함께 갱신된다 (18화: 주문 3만 5천 행을 적재할 때 인덱스 하나로 redo가 1.56 → 12MB가 됐다). INVISIBLE로 만들어 세션에서 재 보고, 원장(고객 390의 구매 35줄과 금액 합계)과 대조한 뒤 T2 절차로 승인받는다.

해설

인덱스를 하나만 만든다면: orders(customer_id, order_date)만 만들고 주문상세는 해시 조인(FULL 290블록)으로 두면 약 320블록이다. 둘 다 있어야 82블록이 된다. 승인서에는 두 인덱스의 효과를 따로 재서 적는다.
커버링: order_items(order_id, product_id, quantity, unit_price)로 만들면 13번 줄의 테이블 액세스 17블록이 없어지지만, 인덱스가 커지는 만큼 적재 비용이 는다. 이 화면에서는 얻는 것이 적다.
M : 1 : M을 들이지 않는다: 이 칸에 상품 평점을 붙이려고 reviews를 바로 조인하면 상품마다 리뷰 수만큼 줄이 곱해진다 (18화 E2, 08화). 평점을 붙이려면 상품별로 먼저 묶어 1 : 1로 조인한다.
힌트를 쓴다면: LEADING(c o i p g) USE_NL(o i)로 고정할 수도 있지만, 지금 통계로도 옵티마이저가 같은 계획을 고른다. 고정한다면 그 판단(고객당 주문 수십 건)의 유효기간을 대장에 적는다 (06화 §8).
18화 E1(주문 내역)도 같은 orders(customer_id, order_date) 인덱스를 쓴다. 두 화면이 함께 쓰는 인덱스는 T2 승인서에 두 화면을 모두 적는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황