1분마다 새로 고치는 고액 주문 타일이 분기 치 인덱스를 매번 다 읽는다

실전SELECT한빛유통92 / 700

상황

재무팀 대시보드의 「분기 고액 주문」 타일은 이번 분기에 49만 원 이상인 주문의 건수와 합계를 보여 준다. 49만 원은 내부통제 기준으로 정해진 값이다. 재무팀 PC 30대가 1분마다 새로 고쳐서 하루 약 18,000번 실행된다. 리스크팀의 「금액대별 분포」 화면도 같은 SQL을 쓴다. 그 화면은 기준 금액을 10만, 30만, 49만 원 가운데서 고르고 하루 수십 번 연다. 조민우의 제안은 이렇다. "금액 쪽이 더 좁은 범위니까 (ORD_AMT, ORD_DT) 인덱스를 하나 더 만들자." 박팀장은 "두 화면이 각각 어떻게 되는지, 다른 방법은 없는지 비교해 와라"라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 2022-01-01 ~ 2024-12-30(하루 913건, 전부 자정) · 2024년 2분기 83,083건
ORD_AMT 1만 ~ 50만 원에 고르게 분포: 49만 원 이상 약 2.05% · 30만 원 이상 약 40.8% · 10만 원 이상 약 81.6%
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): BLEVEL 2 · 리프 3,206블록(교재 04화 실측, 리프 한 블록에 약 312항목) · 통계 최신
조민우 안 IX_ORD_BIG_AMT_DT (ORD_AMT, ORD_DT): 같은 크기 · INVISIBLE로 만들어 세션에서만 재 봤다
운영에서는 주문이 계속 INSERT된다(교재 04화 실측: 인덱스 하나에 5만 건당 redo 약 11.8MB)

원본 SQL

sql

SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT  >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT  <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND ORD_AMT >= :v_amt

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값 · 인덱스 규모는 교재 04화 실측

xplan

[지금 — IX_ORD_BIG_DT_AMT · 2024년 2분기 · :v_amt = 490000]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.02 |     266 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.02 |     266 |
|*  2 |   FILTER           |                   |      1 |        |   1704 |00:00:00.02 |     266 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_DT_AMT |      1 |   4154 |   1704 |00:00:00.02 |     266 |
---------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_AMT">=:V_AMT AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter("ORD_AMT">=:V_AMT)

