주문 관리 메뉴의 SQL 다섯 개 가운데 무엇을 고칠지 실행계획만 보고 정해 달라고 한다

실전SELECT한빛유통122 / 700

상황

한빛유통은 분기마다 튜닝 리뷰를 연다. 이번에는 「주문 관리」 메뉴의 SQL 다섯 개가 올라왔다. 박팀장은 이렇게 말했다. "다섯 개 다 인덱스를 탄다는데 실행계획에 FULL SCAN, SKIP SCAN 같은 말이 섞여 있어 불안하다. 무엇을 고치고 무엇을 둘지 근거와 함께 정리해 와라." Q1은 하루 수백 번, Q2와 Q3는 하루 몇 번 내려받는 파일, Q4는 하루 수천 번, Q5는 하루 수백 번 호출된다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록(블록당 약 112행) · ORD_SEQ 순으로 쌓였다 · ORD_DT DATE(시분초 있음, 2023 ~ 2024, 하루 약 137건) · 같은 날짜의 주문은 ORD_SEQ가 730씩 떨어져 테이블 전체에 흩어져 있다 · 2024-06-14 137건 · 2024년 6월 4,110건
CUS_ID NOT NULL 2,000명(고객당 50건, 역시 흩어져 있다) · PAY_TP CARD · CASH · POINT가 1/3씩(하루 안에서도 셋이 고르게 있다)
PK_T_ORD (ORD_SEQ): BLEVEL 1 · 리프 약 190블록 · CLUSTERING_FACTOR 896
IX_T_ORD_CUS (CUS_ID): BLEVEL 1 · 리프 약 330블록 · CLUSTERING_FACTOR 약 100,000
IX_T_ORD_TP_DT (PAY_TP, ORD_DT): BLEVEL 1 · 리프 약 330블록 · CLUSTERING_FACTOR 약 100,000 · 통계 최신
세션의 정렬 작업 영역은 약 20MB까지다

원본 SQL

sql

-- Q1 「전체 주문 수」 (메뉴 머리말 · 하루 수백 번)
SELECT COUNT(*) FROM T_ORD;

-- Q2 「주문 목록 받기 · 주문번호순」 (하루 몇 번 · 10만 행 전부)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 ORDER BY ORD_SEQ;

-- Q3 「주문 목록 받기 · 고객순」 (하루 몇 번 · 10만 행 전부 · 미리보기가 느리다고 힌트를 넣었다)
SELECT /*+ FIRST_ROWS(100) */ ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 ORDER BY CUS_ID;

-- Q4 「결제수단별 일 건수」 (하루 수천 번)
SELECT PAY_TP, COUNT(*)
  FROM T_ORD
 WHERE ORD_DT >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_dt, 'YYYYMMDD') + 1
 GROUP BY PAY_TP;

-- Q5 「기간 주문 목록」 (하루 수백 번 · 보통 한 달 · 지난해 누군가 힌트를 넣었다)
SELECT /*+ INDEX_SS(o IX_T_ORD_TP_DT) */
       o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.PAY_TP, o.ORD_AMT
  FROM T_ORD o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[Q1]
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      1 |00:00:00.01 |     197 |
|   1 |  SORT AGGREGATE       |          |      1 |      1 |      1 |00:00:00.01 |     197 |
|   2 |   INDEX FAST FULL SCAN| PK_T_ORD |      1 |    100K|    100K|00:00:00.01 |     197 |
----------------------------------------------------------------------------------------------

[Q2]
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |    100K|00:00:00.08 |    1087 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |      1 |    100K|    100K|00:00:00.08 |    1087 |
|   2 |   INDEX FULL SCAN           | PK_T_ORD |      1 |    100K|    100K|00:00:00.02 |     191 |
---------------------------------------------------------------------------------------------------

[Q3]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |    100K|00:00:00.21 |     100K|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD        |      1 |    100K|    100K|00:00:00.21 |     100K|
|   2 |   INDEX FULL SCAN           | IX_T_ORD_CUS |      1 |    100K|    100K|00:00:00.02 |     331 |
-------------------------------------------------------------------------------------------------------
(Q3 0번 줄 Buffers 정확한 값 100,331)

