매장 다섯 곳의 「기존점 객수」 타일이 지난주부터 하루 논리 읽기 3위에 올라 있다

실전SELECT유통264 / 700

상황

유통 본사 상황판의 「기존점 객수」 타일은 개점한 지 1년이 넘은 매장(기존점)만 골라 이번 달 영수증 수와 영업일 수를 보여 준다. 상황판 네 대가 1분마다 새로 고치고 점장 앱도 같은 SQL을 써서 하루 약 6,000번 돈다. 다음 달에는 여섯 번째 매장 1006(판교점)이 문을 연다. 지난주 SQL 점검 도구가 이 SQL의 NO_UNNEST를 "FILTER가 바깥 행마다 서브쿼리를 되풀이하게 만드는 힌트"로 표시했고, 개발자가 힌트를 지워 배포했다. 그 뒤로 이 SQL이 하루 논리 읽기 3위로 올라왔다. 아래는 2023년 12월 화면이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SHP_NO 1001 ~ 1005(다섯 매장 모두 2022-01-01부터 판매가 있다) · 2023년 12월 20,864건 · 기준일(2022-12-01) 앞의 판매 229,434건
인덱스: CF10_PK (SLE_NO) · CF10_X01 (SHP_NO, SLE_DT): 리프 약 1,500블록 · 높이 3
CF07 매장: 6행 · 4블록 · CF07_PK (SHP_NO) · 1000(본사)은 판매가 없다
바인드: :v_from '20231201' · :v_to '20231231' · :v_base '20221201'(이번 달 1일의 1년 전) · 통계 최신

원본 SQL

sql

SELECT a.SHP_NO,
       COUNT(*)                 AS rcpt_cnt,
       COUNT(DISTINCT a.SLE_DT) AS sle_days
  FROM CF10 a
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to
   AND EXISTS (SELECT 1                      -- 기존점 : 기준일 앞에 판매가 있는 매장
                 FROM CF10 p
                WHERE p.SHP_NO = a.SHP_NO
                  AND p.SLE_DT < :v_base)
 GROUP BY a.SHP_NO
 ORDER BY a.SHP_NO

실행계획 학습용 예시 · 매장별 판매 건수는 적재 CSV에서 센 값이다

xplan

