결제일 인덱스도 주문일 인덱스도 있는데 일별 매출 인식 화면이 주문 테이블을 다 읽는다

실전SELECT한빛유통17 / 700

상황

회계 규정상 매출 인식일은 결제일이고, 아직 결제되지 않은 주문은 주문일로 잡는다. 정산팀 「일별 매출 인식」 화면은 이 규정을 NVL 하나로 옮겼다. 정산 담당 8명이 하루 종일 날짜를 바꿔 가며 보고, 매시 정각에는 대사 배치가 최근 7일을 다시 센다. 박팀장은 이렇게 주문했다. "인덱스는 둘 다 있다는데 왜 안 쓰는지, 고칠 방법이 여럿이면 비교해서 하나를 골라 와."

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · 하루 136 ~ 137건이 서로 다른 블록에 흩어져 있다
ORD_DT DATE(시분초 있음) · PAY_DT DATE(결제일, 결제 전이면 NULL · 지금 미결제 41건) (문제 설정: 2024년 12월 결제 연동 개편 뒤의 모습이다. IDX-004와 같다)
2024년 12월 20일 매출 인식분: 137건(그날 결제된 134건 + 그날 주문했지만 아직 미결제인 3건)
인덱스 IX_T_ORD_DT (ORD_DT) · IX_T_ORD_PAY (PAY_DT): 둘 다 BLEVEL 1 · 리프 265 · 통계 최신
주문 INSERT는 하루 약 140건이고 결제 때마다 PAY_DT를 UPDATE한다. 적재 배치 일부는 INSERT INTO T_ORD VALUES (...)처럼 컬럼 목록 없이 넣는다

원본 SQL

sql

SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD
 WHERE NVL(PAY_DT, ORD_DT) >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND NVL(PAY_DT, ORD_DT) <  TO_DATE(:v_dt, 'YYYYMMDD') + 1

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      1 |00:00:00.02 |     896 |
|   1 |  SORT AGGREGATE     |       |      1 |      1 |      1 |00:00:00.02 |     896 |
|*  2 |   FILTER            |       |      1 |        |    137 |00:00:00.02 |     896 |
|*  3 |    TABLE ACCESS FULL| T_ORD |      1 |    250 |    137 |00:00:00.02 |     896 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_DT,'YYYYMMDD')+1>TO_DATE(:V_DT,'YYYYMMDD'))
   3 - filter((NVL("PAY_DT","ORD_DT")>=TO_DATE(:V_DT,'YYYYMMDD') AND
              NVL("PAY_DT","ORD_DT")<TO_DATE(:V_DT,'YYYYMMDD')+1))

문제

인덱스가 둘 다 있는데 테이블 전체를 읽는 이유를 실행계획에서 찾아 설명하시오.
(가) 조건을 OR로 풀어 두 인덱스를 쓰는 방법, (나) 함수 기반 인덱스, (다) 가상 컬럼과 인덱스를 읽는 양과 대가로 비교하고, 하나를 골라 SQL과 DDL을 작성하시오.
고른 방법이 원래 SQL과 같은 답을 낸다는 것을 어떻게 확인할지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (나) 고른 것 : 조건과 같은 식 + 집계 컬럼까지 담은 함수 기반 인덱스 (SQL은 그대로)
CREATE INDEX IX_T_ORD_RCG ON T_ORD (NVL(PAY_DT, ORD_DT), ORD_AMT) INVISIBLE;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD', method_opt => 'FOR ALL HIDDEN COLUMNS SIZE 1');
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서 재고 → ALTER INDEX IX_T_ORD_RCG VISIBLE

SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD
 WHERE NVL(PAY_DT, ORD_DT) >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND NVL(PAY_DT, ORD_DT) <  TO_DATE(:v_dt, 'YYYYMMDD') + 1;

-- (가) 비교용 : 인덱스를 더하지 않고 두 갈래로 (PAY_DT가 NULL인지로 갈려 겹치지 않는다)
-- SELECT COUNT(*), SUM(ORD_AMT) FROM T_ORD
--  WHERE (PAY_DT >= TO_DATE(:v_dt, 'YYYYMMDD') AND PAY_DT < TO_DATE(:v_dt, 'YYYYMMDD') + 1)
--     OR (PAY_DT IS NULL AND ORD_DT >= TO_DATE(:v_dt, 'YYYYMMDD') AND ORD_DT < TO_DATE(:v_dt, 'YYYYMMDD') + 1);

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

