선두 컬럼이 세 종류뿐이라던 고객 상태 요약이 SKIP SCAN으로 인덱스를 4천 블록 넘게 읽는다

실전SELECT한빛유통114 / 700

상황

고객센터 상담 화면에 「고객 주문 상태 요약」을 새로 붙였다. 고객 번호 하나로 상태별 주문 건수를 보여 주며, 상담원들이 하루 3,000번쯤 연다. 개발자는 고객으로 찾는 인덱스가 없다는 것을 알고 기존 인덱스에 INDEX_SS 힌트를 붙였다. 메모에는 이렇게 적혀 있다. "선두 ORD_ST는 3종뿐이라 SKIP SCAN이면 세 번만 찾아간다. 인덱스를 새로 만들 필요 없음." 그런데 한 번에 4,412블록을 읽는다. 박팀장은 대안을 비교해 오라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 1,095일(하루 913건) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · CUS_ID 2,000명(고객당 500건)
적재 규칙 때문에 PENDING과 CANCEL은 닷새에 하루(219일)에만 있다. 그래서 (ORD_ST, ORD_DT) 조합은 COMPLETE 1,095개, PENDING 219개, CANCEL 219개를 합쳐 1,533개다
이 데이터에서는 고객 한 명의 주문이 모두 같은 상태다(CUS_000123은 500건 모두 COMPLETE)
인덱스 IX_ORD_BIG_ST_DT_CUS (ORD_ST, ORD_DT, CUS_ID): ACC-001의 (ORD_ST, ORD_DT)에 CUS_ID를 덧붙인 인덱스 · BLEVEL 2 · 리프 약 4,350블록(리프 한 블록에 약 230항목) · 통계 최신 · 「취소 추이」(ORD_ST = 'CANCEL' AND ORD_DT 범위)와 「상태별 일 고객 목록」(ORD_ST = :st AND ORD_DT = :d)이 쓴다
다른 인덱스는 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)뿐이다 · 주문은 운영에서 계속 INSERT된다(교재 04화 실측: 인덱스 하나에 5만 건당 redo 약 11.8MB)

원본 SQL

sql

SELECT /*+ INDEX_SS(o IX_ORD_BIG_ST_DT_CUS) */
       o.ORD_ST, COUNT(*) AS ord_cnt
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :v_cus
 GROUP BY o.ORD_ST

실행계획 학습용 예시 · 건수와 조합 수는 적재 스크립트 그대로 · 적재 비용은 교재 04화 실측

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      |      1 |        |      1 |00:00:00.05 |    4412 |
|   1 |  SORT GROUP BY NOSORT|                      |      1 |      3 |      1 |00:00:00.05 |    4412 |
|*  2 |   INDEX SKIP SCAN    | IX_ORD_BIG_ST_DT_CUS |      1 |    500 |    500 |00:00:00.05 |    4412 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"=:V_CUS)
       filter("O"."CUS_ID"=:V_CUS)

[같은 SQL · 힌트를 떼면] TABLE ACCESS FULL 5,826

문제

선두 컬럼이 3종인데도 SKIP SCAN이 4,412블록을 읽은 이유를 이 인덱스의 컬럼 구성과 데이터 분포로 설명하시오.
대안 넷을 비교하시오. ① 그대로 둔다(INDEX_SS) ② 힌트만 뗀다 ③ 고객이 선두인 인덱스를 새로 만든다 ④ 기존 인덱스의 순서를 (ORD_ST, CUS_ID, ORD_DT)로 바꾼다. 각각의 Buffers, 다른 화면에 주는 영향, 적재 비용을 쓰시오.
하나를 골라 SQL과 DDL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ③ 고객이 = 로 들어오는 화면: 고객을 앞에, 상태를 뒤에 (COUNT 와 GROUP BY 를 인덱스만으로)
CREATE INDEX IX_ORD_BIG_CUS_ST ON T_ORD_BIG (CUS_ID, ORD_ST) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

SELECT o.ORD_ST, COUNT(*) AS ord_cnt          -- INDEX_SS 힌트를 뗀다
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :v_cus
 GROUP BY o.ORD_ST;

개선 후 실행계획 학습용 예시 · 건수와 조합 수는 적재 스크립트 그대로 · 적재 비용은 교재 04화 실측

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT GROUP BY NOSORT|                   |      1 |      3 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN   | IX_ORD_BIG_CUS_ST |      1 |    500 |    500 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------
   2 - access("O"."CUS_ID"=:V_CUS)

