PART-005

하룻밤 판매 40건을 세는 조회가 판매 테이블을 통째로 읽는다

중급SELECT유통380 / 700

상황

본사 영업관리팀의 「야간 판매 점검」은 영업일 22시부터 다음 날 06시까지의 판매를 매장별, 일자별로 센다. 야간 운영 매장을 점검하려고 매일 아침 담당자 다섯 명이 몇 번씩 돌린다. 화면은 시작과 끝을 'YYYYMMDDHH24MISS' 14자리 문자열(:v_from, :v_to)로 넘긴다. 아래는 2023년 6월 30일 22시 ~ 7월 1일 06시('20230630220000' ~ '20230701060000')를 넘긴 실행이다.

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312, 경계는 VALUES LESS THAN ('20220201') 꼴)과 빈 PMAX 1칸으로 나눠 CTAS로 지었다. 칸 하나는 약 20,800행이고 FULL로 읽으면 약 191블록, 24칸 전체는 약 4,610블록이다.
SLE_DT는 VARCHAR2(8) '20230630', SLE_HMS는 VARCHAR2(6) '223015'이다. 둘 다 자릿수가 고정이고 숫자만 들어 있다(교재 03화 실측).
P202306은 18번째 칸, P202307은 19번째 칸이다. 22시 ~ 06시 판매는 하룻밤에 약 40건이다.
이 조회에 쓸 인덱스는 없다. 통계는 최신이다.

원본 SQL

sql

SELECT SHP_NO, SLE_DT, COUNT(*) AS night_cnt
  FROM CF10_P
 WHERE SLE_DT || SLE_HMS BETWEEN :v_from AND :v_to
 GROUP BY SHP_NO, SLE_DT
 ORDER BY SHP_NO, SLE_DT

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |     10 | 00:00:00.09 |    4610 |        |       |
|   1 |  SORT GROUP BY        |        |      1 |   1250 |     10 | 00:00:00.09 |    4610 |        |       |
|   2 |   PARTITION RANGE ALL |        |      1 |   1250 |     41 | 00:00:00.09 |    4610 |      1 |    25 |
|*  3 |    TABLE ACCESS FULL  | CF10_P |     24 |   1250 |     41 | 00:00:00.08 |    4610 |      1 |    25 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("SLE_DT"||"SLE_HMS">=:V_FROM AND "SLE_DT"||"SLE_HMS"<=:V_TO))

문제

칸을 몇 개 열었는지 실행계획에서 찾고, 두 컬럼을 이어 붙인 조건으로는 칸을 고르지 못하는 까닭을 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 필요한 칸만 열게 하고, 이번 실행에서 몇 칸을 열지 쓰시오. 이어 붙인 조건을 지워도 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 칸을 고르는 조건(SLE_DT 맨몸)을 더하고, 시각 경계는 원래 조건이 지킨다
SELECT SHP_NO, SLE_DT, COUNT(*) AS night_cnt
  FROM CF10_P
 WHERE SLE_DT BETWEEN SUBSTR(:v_from, 1, 8) AND SUBSTR(:v_to, 1, 8)
   AND SLE_DT || SLE_HMS BETWEEN :v_from AND :v_to
 GROUP BY SHP_NO, SLE_DT
 ORDER BY SHP_NO, SLE_DT;

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |     10 | 00:00:00.01 |     384 |        |       |
|   1 |  SORT GROUP BY              |        |      1 |      1 |     10 | 00:00:00.01 |     384 |        |       |
|*  2 |   FILTER                    |        |      1 |        |     41 | 00:00:00.01 |     384 |        |       |
|   3 |    PARTITION RANGE ITERATOR |        |      1 |      1 |     41 | 00:00:00.01 |     384 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | CF10_P |      2 |      1 |     41 | 00:00:00.01 |     384 |    KEY |   KEY |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SUBSTR(:V_FROM,1,8)<=SUBSTR(:V_TO,1,8))
   4 - filter(("SLE_DT">=SUBSTR(:V_FROM,1,8) AND "SLE_DT"<=SUBSTR(:V_TO,1,8) AND
              "SLE_DT"||"SLE_HMS">=:V_FROM AND "SLE_DT"||"SLE_HMS"<=:V_TO))

  (실행 때 고른 칸 — 18번 P202306 · 19번 P202307)

모범 답안 서술

2번 줄의 PARTITION RANGE ALL과 Pstart 1, Pstop 25, 3번 줄의 Starts 24를 보면 데이터가 있는 24칸을 다 열었다는 것을 알 수 있다. 하룻밤 41건을 세려고 2년 치 4,610블록을 읽었다. 칸막이에는 SLE_DT 값의 경계('20230701' 미만 같은)만 붙어 있다. 조건은 SLE_DT || SLE_HMS처럼 두 컬럼을 이어 붙인 값이라 파티션 키가 맨몸으로 오지 않았고, 옵티마이저는 이어 붙인 값이 어느 칸에 드는지 거꾸로 풀지 않는다. 컬럼을 가공한 것과 같아서 칸을 고르지 못하고, 24칸을 다 연 뒤 행마다 이어 붙여 비교한다. 칸을 고를 조건 SLE_DT BETWEEN SUBSTR(:v_from, 1, 8) AND SUBSTR(:v_to, 1, 8)을 더한다. 가공은 바인드 쪽에만 있으므로 Pstart와 Pstop은 KEY로 찍히고, 이번 실행에서는 18번(P202306)과 19번(P202307) 두 칸만 연다(Starts 2). Buffers는 4,610 → 384로 준다. 이어 붙인 조건은 지우면 안 된다. 더한 SLE_DT 조건은 6월 30일과 7월 1일 하루 전체를 고르므로, 그것만 남기면 30일 22시 전과 1일 06시 뒤의 판매까지 세어 결과가 바뀐다. 칸은 SLE_DT 조건으로 고르고, 시각 경계는 원래 조건으로 거른다.

해설

다른 정답: (SLE_DT = :d1 AND SLE_HMS >= :h1) OR (SLE_DT > :d1 AND SLE_DT < :d2) OR (SLE_DT = :d2 AND SLE_HMS <= :h2)처럼 날짜와 시각을 나눠 써도 칸을 고를 수 있다. 다만 조건이 길어지고 화면이 바인드 변수를 넷으로 나눠 넘겨야 한다.
이 방법은 SLE_DT가 고정 8자리 숫자 문자열이라서 성립한다. 그래서 문자 범위 비교가 날짜 순서와 같다(교재 03화 §2). SUBSTR(:v_from, 1, 8)은 값 쪽 가공이라 칸 고르기를 막지 않는다.
두 칸을 골라도 하룻밤 41건을 찾으려고 384블록을 읽는다. 자주 돌린다면 LOCAL 인덱스 (SLE_DT, SLE_HMS)를 검토한다. 파티션 키가 선두라서 칸 고르기와 칸 안의 범위 스캔을 함께 할 수 있다.
날짜와 시각을 DATE 컬럼 하나로 다시 설계하면 이런 조건이 처음부터 생기지 않는다. 레거시라서 당장은 어렵다(교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황