「상품별 현재 판매가」에 상품이 두 줄씩 나온다

중급SELECT유통309 / 700

상황

본사 상품팀의 「상품별 현재 판매가」 화면은 상품 하나에 한 줄이어야 하는데 157줄이 나온다. 48개 상품이 가격 둘(판매가와 매입가처럼 보이는 값)로 두 번씩 나온다. 개발자: "적용일자가 같은 날이라 그렇다. ROWNUM = 1로 하나만 고르면 되지 않나?" 이 SQL은 판매상품(CF11)에 붙는 「정가 기준 매출」 리포트의 인라인 뷰로도 쓰인다.

테이블과 인덱스

CF05 상품가격: 157행 · 5블록 · PK CF05_PK (ITM_NO, UPRC_KN_CD, APL_DT) · 적용일자 이력
UPRC_KN_CD 단가종류(CF16 공통코드): '10' 판매단가 89행 · '11' 발주단가 68행 · 둘 다 있는 상품 48개 · 판매단가만 41개 · 발주단가만 20개
APL_DT VARCHAR2(8): 지금은 157행 전부 '20200101'(첫 적재)이다. 다음 달부터 가격 개정분이 쌓인다.
CF01 상품: 109행 · 5블록 · PK ITM_NO · 통계 최신

원본 SQL

sql

SELECT a.ITM_NO, i.ITM_NM, a.APL_DT, a.ITM_UPRC
  FROM CF05 a, CF01 i
 WHERE i.ITM_NO = a.ITM_NO
   AND a.APL_DT = (SELECT MAX(b.APL_DT)
                     FROM CF05 b
                    WHERE b.ITM_NO = a.ITM_NO)
 ORDER BY a.ITM_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |      1 |        |    157 | 00:00:00.01 |      12 |
|   1 |  SORT ORDER BY             |         |      1 |    157 |    157 | 00:00:00.01 |      12 |
|*  2 |   HASH JOIN                |         |      1 |    157 |    157 | 00:00:00.01 |      12 |
|*  3 |    HASH JOIN               |         |      1 |    157 |    157 | 00:00:00.01 |       7 |
|   4 |     VIEW                   | VW_SQ_1 |      1 |    109 |    109 | 00:00:00.01 |       2 |
|   5 |      HASH GROUP BY         |         |      1 |    109 |    109 | 00:00:00.01 |       2 |
|   6 |       INDEX FAST FULL SCAN | CF05_PK |      1 |    157 |    157 | 00:00:00.01 |       2 |
|   7 |     TABLE ACCESS FULL      | CF05    |      1 |    157 |    157 | 00:00:00.01 |       5 |
|   8 |    TABLE ACCESS FULL       | CF01    |      1 |    109 |    109 | 00:00:00.01 |       5 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_NO"="A"."ITM_NO")
   3 - access("A"."APL_DT"="MAX(B.APL_DT)" AND "ITEM_1"="A"."ITM_NO")

문제

157줄이 나오는 까닭을 데이터(단가종류, 적용일자)와 실행계획으로 설명하고, 이 SQL이 CF05를 몇 번 읽는지 쓰시오.
상품마다 현재 판매단가 한 줄이 나오도록 CF05를 한 번만 읽는 SQL로 고치시오.
'같은 상품, 같은 적용일자'인 행이 둘이면 MAX 서브쿼리, ROW_NUMBER, RANK, KEEP (DENSE_RANK LAST)가 각각 무엇을 돌려주는지 설명하고, 하나를 어떻게 정해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매단가('10')만 · 상품별 적용일자가 가장 늦은 한 줄 · CF05 를 한 번 읽는다
SELECT ITM_NO, ITM_NM, APL_DT, ITM_UPRC
  FROM (SELECT a.ITM_NO, i.ITM_NM, a.APL_DT, a.ITM_UPRC,
               ROW_NUMBER() OVER (PARTITION BY a.ITM_NO ORDER BY a.APL_DT DESC) AS rn
          FROM CF05 a, CF01 i
         WHERE i.ITM_NO = a.ITM_NO
           AND a.UPRC_KN_CD = '10')
 WHERE rn = 1
 ORDER BY ITM_NO;

