직영점 세 곳의 하루 판매 건수를 세는 데 인덱스를 600블록 가까이 읽는다

모범 답안 SQL ​

SELECT h.SHP_NO,
       (SELECT s.SHP_NM FROM CF07 s WHERE s.SHP_NO = h.SHP_NO) AS shp_nm,
       COUNT(*) AS sle_cnt
  FROM CF10 h
 WHERE h.SHP_NO IN (1001, 1002, 1003)     -- BETWEEN 대신 IN 목록: 매장마다 (SHP_NO = , SLE_DT = ) 로 찾는다
   AND h.SLE_DT = :v_dt
 GROUP BY h.SHP_NO
 ORDER BY h.SHP_NO;

-- 오래 가는 방법: CF07 에 직영 여부(예: SHP_TP_CD)를 두고 그 목록을 읽어 매장마다 찾아가게 한다 (NL 조인)

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 ​

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |      3 |00:00:00.01 |      16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF07           |      3 |      1 |      3 |00:00:00.01 |       6 |
|*  2 |   INDEX UNIQUE SCAN         | CF07_PK        |      3 |      1 |      3 |00:00:00.01 |       3 |
|   3 |  SORT GROUP BY NOSORT       |                |      1 |      3 |      3 |00:00:00.01 |      10 |
|   4 |   INLIST ITERATOR           |                |      1 |        |    436 |00:00:00.01 |      10 |
|*  5 |    INDEX RANGE SCAN         | IX_CF10_SHP_DT |      3 |    411 |    436 |00:00:00.01 |      10 |
-------------------------------------------------------------------------------------------------------
   2 - access("S"."SHP_NO"=:B1)
   5 - access((("H"."SHP_NO"=1001 OR "H"."SHP_NO"=1002 OR "H"."SHP_NO"=1003)) AND "H"."SLE_DT"=:V_DT)

모범 답안 서술 ​

4번 줄의 access에 "H"."SLE_DT"=:V_DT가 있지만 filter에도 같은 조건이 또 있다. 인덱스는 SHP_NO로 먼저 정렬되어 있고, SHP_NO가 BETWEEN 범위로 들어왔다. 범위 조건 뒤의 SLE_DT는 스캔의 시작점(1001의 12월 15일)과 끝점(1003의 12월 15일)만 좁힐 뿐이다. 그 사이에 있는 1001의 나머지 날짜, 1002의 2년 치 전부, 1003의 12월 15일 이전 날짜를 다 읽으면서 filter로 거른다. 그래서 약 20만 항목(200,561개), 리프 약 590블록을 읽고 436건만 남긴다(Buffers 592). SHP_NO를 IN (1001, 1002, 1003)으로 바꾸면 INLIST ITERATOR가 매장 번호마다 한 번씩(5번 줄 Starts 3) SHP_NO = 값 AND SLE_DT = :v_dt로 찾는다. 두 컬럼이 모두 =이므로 SLE_DT도 access가 되어 그 매장의 그날 항목만 읽고, filter는 사라진다. Buffers는 592 → 10, 전체는 598 → 16이다. 인덱스를 새로 만들지 않고 SQL만 고친 결과다. 새 인덱스 (SLE_DT, SHP_NO)를 만들면 SLE_DT =로 시작해 SHP_NO 범위가 바로 이어지므로 BETWEEN 그대로도 3 ~ 4블록이면 된다. 하지만 POS가 판매를 넣을 때마다 인덱스 하나를 더 고치는 적재 비용이 들고, 이 화면 하나를 위해 인덱스를 늘리는 셈이다. SQL로 풀리면 SQL을 먼저 고친다. IN 목록은 "직영점은 1001 ~ 1003뿐"이라는 전제에 기댄다. SHP_NO는 정수라서 지금은 BETWEEN과 IN의 결과가 같다. 그런데 직영점이 새로 생기거나 번호 규칙이 바뀌면 IN 목록을 고쳐야 하고, 고치지 않으면 새 매장이 조용히 빠진다. 그래서 직영 여부를 CF07의 구분 컬럼(또는 코드 테이블)으로 두고, 그 목록을 읽어 매장마다 찾아가게(NL 조인) 하는 것이 오래 가는 방법이다. 그때도 CF10 쪽은 매장마다 SHP_NO = 값으로 찾으므로 일량은 같다.

해설 ​

• BETWEEN을 IN 목록으로 바꿔 효과를 보려면 두 가지가 맞아야 한다. 범위 안의 값이 몇 개 안 되고(여기서는 셋), 값 하나에 딸린 항목이 많아야 한다(매장 하나에 10만 항목). 값마다 리프 몇 블록뿐이라면 찾아 내려가는 비용이 아낀 몫을 먹는다(ACC-010).
• 값이 이산적이어야 한다. SHP_NO는 정수라 1001 ~ 1003 사이에 셋뿐이지만, 시분초가 있는 DATE나 금액 같은 연속값은 IN 목록으로 바꿀 수 없다. 바꾸면 결과가 빠진다.
• 옵티마이저는 BETWEEN을 IN 목록으로 스스로 바꿔 주지 않는다. '숫자 범위 안의 값이 셋뿐'이라는 것은 데이터를 아는 사람만 안다.
• INLIST ITERATOR는 목록의 값을 정렬된 순서로 찾으므로 GROUP BY SHP_NO는 SORT GROUP BY NOSORT 그대로 유지된다.

관련 교재 ​

• 04화 · 결합 인덱스 컬럼 순서
• 02화 · 인덱스 스캔 6종