카테고리 평균 대비 칸을 더한 뒤로 상품 판매 분석 화면의 읽기 블록이 두 배가 됐다

기초SELECT유통305 / 700

상황

유통 본사 「상품 판매 분석」 화면은 상품별 6월 판매 수량을 보여 준다. 지난주 상품마다 같은 카테고리 평균과의 비율(%) 칸을 더했는데, 그 뒤로 화면의 읽기 블록이 두 배가 됐다. 개발자는 상품별 집계를 인라인 뷰로 한 번 더 써서 카테고리 평균을 구했다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매번호는 판매일자 8자리로 시작한다 · 2023년 6월 45,935줄
CF01 상품: 109행 · 5블록 · CTG_NO 카테고리 · 6월에 팔린 상품 88개(카테고리 12개)
통계 최신

원본 SQL

sql

SELECT a.ITM_NO, a.CTG_NO, a.SLE_QTY,
       ROUND(a.SLE_QTY * 100 / b.AVG_QTY, 1) AS VS_CTG_AVG
  FROM (SELECT i.ITM_NO, i.CTG_NO, SUM(s.SLE_QTY) AS SLE_QTY
          FROM CF11 s, CF01 i
         WHERE s.SLE_NO >= '20230601' AND s.SLE_NO < '20230701'
           AND i.ITM_NO = s.ITM_NO
         GROUP BY i.ITM_NO, i.CTG_NO) a,
       (SELECT CTG_NO, AVG(SLE_QTY) AS AVG_QTY
          FROM (SELECT i.ITM_NO, i.CTG_NO, SUM(s.SLE_QTY) AS SLE_QTY
                  FROM CF11 s, CF01 i
                 WHERE s.SLE_NO >= '20230601' AND s.SLE_NO < '20230701'
                   AND i.ITM_NO = s.ITM_NO
                 GROUP BY i.ITM_NO, i.CTG_NO)
         GROUP BY CTG_NO) b
 WHERE b.CTG_NO = a.CTG_NO
 ORDER BY a.CTG_NO, a.ITM_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |      1 |        |     88 | 00:00:00.10 |    2658 |
|   1 |  SORT ORDER BY                               |         |      1 |     88 |     88 | 00:00:00.10 |    2658 |
|*  2 |   HASH JOIN                                  |         |      1 |     88 |     88 | 00:00:00.10 |    2658 |
|   3 |    VIEW                                      |         |      1 |     12 |     12 | 00:00:00.05 |    1329 |
|   4 |     HASH GROUP BY                            |         |      1 |     12 |     12 | 00:00:00.05 |    1329 |
|   5 |      VIEW                                    |         |      1 |     88 |     88 | 00:00:00.05 |    1329 |
|   6 |       HASH GROUP BY                          |         |      1 |     88 |     88 | 00:00:00.05 |    1329 |
|*  7 |        HASH JOIN                             |         |      1 |  45935 |  45935 | 00:00:00.04 |    1329 |
|   8 |         TABLE ACCESS FULL                    | CF01    |      1 |    109 |    109 | 00:00:00.01 |       5 |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED  | CF11    |      1 |  45935 |  45935 | 00:00:00.02 |    1324 |
|* 10 |          INDEX RANGE SCAN                    | CF11_PK |      1 |  45935 |  45935 | 00:00:00.01 |     176 |
|  11 |    VIEW                                      |         |      1 |     88 |     88 | 00:00:00.05 |    1329 |
|  12 |     HASH GROUP BY                            |         |      1 |     88 |     88 | 00:00:00.05 |    1329 |
|* 13 |      HASH JOIN                               |         |      1 |  45935 |  45935 | 00:00:00.04 |    1329 |
|  14 |       TABLE ACCESS FULL                      | CF01    |      1 |    109 |    109 | 00:00:00.01 |       5 |
|  15 |       TABLE ACCESS BY INDEX ROWID BATCHED    | CF11    |      1 |  45935 |  45935 | 00:00:00.02 |    1324 |
|* 16 |        INDEX RANGE SCAN                      | CF11_PK |      1 |  45935 |  45935 | 00:00:00.01 |     176 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."CTG_NO"="A"."CTG_NO")
   7 - access("I"."ITM_NO"="S"."ITM_NO")
  10 - access("S"."SLE_NO">='20230601' AND "S"."SLE_NO"<'20230701')
  13 - access("I"."ITM_NO"="S"."ITM_NO")
  16 - access("S"."SLE_NO">='20230601' AND "S"."SLE_NO"<'20230701')

문제

읽기 블록이 두 배가 된 이유를 실행계획의 줄 번호와 Buffers로 설명하시오.
결과가 바뀌지 않게 상품별 집계를 한 번만 하도록 SQL을 고치시오. 다른 방법이 있다면 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품별 집계에 이름(m)을 붙여 한 번만 쓰고, 본문에서 두 번 참조한다
WITH m AS (
  SELECT i.ITM_NO, i.CTG_NO, SUM(s.SLE_QTY) AS SLE_QTY
    FROM CF11 s, CF01 i
   WHERE s.SLE_NO >= '20230601' AND s.SLE_NO < '20230701'
     AND i.ITM_NO = s.ITM_NO
   GROUP BY i.ITM_NO, i.CTG_NO
)
SELECT a.ITM_NO, a.CTG_NO, a.SLE_QTY,
       ROUND(a.SLE_QTY * 100 / b.AVG_QTY, 1) AS VS_CTG_AVG
  FROM m a,
       (SELECT CTG_NO, AVG(SLE_QTY) AS AVG_QTY FROM m GROUP BY CTG_NO) b
 WHERE b.CTG_NO = a.CTG_NO
 ORDER BY a.CTG_NO, a.ITM_NO

