상품은 100개뿐인데 누적 판매 순위를 내는 데 상품 마스터를 5천 번 찾는다

기초SELECT한빛유통248 / 700

상황

MD팀의 「상품별 누적 판매 순위」는 신 시스템이 문을 연 뒤의 주문상세 전체를 상품명별로 묶어 판매 금액과 판매 줄 수를 보여 준다. 다음 달 상품 개편 회의를 앞두고 MD 열 명이 하루 몇 번씩 돌린다. 개발자는 상품명으로 묶어 보여 주려고 주문상세 줄마다 스칼라 서브쿼리로 상품명을 붙인 뒤 그 상품명으로 묶었다. MD팀은 "개편 때 단종 상품의 이름을 새 상품에 다시 쓸 생각"이라고 했다.

테이블과 인덱스

T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문번호 순서로 쌓여 있다 · 상품 100종류가 2,000줄씩 · 한 주문의 줄은 서로 다른 상품이고, 바로 앞 줄과 같은 상품이 이어지는 일이 상품마다 333 ~ 334번 있다
M_ITM 상품: 100행 · 5블록 · PK_M_ITM (ITM_ID)(블록 하나짜리 인덱스) · 지금은 상품명이 겹치지 않는다
통계 최신

원본 SQL

sql

SELECT v.itm_nm, SUM(v.amt) AS sle_amt, COUNT(*) AS line_cnt
  FROM (SELECT (SELECT i.ITM_NM FROM M_ITM i WHERE i.ITM_ID = d.ITM_ID) AS itm_nm,
               d.ORD_QTY * d.UNT_PRC AS amt
          FROM T_ORD_DET d) v
 GROUP BY v.itm_nm
 ORDER BY sle_amt DESC

실행계획 학습용 예시 · 캐시에 자리를 얻은 상품 수는 교재 08화의 같은 데이터 실측(FILTER의 메모판)에 맞췄다

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |    100 |00:00:00.14 |   11090 |
|   1 |  SORT ORDER BY                 |           |      1 |    100 |    100 |00:00:00.14 |   11090 |
|   2 |   HASH GROUP BY                |           |      1 |    100 |    100 |00:00:00.14 |   11090 |
|   3 |    VIEW                        |           |      1 |    200K|    200K|00:00:00.12 |   11090 |
|   4 |     TABLE ACCESS BY INDEX ROWID| M_ITM     |   5097 |      1 |   5097 |00:00:00.02 |   10194 |
|*  5 |      INDEX UNIQUE SCAN         | PK_M_ITM  |   5097 |      1 |   5097 |00:00:00.01 |    5097 |
|   6 |     TABLE ACCESS FULL          | T_ORD_DET |      1 |    200K|    200K|00:00:00.03 |     896 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("I"."ITM_ID"=:B1)

문제

4번 줄의 Starts가 100이 아니라 5,097인 이유를 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 상품명을 찾는 횟수를 줄이시오. 지금처럼 상품명으로 묶으면 개편 뒤에 어떤 문제가 생기는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품번호(키)로 먼저 묶고, 상품명은 묶인 100 행에만 붙인다
SELECT (SELECT i.ITM_NM FROM M_ITM i WHERE i.ITM_ID = v.ITM_ID) AS itm_nm,
       v.sle_amt, v.line_cnt
  FROM (SELECT d.ITM_ID,
               SUM(d.ORD_QTY * d.UNT_PRC) AS sle_amt,
               COUNT(*)                   AS line_cnt
          FROM T_ORD_DET d
         GROUP BY d.ITM_ID) v
 ORDER BY v.sle_amt DESC;
-- 조인으로 붙여도 된다 : ... FROM (위 v) v, M_ITM i WHERE i.ITM_ID(+) = v.ITM_ID (해시 조인 · 901 블록)

개선 후 실행계획 학습용 예시 · 캐시에 자리를 얻은 상품 수는 교재 08화의 같은 데이터 실측(FILTER의 메모판)에 맞췄다

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |    100 |00:00:00.05 |    1096 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | M_ITM     |    100 |      1 |    100 |00:00:00.01 |     200 |
|*  2 |   INDEX UNIQUE SCAN            | PK_M_ITM  |    100 |      1 |    100 |00:00:00.01 |     100 |
|   3 |  SORT ORDER BY                 |           |      1 |    100 |    100 |00:00:00.05 |    1096 |
|   4 |   VIEW                         |           |      1 |    100 |    100 |00:00:00.05 |     896 |
|   5 |    HASH GROUP BY               |           |      1 |    100 |    100 |00:00:00.05 |     896 |
|   6 |     TABLE ACCESS FULL          | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_ID"=:B1)

모범 답안 서술

스칼라 서브쿼리가 인라인 뷰 안에 있어서 주문상세 200,000줄마다 불린다. 캐시가 같은 상품번호를 기억해 주지만 칸이 정해져 있어 100종류 가운데 97개만 자리를 얻었다. 자리를 못 얻은 3개 상품은 값이 바뀌어 들어올 때마다 상품 마스터를 다시 찾았다. 바로 앞 줄과 같은 상품이면 다시 찾지 않으므로 상품마다 2,000줄 가운데 1,666 ~ 1,667번이다. 그래서 Starts가 97 + (1,666 + 1,667 + 1,667) = 5,097이고, 상품 마스터 읽기만 10,194블록이다. 주문상세를 읽는 데는 896블록이 들었다. 주문상세를 상품번호로 먼저 묶고(100행), 묶은 뒤에 상품명을 붙인다. 그러면 스칼라 서브쿼리는 묶인 100행에서 한 번씩만 돌아 Starts 100, 200블록이다. Buffers는 11,090 → 1,096이 된다. 상품명은 조인으로 붙여도 된다(M_ITM 5블록, 해시 조인이면 901블록). 상품명으로 묶으면 개편 때 단종 상품의 이름을 새 상품에 다시 쓰는 순간 두 상품의 판매가 한 줄로 합쳐진다. 지금은 상품명이 겹치지 않아 답이 같지만, 묶는 기준은 상품번호(키)여야 한다. 상품명은 묶은 다음에 붙이는 표시 값이다.

해설

스칼라 서브쿼리는 늦게, 적게 부른다: 묶기 전에 부르면 줄 수만큼, 묶은 뒤에 부르면 묶음 수만큼 돈다. 캐시가 일부를 덜어 주더라도 입력 종류가 수십 개만 넘으면 자리를 못 얻는 값이 생긴다(교재 07화: 입력 64종류에서 6개가 자리를 못 얻었다).
이름을 키처럼 쓰는 함정: 이름, 설명 같은 표시 값은 유일하다는 보장이 없다. 화면에 이름만 보여 주더라도 묶음과 정렬은 키로 하고, 이름이 겹칠 수 있다면 상품번호도 함께 보여 준다.
조인으로 붙이는 안: 묶은 100행에 M_ITM을 해시 외부 조인으로 붙여도 같다. 마스터에 없는 상품번호가 들어와도 스칼라 서브쿼리처럼 그 줄을 남기려면 외부 조인으로 쓴다.
판매가 없는 상품은 원래 SQL에도 나오지 않는다. 판매가 없는 상품까지 0으로 보여 달라는 요구라면 상품 마스터를 기준으로 외부 조인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 캐시에 자리를 얻은 상품 수는 교재 08화의 같은 데이터 실측(FILTER의 메모판)에 맞췄다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 캐시에 자리를 얻은 상품 수는 교재 08화의 같은 데이터 실측(FILTER의 메모판)에 맞췄다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황