쉐이크 네 가지의 12월 판매 건수를 세는데 판매상품 인덱스를 네 번 훑는다

모범 답안 SQL ​

-- 조인 키(ITM_NO, '=')를 앞에, 기간(SLE_NO, 범위)을 뒤에 둔다
CREATE INDEX CF11_X01 ON CF11 (ITM_NO, SLE_NO);

SELECT /*+ LEADING(p) USE_NL(i) INDEX(i CF11_X01) */
       p.ITM_NO, p.ITM_NM, COUNT(*) AS sle_lines
  FROM CF01 p, CF11 i
 WHERE p.CTG_NO = :ctg
   AND i.ITM_NO = p.ITM_NO
   AND i.SLE_NO >= :v_from
   AND i.SLE_NO <  :v_to
 GROUP BY p.ITM_NO, p.ITM_NM

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      4 |00:00:00.01 |      17 |
|   1 |  HASH GROUP BY        |          |      1 |      8 |      4 |00:00:00.01 |      17 |
|*  2 |   FILTER              |          |      1 |        |    548 |00:00:00.01 |      17 |
|   3 |    NESTED LOOPS       |          |      1 |   4184 |    548 |00:00:00.01 |      17 |
|*  4 |     TABLE ACCESS FULL | CF01     |      1 |      8 |      4 |00:00:00.01 |       5 |
|*  5 |     INDEX RANGE SCAN  | CF11_X01 |      4 |    523 |    548 |00:00:00.01 |      12 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<:V_TO)
   4 - filter("P"."CTG_NO"=:CTG)
   5 - access("I"."ITM_NO"="P"."ITM_NO" AND "I"."SLE_NO">=:V_FROM AND "I"."SLE_NO"<:V_TO)

모범 답안 서술 ​

5번 줄 INDEX RANGE SCAN CF11_PK의 Starts는 4이고 Buffers는 732다. 쉐이크 4개마다 한 번씩 인덱스에 들어갔는데, 한 번에 약 183블록을 읽었다. 183블록은 12월 한 달 46,322줄이 차지하는 CF11_PK의 리프 범위 전체다. 상품 하나의 12월 판매는 140줄 안팎인데, 매번 한 달치 4만 6천 줄을 다 훑고 그중 그 상품의 줄만 남겼다. 원인은 인덱스 컬럼 순서다. CF11_PK는 (SLE_NO, ITM_NO) 순서라서 SLE_NO의 범위 조건이 먼저 스캔 구간을 정한다. 범위 조건 뒤에 오는 ITM_NO는 그 구간 안에 흩어져 있으므로 스캔 범위를 줄이지 못한다. 그래서 Predicate 5번에 조인 조건이 access에도 나오지만 filter에도 다시 나온다. 실제로는 리프 블록을 읽으면서 한 줄씩 거르는 조건이다. NL의 inner는 바깥 한 행마다 '=' 조건으로 찾아 들어간다. 그러므로 inner 인덱스는 '=' 조건인 조인 키 ITM_NO를 앞에, 범위 조건인 SLE_NO를 뒤에 둔다. (ITM_NO, SLE_NO) 인덱스에서는 상품마다 그달 구간만 읽으므로 한 번에 3블록이면 된다. COUNT(*)만 세므로 테이블은 읽지 않는다. Buffers는 737 → 17로 준다. 대가도 있다. CF11은 가장 큰 테이블이어서 인덱스를 하나 더 만들면 판매를 적재할 때마다 인덱스도 함께 갱신해야 한다. 인덱스를 당장 만들 수 없다면 힌트를 떼고 해시 조인으로 바꾼다. 12월 범위를 한 번만 읽으므로 약 186블록이다.

해설 ​

• 같은 컬럼, 다른 자리: CF11_PK에 ITM_NO가 들어 있으니 인덱스를 탄다고 생각하기 쉽다. 컬럼이 인덱스에 있는지보다 어느 자리에 있는지가 중요하다. 범위 조건 뒤의 컬럼은 스캔 범위를 줄이지 못한다(교재 04화 §6).
• 다른 정답: 힌트를 떼면 옵티마이저는 해시 조인(CF11_PK 범위 한 번과 CF01, 약 186블록)을 고를 수 있다. 인덱스 없이 4배 낫지만, 하루 3천 번이면 약 56만 블록이다. 새 인덱스로는 약 5만 블록이다.
• 집계 컬럼이 늘면: COUNT(*) 대신 SUM(SLE_QTY)를 낸다면 테이블 랜덤 액세스가 548번 더 든다. 그때는 (ITM_NO, SLE_NO, SLE_QTY)처럼 집계 컬럼까지 넣을지를 호출 빈도와 함께 판단한다.
• 적재 비용: 새 인덱스는 ITM_NO가 앞이라 판매가 들어올 때마다 상품 88개의 리프 블록에 흩어져 들어간다. 날짜순으로 오른쪽에만 쌓이는 PK보다 갱신 부담이 크므로 INVISIBLE로 만들어 재 본 뒤 승인받는다(교재 04화 §8, §9).

관련 교재 ​

• 06화 · NL 조인의 총일량
• 04화 · 결합 인덱스 컬럼 순서