[지금 · 힌트를 지운 뒤]
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      5 |00:00:00.09 |     781 |          |
|   1 |  SORT GROUP BY     |          |      1 |      5 |      5 |00:00:00.09 |     781 | 2048  (0)|
|*  2 |   HASH JOIN SEMI   |          |      1 |  20800 |  20864 |00:00:00.08 |     781 | 1795K (0)|
|*  3 |    INDEX SKIP SCAN | CF10_X01 |      1 |  20800 |  20864 |00:00:00.01 |      75 |          |
|*  4 |    INDEX SKIP SCAN | CF10_X01 |      1 |    229K|    229K|00:00:00.04 |     706 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."SHP_NO"="A"."SHP_NO")
   3 - access("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO)
       filter(("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO))
   4 - access("P"."SLE_DT"<:V_BASE)
       filter("P"."SLE_DT"<:V_BASE)

[지난주까지 · 서브쿼리에 NO_UNNEST]
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      5 |00:00:00.01 |      90 |          |
|   1 |  SORT GROUP BY     |          |      1 |      5 |      5 |00:00:00.01 |      90 | 2048  (0)|
|*  2 |   FILTER           |          |      1 |        |  20864 |00:00:00.01 |      90 |          |
|*  3 |    INDEX SKIP SCAN | CF10_X01 |      1 |  20800 |  20864 |00:00:00.01 |      75 |          |
|*  4 |    INDEX RANGE SCAN| CF10_X01 |      5 |      1 |      5 |00:00:00.01 |      15 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "CF10" "P" WHERE "P"."SHP_NO"=:B1 AND "P"."SLE_DT"<:V_BASE))
   3 - access("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO)
       filter(("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO))
   4 - access("P"."SHP_NO"=:B1 AND "P"."SLE_DT"<:V_BASE)

문제

두 계획에서 서브쿼리 쪽의 일이 각각 몇 번, 몇 블록인지 줄 번호로 설명하시오. FILTER 계획에서 4번 줄의 Starts가 20,864가 아니라 5인 이유도 쓰시오.
지금 계획(펼친 뒤 해시 세미 조인), 펼친 뒤 NL 세미 조인, NO_UNNEST의 FILTER, 매장 마스터에서 기존점을 먼저 고르는 모양의 일량을 어림해 비교하고, SQL을 고치시오.
고른 방법의 부작용과, 이 판단이 뒤집히는 조건을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 기존점 판정은 매장마다 한 번이면 된다 : 펼치지 않고 FILTER 로 두어 매장 다섯 곳만 확인한다
-- 힌트 대장 : 넣은 이유 = 입력이 매장번호 5 종류라 FILTER 의 캐시가 다 받는다
--             다시 볼 조건 = 매장이 수백 곳으로 늘 때, 바깥이 매장 순서로 들어오지 않게 바뀔 때
SELECT a.SHP_NO,
       COUNT(*)                 AS rcpt_cnt,
       COUNT(DISTINCT a.SLE_DT) AS sle_days
  FROM CF10 a
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to
   AND EXISTS (SELECT /*+ NO_UNNEST */ 1
                 FROM CF10 p
                WHERE p.SHP_NO = a.SHP_NO
                  AND p.SLE_DT < :v_base)
 GROUP BY a.SHP_NO
 ORDER BY a.SHP_NO;

-- 힌트 없이 같은 일을 하는 다른 답 : 매장 마스터(6 행)에서 기존점을 먼저 고른다 (약 97 블록)
-- SELECT a.SHP_NO, COUNT(*) AS rcpt_cnt, COUNT(DISTINCT a.SLE_DT) AS sle_days
--   FROM CF10 a
--  WHERE a.SLE_DT BETWEEN :v_from AND :v_to
--    AND a.SHP_NO IN (SELECT f.SHP_NO
--                       FROM CF07 f
--                      WHERE EXISTS (SELECT 1 FROM CF10 p
--                                     WHERE p.SHP_NO = f.SHP_NO
--                                       AND p.SLE_DT < :v_base))
--  GROUP BY a.SHP_NO
--  ORDER BY a.SHP_NO;

개선 후 실행계획 학습용 예시 · 매장별 판매 건수는 적재 CSV에서 센 값이다

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |      5 |00:00:00.01 |      90 |          |
|   1 |  SORT GROUP BY     |          |      1 |      5 |      5 |00:00:00.01 |      90 | 2048  (0)|
|*  2 |   FILTER           |          |      1 |        |  20864 |00:00:00.01 |      90 |          |
|*  3 |    INDEX SKIP SCAN | CF10_X01 |      1 |  20800 |  20864 |00:00:00.01 |      75 |          |
|*  4 |    INDEX RANGE SCAN| CF10_X01 |      5 |      1 |      5 |00:00:00.01 |      15 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "CF10" "P" WHERE "P"."SHP_NO"=:B1 AND "P"."SLE_DT"<:V_BASE))
   3 - access("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO)
       filter(("A"."SLE_DT">=:V_FROM AND "A"."SLE_DT"<=:V_TO))
   4 - access("P"."SHP_NO"=:B1 AND "P"."SLE_DT"<:V_BASE)

[네 모양의 일량 · 한 번 실행]
  펼친 뒤 해시 세미 조인 (지금)        781블록   (영수증 75 + 기준일 앞 판매 229,434줄 706)
  펼친 뒤 NL 세미 조인               약 62,700블록 (75 + 20,864번 × 3)
  NO_UNNEST의 FILTER                    90블록   (75 + 매장 5번 × 3)
  CF07에서 기존점을 먼저 고르기        약 97블록   (CF07 4 + 매장 6번 × 3 + 영수증 75)

모범 답안 서술

지금 계획은 서브쿼리를 펼쳐 2번 줄 해시 세미 조인으로 처리한다. 3번 줄에서 이번 달 영수증 20,864장을 CF10_X01의 매장별 범위로 읽고(INDEX SKIP SCAN, 75블록), 4번 줄에서 기준일 앞의 판매 229,434줄을 매장 다섯 곳의 범위에서 모두 읽는다(706블록). 기준일이 달마다 한 달씩 밀리므로 이 범위는 다달이 약 2만 줄씩 늘어난다. 기존점인지 알려면 매장마다 기준일 앞 판매가 한 줄만 있으면 되는데, 해시 세미 조인은 영수증 쪽(3번 줄)을 해시 테이블로 올린 뒤 4번 줄의 22만 줄을 끝까지 흘려보내며 짝을 맞춘다. 다섯 매장의 짝이 모두 정해진 뒤에도 멈추지 않는다. 실행마다 781블록이고 하루 6,000번이면 약 469만 블록이다. 지난주까지의 FILTER 계획은 바깥 행마다 서브쿼리를 열게 되어 있지만 4번 줄의 Starts는 5다. 서브쿼리에 들어가는 값(:B1)은 매장번호이고, 이번 달 영수증 20,864장의 매장번호는 다섯 종류뿐이다. FILTER의 캐시(07화의 메모판)가 다섯 값을 모두 기억하고, 매장별 범위로 읽은 영수증은 같은 매장이 연달아 들어와 바로 앞 값으로도 걸러진다. 한 번 실행할 때는 (매장, 기준일 앞) 범위의 첫 항목 하나만 찾으면 '있다'가 정해지므로 3블록이다. 서브쿼리 쪽 일은 5 × 3 = 15블록이고 전체 90블록이다. 네 모양을 비교한다. 펼친 뒤 해시 세미 조인(지금)은 781블록이다. 펼친 뒤 NL 세미 조인이면 영수증 20,864장마다 인덱스를 3블록씩 찾아 약 62,700블록이 된다. NL 세미 조인에는 캐시가 없어서 같은 매장을 2만 번 다시 묻는다. NO_UNNEST로 FILTER에 두면 90블록이다. 매장 마스터 CF07(6행)에서 기존점을 먼저 고르고 그 매장의 영수증만 세는 모양은 CF07 4블록, 매장 여섯 곳 확인 18블록, 영수증 75블록으로 약 97블록이다. 힌트를 되살린다. 점검 도구의 규칙은 서브쿼리 입력이 바깥 행마다 다른 경우(주문번호, 영수증번호처럼 유일한 값)에는 맞지만, 입력이 매장번호처럼 몇 종류뿐이면 FILTER의 캐시가 되풀이를 막아 준다. Buffers는 781 → 90, 하루 약 469만 → 54만 블록이다. 부작용도 있다. 힌트는 힌트 대장에 넣은 이유(입력이 매장 다섯 종류)와 다시 볼 조건을 적어 두어야 다음 점검 때 또 지워지지 않는다. 판단이 뒤집히는 조건은 입력 종류가 늘어나 캐시가 다 받지 못할 때다. 매장이 수백 곳으로 늘면 자리를 못 얻는 매장이 생겨 FILTER가 NL 세미 조인 쪽으로 다가간다(07화: 입력 256종류에서 55개가 자리를 못 얻었다). 그때는 캐시에 기대지 않는 CF07 모양이 맞다. CF07 모양은 지금도 힌트 없이 같은 일을 하므로, 배포를 한 번 더 할 수 있다면 더 오래가는 답이다.

해설

규칙의 뒷면: NO_UNNEST가 느린 경우는 서브쿼리 입력이 바깥 행마다 다를 때다. 입력 종류가 적으면 FILTER는 종류 수만큼만 서브쿼리를 실행한다. 힌트를 지우거나 붙이기 전에 FILTER 아래 줄의 Starts와 입력 컬럼의 종류 수를 본다(교재 08화 「서브쿼리 Unnesting을 막으면 07화의 메모판이 돌아온다」).
해시 세미 조인의 성질: 해시 조인은 build 입력을 모두 올린 뒤 probe 입력을 끝까지 읽는다. 짝이 다 정해져도 멈추지 않으므로, '하나라도 있으면 된다'는 세미 조인의 이점은 첫 짝에서 멈추는 NL 세미 조인과 FILTER에서만 산다.
개점일을 데이터로 두는 안: CF07에 개점일 컬럼을 두면 기존점 판정이 판매 테이블을 읽지 않는 조건 하나가 된다. 스키마를 바꾸는 일이라 이번에는 SQL로 풀었다.
판교점이 문을 열면: 1006은 기준일 앞 판매가 없어 서브쿼리가 0건으로 끝나고(3블록) 기존점에서 빠진다. 1년 뒤 기준일이 개점일을 지나면 저절로 들어온다. FILTER의 Starts는 6이 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 매장별 판매 건수는 적재 CSV에서 센 값이다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 매장별 판매 건수는 적재 CSV에서 센 값이다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황