DBA가 만든 배송요청일 인덱스를 두고도 택배 연계 배치는 주문 100만 건을 다 읽는다

실전UPDATE한빛유통47 / 700

상황

한빛유통은 결제가 끝난 주문(COMPLETE)을 5분마다 모아 택배사에 배송을 요청한다. 연계 배치는 보낼 주문에 요청 시각을 찍으면서 그 주문번호를 돌려받아 택배사 연계 테이블에 넣는다. 한 번에 300건 안팎인데 한 번 도는 데 0.3초, 하루 288번 돈다. 지난주 DBA가 DLV_REQ_DT에 인덱스를 만들었지만 계획은 그대로다. 조민우는 (DLV_REQ_DT, 0)으로 다시 만들자고 하고, 오과장은 배송 대기 플래그 컬럼을 새로 두자고 한다. 박팀장은 대안을 비교하고 DML 쪽 대가까지 적어 오라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 6,816블록(배송 요청 컬럼을 더한 뒤) · ORD_SEQ 유일 · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%
DLV_REQ_DT DATE: 택배사에 배송을 요청한 시각 · 요청 전이면 NULL · 요청 대상은 COMPLETE뿐이라 PENDING 8만 건과 CANCEL 2만 건은 계속 NULL로 남는다 · 5분마다 새로 COMPLETE가 된 주문 약 300건이 NULL로 들어온다 (문제 설정: 교재의 T_ORD_BIG에는 이 컬럼이 없다. 배송 연계 개편 때 더한 컬럼이라고 본다)
인덱스 IX_ORD_BIG_SEQ (ORD_SEQ) 유니크 · IX_ORD_BIG_DLV (DLV_REQ_DT): 지난주 생성 · 리프 2,650 · 통계 최신(NUM_NULLS 100,300)
새로 COMPLETE가 된 주문은 대부분 테이블 끝에 쌓인 최근 주문이라, 한 번에 보낼 300건은 테이블 끝 블록 몇 개에 모여 있다

원본 SQL

sql

DECLARE
  TYPE t_seq_tab IS TABLE OF T_ORD_BIG.ORD_SEQ%TYPE;
  v_seq t_seq_tab;
BEGIN
  UPDATE T_ORD_BIG
     SET DLV_REQ_DT = SYSDATE
   WHERE ORD_ST = 'COMPLETE'
     AND DLV_REQ_DT IS NULL
  RETURNING ORD_SEQ BULK COLLECT INTO v_seq;

  PKG_DLV_IF.SEND(v_seq);          -- 택배사 연계 테이블에 넣는다
  COMMIT;
END;

실행계획 학습용 예시

xplan

[UPDATE 한 번 · 300건]
---------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:00:00.31 |    7428 |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:00:00.31 |    7428 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   9027 |    300 |00:00:00.29 |    6816 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("DLV_REQ_DT" IS NULL AND "ORD_ST"='COMPLETE'))

문제

IX_ORD_BIG_DLV가 쓰이지 않는 이유를 설명하고, 조민우의 (DLV_REQ_DT, 0)도 이 배치를 빠르게 하지 못하는 이유를 숫자로 설명하시오.
① (DLV_REQ_DT, ORD_ST) 결합 인덱스 ② 보낼 주문만 담는 함수 기반 인덱스(또는 가상 컬럼) ③ 배송 대기 플래그 컬럼을 새로 두는 모델 변경을 비교하시오. 인덱스 크기와 UPDATE마다 드는 인덱스 유지 비용을 함께 따지시오.
고른 방법의 DDL과 고친 배치 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 보낼 주문(COMPLETE 이면서 아직 요청 전)일 때만 값이 있는 가상 컬럼 → 그 인덱스에는 수백 건만 들어간다
ALTER TABLE T_ORD_BIG ADD (
  DLV_WAIT_YN VARCHAR2(1) GENERATED ALWAYS AS
    (CASE WHEN DLV_REQ_DT IS NULL AND ORD_ST = 'COMPLETE' THEN 'Y' END) VIRTUAL
);
CREATE INDEX IX_ORD_BIG_DLV_WAIT ON T_ORD_BIG (DLV_WAIT_YN);
-- NULL 을 못 담는 IX_ORD_BIG_DLV 는 다른 SQL 이 쓰는지 확인한 뒤 지운다 (요청 때마다 고치는 비용만 든다)

DECLARE
  TYPE t_seq_tab IS TABLE OF T_ORD_BIG.ORD_SEQ%TYPE;
  v_seq t_seq_tab;
BEGIN
  UPDATE T_ORD_BIG
     SET DLV_REQ_DT = SYSDATE
   WHERE DLV_WAIT_YN = 'Y'
  RETURNING ORD_SEQ BULK COLLECT INTO v_seq;

  PKG_DLV_IF.SEND(v_seq);
  COMMIT;
