날짜 30개짜리 IN 목록으로 바꿨는데 고객 주문 이력은 여전히 100블록을 읽는다

중급SELECT한빛유통85 / 700

상황

고객센터의 「고객 주문 이력」은 고객 한 명의 한 달 치 주문을 보여 준다. 상담원 서른 명이 하루 6천 번쯤 연다. 조민우는 사내 위키에서 "BETWEEN을 IN 목록으로 바꾸면 뒤 컬럼도 access가 된다"는 글을 읽고, 한 달 범위를 날짜 30개짜리 IN 목록으로 바꿨다. 실행계획에서 filter는 사라졌지만 Buffers는 128 → 104로 거의 그대로다. 아래는 고객 CUS_000123의 2024년 6월이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 하루 913건(전부 자정) · 2024년 6월 27,390건 · CUS_ID 2,000명(고객당 500건)
CUS_000123의 2024년 6월 주문: 14건(6일)
인덱스 IX_ORD_BIG_DT_CUS (ORD_DT, CUS_ID): 「일 정산 대사」(ORD_DT = :d, 고객순)용 · BLEVEL 2 · 리프 약 4,100블록(리프 한 블록에 약 245항목) · CLUSTERING_FACTOR 약 1,000,000 · 통계 최신
다른 인덱스 없음

원본 SQL

sql

-- 고치기 전
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

-- 조민우가 고친 판 (화면이 그달의 날짜 수만큼 IN 목록을 만든다)
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT IN (TO_DATE(:d01, 'YYYYMMDD'), TO_DATE(:d02, 'YYYYMMDD'), TO_DATE(:d03, 'YYYYMMDD'),
                  /* … :d04 ~ :d29 … */
                  TO_DATE(:d30, 'YYYYMMDD'));

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

xplan

[고치기 전 — 범위]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |     14 |00:00:00.01 |     128 |
|*  1 |  FILTER                               |                   |      1 |        |     14 |00:00:00.01 |     128 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |      1 |     14 |     14 |00:00:00.01 |     128 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_DT_CUS |      1 |     14 |     14 |00:00:00.01 |     114 |
--------------------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "CUS_ID"=:V_CUS AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter("CUS_ID"=:V_CUS)

[조민우 판 — IN 목록 30개]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |     14 |00:00:00.01 |     104 |
|   1 |  INLIST ITERATOR                      |                   |      1 |        |     14 |00:00:00.01 |     104 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |     30 |     14 |     14 |00:00:00.01 |     104 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_DT_CUS |     30 |     14 |     14 |00:00:00.01 |      90 |
--------------------------------------------------------------------------------------------------------------------
   3 - access((("ORD_DT"=TO_DATE(:D01,'YYYYMMDD') OR "ORD_DT"=TO_DATE(:D02,'YYYYMMDD') OR … OR
              "ORD_DT"=TO_DATE(:D30,'YYYYMMDD'))) AND "CUS_ID"=:V_CUS)

문제