xplan

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_DT,'YYYYMMDD')+1>TO_DATE(:V_DT,'YYYYMMDD'))
   3 - access(NVL("PAY_DT","ORD_DT")>=TO_DATE(:V_DT,'YYYYMMDD') AND
              NVL("PAY_DT","ORD_DT")<TO_DATE(:V_DT,'YYYYMMDD')+1)

모범 답안 서술

3번 줄은 TABLE ACCESS FULL이고 조건은 filter((NVL("PAY_DT","ORD_DT")>=…))이다. IX_T_ORD_PAY에는 PAY_DT가, IX_T_ORD_DT에는 ORD_DT가 정렬되어 있지만 조건은 두 컬럼을 NVL로 감싼 값을 묻는다. 그 값은 어느 인덱스에도 정렬되어 있지 않으므로 access로 범위를 지목하지 못하고 10만 행을 다 읽는다(Buffers 896). E-Rows 250도 식에 대한 양쪽 범위 조건의 기본 선택도 0.25%다. 세 방법을 비교한다. (가) OR로 풀기: (PAY_DT가 그날) OR (PAY_DT IS NULL AND ORD_DT가 그날)은 원래 조건과 같다. 두 갈래는 PAY_DT가 NULL인지로 갈려 겹치지 않으므로 UNION ALL로 나눠도 중복이 없다. 옵티마이저가 OR-Expansion으로 갈래마다 인덱스를 쓰면, 결제일 갈래는 137건을 읽고 주문일 갈래도 그날 주문 137건을 테이블에서 읽은 뒤 미결제 3건만 남기므로 합계 약 283블록이다. 인덱스를 더하지 않는 것이 장점이지만 SQL이 길어지고, 미결제 갈래는 주문일 하루치를 다 읽는다. (나) 함수 기반 인덱스: 조건과 같은 식 NVL(PAY_DT, ORD_DT)에 ORD_AMT까지 담으면 SQL을 고치지 않고 인덱스만 읽는다(896 → 2). 대가는 셋이다. 결제로 PAY_DT를 UPDATE할 때마다 이 인덱스도 고쳐야 하고, 식이 글자까지 같아야 쓰이며(COALESCE(PAY_DT, ORD_DT)로 쓴 화면은 못 쓴다), 숨은 컬럼의 통계를 따로 모아야 한다. (다) 가상 컬럼: 같은 효과에 컬럼 이름(RCG_DT)이 생겨 다른 화면이 쓰기 쉽다. 하지만 테이블 컬럼 수가 바뀌어 컬럼 목록 없이 INSERT하는 적재 배치가 ORA-00947로 깨진다. 화면 호출이 잦고 대사 배치도 매시 도는 데 비해 결제 UPDATE는 하루 140건 남짓이라 인덱스 유지 비용이 작다. 그래서 (나)를 고른다. INVISIBLE로 만들어 내 세션에서 계획과 Buffers를 확인한 뒤 VISIBLE로 바꾼다. 확인은 원래 SQL과 고친 판의 건수와 금액을 여러 날짜에 걸쳐 대조하는 것으로 한다. 특히 미결제 주문이 있는 날(NULL이 포함된 날)과 자정 직전, 직후에 주문이 있는 날을 꼭 넣는다.

해설

함정: OR로 풀면서 미결제 갈래의 PAY_DT IS NULL을 빠뜨리면, 결제일은 다른 날인데 주문일이 그날인 주문까지 두 번 센다. 갈래가 겹치는지는 원래 식(NVL)이 무엇을 고르는지로 따진다.
단일 컬럼 인덱스 IX_T_ORD_PAY에는 PAY_DT가 NULL인 행이 없다(IDX-004). 그래서 (가)의 미결제 갈래는 PAY_DT 인덱스가 아니라 ORD_DT 인덱스로 찾고, PAY_DT IS NULL은 테이블에서 거른다.
근본 해결은 모델이다. 매출 인식일을 주문과 결제 처리 때 채우는 실제 컬럼으로 두면 식도 인덱스 함정도 없다. 대신 두 컬럼과 값이 어긋나지 않게 지키는 책임이 생긴다(트리거나 프로그램).
03화에서는 FBI와 가상 컬럼을 재 보고 채택하지 않았다. SQL을 고쳐 3블록 차이로 해결됐기 때문이다. 여기서는 SQL로 풀면 약 283블록, 인덱스로 풀면 2블록이고 호출이 잦다. 같은 도구라도 숫자가 다르면 답이 달라진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황