감사팀의 6월 영수증 대장이 판매상품 110만 줄을 세 번 묶고 TEMP까지 쓴다

실전SELECT유통251 / 700

상황

본사 감사팀의 「월 영수증 대장」은 한 달 치 영수증마다 판매번호, 일시, 매장명, 상품 줄 수, 판매 수량, 판매 금액을 보여 준다. 감사 기간에는 하루 수십 번 내려받는데, 2023년 6월 치(20,953건)에 4.2초가 걸리고 그때마다 TEMP 사용량 경보가 울린다. 개발자는 "칸마다 스칼라 서브쿼리를 하나씩 썼다. 19c가 알아서 펼쳐 줬다는데 왜 느린지 모르겠다"라고 했다. 박팀장은 서브쿼리마다 남길지 바꿀지 근거를 붙여 정리해 달라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · 2023년 6월 20,953건 · 판매가 있는 매장은 5곳(1001 ~ 1005)
CF11 판매상품: 1,096,708행 · 8,204블록 · 판매 50만 건에 1 ~ 4줄씩 · CF11_PK (SLE_NO, ITM_NO)(인덱스 4,213블록) · 2023년 6월 45,935줄 · 판매일자 순서로 쌓여 있다
판매번호는 판매일자 8자리로 시작한다('20230601-0308'). 지금 데이터에서 SUBSTR(SLE_NO, 1, 8) <> SLE_DT인 판매는 0건이다
CF07 매장: 6행 · 4블록 · CF07_PK (SHP_NO)
스칼라 서브쿼리 Unnesting은 켜져 있다(기본값) · PGA 작업 영역은 기본 설정 · 통계 최신

원본 SQL

sql

SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS,
       (SELECT f.SHP_NM FROM CF07 f WHERE f.SHP_NO = h.SHP_NO)                    AS shp_nm,
       (SELECT COUNT(*) FROM CF11 s WHERE s.SLE_NO = h.SLE_NO)                    AS line_cnt,
       (SELECT SUM(s.SLE_QTY) FROM CF11 s WHERE s.SLE_NO = h.SLE_NO)              AS qty,
       (SELECT SUM(s.SLE_QTY * s.SLE_UPRC) FROM CF11 s WHERE s.SLE_NO = h.SLE_NO) AS amt
  FROM CF10 h
 WHERE h.SLE_DT BETWEEN '20230601' AND '20230630'
 ORDER BY h.SLE_NO

실행계획 학습용 예시 · 건수와 금액은 적재 데이터 그대로

