프로모션 시간대 매출을 볼 때마다 2년 치 주문을 다 읽는다

모범 답안 SQL ​

SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD
 WHERE ORD_DT BETWEEN SUBSTR(:v_from_dtm, 1, 8) AND SUBSTR(:v_to_dtm, 1, 8)   -- 선두 컬럼 범위 (access)
   AND ORD_DT || ORD_HMS BETWEEN :v_from_dtm AND :v_to_dtm                    -- 첫날과 마지막 날의 시각 (filter)
 GROUP BY SHOP_NO

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |    200 |00:00:00.01 |      72 |
|   1 |  HASH GROUP BY                        |         |      1 |     10 |    200 |00:00:00.01 |      72 |
|*  2 |   FILTER                              |         |      1 |        |   2112 |00:00:00.01 |      72 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |     10 |   2112 |00:00:00.01 |      72 |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01 |      1 |     10 |   2112 |00:00:00.01 |      17 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SUBSTR(:V_FROM_DTM,1,8)<=SUBSTR(:V_TO_DTM,1,8))
   4 - access("ORD_DT">=SUBSTR(:V_FROM_DTM,1,8) AND "ORD_DT"<=SUBSTR(:V_TO_DTM,1,8))
       filter(("ORD_DT"||"ORD_HMS">=:V_FROM_DTM AND "ORD_DT"||"ORD_HMS"<=:V_TO_DTM))

모범 답안 서술 ​

2번 줄은 filter(("ORD_DT"||"ORD_HMS">=:V_FROM_DTM AND ...))이다. ORD_X01은 ORD_DT, ORD_HMS 순으로 정렬되어 있지만, 두 컬럼을 이어 붙인 14자리 값은 인덱스 어디에도 없다. 이어 붙이기(||)도 컬럼 가공이라 선두 컬럼 ORD_DT로 범위를 지목하지 못하고 100만 행을 다 읽는다(Buffers 22,239). 구간이 걸친 날짜는 시작 일시와 끝 일시의 앞 8자리 사이에 있다. 그래서 ORD_DT BETWEEN SUBSTR(:v_from_dtm, 1, 8) AND SUBSTR(:v_to_dtm, 1, 8)을 더하면 원래 조건이 고르는 행을 하나도 빠뜨리지 않으면서 선두 컬럼에 access 범위가 생긴다. SUBSTR은 바인드 쪽에 썼으므로 가공이 아니다. 이어 붙인 조건은 지우지 않고 남겨 둔다. 3월 1일 18시 전과 3월 3일 12시 후의 주문을 걸러 내는 일은 그 조건이 하기 때문이다. 두 컬럼이 다 인덱스에 있어서 이 조건은 4번 줄의 인덱스 filter로 처리되고, 테이블에는 2,112건만 간다. 사흘 치 인덱스 항목 약 4천 개(리프 15블록 남짓)와 날짜순으로 모여 있는 테이블 블록 50여 개만 읽으므로 Buffers는 22,239 → 72로 준다. 이 방식은 ORD_DT가 늘 8자리, ORD_HMS가 늘 6자리여서 이어 붙인 문자열의 순서가 시간 순서와 같다는 전제에 기댄다. 길이가 하나라도 다르면(앞의 0이 빠진 시각 등) 이어 붙인 비교부터 틀린다. 더 정확히 읽으려면 OR 세 갈래(첫날의 시작 시각 이후, 가운데 날 전체, 마지막 날의 끝 시각 이전)로 나눠 첫날과 마지막 날도 ORD_HMS까지 access로 읽을 수 있다. 하지만 줄어드는 것은 리프 몇 블록이라 SQL이 복잡해지는 값을 하지 못한다. 근본적으로는 일시를 DATE 컬럼 하나로 두는 모델이 맞다.

해설 ​

• 함정 ①: 이어 붙인 조건을 지우고 ORD_DT BETWEEN만 남기면 첫날 18시 전과 마지막 날 12시 후의 주문까지 세어 답이 틀린다. 이 구간이면 약 1,900건이 더 섞인다.
• 함정 ②: 유통 CF10.SLE_HMS처럼 8시, 9시 주문의 앞 0이 빠져 5자리로 들어간 데이터라면 이어 붙인 값의 순서가 시간 순서와 어긋난다(교재 03화 §3). 이어 붙이기 전에 길이부터 확인한다.
• SQL을 고칠 수 없는 화면이라면 (ORD_DT || ORD_HMS) 함수 기반 인덱스로 같은 식을 인덱싱할 수 있다. 대신 100만 행짜리 인덱스가 하나 늘어 POS 주문을 넣을 때마다 고쳐야 하고, 식이 글자 하나라도 다르면 쓰이지 않는다.
• 선두 컬럼에 범위 조건을 '덧붙여' access를 만드는 방법은 덧붙인 조건이 결과를 바꾸지 않을 때만 쓸 수 있다. 원래 조건을 만족하는 행이 덧붙인 조건도 반드시 만족하는지 따져 본다.

관련 교재 ​

• 02화 · 무력화 6종 (① 컬럼 가공)
• 03화 · 문자 날짜가 조용히 틀리는 자리