결제 개편 뒤 미결제 주문을 찾는 화면과 배치 셋이 모두 주문 테이블을 처음부터 읽는다

실전DDL한빛유통48 / 700

상황

결제 연동 개편 뒤로 미결제 주문(PAY_DT가 NULL)을 읽는 SQL이 늘었다. 조민우는 SQL마다 인덱스를 하나씩, 모두 셋을 만들겠다는 요청서를 올렸다. S1용 (PAY_DT, 0), S2용 (PAY_DT, ORD_DT), S3용 (CUS_ID, PAY_DT)다. 박팀장은 요청서를 돌려보내며 이렇게 적었다. "T_ORD는 결제 때마다 UPDATE되는 테이블이다. 지금 있는 PAY_DT 인덱스 하나를 바꿔서 넷을 다 받쳐 와라."

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · CUS_ID VARCHAR2(40) NOT NULL(고객 2,000명, 고객당 50건) · ORD_DT DATE(NOT NULL 제약 없음, 실제 NULL 0건) · ORD_ST · PAY_TP · PAY_AMT
PAY_DT DATE: 결제 시각 · 결제 전이면 NULL · 지금 미결제 41건(그중 3일이 지난 PENDING 9건) · 하루 결제 약 137건 · 한 고객의 미결제는 보통 0 ~ 1건 (문제 설정: IDX-004와 같다. 2024.12 결제 연동 개편 뒤의 모습이라고 본다)
인덱스 IX_T_ORD_DT (ORD_DT): 리프 265 · IX_T_ORD_PAY (PAY_DT): BLEVEL 1 · 리프 265 · 통계 최신

원본 SQL

sql

-- S1 「미결제 독촉」 알림 · 10분마다
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE PAY_DT IS NULL;

-- S2 「자동 취소」 배치 · 매시 정각 · 3일 지난 미결제를 취소한다
UPDATE T_ORD
   SET ORD_ST = 'CANCEL'
 WHERE PAY_DT IS NULL
   AND ORD_ST = 'PENDING'
   AND ORD_DT < TRUNC(SYSDATE) - 3;

-- S3 상담 화면 「이 고객의 미결제」 · 하루 약 3천 번
SELECT ORD_SEQ, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE CUS_ID = :v_cus
   AND PAY_DT IS NULL;

-- S4 결제팀 「일 결제 현황」 · 수시로 · 포인트 결제는 뺀다
SELECT PAY_TP, COUNT(*) AS cnt, SUM(PAY_AMT) AS amt
  FROM T_ORD
 WHERE PAY_DT >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND PAY_DT <  TO_DATE(:v_dt, 'YYYYMMDD') + 1
   AND PAY_TP <> 'POINT'
 GROUP BY PAY_TP;

실행계획 학습용 예시

xplan

[S1]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |     41 |00:00:00.01 |     896 |
|*  1 |  TABLE ACCESS FULL                  | T_ORD        |      1 |     41 |     41 |00:00:00.01 |     896 |
------------------------------------------------------------------------------------------------------------
   1 - filter("PAY_DT" IS NULL)

[S2]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |              |      1 |        |      0 |00:00:00.01 |     916 |
|   1 |  UPDATE                             | T_ORD        |      1 |        |      0 |00:00:00.01 |     916 |
|*  2 |   TABLE ACCESS FULL                 | T_ORD        |      1 |      1 |      9 |00:00:00.01 |     896 |
------------------------------------------------------------------------------------------------------------
   2 - filter(("PAY_DT" IS NULL AND "ORD_ST"='PENDING' AND "ORD_DT"<TRUNC(SYSDATE@!)-3))

[S3 · CUS_000777]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |      1 |00:00:00.01 |     896 |
|*  1 |  TABLE ACCESS FULL                  | T_ORD        |      1 |      1 |      1 |00:00:00.01 |     896 |
------------------------------------------------------------------------------------------------------------
   1 - filter(("PAY_DT" IS NULL AND "CUS_ID"=:V_CUS))

[S4 · 2024-06-14]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |      2 |00:00:00.01 |     141 |
|   1 |  HASH GROUP BY                      |              |      1 |      2 |      2 |00:00:00.01 |     141 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD       |      1 |     91 |     91 |00:00:00.01 |     141 |
|*  3 |    INDEX RANGE SCAN                 | IX_T_ORD_PAY |      1 |    137 |    137 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------
   2 - filter("PAY_TP"<>'POINT')
   3 - access("PAY_DT">=TO_DATE(:V_DT,'YYYYMMDD') AND "PAY_DT"<TO_DATE(:V_DT,'YYYYMMDD')+1)

문제

S1 ~ S3이 IX_T_ORD_PAY를 쓰지 못하는 이유와, S4는 그 인덱스를 쓰는 이유를 설명하시오.
조민우가 요청한 인덱스 셋의 문제를 각각 설명하시오. 특히 (PAY_DT, ORD_DT)가 S1을 받치지 못하는 이유를 설명하시오.
IX_T_ORD_PAY 하나를 바꿔 넷을 받치는 DDL을 작성하고, S3에서 PAY_DT IS NULL과 CUS_ID = :v_cus가 각각 어떤 조건이 되는지와 반영 절차를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- IX_T_ORD_PAY (PAY_DT) 를 (PAY_DT, CUS_ID) 로 바꾼다. CUS_ID 는 NOT NULL 이라 모든 행이 인덱스에 들어간다
CREATE INDEX IX_T_ORD_PAY_CUS ON T_ORD (PAY_DT, CUS_ID) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;     -- 내 세션에서 S1 ~ S4 를 잰다
ALTER INDEX IX_T_ORD_PAY_CUS VISIBLE;
ALTER INDEX IX_T_ORD_PAY INVISIBLE;                            -- 한동안 다른 SQL 이 견디는지 지켜본 뒤
DROP INDEX IX_T_ORD_PAY;

