PK에 상품번호가 들어 있는데 메뉴별 실적 조회가 테이블을 다 읽는다

중급SELECT유통06 / 700

상황

유통 본사 MD가 쓰는 「메뉴별 판매 실적」 화면은 메뉴 하나를 고르면 2년치 판매 건수와 수량, 금액을 보여 준다. MD 여섯 명이 하루 수백 번 조회한다. 개발자는 이렇게 말한다. "PK에 ITM_NO가 있으니 인덱스를 탈 줄 알았다." 그래서 INDEX_SS 힌트를 붙여 봤는데 더 느려졌다. 아래는 콜드브루(1010)를 골랐을 때다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록(블록당 약 134행) · 판매번호(SLE_NO) 순으로 적재되어 있다
CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · SLE_NO 500,000종(판매 한 건에 상품 평균 2.2개)
ITM_NO: 팔린 메뉴 88종 · 메뉴당 3,032 ~ 275,135행 · (ICE)아메리카노 275,135 · (HOT)아메리카노 202,156 · 중간쯤 메뉴 약 6,200 · 콜드브루 15,493행은 8,204블록 중 7,025블록에 흩어져 있다
다른 인덱스 없음 · 통계 최신(ITM_NO 히스토그램 없음)

원본 SQL

sql

SELECT COUNT(*) AS sle_cnt, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11
 WHERE ITM_NO = :v_itm

실행계획 학습용 예시 · 분포는 적재 데이터 그대로

xplan

[원본]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.07 |    8206 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.07 |    8206 |
|*  2 |   TABLE ACCESS FULL| CF11 |      1 |  12463 |  15493 |00:00:00.07 |    8206 |
-------------------------------------------------------------------------------------
   2 - filter("ITM_NO"=:V_ITM)

[개발자의 시도 — /*+ INDEX_SS(CF11 CF11_PK) */]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |      1 |00:00:00.11 |   11471 |
|   1 |  SORT AGGREGATE                      |         |      1 |      1 |      1 |00:00:00.11 |   11471 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF11    |      1 |  12463 |  15493 |00:00:00.10 |   11471 |
|*  3 |    INDEX SKIP SCAN                   | CF11_PK |      1 |  12463 |  15493 |00:00:00.05 |    4418 |
---------------------------------------------------------------------------------------------------------
   3 - access("ITM_NO"=:V_ITM)
       filter("ITM_NO"=:V_ITM)

문제

CF11_PK에 ITM_NO가 있는데도 원본이 FULL로 처리되는 이유와, INDEX SKIP SCAN이 오히려 더 많이 읽은(11,471) 이유를 설명하시오. SKIP SCAN이 효과를 보는 인덱스는 어떤 모양인지도 설명하시오.
보조 인덱스를 DDL로 제안하시오. ITM_NO 하나짜리 인덱스로 충분한지 숫자로 따져 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ITM_NO 를 선두로 · 집계에 쓰는 SLE_QTY · SLE_UPRC 까지 담아 테이블에 가지 않는다
CREATE INDEX IX_CF11_ITM ON CF11 (ITM_NO, SLE_QTY, SLE_UPRC);

SELECT COUNT(*) AS sle_cnt, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11
 WHERE ITM_NO = :v_itm;

개선 후 실행계획 학습용 예시 · 분포는 적재 데이터 그대로

xplan

------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      1 |00:00:00.01 |      48 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |      1 |00:00:00.01 |      48 |
|*  2 |   INDEX RANGE SCAN| IX_CF11_ITM |      1 |  12463 |  15493 |00:00:00.01 |      48 |
------------------------------------------------------------------------------------------
   2 - access("ITM_NO"=:V_ITM)

모범 답안 서술

CF11_PK는 (SLE_NO, ITM_NO)라서 판매번호로 먼저 정렬되고, 같은 판매번호 안에서만 상품번호 순이다. 콜드브루의 항목은 50만 개 판매번호 아래에 조금씩 흩어져 있다. 그래서 선두 컬럼 SLE_NO 조건이 없으면 정렬 위에서 '여기부터 여기까지'를 정할 수 없고, 원본은 테이블 FULL(Buffers 8,206)로 처리된다. INDEX SKIP SCAN은 선두 컬럼의 값마다 건너뛰며 찾는 방식이라 선두 컬럼의 종류가 적을 때(매장 5곳, 성별, 연도)만 싸다. 선두 SLE_NO가 50만 종이면 건너뛸 것이 없어 리프를 거의 다 읽고(3번 줄 4,418), 찾은 15,493건을 테이블에서 다시 읽는다(약 7,050). 그래서 합계가 11,471이 된다. 같은 원리로 CF10에 (SHP_NO, SLE_DT) 인덱스가 있다면, SLE_DT만 줘도 매장 5곳마다 한 번씩 건너뛰며 싸게 찾는다. 보조 인덱스는 ITM_NO를 선두로 둔다. 다만 ITM_NO 하나짜리 인덱스라면 콜드브루 15,493건이 8,204블록 중 7,025블록(86%)에 흩어져 있어서 테이블 랜덤 액세스만 약 7천 블록이 된다. FULL과 별 차이가 없고, 한 블록씩 읽으므로 시간은 더 걸릴 수 있다. 옵티마이저도 FULL을 고를 것이다. 집계에 쓰는 SLE_QTY와 SLE_UPRC까지 담은 커버링 인덱스 (ITM_NO, SLE_QTY, SLE_UPRC)를 만들어 테이블에 가지 않게 한다(Buffers 8,206 → 48). 대가도 있다. 판매 한 건마다 상품 줄을 INSERT할 때 이 인덱스도 고친다(적재 비용). 그리고 커버링은 '이 화면의 컬럼 목록'을 전제로 한다. SELECT에 컬럼이 하나만 늘어도 테이블 액세스가 돌아온다.

해설

쏠린 메뉴: 아메리카노 두 종이 전체의 43%다. 커버링 인덱스라면 (ICE)아메리카노 275,135건도 리프 약 810블록이라 FULL보다 싸다. ITM_NO 단독 인덱스였다면 8,204블록을 전부 한 블록씩 다시 읽어야 해서 FULL보다 비싸다. 바인드 값에 따라 답이 갈리는 컬럼이다(OPT, PARSE 단원).
PK를 뒤집은 (ITM_NO, SLE_NO) 인덱스도 흔한 답이다. 판매번호 목록을 뽑거나 CF10과 조인할 때는 좋지만, 이 집계는 SLE_QTY를 읽으러 테이블에 가야 한다.
SKIP SCAN은 '선두가 빠져도 탄다'는 뜻이 아니라 '선두 컬럼의 종류가 적으면 싸게 탄다'는 뜻이다. 실행계획에 SKIP SCAN이 보이면 선두 컬럼의 NUM_DISTINCT부터 확인한다.
교재 02화 무력화 ⑥: 선두 컬럼이 빠지면 FAST FULL SCAN 3,227, 선두 조건을 주면 RANGE SCAN 91이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 분포는 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 분포는 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황