일 마감 화면의 하루 매출 조회에 SKIP SCAN이 보인다며 날짜 인덱스를 따로 만들자고 한다

기초SELECT이커머스111 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

운영팀의 「일 마감」 화면은 날짜 하나를 고르면 그날의 주문 건수와 매출 합계를 보여 준다. 마감 담당자들이 하루 200번쯤 연다. 실행계획을 본 조민우가 이렇게 말했다. "INDEX SKIP SCAN이 보인다. SKIP SCAN은 선두 컬럼을 빠뜨렸을 때 나오는 나쁜 신호라고 배웠다(IDX-006). 매출까지 담은 (order_date, total_amount) 인덱스를 따로 만들자." 아래는 2024년 6월 14일을 골랐을 때다.

테이블과 인덱스

orders: 34,908행 · 193블록 · order_date DATE(시분초 있음, 2020 ~ 2024, 하루 약 20건) · status 6종 · 2024-06-14 주문 19건
인덱스 PK_ORDERS (order_id) · IX_ORD_CUST_DT (customer_id, order_date) · IX_ORD_ST_DT (status, order_date, total_amount): 뒤의 둘은 ACC-005에서 만든 인덱스다 · IX_ORD_ST_DT는 BLEVEL 1 · 리프 약 150블록(상태 하나에 약 25블록) · 통계 최신
인덱스가 하나 늘면 주문 INSERT마다 고칠 인덱스가 넷이 된다 · 참고(교재 18화 실측): orders 34,908행을 적재할 때 인덱스 하나가 redo를 약 1.56 → 12.1MB로 늘린다

원본 SQL

sql

SELECT COUNT(*) AS ord_cnt, SUM(total_amount) AS amt
  FROM orders
 WHERE order_date >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND order_date <  TO_DATE(:v_dt, 'YYYYMMDD') + 1

실행계획 학습용 예시 · 인덱스는 ACC-005의 처방 · 적재 비용은 교재 18화 실측

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |      1 |00:00:00.01 |      13 |
|   1 |  SORT AGGREGATE    |              |      1 |      1 |      1 |00:00:00.01 |      13 |
|*  2 |   FILTER           |              |      1 |        |     19 |00:00:00.01 |      13 |
|*  3 |    INDEX SKIP SCAN | IX_ORD_ST_DT |      1 |     19 |     19 |00:00:00.01 |      13 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_DT,'YYYYMMDD')+1>TO_DATE(:V_DT,'YYYYMMDD'))
   3 - access("ORDER_DATE">=TO_DATE(:V_DT,'YYYYMMDD') AND "ORDER_DATE"<TO_DATE(:V_DT,'YYYYMMDD')+1)
       filter(("ORDER_DATE">=TO_DATE(:V_DT,'YYYYMMDD') AND "ORDER_DATE"<TO_DATE(:V_DT,'YYYYMMDD')+1))

[같은 조건 · FULL 강제] 193

문제

3번 줄의 INDEX SKIP SCAN이 13블록만 읽은 이유를 IX_ORD_ST_DT의 선두 컬럼 분포로 설명하시오. IDX-006에서 SKIP SCAN이 오히려 더 많이 읽었던 경우와 무엇이 다른지도 설명하시오.
조민우의 제안을 받아들일지 숫자로 판단하시오. SKIP SCAN에 맡겨 둘 때 무엇이 바뀌면 다시 재 봐야 하는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고치지 않는다. SQL 도 인덱스도 그대로 둔다 (선두 6 종을 건너뛰는 SKIP SCAN 13 블록)
SELECT COUNT(*) AS ord_cnt, SUM(total_amount) AS amt
  FROM orders
 WHERE order_date >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND order_date <  TO_DATE(:v_dt, 'YYYYMMDD') + 1;

개선 후 실행계획 학습용 예시 · 인덱스는 ACC-005의 처방 · 적재 비용은 교재 18화 실측

xplan

[조민우 안 (order_date, total_amount) 을 INVISIBLE 로 만들어 세션에서만 재 본 결과]
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE    |               |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   FILTER           |               |      1 |        |     19 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_DT_AMT |      1 |     19 |     19 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------
   3 - access("ORDER_DATE">=TO_DATE(:V_DT,'YYYYMMDD') AND "ORDER_DATE"<TO_DATE(:V_DT,'YYYYMMDD')+1)

→ 13 → 2 · 하루 200번이면 하루 2,200블록을 아낀다

모범 답안 서술

3번 줄은 IX_ORD_ST_DT의 INDEX SKIP SCAN이다. 이 인덱스는 status → order_date 순으로 정렬되어 있는데 조건은 두 번째 컬럼 order_date에만 있다. 선두 status는 6종뿐이므로 옵티마이저는 상태 값마다 한 번씩, 6번 '그 상태의 6월 14일' 자리로 찾아 내려가고, 그 사이의 다른 날짜 구간(상태 하나에 리프 약 25블록)은 읽지 않고 건너뛴다. 6번 × 리프 1 ~ 2블록에 루트를 더해 13블록이다. SUM에 쓰는 total_amount도 인덱스에 있어서 테이블(193블록)에는 가지 않는다. IDX-006에서 SKIP SCAN이 오히려 더 많이 읽은 것은 선두 SLE_NO가 50만 종이라 건너뛸 구간이 없어 리프를 거의 다 읽었기 때문이다. SKIP SCAN의 비용은 선두 컬럼 값의 수에 비례한다. 여기처럼 선두 종류가 적고 값마다 구간이 크면 건너뛰는 양이 많아서 효과적이다. 조민우 안 (order_date, total_amount)를 INVISIBLE로 만들어 재 보면 13 → 2블록이다. 하루 200번이면 하루 2,200블록을 아끼는데, 이는 테이블을 열한 번 FULL로 읽는 양에 불과하다. 반면 인덱스가 하나 늘면 주문 INSERT마다 고칠 인덱스가 넷이 되고, 이관이나 대량 적재 때 비용이 커진다(교재 18화 실측: 인덱스 하나가 redo를 약 1.56 → 12.1MB로 늘린다). 그래서 새 인덱스는 만들지 않고 SQL도 그대로 둔다. 다시 재 봐야 하는 때는 두 가지다. 상태 종류가 늘면 찾아 내려가는 횟수가 그만큼 는다. 한 달처럼 긴 기간을 고르는 조회가 이 인덱스를 쓰기 시작하면 상태마다 읽는 리프도 늘어난다. 그때 이 판단을 숫자로 다시 한다.

해설

SKIP SCAN은 선두 컬럼에 조건이 없을 때 나오므로 살펴볼 신호인 것은 맞다. 판정은 선두 컬럼의 종류 수(NUM_DISTINCT)와 값마다의 구간 크기를 보고 한다. 상태, 성별, 매장처럼 종류가 몇 개뿐인 컬럼이 선두이면 오히려 인덱스 하나를 아끼는 수단이 된다.
status IN (…)으로 선두를 채워도 13블록 안팎이 된다. 하지만 상태 목록을 SQL에 적어야 하고, 상태가 새로 생기면 그 주문이 조용히 빠진다(ACC-020). SKIP SCAN은 인덱스에 들어 있는 값을 차례로 찾아가므로 그런 위험이 없다.
옵티마이저가 SKIP SCAN을 고르려면 선두 컬럼의 통계(NUM_DISTINCT)가 맞아야 한다. 통계가 오래되어 종류 수를 잘못 알면 같은 SQL이 FULL로 바뀔 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스는 ACC-005의 처방 · 적재 비용은 교재 18화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스는 ACC-005의 처방 · 적재 비용은 교재 18화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황