부정형을 고치라는 코드 리뷰대로 고쳤더니 월 매출 집계가 두 배 넘게 읽는다

기초SELECT한빛유통36 / 700

상황

정산팀 「월 매출(취소 제외)」 화면은 한 달 동안 취소되지 않은 주문의 건수와 금액을 낸다. 정산 담당 8명이 하루 종일 연다. 코드 리뷰에서 "부정형(<>)은 인덱스를 못 탄다"는 지적을 받은 조민우가 조건을 긍정형 IN으로 바꿨다. 그래도 계획이 FULL이라 INDEX 힌트까지 붙였더니 오히려 느려졌다. 두 판의 계획이 아래에 있다(2024년 6월).

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · ORD_AMT
2024년 6월 주문 27,390건 · 그중 CANCEL 548건 · 취소 제외 26,842건
인덱스 IX_ORD_BIG_DT (ORD_DT): 리프 2,653 · IX_ORD_BIG_ST (ORD_ST): BLEVEL 2 · 리프 2,775 · 통계 최신
같은 상태값의 인덱스 항목은 ROWID 순으로 놓여 있다. 상태 하나의 범위를 따라 테이블로 가면 블록을 앞에서부터 차례로 한 번씩 거친다

원본 SQL

sql

-- [처음 판]
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND ORD_ST <> 'CANCEL';

-- [조민우의 수정안]
SELECT /*+ INDEX(T_ORD_BIG IX_ORD_BIG_ST) */ COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND ORD_ST IN ('COMPLETE', 'PENDING');

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[처음 판 · ORD_ST <> 'CANCEL']
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |      1 |00:00:00.05 |    5826 |
|   1 |  SORT AGGREGATE                      |               |      1 |      1 |      1 |00:00:00.05 |    5826 |
|*  2 |   TABLE ACCESS FULL                  | T_ORD_BIG     |      1 |  27769 |  26842 |00:00:00.05 |    5826 |
-------------------------------------------------------------------------------------------------------------
   2 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_ST"<>'CANCEL' AND
              "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[조민우의 수정안 · IN + INDEX 힌트]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |      1 |00:00:00.41 |   14382 |
|   1 |  SORT AGGREGATE                      |               |      1 |      1 |      1 |00:00:00.41 |   14382 |
|   2 |   INLIST ITERATOR                    |               |      1 |        |  26842 |00:00:00.41 |   14382 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG    |      2 |  27769 |  26842 |00:00:00.40 |   14382 |
|*  4 |     INDEX RANGE SCAN                 | IX_ORD_BIG_ST |      2 |    980K|    980K|00:00:00.09 |    2722 |
-------------------------------------------------------------------------------------------------------------
   3 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))
   4 - access(("ORD_ST"='COMPLETE' OR "ORD_ST"='PENDING'))

문제

처음 판이 IX_ORD_BIG_ST를 쓰지 않은 이유와, 수정안이 인덱스를 쓰고도 처음 판보다 많이 읽은(14,382블록) 이유를 실행계획의 숫자로 설명하시오.
결과가 바뀌지 않게 SQL과 인덱스를 고쳐 6월 범위만 읽도록 하시오. 취소 제외 조건은 어디에 두면 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 힌트를 떼고 조건은 처음 판 그대로 둔다 (부정형이어도 된다)
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND ORD_ST <> 'CANCEL';

-- 찾아가는 것은 ORD_DT, 거르는 것은 ORD_ST, 합계는 ORD_AMT 까지 인덱스 안에서 끝낸다
CREATE INDEX IX_ORD_BIG_DT_ST_AMT ON T_ORD_BIG (ORD_DT, ORD_ST, ORD_AMT);

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                      |      1 |        |      1 |00:00:00.01 |     117 |
|   1 |  SORT AGGREGATE    |                      |      1 |      1 |      1 |00:00:00.01 |     117 |
|*  2 |   FILTER           |                      |      1 |        |  26842 |00:00:00.01 |     117 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_DT_ST_AMT |      1 |  27769 |  26842 |00:00:00.01 |     117 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter("ORD_ST"<>'CANCEL')

모범 답안 서술

처음 판이 IX_ORD_BIG_ST를 쓰지 않은 것은 부정형이라서만이 아니다. CANCEL이 아닌 주문은 전체의 98%다. 부정형을 긍정형 IN ('COMPLETE', 'PENDING')으로 바꿔도 고르는 비율은 그대로 98%이므로, 이 조건은 어떻게 써도 찾아가는 조건(access)으로 쓸 만한 조건이 아니다. 옵티마이저는 그래서 FULL(5,826블록)을 골랐다. 수정안에서는 힌트 때문에 4번 줄이 COMPLETE와 PENDING 두 범위에서 인덱스 항목 98만 개를 읽는다(2,722블록). 3번 줄은 그 ROWID마다 테이블에 가서 날짜를 확인하는데, 같은 상태값의 항목은 ROWID 순이라 상태 하나마다 테이블을 처음부터 끝까지 한 번씩 훑는 셈이 된다. 두 범위로 약 11,660블록이고, 6월이 아닌 95만 건은 테이블까지 가서야 버려진다. 합계 14,382블록으로 FULL의 2.5배이고, 한 블록씩 읽으므로 시간은 8배(0.05 → 0.41초)다. 이 집계에서 범위를 좁혀 주는 조건은 ORD_DT(6월, 2.74%)다. 다만 IX_ORD_BIG_DT로 27,390건을 한 건씩 테이블에서 읽으면 FULL보다 비싸다(교재 02화 rev01 힌트 판 27,466블록). 그래서 ORD_DT를 앞에 두고 ORD_ST와 ORD_AMT까지 담은 (ORD_DT, ORD_ST, ORD_AMT) 인덱스를 만든다. ORD_DT 범위는 access로 6월만 읽고, 취소 제외 조건은 인덱스 안의 filter로 548건을 거르며, 합계도 인덱스만으로 끝난다(Buffers 5,826 → 117, 수정안 기준으로는 14,382 → 117). 취소 제외 조건은 처음 판의 부정형 그대로 둔다. 찾아가는 일은 다른 조건이 하고 부정형은 거르기만 하면 되므로, 부정형이어도 인덱스 효율에는 차이가 없다. IN으로 바꾸면 새 상태값(예: RETURN)이 생겼을 때 조용히 빠뜨리는 대가만 생긴다.

해설

함정: 부정형을 긍정형으로 바꾸는 처방(IDX-005)은 남는 값이 적을 때만 효과가 있다. 고치기 전에 그 조건이 고르는 비율부터 본다.
힌트는 옵티마이저가 비싸다고 버린 길을 억지로 고르게 할 뿐이다(교재 06화 §7). FULL이 이긴 데는 이유가 있다.
IDX-001의 (ORD_DT, ORD_AMT)가 이미 있다면 하나를 더 만들지 말고 (ORD_DT, ORD_AMT, ORD_ST)로 바꾼다. ORD_DT가 범위 조건이므로 뒤 컬럼은 순서와 상관없이 filter로 쓰인다.
교재 02화 무력화 ④의 교정형에도 "애초에 범위가 커서 FULL이 옳을 수도 있다"고 적혀 있다. 부정형이 문제인 경우는 그 조건이 소수를 고를 때다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황