점검 도구의 FULL 경보를 없애려고 기간 조회 SQL 세 개에 같은 INDEX 힌트를 넣자는 표준안이 올라왔다

실전SELECT한빛유통101 / 700

상황

「주문 조회」 메뉴에는 기간(하루 ~ 석 달)을 고르는 화면이 셋 있다. S1 「기간 매출 요약」은 정산팀 대시보드가 하루 2천 번쯤 부르고, 기간은 보통 한 달이다. S2 「기간 주문 목록」은 상담원이 하루 5천 번쯤 열고 첫 화면 50건만 본다. S3 「엑셀 다운로드」는 하루 수십 번이고 주로 한 달 ~ 석 달이다. 새로 들인 SQL 점검 도구가 S1과 S3에 'TABLE ACCESS FULL 경보'를 띄우자, 개발 리더가 표준안을 올렸다. "기간 조회는 인덱스를 타야 한다. 세 SQL 모두 /*+ INDEX(o IX_T_ORD_DT) */를 넣는다." 박팀장은 "SQL마다 따로, 숫자로 판단해 와라"라고 했다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록(블록당 약 112행) · ORD_SEQ 순으로 쌓였다 · ORD_DT DATE(시분초 있음, 2023 ~ 2024) · 하루 약 137건 · ORD_DT는 주문 번호와 상관없이 730일을 돌아가며 붙어 있다
2024-06-14 137건 · 2024년 6월 4,110건 · 2024년 2분기 12,467건 · ORD_ST 3종
인덱스 IX_T_ORD_DT (ORD_DT): BLEVEL 1 · 리프 약 265블록 · CLUSTERING_FACTOR 99,986 · 통계 최신
그 밖에 PK_T_ORD (ORD_SEQ) · 주문은 운영에서 계속 INSERT된다(하루 약 137건)

원본 SQL

sql

-- 표준안을 적용한 세 SQL (FROM T_ORD o)
-- S1 「기간 매출 요약」
SELECT /*+ INDEX(o IX_T_ORD_DT) */
       o.ORD_ST, COUNT(*) AS cnt, SUM(o.ORD_AMT) AS amt
  FROM T_ORD o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY o.ORD_ST;

-- S2 「기간 주문 목록」 첫 화면
SELECT /*+ INDEX(o IX_T_ORD_DT) */
       o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, 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
 ORDER BY o.ORD_DT DESC
 FETCH FIRST 50 ROWS ONLY;

-- S3 「엑셀 다운로드」
SELECT /*+ INDEX(o IX_T_ORD_DT) */
       o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.PAY_DT, o.PAY_TP, o.ORD_AMT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY o.ORD_DT;

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

xplan