END;

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

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                     |      1 |        |      0 |00:00:00.01 |     618 |
|   1 |  UPDATE                      | T_ORD_BIG           |      1 |        |      0 |00:00:00.01 |     618 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_BIG           |      1 |    300 |    300 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN          | IX_ORD_BIG_DLV_WAIT |      1 |    300 |    300 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DLV_WAIT_YN"='Y')

모범 답안 서술

IX_ORD_BIG_DLV는 단일 컬럼 인덱스라 DLV_REQ_DT가 NULL인 행을 담지 않는다. 이번에 보낼 300건은 아직 요청 전이라 NULL이므로 인덱스 어디에도 없다. DLV_REQ_DT IS NULL로 찾아갈 곳이 없어 테이블 전체를 읽는다(2번 줄 Buffers 6,816, UPDATE까지 7,428). 이 인덱스는 요청 시각을 찍을 때마다 항목을 하나씩 넣는 비용만 들고, 이 배치에는 아무 도움이 되지 않는다. 조민우의 (DLV_REQ_DT, 0)은 NULL 행까지 담는다. 그러나 NULL 범위의 크기를 보면 답이 안 된다. NUM_NULLS 100,300 가운데 보낼 주문은 300건뿐이고, 나머지 10만 건은 영영 요청하지 않을 PENDING 8만 건과 CANCEL 2만 건이 NULL로 쌓여 있는 것이다. 이 인덱스로는 NULL 범위 10만 항목을 읽고 그 행마다 테이블에서 ORD_ST를 확인해야 하므로 FULL보다 비싸고, 옵티마이저도 계속 FULL을 고른다. 대안을 비교한다. ① (DLV_REQ_DT, ORD_ST): 결합 인덱스에서 IS NULL은 등치처럼 쓰이므로 access("DLV_REQ_DT" IS NULL AND "ORD_ST"='COMPLETE')로 300건만 찾아간다. SQL을 고치지 않아도 되는 것이 장점이다. 대신 ORD_ST가 늘 값이 있어 인덱스가 100만 행을 다 담고(리프 약 3,000블록), 요청 시각을 찍을 때마다 (NULL, COMPLETE) 항목을 지우고 (요청 시각, COMPLETE) 항목을 넣는다. 테이블이 클수록 인덱스도 계속 자란다. ② 보낼 주문만 담는 함수 기반 인덱스: CASE WHEN DLV_REQ_DT IS NULL AND ORD_ST = 'COMPLETE' THEN 'Y' END는 보낼 주문일 때만 'Y'이고 나머지는 NULL이다. NULL인 행은 인덱스에 들어가지 않으므로 인덱스에는 지금 보낼 수백 건만 있고, 리프 한두 블록이다. 요청 시각이 찍히면 식이 NULL이 되어 항목이 빠지므로 인덱스는 늘 작게 유지된다. 대신 SQL이 인덱스와 같은 식을 써야 하므로 가상 컬럼(DLV_WAIT_YN)으로 이름을 붙여 WHERE DLV_WAIT_YN = 'Y'로 쓴다. ③ 플래그 컬럼: 주문 상태를 바꾸는 모든 프로그램이 플래그를 제대로 고쳐야 하고(빠뜨리면 배송이 누락된다), NOT NULL 플래그라면 인덱스가 100만 행을 담는다. 모델과 여러 프로그램을 함께 고쳐야 하는 가장 비싼 길이다. ②를 고른다. Buffers는 7,428 → 618이 되고(테이블 읽기 6,816 → 6), 남는 600여 블록은 300건을 고치는 몫이다. 하루 288번이면 약 214만 → 18만 블록이다. 반영 전에 가상 컬럼이 생겨 컬럼 목록 없는 INSERT가 깨지지 않는지 확인하고, 가상 컬럼의 통계를 모은다.

해설

함정: 가상 컬럼의 식에서 ORD_ST를 빼고 CASE WHEN DLV_REQ_DT IS NULL THEN 'Y' END로 만들면 PENDING과 CANCEL 10만 건이 다 들어가 (DLV_REQ_DT, 0)과 같은 문제가 된다. '보낼 주문'의 정의를 그대로 식에 옮긴다.
SQL을 고칠 수 없는 패키지라면 ①이 답이다. 인덱스는 크지만 SQL을 그대로 두고 300건만 찾아간다.
비트맵 인덱스도 NULL을 담지만, 주문처럼 여러 세션이 동시에 고치는 테이블에서는 키 하나에 많은 행이 묶여 잠금이 넓어지므로 쓰지 않는다.
택배사 요청이 실패해 DLV_REQ_DT를 다시 NULL로 되돌리면 그 주문은 저절로 대기 인덱스에 다시 들어가 다음 회차에 재전송된다. 처리 대기 큐를 NULL과 희소 인덱스로 두는 설계의 장점이다.
교재 04화 §9: 인덱스 하나는 5만 건 적재에 redo 약 11.8MB를 더했다. 100만 행을 다 담는 인덱스와 수백 건만 담는 인덱스는 UPDATE 때 고치는 양이 같아도 크기, 캐시, 통계 수집 부담이 다르다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황