주문 583건의 '구매 직전에 본 상품'을 찾는 데 조회 로그를 583번 통째로 읽고, 14건은 결과에 없다

모범 답안 SQL ​

-- 주문과 '주문 전의 조회'를 한 번에 조인하고, 주문마다 가장 늦은 조회 한 건의 상품을 꺼낸다
-- 외부 조인: 조회 기록이 없는 주문도 남긴다 (직전 조회 상품은 빈칸)
SELECT o.order_id, o.customer_id, o.order_date,
       MAX(v.product_id) KEEP (DENSE_RANK LAST ORDER BY v.view_date, v.view_id) AS last_view_pid,
       MAX(v.view_date) AS last_view_date
  FROM orders o
  LEFT JOIN product_views v
    ON v.customer_id = o.customer_id
   AND v.view_date  <= o.order_date
 WHERE o.order_date >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.order_date <  TO_DATE(:v_to, 'YYYYMMDD')
 GROUP BY o.order_id, o.customer_id, o.order_date

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |    583 |00:00:00.06 |     560 |           |
|   1 |  SORT GROUP BY       |               |      1 |    583 |    583 |00:00:00.06 |     560 |  124K (0) |
|*  2 |   HASH JOIN OUTER    |               |      1 |   4460 |   4678 |00:00:00.05 |     560 | 1238K (0) |
|*  3 |    TABLE ACCESS FULL | ORDERS        |      1 |    583 |    583 |00:00:00.01 |     193 |           |
|*  4 |    TABLE ACCESS FULL | PRODUCT_VIEWS |      1 |  80000 |  80000 |00:00:00.02 |     367 |           |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V"."CUSTOMER_ID"(+)="O"."CUSTOMER_ID")
       filter("V"."VIEW_DATE"(+)<="O"."ORDER_DATE")
   3 - filter(("O"."ORDER_DATE">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORDER_DATE"<TO_DATE(:V_TO,'YYYYMMDD')))
   4 - filter("V"."CUSTOMER_ID" IS NOT NULL)

모범 답안 서술 ​

5번과 6번 줄의 Starts가 583, 곧 6월 주문 수다. 서브쿼리는 주문마다 '그 고객의, 주문 시각 이전 조회 가운데 가장 늦은 시각'을 구하는데, product_views의 customer_id에 인덱스가 없어서 주문 한 건마다 조회 로그 367블록을 통째로 읽었다. 583 × 367 = 213,961블록이 전체 214K의 거의 전부다. 조인 자체(2번 줄)는 두 테이블을 한 번씩 읽은 560블록이다. 14건이 빠진 까닭은 두 겹이다. 주문 전에 조회 기록이 없는 고객이면 서브쿼리의 MAX가 NULL을 돌려주고, v.view_date = NULL은 참이 아니므로 그 주문의 줄이 1번 줄 FILTER에서 모두 버려진다. 조회 로그와의 조인도 내부 조인이라 남을 줄이 없다. 분석의 분모(그달 주문 수)가 583에서 569로 줄어 비율이 틀린다. 고친 SQL은 주문과 '주문 전의 조회'를 한 번에 조인한다. 주문 한 건에 그 이전 조회가 평균 8줄 붙고(4,678행), 주문마다 MAX(v.product_id) KEEP (DENSE_RANK LAST ORDER BY v.view_date, v.view_id)로 가장 늦은 한 줄의 상품만 꺼낸다. 같은 초에 두 상품을 본 경우에도 view_id가 하나로 정해 준다. 외부 조인이라 조회 기록이 없는 14건도 빈칸으로 남는다. 두 테이블을 한 번씩만 읽으므로 Buffers는 214K → 560, 결과는 569행 → 583행이 된다. (customer_id, view_date) 인덱스를 만들고 주문마다 OUTER APPLY ... FETCH FIRST 1 ROW ONLY로 한 건에서 멈추는 안은 주문 한 건에 인덱스 3블록과 테이블 1블록, 583 × 4 + 주문 193 = 약 2,500블록이다. 조인 한 번(560블록)보다 네 배 넘게 무겁고 승인 절차와 적재 비용(조회 로그는 하루에도 수십 건씩 들어온다)까지 든다. 한 달 치 수백 건을 한꺼번에 보는 분석에는 조인 한 번이 맞다.

해설 ​

• 대상이 적을 때와 많을 때: 고객센터 화면처럼 주문 한 건의 직전 조회를 보는 것이라면 인덱스와 한 건 멈추기가 맞다(약 5블록). 대상이 수백, 수천 건이면 그만큼 왕복이 생기므로 양쪽을 한 번씩 읽는 조인 + KEEP이 낫다. 기준은 '대상 수 × 한 번에 찾는 비용'과 'FULL 한 번'의 비교다 (교재 06화 §5).
• ROW_NUMBER로 써도 된다: 조인 결과에 ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY v.view_date DESC, v.view_id DESC)를 매겨 1번만 남겨도 같다. 칸이 여럿 필요하면 이쪽이 편하다.
• 조인 결과가 커지는 경우: 고객마다 조회가 수천 건씩 쌓이면 '주문 전의 조회 전부'가 주문마다 붙어 조인 결과가 커진다. 그때는 조회 로그 쪽에도 기간 조건(예: 주문 달과 그 전달)을 줘서 붙는 줄을 줄인다.
• 원본처럼 = (SELECT MAX ...)로 다시 조인하는 모양은 같은 시각의 조회가 둘이면 주문이 두 줄로 나오는 함정도 있다. '가장 늦은 한 건'은 동점을 가를 키까지 정렬 키에 넣어야 한 건으로 정해진다.

관련 교재 ​

• 06화 · 손익분기점을 직접 재는 법
• 18화 · 졸업 과제 (이커머스 · 인덱스 승인 절차)