[표준안 · S1 · 2024년 6월]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |      3 |00:00:00.02 |    4122 |
|   1 |  HASH GROUP BY                        |             |      1 |      3 |      3 |00:00:00.02 |    4122 |
|*  2 |   FILTER                              |             |      1 |        |   4110 |00:00:00.02 |    4122 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD       |      1 |   4110 |   4110 |00:00:00.02 |    4122 |
|*  4 |     INDEX RANGE SCAN                  | IX_T_ORD_DT |      1 |   4110 |   4110 |00:00:00.01 |      12 |
-------------------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   4 - access("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

[표준안 · S2 · 2024년 6월 · 첫 화면 50건]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |     50 |00:00:00.01 |      52 |
|*  1 |  VIEW                                 |             |      1 |     50 |     50 |00:00:00.01 |      52 |
|*  2 |   WINDOW NOSORT STOPKEY               |             |      1 |   4110 |     50 |00:00:00.01 |      52 |
|*  3 |    FILTER                             |             |      1 |        |     50 |00:00:00.01 |      52 |
|   4 |     TABLE ACCESS BY INDEX ROWID       | T_ORD       |      1 |   4110 |     50 |00:00:00.01 |      52 |
|*  5 |      INDEX RANGE SCAN DESCENDING      | IX_T_ORD_DT |      1 |   4110 |     50 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORD_DT") DESC )<=50)

[측정 요약 · Buffers]              표준안(INDEX 힌트)            힌트 없음(지금, 옵티마이저 선택)
  S1  하루 / 한 달 / 석 달          139 / 4,122 / 12,501          139 / 896 / 896   (한 달부터 FULL)
  S2  첫 화면 50건 (기간과 상관없이)  52                            52                (지금도 인덱스 역순)
  S3  하루 / 한 달 / 석 달          139 / 4,122 / 12,501          139 / 896 / 896   (FULL + SORT ORDER BY)

문제

표준안대로 힌트를 넣으면 S1과 S3의 한 달 조회가 896 → 4,122블록이 되는 이유를 IX_T_ORD_DT의 BLEVEL, 리프, CLUSTERING_FACTOR로 분해하여 설명하시오. 이 테이블에서 이 인덱스가 FULL보다 싼 기간이 며칠까지인지 구하시오.
S2는 같은 인덱스로 같은 기간을 고르는데 기간이 넓어져도 52블록으로 끝나는 이유를 설명하시오.
세 SQL의 접근 경로를 각각 정하고, 필요한 인덱스를 설계하여 SQL과 DDL을 작성하시오. 점검 도구의 'FULL 경보'를 어떻게 다룰지와 인덱스의 대가도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- S1 : 쓰는 컬럼(ORD_DT · ORD_ST · ORD_AMT)을 다 담는다 → 어느 기간이든 인덱스 범위만 읽는다
CREATE INDEX IX_T_ORD_DT_ST_AMT ON T_ORD (ORD_DT, ORD_ST, ORD_AMT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

SELECT o.ORD_ST, COUNT(*) AS cnt, SUM(o.ORD_AMT) AS amt
  FROM T_ORD o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY o.ORD_ST;

-- S2 : 그대로 둔다 — FETCH FIRST 라서 옵티마이저가 인덱스 역순 + STOPKEY 를 고른다 (힌트 불필요)
SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, 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
 ORDER BY o.ORD_DT DESC
 FETCH FIRST 50 ROWS ONLY;

-- S3 : 힌트 없이 옵티마이저에 맡긴다 — 6일까지는 인덱스, 7일부터는 FULL + 정렬
SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.PAY_DT, o.PAY_TP, o.ORD_AMT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY o.ORD_DT;

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

xplan

[S1 · 2024년 6월 · 커버링]
----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |      1 |        |      3 |00:00:00.01 |      18 |
|   1 |  HASH GROUP BY      |                    |      1 |      3 |      3 |00:00:00.01 |      18 |
|*  2 |   FILTER            |                    |      1 |        |   4110 |00:00:00.01 |      18 |
|*  3 |    INDEX RANGE SCAN | IX_T_ORD_DT_ST_AMT |      1 |   4110 |   4110 |00:00:00.01 |      18 |
----------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

[요약]  S1 하루 / 한 달 / 석 달 : 3 / 18 / 53 · S2 : 52 · S3 : 139 / 896 / 896 (옵티마이저 선택)

모범 답안 서술

S1 계획에서 4번 줄 인덱스는 12블록이고 나머지 4,110블록은 3번 줄 테이블 액세스에서 나온다. 한 달 선택도 s = 4,110 / 100,000 = 4.1%로 분해하면 수직 탐색 BLEVEL 1 + 수평 탐색 리프 265 × s ≈ 11 + 테이블 랜덤 액세스 CF 99,986 × s ≈ 4,110 = 약 4,122다. CF가 행 수와 같아서, T_ORD에서 하루치 주문은 행마다 다른 블록에 있다. 그래서 4,110행을 찾으려고 테이블 블록을 4,110번 하나씩 찾아간다. FULL은 같은 896블록을 한 번씩 멀티블록으로 읽으므로 896에 끝난다. 손익분기는 (리프 265 + CF 99,986) × s = 896에서 s ≈ 0.89%, 약 894건이다. 하루가 137건이니 6일까지는 인덱스가 이기고 7일부터는 FULL이 이긴다. 한 달이나 석 달을 고르는 S1과 S3에 인덱스를 강제하면 896 → 4,122(석 달은 12,501)로 나빠진다. S2는 사정이 다르다. ORDER BY ORD_DT DESC와 FETCH FIRST 50이 있어서, 인덱스를 역순으로 읽다가 50건을 채우면 멈춘다(WINDOW NOSORT STOPKEY). 읽는 양은 기간이 아니라 50건이 정하므로 하루든 석 달이든 52블록이다. 손익분기 계산은 범위 안의 행을 전부 읽는다는 전제에서 나온 것이라, 부분범위 처리로 멈추는 SQL에는 맞지 않는다. 이미 옵티마이저가 이 길을 고르고 있으니 힌트도 필요 없다. 그래서 SQL마다 다르게 정한다. S1은 쓰는 컬럼이 ORD_DT, ORD_ST, ORD_AMT 셋뿐이므로 (ORD_DT, ORD_ST, ORD_AMT) 커버링 인덱스를 만든다. 테이블에 가지 않으니 CF가 청구되지 않고 손익분기도 없다(한 달 4,122 → 18, 지금의 FULL 896과 비교해도 50배). 하루 2천 번이면 약 180만 → 4만 블록이다. S2는 그대로 둔다. S3는 힌트 없이 옵티마이저에 맡긴다. 다운로드는 모든 컬럼이 필요해 커버링이 맞지 않고, 하루 수십 번이라 FULL 896이면 충분하다. 점검 도구의 FULL 경보는 'FULL이 나쁘다'는 규칙이 아니라 블록 수로 판정한다. S3의 FULL은 이 테이블에서 한 달 이상을 읽는 가장 싼 길이므로 예외로 등록하고 근거(손익분기 6일)를 적는다. 커버링 인덱스의 대가는 주문 INSERT마다 인덱스를 하나 더 고치는 적재 비용이다. 하루 137건이라 작다. 또 S1에 컬럼이 하나 늘면 테이블 액세스가 돌아오므로 전제하는 컬럼 목록을 T2 승인서에 적는다.

해설

함정: "기간 조회는 인덱스"라는 규칙은 기간이 짧을 때만 맞다. 이 테이블의 손익분기는 6일이고, 그 숫자는 CF가 정한다. 같은 100만 행이라도 버거 ORD는 85%까지 인덱스가 이긴다(ACC-029).
S2에 표준안 힌트를 넣어도 결과는 같다(52). 하지만 이미 옵티마이저가 고르는 길에 힌트를 박으면, 나중에 더 좋은 인덱스가 생겨도 쓰지 못한다. 힌트는 옵티마이저가 틀릴 때만 쓴다.
S3도 기간마다 SQL을 나누어 힌트를 달 수 있지만 얻는 것이 작다. 옵티마이저는 바인드 값을 보고 하루짜리에는 인덱스를 고른다. 바인드 피킹 때문에 첫 값에 계획이 묶이는 문제는 PARSE 단원에서 다룬다.
커버링 인덱스가 생기면 S2와 S3도 그 인덱스를 고를 수 있다. 선두가 ORD_DT로 같아서 결과와 일량은 비슷하지만, 바뀐 계획은 INVISIBLE 단계에서 세 SQL을 모두 재 보고 확인한다(교재 04화 §8).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황