메뉴를 여러 개 고르는 실적 비교 화면이 상품 인덱스를 처음부터 끝까지 읽는다

중급SELECT유통55 / 700

상황

유통 본사 MD의 「메뉴 비교」 화면은 메뉴를 2 ~ 6개 골라 2년 치 판매 줄 수와 수량, 금액을 나란히 보여 준다. 화면은 고른 상품번호를 쉼표로 이어 문자열 하나('1010,1047,1052')로 넘긴다. 메뉴 하나를 고르는 실적 화면은 같은 인덱스로 0.01초에 끝나는데, 이 화면은 메뉴를 몇 개 고르든 0.2초가 걸린다. MD 여섯 명이 하루 수백 번 쓴다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_NO · ITM_NO NUMBER(5)(팔린 메뉴 88종) · SLE_QTY · SLE_UPRC
인덱스 CF11_PK (SLE_NO, ITM_NO) · IX_CF11_ITM (ITM_NO, SLE_QTY, SLE_UPRC): BLEVEL 2 · 리프 약 3,230블록 · 통계 최신
고른 메뉴의 2년 치 줄 수: 1010(콜드브루) 15,493 · 1047 8,512 · 1052 6,204(합계 30,209)

원본 SQL

sql

SELECT ITM_NO, COUNT(*) AS sle_cnt, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11
 WHERE INSTR(',' || :v_items || ',', ',' || ITM_NO || ',') > 0
 GROUP BY ITM_NO
 ORDER BY ITM_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |      3 | 00:00:00.21 |    3244 |
|   1 |  SORT GROUP BY         |             |      1 |     88 |      3 | 00:00:00.21 |    3244 |
|*  2 |   INDEX FAST FULL SCAN | IX_CF11_ITM |      1 |  54835 |  30209 | 00:00:00.19 |    3244 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(INSTR(','||:V_ITEMS||',',','||TO_CHAR("ITM_NO")||',')>0)

문제

2번 줄이 INDEX RANGE SCAN이 아니라 INDEX FAST FULL SCAN인 이유를 Predicate Information으로 설명하시오.
고른 메뉴마다 인덱스를 찾아가도록 SQL을 고치시오. 화면이 값을 넘기는 방식을 바꿀 수 없을 때와 바꿀 수 있을 때를 나눠 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 화면을 못 바꿀 때 : 쉼표 문자열을 행으로 풀어 ITM_NO와 = 로 비교한다 (컬럼은 그대로)
SELECT s.ITM_NO, COUNT(*) AS sle_cnt, SUM(s.SLE_QTY) AS qty, SUM(s.SLE_QTY * s.SLE_UPRC) AS amt
  FROM CF11 s
 WHERE s.ITM_NO IN (SELECT TO_NUMBER(REGEXP_SUBSTR(:v_items, '[^,]+', 1, LEVEL))
                      FROM dual
                   CONNECT BY LEVEL <= REGEXP_COUNT(:v_items, ',') + 1)
 GROUP BY s.ITM_NO
 ORDER BY s.ITM_NO;

-- 화면을 바꿀 수 있을 때 ① 6칸 고정 바인드 (빈 칸은 마지막 값을 되풀이) → INLIST ITERATOR
--   ... WHERE s.ITM_NO IN (:i1, :i2, :i3, :i4, :i5, :i6)
-- ② 숫자 배열(컬렉션) 바인드
--   ... WHERE s.ITM_NO IN (SELECT column_value FROM TABLE(:arr))

