JOIN-063

버거와 감자튀김과 콜라를 낱개로 함께 산 주문을 세는 데 주문상품 PK를 1만 3천 번 찾아간다

기초SELECT버거213 / 700

상황

본사 마케팅팀은 세트 메뉴 판촉을 위해 「세트 전환 가능 주문」을 날마다 센다. 불고기버거와 감자튀김과 콜라를 세트가 아니라 낱개로 함께 산 주문이 하루에 몇 건인지 보는 표다. 메뉴 세 개를 바꿔 가며 하루 200번쯤 돌린다. SQL은 주문상품 테이블을 메뉴마다 한 번씩, 세 번 조인한다. 아래는 2013년 12월로 조회한 실행계획이다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)(날짜순 적재, CF 좋음) · 둘 다 BLEVEL 2
2013년 12월 주문상품 104,750줄(X01 리프 약 390블록, 테이블 약 2,340블록에 모여 있다)
12월 불고기버거(ITEM_ID = 3) 8,931줄 · 감자튀김(52) 7,104줄 · 콜라(71) 5,669줄 · 버거와 감자가 함께 든 주문 4,102건 · 셋 다 든 주문 2,267건
같은 주문에 같은 메뉴는 한 줄뿐이다(여러 개면 ORD_ITEM_QTY가 커진다) · ORD_DT는 주문 헤더에서 복사한 값이라 한 주문 안에서 같다 · ITEM_ID에 히스토그램 없음 · 통계 최신

원본 SQL

sql

SELECT a.ORD_DT, COUNT(*) AS combo_ord
  FROM ORD_ITEM a, ORD_ITEM b, ORD_ITEM c
 WHERE a.ORD_DT BETWEEN :v_from AND :v_to
   AND a.ITEM_ID = :v_burger
   AND b.ORD_NO  = a.ORD_NO
   AND b.ITEM_ID = :v_fries
   AND c.ORD_NO  = a.ORD_NO
   AND c.ITEM_ID = :v_coke
 GROUP BY a.ORD_DT
 ORDER BY a.ORD_DT

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |     31 |00:00:00.19 |   42217 |          |
|   1 |  SORT GROUP BY                           |              |      1 |     31 |     31 |00:00:00.19 |   42217 | 2048 (0) |
|   2 |   NESTED LOOPS                           |              |      1 |      1 |   2267 |00:00:00.19 |   42217 |          |
|   3 |    NESTED LOOPS                          |              |      1 |     31 |   4102 |00:00:00.14 |   29911 |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   1139 |   8931 |00:00:00.04 |    2729 |          |
|*  5 |      INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.01 |     392 |          |
|*  6 |     INDEX UNIQUE SCAN                    | ORD_ITEM_PK  |   8931 |      1 |   4102 |00:00:00.09 |   27182 |          |
|*  7 |    INDEX UNIQUE SCAN                     | ORD_ITEM_PK  |   4102 |      1 |   2267 |00:00:00.04 |   12306 |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("A"."ITEM_ID"=:V_BURGER)
   5 - access("A"."ORD_DT">=:V_FROM AND "A"."ORD_DT"<=:V_TO)
   6 - access("B"."ORD_NO"="A"."ORD_NO" AND "B"."ITEM_ID"=:V_FRIES)
   7 - access("C"."ORD_NO"="A"."ORD_NO" AND "C"."ITEM_ID"=:V_COKE)

문제

6번 줄과 7번 줄의 Starts를 보고, 같은 테이블을 세 번 조인하는 이 SQL의 일량이 어디서 나오는지 설명하시오.
주문상품을 한 번만 읽도록 SQL을 고치시오. 고친 SQL이 원래 SQL과 같은 결과를 내는 근거도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 12월 범위를 한 번 읽어 세 메뉴의 줄만 남기고, 주문별로 묶어 세 메뉴가 다 있는 주문만 고른다
-- PK (ORD_NO, ITEM_ID) 때문에 한 주문에 같은 메뉴는 한 줄이므로 세 줄이면 세 메뉴가 하나씩 있다
SELECT ORD_DT, COUNT(*) AS combo_ord
  FROM (SELECT ORD_DT, ORD_NO
          FROM ORD_ITEM
         WHERE ORD_DT BETWEEN :v_from AND :v_to
           AND ITEM_ID IN (:v_burger, :v_fries, :v_coke)
         GROUP BY ORD_DT, ORD_NO
        HAVING COUNT(*) = 3)
 GROUP BY ORD_DT
 ORDER BY ORD_DT

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