-- S1 ~ S4 는 SQL 을 고치지 않는다

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

xplan

[S3 · 고친 뒤]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD            |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | IX_T_ORD_PAY_CUS |      1 |      1 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------
   2 - access("PAY_DT" IS NULL AND "CUS_ID"=:V_CUS)

[S1 · 고친 뒤]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |     41 |00:00:00.01 |      43 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD            |      1 |     41 |     41 |00:00:00.01 |      43 |
|*  2 |   INDEX RANGE SCAN                  | IX_T_ORD_PAY_CUS |      1 |     41 |     41 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------
   2 - access("PAY_DT" IS NULL)

[S2 · 고친 뒤]  INDEX RANGE SCAN IX_T_ORD_PAY_CUS · access("PAY_DT" IS NULL) 41건 → 테이블에서 ORD_ST, ORD_DT를 걸러 9건 취소 · Buffers 916 → 61
[S4 · 고친 뒤]  INDEX RANGE SCAN IX_T_ORD_PAY_CUS · access("PAY_DT">=… AND "PAY_DT"<…) · Buffers 141 그대로

모범 답안 서술

IX_T_ORD_PAY는 단일 컬럼 인덱스라 PAY_DT가 NULL인 미결제 41건을 담지 않는다. S1 ~ S3은 모두 PAY_DT IS NULL인 행을 찾으므로 이 인덱스에는 찾아갈 곳이 없어 셋 다 테이블 전체를 읽는다(896블록). S4는 PAY_DT에 값이 있는 하루치 범위를 찾으므로 같은 인덱스로 137건을 찾아가고, PAY_TP <> 'POINT'는 테이블에서 거르는 filter로 충분하다. S4는 고칠 필요가 없다. 조민우의 요청을 하나씩 본다. (PAY_DT, 0)은 S1, S2를 받치지만 S3의 고객 조건은 미결제 41건을 테이블에서 읽어 거른다. (PAY_DT, ORD_DT)는 S2에 맞아 보이지만 ORD_DT도 NOT NULL 제약이 없는 NULL 허용 컬럼이다. 결합 인덱스는 키가 모두 NULL인 행을 담지 않으므로, 옵티마이저 입장에서는 PAY_DT도 ORD_DT도 NULL인 주문이 이 인덱스에서 빠져 있을 수 있다. ORD_DT의 실제 NULL이 0건이어도 옵티마이저는 데이터가 아니라 제약을 보므로, S1(PAY_DT IS NULL만 있는 조건)에는 이 인덱스를 쓰지 못한다. S2는 ORD_DT < … 조건이 ORD_DT가 NULL이 아님을 보장하므로 쓸 수 있다. (CUS_ID, PAY_DT)는 S3을 받치지만 S1, S2에는 선두 조건이 없다. 무엇보다 셋을 다 만들면 결제 UPDATE가 일어날 때마다 PAY_DT가 든 인덱스 셋을 고쳐야 한다. IX_T_ORD_PAY를 (PAY_DT, CUS_ID)로 바꾼다. CUS_ID는 NOT NULL이므로 모든 행이 인덱스에 들어가고, 미결제 41건은 (NULL, 고객번호) 항목으로 정렬의 맨 끝에 모인다. S1과 S2는 access("PAY_DT" IS NULL)로 41건만 찾아간다(S1 896 → 43, S2 916 → 61). S3에서는 PAY_DT IS NULL이 등치 조건처럼 쓰여 뒤 컬럼 CUS_ID까지 access가 되므로, 그 고객의 미결제 항목만 바로 찾는다(896 → 3). S4는 PAY_DT 범위가 그대로 선두라 계획이 같다(141). 인덱스 개수는 그대로이고, 결제 UPDATE마다 고치는 인덱스도 지금처럼 하나다. 키가 길어져 리프가 265 → 약 370블록으로 커지는 것이 대가다. 반영은 새 인덱스를 INVISIBLE로 만들어 내 세션에서 S1 ~ S4를 잰 뒤 VISIBLE로 바꾸고, 옛 인덱스를 INVISIBLE로 돌려 다른 SQL이 견디는지 한동안 지켜본 다음 지운다.

해설

(PAY_DT, ORD_DT)도 ORD_DT에 NOT NULL 제약을 걸면 S1을 받친다(실제 NULL 0건). 그러면 S2의 ORD_DT까지 access가 되지만, 미결제가 41건뿐이라 이득은 거의 없고 S3은 여전히 41건을 테이블에서 거른다.
지금은 미결제가 41건이라 NULL 범위를 다 읽어도 싸다. 연동 장애로 미결제가 수만 건 쌓이면 S2는 ORD_ST, ORD_DT까지 인덱스에 있어야 하고 S1은 독촉 대상만 담는 희소 인덱스(IDX-047)가 낫다. 숫자가 바뀌면 설계도 다시 본다.
IS NULL이 등치처럼 쓰인다는 것은, 결합 인덱스에 다른 키가 NULL이 아닌 행이 (NULL, 값) 항목으로 정렬의 맨 끝에 한 값처럼 모여 있다는 뜻이다. 단일 컬럼 인덱스에는 그 항목 자체가 없다.
인덱스를 더하는 대신 기존 인덱스를 고쳐 쓰는 설계는 결제 UPDATE의 비용을 늘리지 않는다(교재 04화 §9). 기존 인덱스가 새 인덱스의 앞부분이면 대개 바꿀 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황