바인드를 모두 문자로 넘기는데도 「매장별 주문 순위」가 한 주만 골라도 주문 100만 건을 다 읽는다

모범 답안 SQL ​

-- DECODE 는 첫 결과의 타입을 따른다 : 첫 결과를 문자 상수로 써서 식 전체가 VARCHAR2 를 돌려주게 한다
--   (CASE 로 쓴다면 CASE WHEN :v_all = 'Y' THEN '00000000' ELSE :v_from END 처럼 결과를 모두 문자로)
SELECT SHOP_NO, COUNT(*) AS ord_cnt,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM ORD
 WHERE ORD_DT BETWEEN DECODE(:v_all, 'Y', '00000000', :v_from)
                  AND DECODE(:v_all, 'Y', '99991231', :v_to)
 GROUP BY SHOP_NO
 ORDER BY rnk

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

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |    200 |00:00:00.02 |     250 |
|   1 |  WINDOW SORT                            |         |      1 |    200 |    200 |00:00:00.02 |     250 |
|   2 |   HASH GROUP BY                         |         |      1 |    200 |    200 |00:00:00.02 |     250 |
|*  3 |    FILTER                               |         |      1 |        |   9604 |00:00:00.02 |     250 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD     |      1 |   2500 |   9604 |00:00:00.02 |     250 |
|*  5 |      INDEX RANGE SCAN                   | ORD_X01 |      1 |   2500 |   9604 |00:00:00.01 |      37 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(DECODE(:V_ALL,'Y','99991231',:V_TO)>=DECODE(:V_ALL,'Y','00000000',:V_FROM))
   5 - access("ORD_DT">=DECODE(:V_ALL,'Y','00000000',:V_FROM) AND
              "ORD_DT"<=DECODE(:V_ALL,'Y','99991231',:V_TO))

모범 답안 서술 ​

4번 줄 조건은 filter(TO_NUMBER("ORD_DT")>=DECODE(:V_ALL,'Y',0,TO_NUMBER(:V_FROM)) …)이다. 바인드 셋은 모두 VARCHAR2인데 TO_NUMBER가 바인드 :V_FROM과 컬럼 ORD_DT 양쪽에 붙었다. 원인은 DECODE다. DECODE는 첫 번째 결과의 타입으로 식 전체의 타입을 정한다. 하한 쪽의 첫 결과가 숫자 0(상한 쪽은 99991231)이라 DECODE가 NUMBER를 돌려주고, 나머지 결과인 :v_from도 숫자로 바뀐다. 그러면 VARCHAR2 컬럼 ORD_DT를 숫자와 비교하게 되어 묵시적 형변환으로 컬럼 쪽이 TO_NUMBER로 바뀐다. SQL 안에서 생긴 변환이라 바인드 타입을 아무리 맞춰도 바인드 정보에는 드러나지 않는다. 컬럼이 숫자로 바뀌었으니 ORD_X01에서 범위를 정하지 못하고 100만 행을 다 읽어 그 주의 9,604건을 남긴다(Buffers 22,426). 고치는 방법은 DECODE의 첫 결과를 문자 상수로 쓰는 것이다. '00000000', '99991231'처럼 따옴표를 붙이면 DECODE가 VARCHAR2를 돌려주어 문자끼리 비교한다. 그러면 5번 줄처럼 access("ORD_DT">=DECODE(:V_ALL,'Y','00000000',:V_FROM) …)로 그 주의 범위만 읽는다(Buffers 22,426 → 250). 같은 로직을 CASE WHEN :v_all = 'Y' THEN 0 ELSE :v_from END로 썼다면 결과 타입이 섞였다는 이유로 파싱할 때 ORA-00932가 나서 바로 드러났을 것이다. CASE는 결과의 타입이 모두 같아야 하지만, DECODE는 첫 결과의 타입으로 나머지를 조용히 바꾼다. 그리고 지금 SQL은 느리기만 한 것이 아니다. 행마다 TO_NUMBER("ORD_DT")를 하므로, 숫자로 바뀌지 않는 값(공백이나 '2013-12-0' 같은 값)이 한 건만 들어와도 화면 전체가 ORA-01722로 멈춘다. 지금은 ORD_DT가 모두 숫자 여덟 자리라 답이 맞을 뿐이다.

해설 ​

• 규칙: DECODE가 돌려주는 값의 타입은 첫 결과가 정한다. 첫 결과가 숫자면 NUMBER, 문자나 NULL이면 VARCHAR2다. 그래서 vc_col = DECODE(:m, 'A', 0, :v)처럼 첫 결과에 숫자를 쓰면 문자 바인드 :v도 숫자로 바뀌고, 그 식과 비교한 문자 컬럼이 TO_NUMBER로 감싸인다. NVL(a, b)도 첫 인자의 타입을 따른다.
• IDX-014 해설은 바인드 쪽에만 쓴 DECODE는 컬럼을 가공하지 않는다고 했다. DECODE가 돌려주는 타입이 컬럼 타입과 같을 때의 이야기다. 바인드 타입이 깨끗한데도 컬럼에 변환이 붙어 있으면(IDX-022와 다른 원인) Predicate Information에서 그 변환이 어느 식에서 왔는지 찾는다.
• 다른 답: CASE WHEN :v_all = 'Y' THEN '00000000' ELSE :v_from END도 같다. CASE는 타입이 섞이면 에러로 막으므로 이런 실수가 일찍 드러난다. 새로 쓰는 조건에는 CASE를 권한다.
• 「전체 기간」의 대가: 고친 SQL은 커서 하나로 두 경우를 받는다. 「전체 기간」을 켜면 같은 인덱스 계획으로 100만 행을 읽어 약 2만 6천 블록이 되어 FULL(22,426)보다 조금 무겁다. 「전체 기간」을 자주 쓴다면 그 경우만 SQL을 따로 나눈다(IDX-040의 갈래 나누기).

관련 교재 ​

• 03화 · 묵시적 형변환은 어느 쪽이 변환되는가
• 02화 · 무력화 6종 (② 묵시적 형변환)