연간으로 고르면 일별 매출 그래프의 선이 앞뒤로 엉킨다

중급SELECT한빛유통112 / 700

상황

재무팀의 「일별 매출 추이」는 기간을 고르면 일자별 주문 건수와 매출을 선 그래프로 그린다. 그래프 부품은 받은 행 순서대로 점을 잇는다. 한 달을 고를 때는 문제가 없었는데, 올해부터 1년을 고르기 시작하자 선이 앞뒤로 엉킨 그림이 나온다. SQL에는 개발 당시의 주석 -- 인덱스가 날짜순이라 ORDER BY 는 뺐다 (정렬 비용 절약)이 남아 있다. 월간 메뉴와 연간 메뉴는 화면이 달라 커서도 따로 있다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 2022-01-01 ~ 2024-12-30(하루 913건, 전부 자정) · 2024년 6월 27,390건 · 2024년 333,245건(365일)
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): BLEVEL 2 · 리프 3,206블록(교재 04화 실측) · 통계 최신 · 다른 인덱스 없음
db_file_multiblock_read_count = 128

원본 SQL

sql

SELECT ORD_DT, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT
 -- 인덱스가 날짜순이라 ORDER BY 는 뺐다 (정렬 비용 절약)

실행계획 학습용 예시 · 범위별 Buffers(한 달 91, 1년 FAST FULL SCAN 3,227)는 교재 04화 실측

xplan

[월간 메뉴 — 2024년 6월]
-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |      1 |        |     30 |00:00:00.01 |      91 |
|   1 |  SORT GROUP BY NOSORT |                   |      1 |     30 |     30 |00:00:00.01 |      91 |
|*  2 |   FILTER              |                   |      1 |        |  27390 |00:00:00.01 |      91 |
|*  3 |    INDEX RANGE SCAN   | IX_ORD_BIG_DT_AMT |      1 |  27390 |  27390 |00:00:00.01 |      91 |
-----------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

[연간 메뉴 — 2024년]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |      1 |        |    365 |00:00:00.12 |    3227 |          |
|   1 |  HASH GROUP BY         |                   |      1 |    365 |    365 |00:00:00.12 |    3227 | 1489K (0)|
|*  2 |   FILTER               |                   |      1 |        |    333K|00:00:00.09 |    3227 |          |
|*  3 |    INDEX FAST FULL SCAN| IX_ORD_BIG_DT_AMT |      1 |    334K|    333K|00:00:00.07 |    3227 |          |
----------------------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[연간 메뉴 · INDEX_RS 힌트로 범위 스캔을 강제하면] 약 1,072블록 (리프 3,206 × 33.3% + 수직 탐색)

문제

월간 메뉴와 연간 메뉴의 1번, 3번 줄을 비교해 연간 조회에서만 행 순서가 섞이는 이유를 설명하시오.
연간 조회에서 옵티마이저가 범위 스캔(약 1,072블록)보다 Buffers가 많은 INDEX FAST FULL SCAN(3,227블록)을 고른 이유를 블록을 읽는 방식으로 설명하시오.
기간과 상관없이 일자순으로 나오도록 SQL을 고치고, 고친 뒤 정렬에 드는 비용을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT ORD_DT, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT
 ORDER BY ORD_DT;      -- 결과의 순서는 실행계획이 아니라 SQL 이 정한다

개선 후 실행계획 학습용 예시 · 범위별 Buffers(한 달 91, 1년 FAST FULL SCAN 3,227)는 교재 04화 실측

xplan

[연간 메뉴 — 2024년 · ORDER BY 를 넣은 뒤]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |      1 |        |    365 |00:00:00.12 |    3227 |          |
|   1 |  SORT GROUP BY         |                   |      1 |    365 |    365 |00:00:00.12 |    3227 |   28K (0)|
|*  2 |   FILTER               |                   |      1 |        |    333K|00:00:00.09 |    3227 |          |
|*  3 |    INDEX FAST FULL SCAN| IX_ORD_BIG_DT_AMT |      1 |    334K|    333K|00:00:00.07 |    3227 |          |
----------------------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[월간 메뉴] 그대로 INDEX RANGE SCAN + SORT GROUP BY NOSORT · 91 (ORDER BY 가 인덱스 순서와 같아 정렬이 붙지 않는다)