개선 후 실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Starts | E-Rows | A-Rows | A-Time      | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |      3 | 00:00:00.01 |      96 |
|   1 |  SORT GROUP BY                    |             |      1 |      1 |      3 | 00:00:00.01 |      96 |
|   2 |   NESTED LOOPS                    |             |      1 |  12463 |  30209 | 00:00:00.01 |      96 |
|   3 |    VIEW                           | VW_NSO_1    |      1 |      1 |      3 | 00:00:00.01 |       0 |
|   4 |     HASH UNIQUE                   |             |      1 |      1 |      3 | 00:00:00.01 |       0 |
|   5 |      CONNECT BY WITHOUT FILTERING |             |      1 |        |      3 | 00:00:00.01 |       0 |
|   6 |       FAST DUAL                   |             |      1 |      1 |      1 | 00:00:00.01 |       0 |
|*  7 |    INDEX RANGE SCAN               | IX_CF11_ITM |      3 |  12463 |  30209 | 00:00:00.01 |      96 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("S"."ITM_NO"="$kkqu_col_1")

모범 답안 서술

2번 줄의 조건은 filter(INSTR(','||:V_ITEMS||',',','||TO_CHAR("ITM_NO")||',')>0)이다. ITM_NO는 숫자인데 문자열과 이어 붙이느라 TO_CHAR로 바뀌었고, 그 결과가 다시 INSTR 함수 안에 들어갔다. 인덱스에는 ITM_NO 숫자가 정렬되어 있지만, 함수 안에 들어간 컬럼으로는 "1010부터 1010까지" 같은 범위를 정할 수 없다. 그래서 필요한 컬럼이 다 든 IX_CF11_ITM을 INDEX FAST FULL SCAN으로 처음부터 끝까지 훑으며 110만 항목마다 문자열을 맞춰 본다(Buffers 3,244). 메뉴를 하나 고르든 여섯 고르든 같다. 컬럼은 그대로 두고 목록 쪽을 행으로 바꾼다. 화면을 바꿀 수 없으면 REGEXP_SUBSTR와 CONNECT BY로 쉼표 문자열을 행 세 개로 풀어 ITM_NO IN (SELECT ...)로 비교한다. 옵티마이저는 이 서브쿼리를 풀어 메뉴 목록을 먼저 만들고, 메뉴마다 IX_CF11_ITM을 ITM_NO = 값으로 찾아간다(NESTED LOOPS, 7번 줄 Starts 3). 세 메뉴의 범위만 읽으므로 Buffers 3,244 → 96이 된다. 집계에 쓰는 수량과 단가가 인덱스에 있어 테이블에는 가지 않는다. 화면을 바꿀 수 있으면 두 가지가 낫다. 하나는 바인드 칸을 여섯 개로 정해 두고 빈 칸은 마지막 값을 되풀이해 ITM_NO IN (:i1, ..., :i6)로 넘기는 것이다. IN 목록의 값마다 INLIST ITERATOR가 찾아가고, 문장은 언제나 하나다. 다른 하나는 숫자 배열(컬렉션)을 바인드해 IN (SELECT column_value FROM TABLE(:arr))로 받는 것이다. 어느 쪽이든 고른 값을 리터럴로 이어 붙여 SQL을 만들면 안 된다. 고르는 조합마다 새 문장이 생겨 하드 파싱이 는다.

해설

함정: ITM_NO IN (:v_items)처럼 문자열 하나를 그대로 넣으면 IN 목록의 값은 '1010,1047,1052'라는 문자열 하나가 되고, 숫자로 바꾸다가 ORA-01722가 난다. IN 목록의 칸 수는 SQL을 쓸 때 정해진다.
CONNECT BY로 만든 목록은 옵티마이저가 1건으로 추정한다(3번 줄 E-Rows 1). 메뉴 여섯 개까지는 NL이 맞지만, 고르는 값이 수천 개로 느는 화면이라면 해시 조인이 나을 수 있으니 계획을 다시 잰다.
이 화면은 결과를 인덱스만으로 집계할 수 있어서 메뉴마다 찾아가는 것이 이득이다. 콜드브루 15,493줄도 커버링 인덱스라 리프 48블록 안팎이면 된다(IDX-006). 테이블까지 가야 했다면 메뉴마다 랜덤 액세스가 수천 번이라 이야기가 달라진다.
리터럴로 이어 붙인 IN 목록과 바인드 칸 수에 따라 문장이 몇 개 생기는지는 교재 11화의 IN 리스트 실험에 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황