[Q4 — 2024-06-14]
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |      3 |00:00:00.01 |       8 |
|   1 |  SORT GROUP BY NOSORT |                |      1 |      3 |      3 |00:00:00.01 |       8 |
|*  2 |   FILTER              |                |      1 |        |    137 |00:00:00.01 |       8 |
|*  3 |    INDEX SKIP SCAN    | IX_T_ORD_TP_DT |      1 |    137 |    137 |00:00:00.01 |       8 |
----------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_DT,'YYYYMMDD')+1>TO_DATE(:V_DT,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_DT,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_DT,'YYYYMMDD')+1)
       filter(("ORD_DT">=TO_DATE(:V_DT,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_DT,'YYYYMMDD')+1))

[Q5 — 2024년 6월]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |   4110 |00:00:00.03 |    4129 |
|*  1 |  FILTER                              |                |      1 |        |   4110 |00:00:00.03 |    4129 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD          |      1 |   4110 |   4110 |00:00:00.03 |    4129 |
|*  3 |    INDEX SKIP SCAN                   | IX_T_ORD_TP_DT |      1 |   4110 |   4110 |00:00:00.01 |      19 |
------------------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter(("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[참고 — T_ORD FULL] 896

문제

Q1 ~ Q5가 각각 어떤 인덱스 스캔을 쓰는지, 그 스캔이 쓰인(또는 강제된) 이유를 한두 문장씩 설명하시오.
Q2와 Q3는 같은 INDEX FULL SCAN인데 Buffers가 1,087과 100,331로 갈린다. 그 이유를 딕셔너리의 숫자로 설명하고, 고칠 쪽을 고치시오.
Q4와 Q5는 같은 INDEX SKIP SCAN인데 하나는 두고 하나는 고쳐야 한다. 이유를 설명하고 고칠 SQL을 작성하시오. 고치지 않는 SQL은 왜 그대로 두는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- Q1 · Q2 · Q4 는 그대로 (각자 맞는 스캔이다)
SELECT COUNT(*) FROM T_ORD;

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 ORDER BY ORD_SEQ;

-- Q3 : FIRST_ROWS 를 뗀다 → FULL + 정렬 (CF 가 나쁜 인덱스로 정렬을 대신하지 않는다)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 ORDER BY CUS_ID;

SELECT PAY_TP, COUNT(*)
  FROM T_ORD
 WHERE ORD_DT >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_dt, 'YYYYMMDD') + 1
 GROUP BY PAY_TP;

-- Q5 : INDEX_SS 를 뗀다 → 한 달이면 FULL 896 (흩어진 4,110 행을 하나씩 찾아가지 않는다)
SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.PAY_TP, o.ORD_AMT
  FROM T_ORD o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[Q3 — 힌트를 뗀 뒤]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    100K|00:00:00.06 |     896 |          |
|   1 |  SORT ORDER BY     |       |      1 |    100K|    100K|00:00:00.06 |     896 | 4380K (0)|
|   2 |   TABLE ACCESS FULL| T_ORD |      1 |    100K|    100K|00:00:00.01 |     896 |          |
-----------------------------------------------------------------------------------------------------------

[Q5 — 힌트를 뗀 뒤 · 2024년 6월] TABLE ACCESS FULL · 4,129 → 896

모범 답안 서술

Q1: COUNT(*)는 행만 세면 되므로 옵티마이저는 가장 작은 인덱스를 통째로 읽는다. PK_T_ORD는 ORD_SEQ가 NOT NULL이라 모든 행이 들어 있고 리프가 190블록으로 가장 작다. 순서가 필요 없으니 INDEX FAST FULL SCAN으로 멀티블록으로 읽는다(197블록). 맞는 스캔이라 그대로 둔다. Q2: ORDER BY ORD_SEQ가 PK 순서와 같다. 옵티마이저는 INDEX FULL SCAN으로 PK를 키 순서대로 읽어 정렬을 대신하고, 행마다 테이블로 간다. 테이블이 ORD_SEQ 순으로 쌓여 PK의 CLUSTERING_FACTOR가 896, 즉 테이블 블록 수와 같다. 인덱스 순서대로 찾아가도 블록을 896번만 바꿔 타므로 191 + 896 = 1,087블록에 정렬이 없다. FULL과 정렬(896블록에 10만 행 정렬)과 비슷하고 첫 행도 바로 나오므로 그대로 둔다. Q3: 같은 INDEX FULL SCAN이지만 IX_T_ORD_CUS의 CLUSTERING_FACTOR가 약 100,000으로 행 수와 같다. 한 고객의 주문 50건이 50블록에 흩어져 있어서, 고객순으로 찾아가면 행마다 다른 블록으로 간다. 그래서 테이블 액세스가 10만 번이고 100,331블록이다. FIRST_ROWS(100)이 '첫 100행을 빨리'를 목표로 정렬을 피하는 이 경로를 골랐다. 10만 행을 다 받는 파일에는 맞지 않으므로 힌트를 뗀다. 그러면 FULL로 896블록을 읽고 10만 행(약 4MB)을 메모리에서 정렬한다(100,331 → 896). Q4: IX_T_ORD_TP_DT는 PAY_TP → ORD_DT 순인데 조건은 ORD_DT에만 있다. 선두 PAY_TP가 3종뿐이므로 INDEX SKIP SCAN이 결제수단마다 한 번씩, 세 번만 그날 구간으로 찾아 내려간다. COUNT에 필요한 컬럼이 다 인덱스에 있어 테이블에도 가지 않는다(8블록). 하루 수천 번 호출되는 SQL에 맞는 계획이라 그대로 둔다. Q5: 스캔 방식은 Q4와 같고 SKIP SCAN 자체도 19블록으로 싸다. 문제는 그다음이다. SELECT 목록에 인덱스에 없는 컬럼(CUS_ID, ORD_AMT)이 있어서 한 달 4,110행을 테이블에서 찾아가야 한다. 같은 날짜의 주문이 730행 간격으로 흩어져 있어(CF 약 100,000) 행마다 다른 블록을 읽으니 4,110블록이다. 테이블 전체가 896블록이므로 FULL로 한 번 읽는 편이 4.6배 싸다. INDEX_SS 힌트를 떼면 옵티마이저가 FULL을 고른다(4,129 → 896). 정리하면 같은 이름의 스캔도 판정은 '인덱스를 몇 블록 읽나'보다 '그 뒤에 테이블을 몇 번 찾아가나'로 갈린다. INDEX FULL SCAN과 SKIP SCAN 모두 테이블에 가지 않거나(Q1, Q4) CF가 좋을 때(Q2) 값을 한다. 고치는 것은 Q3와 Q5의 힌트 두 개뿐이다.

해설

이 테이블에서 날짜 인덱스 경로의 손익분기는 (리프 + CF) × s = 896블록에서 약 896행, 엿새 남짓이다. Q5가 하루치만 고른다면 SKIP SCAN 약 8블록에 테이블 137블록으로 인덱스가 이긴다. 기간을 고르는 화면에서 스캔을 힌트로 고정하면 안 되는 이유다(ACC-003).
Q2를 FULL과 정렬로 바꿔도 틀린 답은 아니다. 다만 10만 행을 정렬하는 메모리와 CPU가 들고 첫 행이 늦다. CF가 좋은 PK로 정렬을 대신하는 지금 계획이 낫다.
Q3에 (CUS_ID, ORD_SEQ, ORD_DT, ORD_AMT) 같은 인덱스를 만들어 정렬과 테이블 액세스를 모두 없애는 길도 있다(ACC-043). 하루 몇 번 내려받는 파일 때문에 10만 행 인덱스를 하나 더 두는 것은 과하다.
실행계획에 FULL이라는 글자가 보인다고 다 나쁜 것은 아니다. INDEX FULL SCAN은 '인덱스를 처음부터 끝까지 키 순서대로', FAST FULL SCAN은 '순서 없이 통째로'라는 읽는 방식일 뿐이다. 판정은 Buffers와 그 뒤의 테이블 액세스로 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황