-- 같은 답 · 집계 한 번 (인라인 뷰 없이)
-- SELECT a.ITM_NO, MAX(a.APL_DT) AS APL_DT,
--        MAX(a.ITM_UPRC) KEEP (DENSE_RANK LAST ORDER BY a.APL_DT) AS ITM_UPRC
--   FROM CF05 a
--  WHERE a.UPRC_KN_CD = '10'
--  GROUP BY a.ITM_NO;

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |     89 | 00:00:00.01 |      10 |
|*  1 |  VIEW                     |      |      1 |     79 |     89 | 00:00:00.01 |      10 |
|*  2 |   WINDOW SORT PUSHED RANK |      |      1 |     79 |     89 | 00:00:00.01 |      10 |
|*  3 |    HASH JOIN              |      |      1 |     79 |     89 | 00:00:00.01 |      10 |
|*  4 |     TABLE ACCESS FULL     | CF05 |      1 |     79 |     89 | 00:00:00.01 |       5 |
|   5 |     TABLE ACCESS FULL     | CF01 |      1 |    109 |    109 | 00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."ITM_NO" ORDER BY
              INTERNAL_FUNCTION("A"."APL_DT") DESC )<=1)
   3 - access("I"."ITM_NO"="A"."ITM_NO")
   4 - filter("A"."UPRC_KN_CD"='10')

모범 답안 서술

157줄이 나오는 까닭은 데이터에 있다. CF05의 키는 (상품, 단가종류, 적용일자)인데 서브쿼리는 MAX(APL_DT)를 상품별로만 구한다. 판매단가('10')와 발주단가('11')가 같은 날('20200101') 적용된 48개 상품은 두 줄 모두 MAX와 같아서 두 줄로 나온다(48 × 2 + 61 = 157). 발주단가만 있는 20개 상품은 발주단가가 '판매가'로 보인다. 실행계획 4번과 7번 줄이 같은 CF05를 두 번 읽는다. 서브쿼리가 펼쳐진 VW_SQ_1에서 상품별 MAX 109줄을 만들고, 본문 157줄을 다시 읽어 3번 줄에서 맞춰 본다. 고친 SQL은 판매단가만(UPRC_KN_CD = '10') 남기고, 상품별로 적용일자 내림차순 첫 줄을 ROW_NUMBER()로 고른다. CF05는 한 번만 읽는다. 결과는 157 → 89줄, Buffers는 12 → 10이다(5블록짜리 테이블이라 성능 차이는 작고, 이 문제에서 얻는 것은 정합성이다). 같은 상품, 같은 적용일자인 행이 둘이면 MAX 서브쿼리와 RANK, DENSE_RANK는 둘 다 돌려주고(중복), ROW_NUMBER는 그중 아무거나 하나를 돌려준다(실행마다 다를 수 있다). KEEP (DENSE_RANK LAST)는 동점 안에서 MAX(ITM_UPRC)처럼 집계로 하나를 만든다. 어느 것도 '맞는 하나'를 알려 주지 않으므로 그 하나는 업무 키로 정한다. 여기서는 단가종류를 조건에 넣으면 PK가 (상품, '10', 적용일자)를 유일하게 보장하므로 ORDER BY APL_DT DESC 하나로 결과가 정해진다.

해설

ROWNUM = 1의 함정: 상관 서브쿼리나 바깥 쿼리에 ROWNUM을 붙여 '하나만' 고르면 어느 하나가 나올지 정해지지 않는다. 오늘은 판매단가가 나와도 실행계획이 바뀐 날에는 발주단가가 나올 수 있다.
서브쿼리를 고치는 답: MAX 서브쿼리를 그대로 두고 바깥과 안쪽 둘 다 UPRC_KN_CD = '10'을 넣어도 89줄로 맞는다. 바깥에만 넣으면 오늘은 맞지만, 발주단가가 판매단가보다 늦게 개정되는 날 그 상품이 화면에서 사라진다. 지금 데이터에서 결과가 같다고 해서 SQL이 맞는 것은 아니다(교재 08화).
KEEP (DENSE_RANK LAST ORDER BY APL_DT)는 GROUP BY 한 번으로 끝나서 인라인 뷰가 필요 없다. 다만 동점이 있으면 앞의 집계 함수(MAX, MIN)가 하나를 고른다는 점을 알고 써야 한다.
정가 기준 매출 리포트: 이 157줄을 판매상품 CF11(110만 행)에 붙이면 48개 상품의 매출이 두 번 더해진다. 1 : M 관계에서 '1 쪽'이 하나가 아니면 조인은 조용히 행을 곱한다(교재 08화).
판매단가가 없는 20개 상품(발주단가만 있다)은 고친 SQL에서 빠진다. 화면에 '판매가 미등록'으로 보여 줘야 한다면 CF01에서 외부 조인한다. 어느 쪽인지는 요구를 물어 정한다.
'오늘 가격'이 아니라 '판매일의 가격'이 필요하면 APL_DT <= 판매일 범위 안에서 같은 방식으로 고르거나, LEAD(APL_DT)로 종료일을 만들어 구간으로 붙인다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황