xplan

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem | Used-Tmp |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |  20953 |00:00:04.21 |   25214 |          |          |
|   1 |  TABLE ACCESS BY INDEX ROWID | CF07     |      5 |      1 |      5 |00:00:00.01 |      10 |          |          |
|*  2 |   INDEX UNIQUE SCAN          | CF07_PK  |      5 |      1 |      5 |00:00:00.01 |       5 |          |          |
|   3 |  SORT ORDER BY               |          |      1 |  20953 |  20953 |00:00:04.21 |   25214 | 2958K (0)|          |
|*  4 |   HASH JOIN OUTER            |          |      1 |  20953 |  20953 |00:00:04.17 |   25204 | 3311K (0)|          |
|*  5 |    HASH JOIN OUTER           |          |      1 |  20953 |  20953 |00:00:02.80 |   17000 | 3296K (0)|          |
|*  6 |     HASH JOIN OUTER          |          |      1 |  20953 |  20953 |00:00:01.42 |    8796 | 3281K (0)|          |
|*  7 |      TABLE ACCESS FULL       | CF10     |      1 |  20953 |  20953 |00:00:00.04 |    4583 |          |          |
|   8 |      VIEW                    | VW_SSQ_1 |      1 |    500K|    500K|00:00:01.31 |    4213 |          |          |
|   9 |       HASH GROUP BY          |          |      1 |    500K|    500K|00:00:01.25 |    4213 |   31M (1)|     22M  |
|  10 |        INDEX FAST FULL SCAN  | CF11_PK  |      1 |   1096K|   1096K|00:00:00.20 |    4213 |          |          |
|  11 |     VIEW                     | VW_SSQ_2 |      1 |    500K|    500K|00:00:01.33 |    8204 |          |          |
|  12 |      HASH GROUP BY           |          |      1 |    500K|    500K|00:00:01.27 |    8204 |   33M (1)|     25M  |
|  13 |       TABLE ACCESS FULL      | CF11     |      1 |   1096K|   1096K|00:00:00.24 |    8204 |          |          |
|  14 |    VIEW                      | VW_SSQ_3 |      1 |    500K|    500K|00:00:01.32 |    8204 |          |          |
|  15 |     HASH GROUP BY            |          |      1 |    500K|    500K|00:00:01.26 |    8204 |   33M (1)|     25M  |
|  16 |      TABLE ACCESS FULL       | CF11     |      1 |   1096K|   1096K|00:00:00.24 |    8204 |          |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."SHP_NO"=:B1)
   4 - access("VW_SSQ_3"."ITEM_1"(+)="H"."SLE_NO")
   5 - access("VW_SSQ_2"."ITEM_1"(+)="H"."SLE_NO")
   6 - access("VW_SSQ_1"."ITEM_1"(+)="H"."SLE_NO")
   7 - filter(("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230630'))

문제

네 스칼라 서브쿼리가 계획에서 각각 어떻게 처리됐는지(남은 것과 펼쳐진 것) 줄 번호와 Starts, Buffers로 설명하시오. 펼쳐진 쪽이 왜 6월 치가 아니라 판매상품 전체를 묶는지도 설명하시오.
스칼라 서브쿼리마다 유지할지, 조인으로 바꿀지, 하나로 합칠지 정하고 개선 SQL을 작성하시오.
세 집계 서브쿼리를 NO_UNNEST로 막는 안, 매장명까지 조인하는 안과 비교해 일량과 부작용(메모리와 TEMP, 판매번호 규칙에 기대는 범위 조건, 매장 테이블의 유일성)을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장명 : 스칼라 서브쿼리 유지 (입력 5 종류 · Starts 5)
-- 줄 수 · 수량 · 금액 : 판매상품을 6월 범위로 한 번만 읽어 판매번호별로 함께 묶고 외부 조인
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS,
       (SELECT f.SHP_NM FROM CF07 f WHERE f.SHP_NO = h.SHP_NO) AS shp_nm,
       NVL(s.line_cnt, 0) AS line_cnt, s.qty, s.amt
  FROM CF10 h,
       (SELECT SLE_NO,
               COUNT(*)                AS line_cnt,
               SUM(SLE_QTY)            AS qty,
               SUM(SLE_QTY * SLE_UPRC) AS amt
          FROM CF11
         WHERE SLE_NO >= '20230601'          -- 판매번호 = 판매일자 8 자리 + '-' + 일련번호
           AND SLE_NO <  '20230701'
         GROUP BY SLE_NO) s
 WHERE h.SLE_DT BETWEEN '20230601' AND '20230630'
   AND s.SLE_NO(+) = h.SLE_NO
 ORDER BY h.SLE_NO;

-- 바꾸기 전 확인 : 0 건이어야 범위 조건이 같은 줄을 고른다
-- SELECT COUNT(*) FROM CF10 WHERE SUBSTR(SLE_NO, 1, 8) <> SLE_DT;

개선 후 실행계획 학습용 예시 · 건수와 금액은 적재 데이터 그대로

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |  20953 |00:00:00.12 |    5917 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID           | CF07    |      5 |      1 |      5 |00:00:00.01 |      10 |          |
|*  2 |   INDEX UNIQUE SCAN                    | CF07_PK |      5 |      1 |      5 |00:00:00.01 |       5 |          |
|   3 |  SORT ORDER BY                         |         |      1 |  20953 |  20953 |00:00:00.12 |    5917 | 2958K (0)|
|*  4 |   HASH JOIN OUTER                      |         |      1 |  20953 |  20953 |00:00:00.09 |    5907 | 3311K (0)|
|*  5 |    TABLE ACCESS FULL                   | CF10    |      1 |  20953 |  20953 |00:00:00.04 |    4583 |          |
|   6 |    VIEW                                |         |      1 |  20953 |  20953 |00:00:00.04 |    1324 |          |
|   7 |     HASH GROUP BY                      |         |      1 |  20953 |  20953 |00:00:00.04 |    1324 | 2627K (0)|
|   8 |      TABLE ACCESS BY INDEX ROWID BATCHED| CF11   |      1 |  45935 |  45935 |00:00:00.02 |    1324 |          |
|*  9 |       INDEX RANGE SCAN                 | CF11_PK |      1 |  45935 |  45935 |00:00:00.01 |     176 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("F"."SHP_NO"=:B1)
   4 - access("S"."SLE_NO"(+)="H"."SLE_NO")
   5 - filter(("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230630'))
   9 - access("SLE_NO">='20230601' AND "SLE_NO"<'20230701')

[대안 비교 — 같은 6월 치]
  지금 (세 서브쿼리가 각각 펼쳐짐)          25,214 블록 · 4.21 초 · TEMP 약 72MB
  세 서브쿼리에 NO_UNNEST                  약 235,000 블록 (20,953 × 3 번 + CF10 4,583)
  고친 SQL (매장명 스칼라 + 한 번 묶기)      5,917 블록 · 0.12 초 · TEMP 0
  + 매장명도 해시 조인                      5,911 블록 (차이 없음)
[대조]  건수 20,953 = 20,953 · 금액 합계 377,266,500 = 377,266,500 · MINUS 양방향 0 / 0

모범 답안 서술

1번 줄의 매장명 스칼라 서브쿼리는 그대로 남았다. 입력(매장번호)이 5종류뿐이라 캐시가 모두 기억해 Starts 5, 10블록이다. 집계가 아닌 단순 조회라 옵티마이저가 펼치지도 않았다. 나머지 세 서브쿼리(줄 수, 수량, 금액)는 스칼라 서브쿼리 Unnesting으로 8번, 11번, 14번 줄의 VW_SSQ_1 ~ 3이 됐다. 판매번호별로 묶는 뷰 세 개가 각각 판매상품 전체(110만 줄, 판매 50만 건)를 읽어 묶는다(4,213 + 8,204 + 8,204블록). CF10의 6월 조건(SLE_DT)은 CF11에 없는 컬럼이라 뷰 안으로 들어가지 못해서, 화면에 필요한 6월 판매 20,953건이 아니라 2년 치를 세 번 묶는다. 묶음 50만 개는 메모리에 다 올라가지 못해 TEMP에도 쓴다(Used-Tmp 22 ~ 25M씩). 그래서 Buffers 25,214, 4.21초다. 판단은 이렇다. 매장명 스칼라 서브쿼리는 남긴다(5번이면 끝나고 조인으로 바꿔도 얻을 것이 없다). 세 집계 서브쿼리는 하나로 합친다. 판매번호가 판매일자 8자리로 시작하므로 SLE_NO >= '20230601' AND SLE_NO < '20230701'로 CF11_PK 범위를 직접 주고, 6월 판매상품 45,935줄만 한 번 읽어(1,324블록) 판매번호별로 묶은 뒤 판매에 외부 조인으로 붙인다. Buffers는 25,214 → 5,917, A-Time은 4.21 → 0.12초로 줄고 TEMP를 쓰지 않는다. NO_UNNEST로 세 서브쿼리를 막는 안은 판매 20,953건마다 서브쿼리 세 개가 돌아(20,953 × 3번) 약 23만 블록을 읽는다(줄 수 3블록, 수량과 금액은 4블록씩). 펼친 지금보다도 나쁘다. 바깥이 2만 건이고 입력이 행마다 다르면 스칼라 서브쿼리보다 한 번 묶는 집합 처리가 맞다. 매장명까지 조인하는 안은 CF07(4블록)을 해시 조인으로 붙여 몇 블록 줄 뿐이라 차이가 없다. 다만 조인으로 바꾸면 '매장 하나에 한 줄'을 PK가 지켜 줘야 한다. 스칼라 서브쿼리는 매장이 두 줄이면 ORA-01427로 멈추지만, 조인은 그 매장의 판매를 조용히 두 배로 늘린다. 범위 조건의 대가는 판매번호 규칙에 기댄다는 점이다. 바꾸기 전에 SUBSTR(SLE_NO, 1, 8) <> SLE_DT인 판매가 0건인지 확인하고(지금 0건), 규칙이 바뀌면 이 SQL도 함께 고쳐야 한다는 것을 리포트에 적는다. 판매상품이 없는 판매는 줄 수 0이 되도록 NVL로 맞춘다(지금은 모든 판매에 판매상품이 있다).

해설

스칼라 서브쿼리를 고르는 기준: 바깥 행이 적거나(페이징) 입력 종류가 적어 캐시가 다 담을 때는 스칼라 서브쿼리가 낫다. 바깥 행이 많고 입력이 행마다 다르면 한 번 묶어 붙이는 조인이 낫다. 이 SQL에는 두 경우가 함께 있었다.
변환은 서브쿼리 하나에 뷰 하나를 만든다: 같은 행을 읽는 집계가 셋이면 뷰도 셋이 되어 같은 테이블을 세 번 읽는다. 한 뷰로 합치는 일은 SQL에서 직접 해야 한다.
CF10도 판매번호 범위로 읽는 안: h.SLE_NO >= '20230601' AND h.SLE_NO < '20230701'을 더하면 CF10을 CF10_PK 범위로 읽어(약 260블록) 전체가 약 1,600블록이 된다. 판매일자 조건도 함께 남겨 두면 규칙이 깨져도 다른 달 판매가 섞이지 않는다.
정합성은 원래 SQL과 건수(20,953), 금액 합계(377,266,500), MINUS 양방향으로 대조한다(교재 08화 정합성 회귀 체크리스트).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 금액은 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 금액은 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황