모범 답안 서술

월간 메뉴는 3번 줄이 INDEX RANGE SCAN이다. 리프를 ORD_DT 순서대로 따라가며 읽으므로, 1번 줄 SORT GROUP BY NOSORT가 날짜가 바뀔 때마다 한 줄씩 내보낸다. 결과가 날짜순으로 나온 것은 이 실행계획 덕분이었을 뿐, SQL이 보장한 것이 아니다. 연간 메뉴는 3번 줄이 INDEX FAST FULL SCAN으로 바뀌었다. FFS는 리프를 키 순서로 따라가지 않고 인덱스 세그먼트를 블록이 놓인 순서대로 통째로 읽는다. 그래서 행이 날짜순으로 나오지 않고, 1번 줄 HASH GROUP BY도 해시로 묶을 뿐 정렬하지 않는다. 결과의 순서가 보장되지 않으니 그래프 선이 엉킨다. 옵티마이저가 Buffers가 더 많은 FFS를 고른 것은 비용을 블록 수가 아니라 I/O 방식으로 따지기 때문이다. 범위 스캔은 약 1,072블록을 한 블록씩(싱글블록) 읽는다. FFS는 3,227블록을 한 번에 128블록씩 멀티블록으로 읽으므로 I/O 요청이 약 26번이다. 그래서 Cost는 FFS가 더 낮게 나온다. 1년이 인덱스의 3분의 1이나 되면 이렇게 갈아탄다(교재 04화 실측에서도 1년 범위에서 3,227블록 FFS로 바뀌었다). 고칠 곳은 실행계획이 아니라 SQL이다. ORDER BY ORD_DT를 넣으면 어느 계획이든 일자순이 보장된다. 연간 조회는 FFS 뒤에 SORT GROUP BY가 붙지만, 정렬하는 것은 그룹 365개뿐이라 메모리 28K 안에서 끝나고 Buffers도 3,227 그대로다. 정렬 비용은 거의 없다. 월간 조회는 범위 스캔이 이미 ORD_DT 순서이므로 옵티마이저가 정렬을 붙이지 않고 SORT GROUP BY NOSORT 91블록 그대로다. 주석의 '정렬 비용 절약'은 ORDER BY를 빼서 얻은 것이 아니라, 처음부터 들지 않는 비용이었다.

해설

같은 함정은 다른 곳에도 있다. 테이블을 BATCHED로 찾아가거나, 병렬로 읽거나, 옵티마이저가 GROUP BY를 해시로 바꾸면 'ORDER BY 없이도 잘 정렬되어 나오던' 결과가 어느 날 섞인다. 결과 순서는 ORDER BY로만 보장된다.
ORDER BY를 넣는다고 인덱스로 정렬을 대신하는 길이 막히지 않는다. 정렬 키와 인덱스 순서가 같으면 옵티마이저가 알아서 정렬을 생략한다(교재 09화 §7).
연간 조회에 INDEX_RS 힌트로 범위 스캔을 강제하면 Buffers는 3,227 → 약 1,072로 줄지만 1,072블록을 한 블록씩 읽는다. 블록이 모두 버퍼 캐시에 있으면 범위 스캔이 CPU를 조금 덜 쓰고, 디스크에서 읽어야 하면 FFS가 빠르다. 하루 몇 번 여는 재무팀 화면이라 옵티마이저에 맡긴다.
범위가 넓어지면 스캔 방식이 바뀔 수 있다. 짧은 범위에서 본 실행계획의 성질(정렬된 결과)에 기대어 SQL을 쓰면 안 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 범위별 Buffers(한 달 91, 1년 FAST FULL SCAN 3,227)는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 범위별 Buffers(한 달 91, 1년 FAST FULL SCAN 3,227)는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황