[조민우 안 — IX_ORD_BIG_AMT_DT · 세션에서만 · 같은 분기 · :v_amt = 490000]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |      68 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |      68 |
|*  2 |   FILTER           |                   |      1 |        |   1704 |00:00:00.01 |      68 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_AMT_DT |      1 |   4154 |   1704 |00:00:00.01 |      68 |
---------------------------------------------------------------------------------------------------
   3 - access("ORD_AMT">=:V_AMT AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[기준 금액별 Buffers — 같은 분기]
                   IX_ORD_BIG_DT_AMT (지금)    IX_ORD_BIG_AMT_DT (조민우 안)
  49만 원 이상      266                         68      (3년 치 약 20,500항목을 읽고 1,704건)
  30만 원 이상      266                         1,310   (약 408,000항목을 읽고 약 33,900건)
  10만 원 이상      267                         2,618   (약 816,000항목을 읽고 약 67,800건)

문제

지금 인덱스로 1,704건을 세는 데 266블록을 읽는 이유를 3번 줄로 설명하시오. 범위 조건이 둘일 때 인덱스가 어떻게 읽히는지 정리하시오.
조민우 안을 받아들이면 두 화면이 각각 어떻게 되는지 숫자로 쓰시오. 두 화면이 같은 SQL을 쓴다는 점이 왜 문제인지도 설명하시오.
대안을 셋 이상 들어(그대로 둔다, 조민우 안, 타일 전용 SQL과 인덱스, IN 목록 등) 효과와 대가를 비교하고, 하나를 골라 SQL과 DDL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 타일 전용: 기준 금액이 내부통제 기준(49만 원)으로 고정된 화면 — 좁은 범위인 금액을 앞에
CREATE INDEX IX_ORD_BIG_AMT_DT ON T_ORD_BIG (ORD_AMT, ORD_DT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 두 화면을 세션에서 재고 → T2 → VISIBLE

SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt     -- 분기 고액 주문 타일
  FROM T_ORD_BIG
 WHERE ORD_AMT >= 490000
   AND ORD_DT  >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT  <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

-- ② 금액대별 분포: 기준 금액이 바뀌므로 기존 (ORD_DT, ORD_AMT) 로 고정 — 어느 값이든 분기 치 266 블록
SELECT /*+ INDEX(o IX_ORD_BIG_DT_AMT) */
       COUNT(*) AS cnt, SUM(o.ORD_AMT) AS amt
  FROM T_ORD_BIG o
 WHERE o.ORD_DT  >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT  <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND o.ORD_AMT >= :v_amt;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값 · 인덱스 규모는 교재 04화 실측

xplan

[① 타일 — IX_ORD_BIG_AMT_DT · 리터럴 490000]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |      68 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |      68 |
|*  2 |   FILTER           |                   |      1 |        |   1704 |00:00:00.01 |      68 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_AMT_DT |      1 |   1702 |   1704 |00:00:00.01 |      68 |
---------------------------------------------------------------------------------------------------
   3 - access("ORD_AMT">=490000 AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[② 분포 화면] IX_ORD_BIG_DT_AMT 고정 · 10만 · 30만 · 49만 모두 266 안팎

모범 답안 서술

3번 줄의 access에는 ORD_DT 범위와 "ORD_AMT">=:V_AMT가 함께 있지만 filter에 ORD_AMT가 또 나온다. 범위 조건이 둘이면 인덱스에서 읽을 구간을 정하는 것은 앞의 범위(ORD_DT) 하나뿐이다. 뒤의 범위 ORD_AMT는 첫날의 시작점만 좁히고, 나머지 날짜는 날마다 1만 ~ 50만 원 항목을 다 읽으면서 filter로 거른다. 그래서 분기 치 약 82,000항목, 리프 약 263블록을 읽고 1,704건을 남긴다(Buffers 266). 이 값은 기준 금액이 얼마든 거의 같다. 조민우 안 (ORD_AMT, ORD_DT)에서는 ORD_AMT가 앞의 범위가 되고 ORD_DT가 filter가 된다. 49만 원이면 3년 치 49만 원 이상 약 20,500항목만 읽어서 68블록이다. 그러나 30만 원이면 약 408,000항목(1,310블록), 10만 원이면 약 816,000항목(2,618블록)을 읽는다. 지금 인덱스의 266보다 다섯 배에서 열 배 많다. 두 범위 가운데 어느 쪽이 좁은지가 바인드 값에 따라 뒤집히는 것이다. 두 화면이 같은 SQL을 쓰는 것이 문제다. 바인드 변수만 다른 같은 문장이라 커서 하나와 실행계획 하나를 함께 쓴다. 처음 하드 파싱할 때 49만 원이 들어오면 새 인덱스로 계획이 굳고, 그 계획으로 10만 원 분포를 보면 2,618블록을 읽는다. 반대로 10만 원으로 굳으면 타일은 효과를 보지 못한다. 대안을 비교한다. 그대로 두면 타일은 266 × 하루 18,000번 = 약 480만 블록이고, 비용은 들지 않는다. 조민우 안을 그대로 쓰면 타일은 68이 되지만, 분포 화면은 바인드 값과 처음 굳은 계획에 따라 266 ~ 2,618로 흔들린다. IN 목록은 대안이 되지 못한다. ORD_AMT는 연속값이라 목록으로 만들 수 없고, ORD_DT를 91일 IN 목록으로 풀면 91번 × 3블록 ≈ 273블록으로 지금과 같다. 마지막으로 타일 전용 SQL과 인덱스를 두는 방법이 있다. 기준 금액이 고정된 타일은 리터럴 490000으로 따로 쓰고 (ORD_AMT, ORD_DT)를 만든다. 분포 화면은 지금 SQL에 기존 인덱스를 지정해 계획이 흔들리지 않게 한다. 마지막 안을 고른다. 타일은 266 → 68블록, 하루 약 480만 → 120만 블록이다. 분포 화면은 어느 기준 금액이든 266 그대로다. 대가는 주문 INSERT마다 인덱스 하나를 더 고치는 적재 비용(교재 04화 실측: 5만 건에 인덱스 하나당 redo 약 11.8MB)과, 분포 화면에 넣은 힌트를 힌트 대장에 올려 관리하는 일이다. 49만 원 기준이 바뀌면 타일 SQL의 리터럴과 이 판단을 함께 다시 본다.

해설

범위 조건이 둘이면 둘 다 access 범위를 줄일 수는 없다. 좁은 쪽을 앞에 두는 것이 원칙이지만, 어느 쪽이 좁은지가 값마다 달라지면 그 원칙만으로는 정할 수 없다. 이때는 값이 고정된 화면과 값이 바뀌는 화면을 나눠 생각한다.
바인드 변수를 쓰면 옵티마이저는 처음 들어온 값을 보고 계획을 정한다(바인드 피킹, 교재 11화 §6). 적응적 커서 공유(ACS)가 값에 따라 계획을 나눠 줄 수도 있지만, 몇 번 느린 실행을 겪은 뒤에야 움직이고 늘 그렇게 된다는 보장도 없다.
다른 정답: 인덱스를 늘리지 않고 타일 결과를 1분 동안 캐시하는 방법도 있다. PC 30대가 같은 숫자를 보므로 1분에 한 번만 계산하면 된다(하루 18,000번 → 약 600번). 대신 화면 쪽을 고쳐야 한다.
ORD_DT를 IN 목록으로 바꾸는 방법이 통하지 않는 이유는 ACC-010과 같다. 날짜 하나에 913항목(리프 3블록 남짓)뿐이라 찾아 내려가는 비용이 아낀 몫을 먹는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값 · 인덱스 규모는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값 · 인덱스 규모는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황