PART-010

행사일 엿새의 판매를 세는데 2년 치 판매 칸을 다 연다

중급SELECT유통385 / 700

상황

마케팅팀이 관리하는 행사 테이블 MK_EVT_DAY에는 행사일이 NUMBER(8)(20230505)로 들어 있다. 영업관리팀의 「행사일 판매 분석」은 연도를 고르면 그해 행사일의 매장별 판매 건수를 낸다. 분기 리뷰 주간에 수십 번 돌린다. 2023년 행사일은 엿새이고 5월, 6월, 9월, 12월의 네 달에 걸쳐 있다.

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눠 CTAS로 지었다. 칸 하나는 약 20,800행이고 FULL로 약 191블록, 24칸 전체는 약 4,610블록이다. 2023년 5월은 17번째, 12월은 24번째 칸이다.
SLE_DT는 VARCHAR2(8) '20230505'이다. 행사일 하루 판매는 약 700건이고 엿새면 4,236건이다.
MK_EVT_DAY 행사일(문제 설정, 마케팅팀 테이블): 40행 · 4블록 · EVT_CD · EVT_YMD NUMBER(8) · EVT_YY VARCHAR2(4). EVT_YMD는 모두 8자리다.
이 조회에 쓸 인덱스는 없다. 통계는 최신이다.

원본 SQL

sql

SELECT c.SHP_NO, COUNT(*) AS evt_sle_cnt
  FROM CF10_P c
 WHERE c.SLE_DT IN (SELECT e.EVT_YMD
                      FROM MK_EVT_DAY e
                     WHERE e.EVT_YY = :v_yy)
 GROUP BY c.SHP_NO

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |      5 | 00:00:00.11 |    4614 |        |       |
|   1 |  HASH GROUP BY         |            |      1 |      5 |      5 | 00:00:00.11 |    4614 |        |       |
|*  2 |   HASH JOIN RIGHT SEMI |            |      1 |   5000 |   4236 | 00:00:00.11 |    4614 |        |       |
|*  3 |    TABLE ACCESS FULL   | MK_EVT_DAY |      1 |      7 |      6 | 00:00:00.01 |       4 |        |       |
|   4 |    PARTITION RANGE ALL |            |      1 |   500K |   500K | 00:00:00.05 |    4610 |      1 |    25 |
|   5 |     TABLE ACCESS FULL  | CF10_P     |     24 |   500K |   500K | 00:00:00.04 |    4610 |      1 |    25 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."EVT_YMD"=TO_NUMBER("C"."SLE_DT"))
   3 - filter("E"."EVT_YY"=:V_YY)

문제

CF10_P를 몇 칸 열었는지 쓰고, 행사일이 네 달에 몰려 있는데도 칸을 고르지 못한 까닭을 Predicate Information으로 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 행사일이 든 칸만 읽게 하고, 고친 뒤 실행계획에서 무엇이 칸을 고르는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 형변환은 작은 쪽(서브쿼리 값)에서 한다 → 파티션 키 SLE_DT 가 맨몸으로 조인된다
SELECT c.SHP_NO, COUNT(*) AS evt_sle_cnt
  FROM CF10_P c
 WHERE c.SLE_DT IN (SELECT TO_CHAR(e.EVT_YMD)
                      FROM MK_EVT_DAY e
                     WHERE e.EVT_YY = :v_yy)
 GROUP BY c.SHP_NO;