IN 목록으로 바꾼 뒤 filter가 사라졌는데도 Buffers가 거의 줄지 않은 이유를, 고치기 전 3번 줄이 읽은 양과 고친 뒤 3번 줄의 Starts로 설명하시오.
이 화면에 맞는 해법을 SQL과 인덱스 DDL로 작성하시오.
BETWEEN을 IN 목록으로 바꾸는 방법이 효과를 보는 조건을 정리하고, 날짜 수만큼 IN 목록을 만드는 방식의 부작용도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객이 = 로 들어오므로 고객을 앞에, 기간(범위)을 뒤에
CREATE INDEX IX_ORD_BIG_CUS_DT ON T_ORD_BIG (CUS_ID, ORD_DT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

-- IN 목록을 걷고 범위로 되돌린다 (그달의 날짜 수에 따라 문장이 갈라지지 않는다)
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

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

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |     14 |00:00:00.01 |      17 |
|*  1 |  FILTER                               |                   |      1 |        |     14 |00:00:00.01 |      17 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |      1 |     14 |     14 |00:00:00.01 |      17 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_CUS_DT |      1 |     14 |     14 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("CUS_ID"=:V_CUS AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

고치기 전 3번 줄은 ORD_DT 범위로 6월 1일의 CUS_000123 자리에서 시작해 6월 30일 끝까지 읽었다. 범위 조건 뒤의 CUS_ID는 시작점만 좁히고 filter로 거르므로 약 27,300항목, 리프 약 112블록을 읽고 14건을 남겼다(Buffers 114, 전체 128). IN 목록으로 바꾸면 날짜마다 ORD_DT = 값 AND CUS_ID = :v_cus가 되어 filter는 사라진다. 대신 INLIST ITERATOR가 값 30개마다 인덱스를 루트부터 다시 찾아 내려간다(3번 줄 Starts 30). 한 번에 루트, 브랜치, 리프로 3블록이므로 30번 × 3블록 = 90이다. 이 테이블은 하루치가 913항목, 리프 4블록 남짓이다. 날짜 하나에서 아낄 수 있는 것이 리프 몇 블록인데 찾아 내려가는 데 3블록을 쓰니 남는 것이 없다(114 → 90, 전체 128 → 104). 이 화면에 맞는 해법은 컬럼 순서다. 고객은 =로, 기간은 범위로 들어오므로 (CUS_ID, ORD_DT)를 만들면 '이 고객의 6월'이 연속된 한 구간이 된다. 루트에서 한 번 내려가 리프 한 블록 안의 14항목을 읽는다(인덱스 3, 테이블 14, 전체 128 → 17). SQL은 IN 목록을 걷고 범위로 되돌린다. BETWEEN을 IN 목록으로 바꾸는 방법은 범위 안의 값이 몇 개 안 되고, 값 하나에 딸린 항목이 리프 여러 블록에 걸칠 만큼 많을 때 효과가 있다. 직영점 세 곳에 10만 항목씩 있는 경우가 그렇다(ACC-009). 값이 많고 값마다 몇 블록뿐이면 찾아 내려가는 비용이 아낀 몫을 먹는다. 날짜 수만큼 IN 목록을 만들면 부작용도 있다. 달마다 28 ~ 31개로 목록 길이가 달라서, 바인드 변수를 써도 문장이 네 가지로 갈라지고 커서도 따로 생긴다(교재 11화 §12). 기간을 석 달로 늘리면 값이 90개가 되어 찾아 내려가는 횟수만 늘어난다. 새 인덱스는 INVISIBLE로 만들어 세션에서 재고, 주문 INSERT마다 인덱스 하나를 더 고치는 적재 비용을 T2에 함께 적는다. 기존 (ORD_DT, CUS_ID)는 일 정산 대사가 쓰므로 그대로 둔다.

해설

함정: filter가 사라졌다고 적게 읽는 것은 아니다. Buffers와 Starts를 같이 봐야 한다. access만 남은 계획도 같은 길을 30번 찾아가면 비싸다.
IN 목록이 이득을 보는 경우는 ACC-009와 견주어 보면 분명하다. 그쪽은 값 셋으로 592 → 10이었고, 이쪽은 값 서른으로 114 → 90이다.
날짜를 IN 목록으로 바꿀 수 있었던 것은 이 테이블의 ORD_DT가 전부 자정이기 때문이다. 시분초가 있는 T_ORD에 같은 코드를 쓰면 자정에 딱 맞는 주문이 없어서 결과가 0건이 된다.
(CUS_ID, ORD_DT)는 읽는 범위가 '그 고객의 그 기간'으로 정해지므로 주문이 쌓여 테이블이 커져도 일량이 늘지 않는다. (ORD_DT, CUS_ID)는 하루에 들어오는 주문이 늘수록 한 달 치 읽는 양도 같이 는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황