xplan

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |              |      1 |        |     31 |00:00:00.05 |    2729 |           |
|   1 |  SORT GROUP BY                            |              |      1 |     31 |     31 |00:00:00.05 |    2729 |  2048 (0) |
|   2 |   VIEW                                    |              |      1 |    155 |   2267 |00:00:00.05 |    2729 |           |
|*  3 |    FILTER                                 |              |      1 |        |   2267 |00:00:00.05 |    2729 |           |
|   4 |     HASH GROUP BY                         |              |      1 |   3104 |  10650 |00:00:00.05 |    2729 | 1856K (0) |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   3416 |  21704 |00:00:00.03 |    2729 |           |
|*  6 |       INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.01 |     392 |           |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(COUNT(*)=3)
   5 - filter(("ITEM_ID"=:V_BURGER OR "ITEM_ID"=:V_FRIES OR "ITEM_ID"=:V_COKE))
   6 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

모범 답안 서술

원래 SQL은 버거 줄(a)을 12월 범위로 읽은 뒤, 버거 줄 8,931개마다 ORD_ITEM_PK로 감자튀김이 있는지 찾고(6번 줄, Starts 8,931, 27,182블록) 감자까지 있는 주문 4,102건마다 콜라를 또 찾는다(7번 줄, Starts 4,102, 12,306블록). 한 번에 3블록씩 1만 3천 번을 왕복해서, 12월 범위를 읽은 몫(2,729블록)의 14배를 읽었다. 메뉴가 넷이 되면 조인도 왕복도 한 단계씩 더 늘어난다. 같은 테이블을 여러 번 조인해서 '모두 있는 것'을 찾는 SQL은 한 번 읽고 묶어서 세는 SQL로 바꿀 수 있다. 12월 주문상품을 한 번 읽으면서 세 메뉴의 줄만 남기고(ITEM_ID IN (...), 21,704줄), 주문별로 묶어 세 메뉴가 다 있는 주문(HAVING COUNT(*) = 3)만 고른다. Buffers는 42,217 → 2,729이고 날짜별 건수는 그대로다. COUNT(*) = 3이 '세 메뉴가 다 있다'와 같은 뜻인 근거는 PK (ORD_NO, ITEM_ID)다. 한 주문에 같은 메뉴는 한 줄뿐이므로, 세 메뉴로 거른 줄이 세 줄이면 세 메뉴가 하나씩 있다. 같은 값이 여러 줄 들어갈 수 있는 테이블이라면 COUNT(DISTINCT ITEM_ID) = 3으로 세야 한다. ORD_DT는 한 주문 안에서 같으므로 묶음 키에 함께 넣어도 주문이 쪼개지지 않는다.

해설

EXISTS로 바꿔도 같다: 버거 줄에서 출발해 감자와 콜라를 EXISTS로 확인하면 세미 조인이 되지만, PK를 찾아가는 횟수는 그대로다. 횟수를 없애는 것은 '한 번 읽고 묶기'다.
INTERSECT: 메뉴마다 주문 번호를 뽑아 INTERSECT하면 왕복은 없지만 12월 범위를 세 번 읽는다(약 8,200블록).
메뉴 수가 바뀌면: 고친 SQL은 IN 목록과 HAVING의 숫자만 바꾸면 된다. 원래 SQL은 메뉴마다 조인이 하나씩 늘고 왕복도 그만큼 는다.
더 줄이려면: ORD_ITEM (ITEM_ID, ORD_DT, ORD_NO) 인덱스가 있으면 세 메뉴의 줄 21,704개만 인덱스로 읽을 수 있다(약 80블록). 하루 200번 돌리는 표 하나를 위해 가장 큰 테이블에 인덱스를 더할지는 적재 비용과 함께 따진다(교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황