-- 근본 처방 : 행사 테이블의 날짜 컬럼도 SLE_DT 와 같은 VARCHAR2(8) 로 맞춘다

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

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers |  Pstart |   Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |      1 |        |      5 | 00:00:00.02 |     770 |         |         |
|   1 |  HASH GROUP BY                 |            |      1 |      5 |      5 | 00:00:00.02 |     770 |         |         |
|*  2 |   HASH JOIN RIGHT SEMI         |            |      1 |   4900 |   4236 | 00:00:00.02 |     770 |         |         |
|   3 |    PART JOIN FILTER CREATE     | :BF0000    |      1 |      7 |      6 | 00:00:00.01 |       4 |         |         |
|*  4 |     TABLE ACCESS FULL          | MK_EVT_DAY |      1 |      7 |      6 | 00:00:00.01 |       4 |         |         |
|   5 |    PARTITION RANGE JOIN-FILTER |            |      1 |   500K |  84788 | 00:00:00.01 |     766 | :BF0000 | :BF0000 |
|   6 |     TABLE ACCESS FULL          | CF10_P     |      4 |   500K |  84788 | 00:00:00.01 |     766 | :BF0000 | :BF0000 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."SLE_DT"=TO_CHAR("E"."EVT_YMD"))
   4 - filter("E"."EVT_YY"=:V_YY)

  (블룸 필터가 고른 칸 — 17번 P202305 · 18번 P202306 · 21번 P202309 · 24번 P202312)

모범 답안 서술

4번 줄의 PARTITION RANGE ALL(Pstart 1, Pstop 25)과 5번 줄의 Starts 24를 보면 데이터가 있는 24칸을 다 열었다는 것을 알 수 있다. 행사일 판매는 4,236건뿐인데 50만 행을 읽어 해시 조인에 넣었다(4,610블록). 2번 줄의 조인 조건은 "E"."EVT_YMD"=TO_NUMBER("C"."SLE_DT")이다. 서브쿼리가 돌려주는 EVT_YMD는 NUMBER이고 SLE_DT는 VARCHAR2라서, 오라클은 문자 쪽인 SLE_DT를 숫자로 바꿨다(묵시적 형변환). 바뀐 쪽이 큰 테이블의 파티션 키다. 해시 조인은 먼저 읽은 행사일 6행으로 블룸 필터를 만들어 반대쪽 칸을 거를 수 있는데, 그러려면 반대쪽 파티션 키가 맨몸으로 조인되어야 한다. TO_NUMBER(SLE_DT)로는 그 필터를 쓸 수 없어 24칸을 모두 읽었다. 형변환을 서브쿼리 쪽 값에서 한다(SELECT TO_CHAR(e.EVT_YMD) …). 그러면 조인 조건이 "C"."SLE_DT"=TO_CHAR("E"."EVT_YMD")가 되어 파티션 키가 맨몸으로 남는다. 3번 줄 PART JOIN FILTER CREATE가 행사일 6행으로 블룸 필터 :BF0000을 만들고, 5번 줄 PARTITION RANGE JOIN-FILTER가 그 필터로 행사일이 든 네 칸(17번, 18번, 21번, 24번)만 연다(Starts 4). Buffers는 4,614 → 770으로 준다. EVT_YMD가 모두 8자리라서 TO_CHAR 결과가 SLE_DT 형식과 같고, 결과는 바뀌지 않는다. 근본 처방은 행사 테이블의 날짜 컬럼도 VARCHAR2(8)로 맞추는 것이다. 같은 뜻의 컬럼은 같은 타입으로 둔다.

해설

숫자는 문자를 이긴다(교재 03화 §1). 비교하는 두 값 중 문자 쪽이 숫자로 바뀌고, 그 문자가 큰 테이블의 파티션 키이면 칸 고르기가 막힌다. 형변환은 늘 작은 쪽, 값 쪽에서 직접 한다.
블룸 필터 Pruning은 해시 조인에서 반대쪽 파티션 키가 조인 키일 때 생긴다(교재 14화 §7.1 판독표의 :BF0000). 행사일이 열두 달에 고루 흩어져 있었다면 블룸 필터로도 거의 다 열었을 것이다.
다른 정답: 서브쿼리로 범위를 만들어 c.SLE_DT BETWEEN (SELECT TO_CHAR(MIN(EVT_YMD)) …) AND (SELECT TO_CHAR(MAX(EVT_YMD)) …)를 더하면 KEY로 5월 ~ 12월의 8칸을 읽는다(약 1,530블록). 블룸 필터판보다 많다.
NL 조인으로 풀면 행사일 6행마다 KEY로 칸을 찾아가지만 5월 칸을 두 번 여는 등 6칸 × 191블록을 읽는다. 칸 안에 쓸 인덱스가 없으면 해시 조인과 블룸 필터 쪽이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황