매장 상세에 「첫 판매일」 한 칸을 더했더니 3블록이던 조회가 305블록이 됐다

기초SELECT유통314 / 700

상황

본사 매장 관리 화면 맨 위에는 '마지막 판매일'이 있다. 유통지원팀이 영업 기간을 한눈에 보고 싶다며 '첫 판매일'을 같이 보여 달라고 했고, 개발자는 MAX만 있던 SQL에 MIN을 하나 더 넣었다. 화면은 여전히 빠르게 뜨지만, DBA가 이 SQL의 실행당 읽은 블록 수가 100배가 된 것을 찾아냈다. 매장 담당자들이 하루 3천 번쯤 연다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20220101' ~ '20231231' · 판매가 있는 매장 5곳(SHP_NO 1001 ~ 1005) · 매장마다 약 10만 건
인덱스 CF10_PK (SLE_NO), IX_CF10_SHP_DT (SHP_NO, SLE_DT): BLEVEL 2 · 리프 약 1,520블록(매장 하나에 약 300블록) · 통계 최신

원본 SQL

sql

SELECT MIN(SLE_DT) AS first_dt, MAX(SLE_DT) AS last_dt
  FROM CF10
 WHERE SHP_NO = :shp

실행계획 학습용 예시

xplan

[지난달까지 : MAX 하나]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CF10_SHP_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------
   3 - access("SHP_NO"=:SHP)

[지금 : MIN 과 MAX]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |      1 |00:00:00.02 |     305 |
|   1 |  SORT AGGREGATE              |                |      1 |      1 |      1 |00:00:00.02 |     305 |
|*  2 |   INDEX RANGE SCAN           | IX_CF10_SHP_DT |      1 |    100K|    100K|00:00:00.02 |     305 |
---------------------------------------------------------------------------------------------------------
   2 - access("SHP_NO"=:SHP)

문제

MAX 하나일 때는 3블록이던 조회가 MIN을 더하자 305블록이 된 까닭을 두 실행계획의 차이로 설명하시오.
같은 결과를 돌려주면서 다시 몇 블록으로 끝나도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 끝 하나씩 따로 묻는다 : 스칼라 서브쿼리 둘 → 각각 FIRST ROW (MIN/MAX)
SELECT (SELECT MIN(SLE_DT) FROM CF10 WHERE SHP_NO = :shp) AS first_dt,
       (SELECT MAX(SLE_DT) FROM CF10 WHERE SHP_NO = :shp) AS last_dt
  FROM DUAL

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE              |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CF10_SHP_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
|   4 |  SORT AGGREGATE              |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|   5 |   FIRST ROW                  |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  6 |    INDEX RANGE SCAN (MIN/MAX)| IX_CF10_SHP_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
|   7 |  FAST DUAL                   |                |      1 |      1 |      1 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SHP_NO"=:SHP)
   6 - access("SHP_NO"=:SHP)

모범 답안 서술

MAX만 있을 때는 3번 줄 INDEX RANGE SCAN (MIN/MAX)와 2번 줄 FIRST ROW가 보인다. 인덱스 (SHP_NO, SLE_DT)에서 매장 하나의 항목은 판매일 순서로 줄 서 있으므로, 그 범위의 오른쪽 끝 항목 하나가 곧 MAX다. 루트, 브랜치, 리프를 한 블록씩 읽어 3블록으로 끝난다. MIN을 더하면 한 SQL이 왼쪽 끝과 오른쪽 끝을 함께 알아야 한다. 오라클은 한 쿼리 블록 안에서 MIN과 MAX를 함께 구할 때는 FIRST ROW를 쓰지 않으므로, 2번 줄이 매장의 항목 10만 개(리프 약 300블록)를 처음부터 끝까지 읽고 1번 줄이 그중 최솟값과 최댓값을 고른다. 결과는 같지만 305블록을 읽는다. 끝을 하나씩 따로 물으면 된다. MIN과 MAX를 스칼라 서브쿼리 두 개로 나누면 각각 FIRST ROW로 3블록씩, 합계 6블록을 읽는다. Buffers는 305 → 6으로 준다. 하루 3천 번이면 하루 약 92만 블록이 약 1만 8천 블록이 된다.

해설

스칼라 서브쿼리 대신: SELECT MIN(SLE_DT) … UNION ALL SELECT MAX(SLE_DT) …로 두 줄을 받아도 같다. 화면이 한 줄을 기대하면 스칼라 서브쿼리 쪽이 편하다.
FIRST ROW가 되려면 MIN/MAX 인자 컬럼이 등치 조건 컬럼 바로 뒤에 있어야 하고, 조건 컬럼과 인자 컬럼이 모두 인덱스에 있어야 한다. AND SLE_DOW = '1'처럼 인덱스에 없는 컬럼 조건이 하나라도 붙으면 테이블에서 확인해야 하므로 FIRST ROW가 되지 않는다(SORT-010).
MAX(TO_DATE(SLE_DT, 'YYYYMMDD'))처럼 인자를 가공해도 FIRST ROW가 되지 않는다. 가공은 TO_DATE(MAX(SLE_DT), 'YYYYMMDD')처럼 바깥에서 한다. 'YYYYMMDD' 문자열은 날짜와 같은 순서로 정렬되므로 결과가 같다.
화면이 빨라서 아무도 몰랐다. 한 번에 305블록은 작지만, 체감 대신 V$SQL의 BUFFER_GETS / EXECUTIONS로 SQL별 일량을 보는 습관이 이런 회귀를 잡는다(교재 07화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황