날짜 칸을 글자로 바꿔 준 다음 날부터 연간 주문 금액 내려받기가 세 배 느려졌다

기초SELECT한빛유통337 / 700

상황

재무팀과 영업기획팀의 분석 시트 20개는 업무 시간 동안 매시 정각에 「2024년 주문 금액 원자료」(주문일과 금액, 주문일순)를 자동으로 내려받는다. 하루 약 200번이다. 지난주 재무팀이 "엑셀이 날짜를 제멋대로 바꾸니 주문일을 'YYYY-MM-DD' 글자로 달라"고 했고, 개발자는 시트의 칸 이름이 바뀌지 않도록 별칭을 그대로 ORD_DT로 둔 채 컬럼을 TO_CHAR로 감쌌다. 그다음 날부터 내려받기가 0.12초에서 0.38초로 늘었다. DBA는 인덱스만 읽던 SQL의 실행계획에 정렬 줄과 10MB 작업 공간이 새로 생긴 것을 찾았다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정, 2022-01-01 ~ 2024-12-31) · 2024년 주문 333,968행
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)(02화에서 만든 커버링 인덱스): BLEVEL 2 · 리프 약 3,320블록(한 블록에 약 300개) · 2024년 범위는 리프 약 1,110블록
내려받기 도구는 한 번에 5,000행씩 가져온다(Fetch 약 67번). 통계는 최신이다.

원본 SQL

sql

SELECT TO_CHAR(o.ORD_DT, 'YYYY-MM-DD') AS ORD_DT,
       o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'
 ORDER BY ORD_DT

실행계획 학습용 예시

xplan

[지난주까지 : SELECT o.ORD_DT, o.ORD_AMT … ORDER BY ORD_DT]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |    333K|00:00:00.12 |    1179 |       |       |          |
|*  1 |  INDEX RANGE SCAN  | IX_ORD_BIG_DT_AMT |      1 |    334K|    333K|00:00:00.12 |    1179 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

[지금 : TO_CHAR(o.ORD_DT, 'YYYY-MM-DD') AS ORD_DT … ORDER BY ORD_DT]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |    333K|00:00:00.38 |    1112 |       |       |          |
|   1 |  SORT ORDER BY     |                   |      1 |    334K|    333K|00:00:00.38 |    1112 |    12M|  1393K|   10M (0)|
|*  2 |   INDEX RANGE SCAN | IX_ORD_BIG_DT_AMT |      1 |    334K|    333K|00:00:00.07 |    1112 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

문제

같은 인덱스를 같은 범위만큼 읽는데 지난주에는 없던 1번 줄 SORT ORDER BY가 생긴 까닭을 ORDER BY ORD_DT가 무엇을 가리키는지로 설명하시오.
재무팀이 원한 날짜 모양과 칸 이름을 지키면서 정렬 줄이 다시 사라지도록 SQL을 고치고, 고친 SQL의 결과 순서가 지금과 같다고 말할 수 있는 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 칸 모양과 칸 이름은 그대로 · ORDER BY 는 테이블 컬럼을 가리킨다 (테이블 별칭 o. 을 붙인다)
SELECT TO_CHAR(o.ORD_DT, 'YYYY-MM-DD') AS ORD_DT,
       o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'
 ORDER BY o.ORD_DT

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |    333K|00:00:00.12 |    1179 |
|*  1 |  INDEX RANGE SCAN  | IX_ORD_BIG_DT_AMT |      1 |    334K|    333K|00:00:00.12 |    1179 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

모범 답안 서술

1번 줄 SORT ORDER BY는 ORDER BY ORD_DT가 테이블 컬럼이 아니라 SELECT 절의 별칭 ORD_DT, 곧 TO_CHAR(o.ORD_DT, 'YYYY-MM-DD')로 만든 문자열을 가리켜서 생겼다. ORDER BY에 쓴 이름이 SELECT 절의 별칭과 같으면 오라클은 별칭을 먼저 쓴다. 인덱스 IX_ORD_BIG_DT_AMT에 줄 서 있는 것은 날짜 값이고 이 문자열은 인덱스 어디에도 없으므로, 옵티마이저는 인덱스 순서로 정렬을 대신할 수 없다. 그래서 2번 줄이 2024년 범위 333,968행을 다 읽은 뒤 1번 줄이 그 행을 모두 문자열로 다시 정렬한다(OMem 12M, Used-Mem 10M). 첫 행은 정렬이 끝난 뒤에야 나간다. 고치는 방법은 ORDER BY o.ORD_DT처럼 테이블 별칭을 붙여 컬럼을 가리키는 것이다. SELECT 절의 날짜 모양과 칸 이름(ORD_DT)은 재무팀 요구대로 둔다. 그러면 정렬 키가 인덱스의 첫 컬럼과 같아져 인덱스 범위 스캔 순서 그대로 내보내고, 정렬 줄과 10MB 작업 공간이 사라진다(A-Time 0.38 → 0.12초). 결과 순서는 지금과 같다. 'YYYY-MM-DD'는 자릿수가 고정된 문자열이라 문자열 순서가 날짜 순서와 같고, ORD_DT가 전부 자정이라 날짜 하나에 문자열 하나가 대응한다. 같은 날짜 안의 순서는 원래 SQL에서도 정해지지 않았으므로(지금은 정렬이 아무렇게나 두고, 고친 뒤에는 인덱스 순서인 금액순이다) 주문일순이라는 요구를 어기지 않는다. Buffers는 고친 뒤 1,112 → 1,179로 조금 는다. 정렬이 있으면 범위를 한 번에 다 읽고 작업 공간에서 내보내지만, 정렬이 없으면 Fetch Call(약 67번)마다 리프 블록을 다시 찾아 읽기 때문이다. 이 SQL에서 줄어드는 것은 읽는 양이 아니라 33만 행 정렬(CPU와 PGA)과 첫 행까지 기다리는 시간이다.

해설

함정: ORDER BY 1로 바꾸면 SELECT 절의 첫 번째 항목, 곧 문자열을 가리키므로 정렬이 그대로 남는다. 별칭을 ORD_YMD처럼 바꾸고 ORDER BY ORD_DT를 두는 것도 답이지만, 시트의 칸 이름이 바뀌므로 시트 쪽도 함께 고쳐야 한다.
날짜를 글자로 바꾸는 일을 시트 쪽에서 해도 된다. SQL은 DATE를 그대로 내보내고 엑셀에서 그 칸의 서식을 텍스트로 정하면 SQL은 지난주 모양 그대로다.
시분초가 있는 T_ORD에서 같은 일을 하면 'YYYY-MM-DD' 문자열 하나에 여러 시각이 묶인다. 날짜끼리의 순서는 맞지만 같은 날짜 안의 시각 순서는 문자열 정렬로 정해지지 않는다. 시각순까지 필요하면 컬럼으로 정렬한다.
정렬이 있는 계획의 Buffers가 더 작게 나오는 것은 Fetch 방식의 차이다(교재 09화 §11 ARRAYSIZE). 정렬이 있는지는 Buffers보다 OMem, Used-Mem 열에 값이 있는지로 먼저 본다(교재 09화 §1).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황