-- 다른 답 : 한 번 묶은 뒤 AVG(SLE_QTY) OVER (PARTITION BY CTG_NO) 로 같은 카테고리 평균을 붙인다

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

xplan

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                           |      1 |        |     88 | 00:00:00.05 |    1341 |
|   1 |  TEMP TABLE TRANSFORMATION                 |                           |      1 |        |     88 | 00:00:00.05 |    1341 |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9D6633_3C4D5E |      1 |        |      0 | 00:00:00.05 |    1331 |
|   3 |    HASH GROUP BY                           |                           |      1 |     88 |     88 | 00:00:00.05 |    1329 |
|*  4 |     HASH JOIN                              |                           |      1 |  45935 |  45935 | 00:00:00.04 |    1329 |
|   5 |      TABLE ACCESS FULL                     | CF01                      |      1 |    109 |    109 | 00:00:00.01 |       5 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED   | CF11                      |      1 |  45935 |  45935 | 00:00:00.02 |    1324 |
|*  7 |       INDEX RANGE SCAN                     | CF11_PK                   |      1 |  45935 |  45935 | 00:00:00.01 |     176 |
|   8 |   SORT ORDER BY                            |                           |      1 |     88 |     88 | 00:00:00.01 |      10 |
|*  9 |    HASH JOIN                               |                           |      1 |     88 |     88 | 00:00:00.01 |      10 |
|  10 |     VIEW                                   |                           |      1 |     12 |     12 | 00:00:00.01 |       5 |
|  11 |      HASH GROUP BY                         |                           |      1 |     12 |     12 | 00:00:00.01 |       5 |
|  12 |       VIEW                                 |                           |      1 |     88 |     88 | 00:00:00.01 |       5 |
|  13 |        TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6633_3C4D5E |      1 |     88 |     88 | 00:00:00.01 |       5 |
|  14 |     VIEW                                   |                           |      1 |     88 |     88 | 00:00:00.01 |       5 |
|  15 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6633_3C4D5E |      1 |     88 |     88 | 00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("I"."ITM_NO"="S"."ITM_NO")
   7 - access("S"."SLE_NO">='20230601' AND "S"."SLE_NO"<'20230701')
   9 - access("B"."CTG_NO"="A"."CTG_NO")

모범 답안 서술

3번 줄 아래와 11번 줄 아래가 같은 집계다. 두 인라인 뷰가 같은 6월 판매상품 45,935줄을 CF11_PK 범위로 읽고(1,324블록) 상품과 조인해 상품별로 묶는다. 하나는 상품 행을 위해, 다른 하나는 카테고리 평균을 위해서다. 옵티마이저는 글자가 같은 두 인라인 뷰를 하나로 합쳐 주지 않으므로, 같은 집계를 두 번 해서 1,329 × 2 = 2,658블록을 읽는다. 상품별 집계를 WITH 절 m으로 한 번 쓰고, 본문에서 상품 행(a)과 카테고리 평균(b)으로 두 번 참조한다. 오라클은 두 번 참조된 WITH 절을 한 번 계산해 임시 테이블로 만들어 두고(1번 줄 TEMP TABLE TRANSFORMATION, 2번 줄 LOAD AS SELECT), 13번과 15번 줄에서 그 88행을 두 번 읽는다. 88행이라 임시 테이블은 메모리 안에서 끝난다. Buffers는 2,658 → 1,341이고 결과는 같다. 분석 함수로도 된다. 상품별로 한 번 묶은 뒤 AVG(SLE_QTY) OVER (PARTITION BY CTG_NO)로 같은 카테고리의 평균을 행마다 붙이면 임시 테이블도 조인도 없이 한 번에 끝난다(약 1,329블록). 이 WITH 절에 /*+ INLINE */을 붙이면 두 참조에 각각 풀려 원래처럼 두 번 읽게 된다. 무거운 집계를 작은 결과로 만들어 두 번 쓰는 이 SQL에는 구체화(기본 동작)가 맞다.

해설

구체화가 손해인 경우: WITH 절의 결과가 크고 본문에서 거르는 조건이 따로 있으면, 거르기 전의 큰 결과를 TEMP에 쓰게 되어 오히려 느려진다(SUBQ-062). 결과 크기를 보고 고른다.
한 번만 참조하면: 오라클은 한 번만 쓰인 WITH 절을 인라인 뷰처럼 본문에 풀어 넣는다. WITH로 빼는 것만으로는 아무것도 저장되지 않는다.
분석 함수 안의 장점: 임시 테이블을 만들고 읽는 일이 없고, 카테고리 평균과 상품 행이 한 번의 집계에서 나와 SQL도 짧다. 다만 카테고리 평균을 다른 조건으로 따로 구해야 하면 WITH가 더 읽기 쉽다.
비율 칸은 평균이 0이면 0으로 나누게 된다. 지금은 6월에 팔린 상품만 모아 평균이 0이 될 수 없지만, 반품이 많아 순수량 평균이 0인 카테고리가 생기면 NULLIF(b.AVG_QTY, 0)로 막는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황