[비교] ② 힌트만 떼면 FULL 5,826
       ④ (ORD_ST, CUS_ID, ORD_DT) 로 바꾸면: 이 SQL 은 SKIP 3번 · 약 12
          「상태별 일 고객 목록」 COMPLETE 하루치를 찾으려고 COMPLETE 90만 항목(리프 약 3,900)을 훑는다 (지금 약 7)
          「취소 추이」 분기 치는 CANCEL 2만 항목(리프 약 87)을 다 읽는다 (지금 약 9)

모범 답안 서술

2번 줄은 INDEX SKIP SCAN이고 access와 filter 모두 "CUS_ID"=:V_CUS다. SKIP SCAN이 건너뛰는 단위는 선두 컬럼 하나가 아니라, 조건이 걸린 컬럼(CUS_ID) 앞에 있는 컬럼 전부의 조합이다. 이 인덱스는 ORD_ST → ORD_DT → CUS_ID 순이고 가운데 ORD_DT에도 조건이 없으므로, (ORD_ST, ORD_DT) 조합 1,533개마다 한 번씩 '그 상태, 그 날짜, CUS_000123' 자리를 찾아 내려가야 한다. 조합마다 루트와 브랜치를 거쳐 리프를 하나 이상 읽으니 약 4,412블록이고, 리프 4,350블록을 거의 다 읽은 셈이다. 개발자의 '세 번'은 인덱스가 (ORD_ST, CUS_ID)였을 때의 이야기다. 대안을 비교한다. ① 그대로 둔다: 4,412블록 × 하루 3,000번 ≈ 하루 1,300만 블록이다. FULL(5,826)보다 조금 나을 뿐이다. ② 힌트만 떼면 옵티마이저가 FULL을 골라 5,826블록이 된다. 가장 단순하지만 더 나빠진다. ③ (CUS_ID, ORD_ST) 인덱스: 고객의 500항목(리프 2블록)만 읽고 테이블에 가지 않아 4블록이다. 대가는 100만 행 인덱스 하나만큼의 적재 비용이다. 주문 INSERT마다 인덱스 하나를 더 고치고, 5만 건이면 redo가 약 11.8MB 는다. ④ 기존 인덱스를 (ORD_ST, CUS_ID, ORD_DT)로 바꾸면 이 SQL은 상태 3개만 건너뛰어 약 12블록이 되고 인덱스 수도 늘지 않는다. 하지만 ORD_DT가 세 번째로 밀려나 ORD_ST = :st AND ORD_DT = :d의 ORD_DT가 access에서 filter로 바뀐다. 「상태별 일 고객 목록」은 COMPLETE 하루치를 찾으려고 COMPLETE 90만 항목(리프 약 3,900블록)을 훑게 되고, 「취소 추이」도 CANCEL 2만 항목을 다 읽는다. 한 화면을 살리려고 두 화면을 무너뜨리는 셈이다. ③을 고른다. 하루 3,000번 × 약 4,400블록, 하루 약 1,300만 블록을 아끼는 대가로 인덱스 하나의 적재 비용은 치를 만하다. INVISIBLE로 만들어 세션에서 재 본 뒤 VISIBLE로 바꾸고, INDEX_SS 힌트는 뗀다(4,412 → 4블록).

해설

SKIP SCAN이 효과를 보려면 '조건 컬럼 앞에 있는 컬럼들의 조합 수'가 적어야 한다. 선두 하나의 종류만 보고 판단하면 이 문제처럼 틀린다. 실데이터라면 PENDING과 CANCEL이 날마다 있어 조합이 3 × 1,095 = 3,285개가 되므로 더 나쁘다.
조합 수는 SELECT COUNT(*) FROM (SELECT DISTINCT ORD_ST, ORD_DT FROM T_ORD_BIG)처럼 직접 세거나, 앞 컬럼들의 NUM_DISTINCT를 곱해 어림한다. SKIP SCAN 힌트를 넣기 전에 이 숫자부터 확인한다.
고객 화면에 주문 이력(날짜순)도 있다면 (CUS_ID, ORD_ST, ORD_DT)나 (CUS_ID, ORD_DT, ORD_ST)로 만들어 두 SQL을 함께 받을 수 있다. 다만 (CUS_ID, ORD_DT)만으로는 이 SQL이 ORD_ST를 읽으러 테이블에 500번 가야 한다(약 503블록). 고객 화면에 어떤 SQL이 더 있는지 보고 컬럼을 정한다.
이 데이터에서는 고객마다 상태가 하나뿐이라 결과가 한 줄이다. 적재 스크립트가 MOD로 값을 만든 탓이며, 실데이터라면 상태 셋이 섞여 나온다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 조합 수는 적재 스크립트 그대로 · 적재 비용은 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 조합 수는 적재 스크립트 그대로 · 적재 비용은 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황