점장이 영수증 한 장을 찾는 데 판매상품 110만 줄을 다 읽는다

중급SELECT유통258 / 700

상황

매장 POS의 「영수증 찾기」는 손님이 "어제 산 아메리카노 영수증"을 찾을 때 쓴다. 점장이 날짜와 상품을 넣으면 그 매장의 그날 영수증 가운데 그 상품이 든 것만 보여 준다. 다섯 매장에서 하루 수백 번 쓰는데 매장 PC에서 0.1초 가까이 걸린다. 서브쿼리에는 /*+ UNNEST HASH_SJ */가 붙어 있고, 옆에 주석 -- 서브쿼리는 해시 세미 조인으로 (2021 월 판매 집계 개선 때 정한 규칙)이 있다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) (03화에서 만든 인덱스, 하루치가 약 14블록에 모여 있다) · 판매 매장은 1001 ~ 1005의 5곳
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) (높이 3) · ITM_NO로 시작하는 인덱스는 없다
강남점(1001)의 2023-06-01 영수증 150장 · 상품 :v_itm(아메리카노)은 2년 치 판매 줄이 41,280줄이고, 그날 강남점 영수증 중 23장에 들어 있다
통계 최신

원본 SQL

sql

SELECT a.SLE_NO, a.SLE_HMS
  FROM CF10 a
 WHERE a.SLE_DT = :v_dt
   AND a.SHP_NO = :v_shp
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */ 1   -- 서브쿼리는 해시 세미 조인으로 (2021 월 판매 집계 개선 때 정한 규칙)
                 FROM CF11 b
                WHERE b.SLE_NO = a.SLE_NO
                  AND b.ITM_NO = :v_itm)
 ORDER BY a.SLE_NO

실행계획 학습용 예시 · 행 수는 실데이터 규모

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     23 |00:00:00.08 |    8223 |
|   1 |  SORT ORDER BY                        |                |      1 |     12 |     23 |00:00:00.08 |    8223 |
|*  2 |   HASH JOIN SEMI                      |                |      1 |     12 |     23 |00:00:00.08 |    8223 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    140 |    150 |00:00:00.01 |      19 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    700 |    706 |00:00:00.01 |       5 |
|*  5 |    TABLE ACCESS FULL                  | CF11           |      1 |  12463 |  41280 |00:00:00.07 |    8204 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter("A"."SHP_NO"=:V_SHP)
   4 - access("A"."SLE_DT"=:V_DT)
   5 - filter("B"."ITM_NO"=:V_ITM)

문제

5번 줄의 8,204블록이 무엇을 읽은 것인지, 이 화면에 필요한 일과 비교해 설명하시오.
결과가 바뀌지 않게 영수증 150장만큼만 일하도록 SQL을 고치시오. 2021년의 규칙이 이 화면에 맞지 않는 이유도 쓰시오.
서브쿼리를 먼저 읽고(드라이빙) 판매 쪽을 찾아가는 모양으로 바꾸면 어떻게 될지 숫자로 어림하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 힌트를 뗀다: 바깥 150장마다 CF11_PK (SLE_NO, ITM_NO) 로 한 건씩 확인 (NL 세미 조인)
SELECT a.SLE_NO, a.SLE_HMS
  FROM CF10 a
 WHERE a.SLE_DT = :v_dt
   AND a.SHP_NO = :v_shp
   AND EXISTS (SELECT 1
                 FROM CF11 b
                WHERE b.SLE_NO = a.SLE_NO
                  AND b.ITM_NO = :v_itm)
 ORDER BY a.SLE_NO;
-- 방식을 적어 두려면 서브쿼리에 /*+ UNNEST NL_SJ */, FILTER 로 두려면 /*+ NO_UNNEST */

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 규모

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     23 |00:00:00.01 |     469 |
|   1 |  SORT ORDER BY                        |                |      1 |     12 |     23 |00:00:00.01 |     469 |
|   2 |   NESTED LOOPS SEMI                   |                |      1 |     12 |     23 |00:00:00.01 |     469 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    140 |    150 |00:00:00.01 |      19 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    700 |    706 |00:00:00.01 |       5 |
|*  5 |    INDEX UNIQUE SCAN                  | CF11_PK        |    150 |      1 |     23 |00:00:00.01 |     450 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."SHP_NO"=:V_SHP)
   4 - access("A"."SLE_DT"=:V_DT)
   5 - access("B"."SLE_NO"="A"."SLE_NO" AND "B"."ITM_NO"=:V_ITM)

모범 답안 서술

5번 줄은 CF11을 처음부터 끝까지 읽어(8,204블록) 아메리카노가 든 판매 줄 41,280줄을 2년 치 전부에서 골라낸 것이다. 해시 세미 조인은 그 41,280줄을 그날 강남점 영수증 150장(3번 줄)과 대어 보고 23장만 남겼다. 화면에 필요한 일은 영수증 150장이 그 상품을 담고 있는지 하나씩 확인하는 것뿐인데, 판매상품을 통째로 읽었다. 원인은 HASH_SJ 힌트다. 세미 조인 방식을 해시로 정하면 서브쿼리 쪽을 한 번에 다 읽어야 하는데, 서브쿼리에는 날짜도 매장도 없다. 2021년 규칙은 '전 매장 한 달 판매 집계'처럼 바깥이 영수증 수만 장일 때 정한 것이다. 그때는 영수증마다 인덱스를 찾는 것보다 판매상품을 한 번 훑는 쪽이 쌌다. 이 화면은 바깥이 150장이라 반대다. 힌트를 떼면 옵티마이저는 NL 세미 조인을 고른다. 영수증 한 장마다 CF11_PK (SLE_NO, ITM_NO)로 (영수증번호, 상품) 한 건을 찾는다(5번 줄 Starts 150, 한 번에 3블록). Buffers는 8,223 → 469로 준다. NO_UNNEST로 FILTER를 두어도 영수증번호가 유일해 같은 일을 한다. 서브쿼리를 먼저 읽게 하면 더 나쁘다. 아메리카노 판매 줄 41,280줄(8,204블록)을 읽고 영수증번호의 중복을 없앤 뒤(HASH UNIQUE) 영수증 4만여 장을 CF10_PK로 하나씩 찾아가 날짜와 매장을 거른다. 대략 8,204 + 41,280 × 4 ≈ 17만 블록이다. 서브쿼리를 먼저 처리할지 나중에 처리할지는 어느 쪽 집합이 작은가로 정한다.

해설

힌트의 범위: 힌트는 SQL 하나에 대한 판단이다. 화면마다 바깥 집합의 크기가 다르므로 '서브쿼리는 해시 세미 조인으로' 같은 일괄 규칙은 맞지 않는다. 규칙으로 남기려면 어느 크기에서 정한 것인지를 함께 적는다(교재 06화 힌트 유효기간).
FILTER와 NL 세미 조인: 바깥이 작고 안쪽에 맞는 인덱스가 있으면 둘은 거의 같은 일을 한다. 차이는 캐시다. FILTER는 같은 입력 값의 결과를 기억하지만, 이 화면처럼 입력(영수증번호)이 유일하면 캐시가 할 일이 없다.
월 집계 화면은 그대로: 2021년의 월 집계 SQL은 바깥이 영수증 2만 장이라 해시 세미 조인이 맞다. 지울 것은 일괄 규칙이지, 그 SQL의 힌트가 아니다.
CF11_PK가 (SLE_NO, ITM_NO) 순서라서 영수증 쪽에서 들어가는 이 화면에는 새 인덱스가 필요 없다. 상품 쪽에서 들어가는 화면이라면 이야기가 다르다(SUBQ-021).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황