PG사 불일치 주문번호 다섯 개를 찾는 데 정산 화면이 인덱스를 330블록 읽는다

중급SELECT한빛유통89 / 700

상황

정산팀은 매일 아침 PG사가 보낸 「금액 불일치 주문」 목록을 받아 「주문번호 일괄 조회」 화면에 주문번호를 몇 개씩 붙여 넣는다. 보통 다섯 개 안팎이고 많으면 스무 개다. 화면은 기간 칸에 최근 석 달을 기본값으로 채워 함께 보낸다. 정산 담당 열 명이 하루 약 1,500번 조회한다. 개발자는 주문번호를 IN 목록으로 넘기니 INLIST ITERATOR가 번호마다 찾아갈 것이라고 했다. 그런데 주문 다섯 건을 찾는 데 335블록을 읽는다. 아래는 2024년 10 ~ 12월을 기간으로 다섯 건을 찾았을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 하루 913건(전부 자정) · 2024년 10 ~ 12월 83,996건(92일)
ORD_SEQ는 값이 겹치지 않는다. 하지만 PK도 유니크 제약도 없다(교재 데이터 그대로)
인덱스 IX_ORD_BIG_DT_SEQ_AMT (ORD_DT, ORD_SEQ, ORD_AMT): 「정산 목록」 키 기반 페이징과 「기간 매출」이 쓴다(ACC-069) · BLEVEL 2 · 리프 약 3,890블록(리프 한 블록에 약 257항목) · CLUSTERING_FACTOR 약 1,000,000 · 통계 최신
다른 인덱스는 없다 · 낮에는 주문이 하루 약 900건 INSERT된다

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND ORD_SEQ IN (:n1, :n2, :n3, :n4, :n5)

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 ACC-069와 같다

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                       |      1 |        |      5 |00:00:00.01 |     335 |
|*  1 |  FILTER                               |                       |      1 |        |      5 |00:00:00.01 |     335 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG             |      1 |      1 |      5 |00:00:00.01 |     335 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_DT_SEQ_AMT |      1 |      1 |      5 |00:00:00.01 |     330 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - 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_SEQ"=:N1 OR "ORD_SEQ"=:N2 OR "ORD_SEQ"=:N3 OR "ORD_SEQ"=:N4 OR "ORD_SEQ"=:N5))

문제

주문 다섯 건인데 3번 줄이 330블록을 읽는 이유를 Predicate Information으로 설명하시오. 주문번호를 IN 목록으로 줬는데도 INLIST ITERATOR가 나오지 않은 이유도 설명하시오.
(가) 기간을 날짜 목록으로 펼쳐 두 조건을 모두 IN 목록으로 주는 방법과 (나) 주문번호가 앞에 오는 인덱스를 만드는 방법을 읽는 블록 수로 비교하시오. 하나를 골라 SQL과 DDL을 작성하시오.
고른 방법의 대가를 쓰고, 담당자가 기간을 주문일 하루로 좁혀 조회하면 어느 쪽이 싼지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 주문번호는 값이 겹치지 않는다 → 유니크 인덱스로 만들고 PK 로 선언한다
--   번호가 앞이라 IN 목록이 번호마다 = 로 찾아간다 (INLIST ITERATOR)
CREATE UNIQUE INDEX PK_T_ORD_BIG ON T_ORD_BIG (ORD_SEQ) ONLINE;
ALTER TABLE T_ORD_BIG ADD CONSTRAINT PK_T_ORD_BIG PRIMARY KEY (ORD_SEQ) USING INDEX PK_T_ORD_BIG;

-- 조회 SQL 은 그대로 (기간은 찾은 다섯 행을 테이블에서 거르는 조건으로 남는다)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND ORD_SEQ IN (:n1, :n2, :n3, :n4, :n5);

-- IX_ORD_BIG_DT_SEQ_AMT 는 그대로 둔다 (정산 목록 페이징과 기간 매출이 ORD_DT 선두로 범위를 정한다)

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 ACC-069와 같다

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |      5 |00:00:00.01 |      20 |
|*  1 |  FILTER                       |              |      1 |        |      5 |00:00:00.01 |      20 |
|   2 |   INLIST ITERATOR             |              |      1 |        |      5 |00:00:00.01 |      20 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG    |      5 |      1 |      5 |00:00:00.01 |      20 |
|*  4 |     INDEX UNIQUE SCAN         | PK_T_ORD_BIG |      5 |      1 |      5 |00:00:00.01 |      15 |
------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))
   4 - access(("ORD_SEQ"=:N1 OR "ORD_SEQ"=:N2 OR "ORD_SEQ"=:N3 OR "ORD_SEQ"=:N4 OR "ORD_SEQ"=:N5))

[비교 · (가) 날짜 92개를 IN 목록으로 펼친 안]  INLIST ITERATOR · IX_ORD_BIG_DT_SEQ_AMT INDEX RANGE SCAN Starts 460 · Buffers 약 1,385
[비교 · 기간을 주문일 하루로 좁히면]  지금 인덱스 11블록(브랜치 2 + 리프 4 + 테이블 5) · PK_T_ORD_BIG 20블록

모범 답안 서술

3번 줄의 access에는 ORD_DT 범위만 있고, 주문번호 조건은 filter에만 있다. 인덱스는 ORD_DT → ORD_SEQ 순으로 정렬되어 있는데 ORD_DT가 석 달 범위로 들어왔다. 범위 조건 ORD_DT 뒤에 있는 ORD_SEQ는 IN 목록이어도 읽을 구간을 줄이지 못하고, 석 달 치를 읽어 가며 filter로 거른다. 그래서 83,996항목, 리프 약 327블록을 읽고 다섯 건만 남긴다(3번 줄 330, 테이블 5, 합계 335). INLIST ITERATOR는 IN 목록 컬럼 앞의 컬럼이 모두 =로 정해져 있어야 값마다 그 자리로 바로 찾아갈 수 있다. 여기서는 앞 컬럼 ORD_DT가 범위라서, 번호마다 찾아가도 매번 석 달 치를 다시 읽어야 하므로 옵티마이저가 고르지 않았다. 두 방법을 비교한다. (가) T_ORD_BIG의 ORD_DT는 전부 자정이라 석 달을 날짜 92개로 펼칠 수는 있다. 그러면 두 컬럼이 모두 =가 되어 filter는 사라지지만, INLIST ITERATOR가 92 × 5 = 460번 루트부터 찾아 내려가고 한 번에 3블록이 든다. 약 1,380블록에 테이블 5블록을 더하면 지금의 네 배다. 날짜 하나에 딸린 항목이 리프 4블록 남짓뿐이라 찾아 내려가는 비용이 아낀 몫보다 크다. (나) 주문번호가 앞에 오는 인덱스를 만들면 번호 다섯 개마다 한 번씩(4번 줄 Starts 5) =로 찾아간다. 한 번에 3블록, 다섯 번에 15블록이고 테이블 5블록을 더해 335 → 20이다. 기간은 찾은 다섯 행을 테이블에서 거르는 조건으로 남는다. (나)를 고른다. 하루로 치면 약 50만 → 3만 블록이다. ORD_SEQ는 값이 겹치지 않으므로 유니크 인덱스로 만들고 PK로 선언한다. 같은 번호가 두 번 들어오는 일도 함께 막는다. 기존 IX_ORD_BIG_DT_SEQ_AMT를 (ORD_SEQ, ORD_DT, ORD_AMT)로 바꾸는 것은 답이 아니다. 「정산 목록」 페이징과 「기간 매출」이 ORD_DT 선두로 범위를 정하는데, 순서를 바꾸면 그 화면들이 선두 조건을 잃는다. 대가는 주문 INSERT마다 인덱스 하나를 더 고치는 비용이다(하루 약 900건). 100만 행 인덱스를 만드는 동안 주문 입력을 막지 않도록 ONLINE으로 만든다. 담당자가 기간을 주문일 하루로 좁히면 사정이 다르다. 지금 인덱스로는 하루치 913항목(리프 4블록)에 브랜치와 테이블을 더해 11블록이면 되고, PK로는 번호마다 3블록씩 찾아 내려가 20블록이 든다. 기간이 짧으면 범위를 읽으며 거르는 편이 싸고, 길수록 번호로 찾아가는 편이 싸다. 화면 기본값이 석 달이고 대부분 그대로 보내므로 PK가 맞다.

해설

함정: IN 목록이라고 다 INLIST ITERATOR가 되는 것은 아니다. IN 목록 컬럼 앞에 범위 조건 컬럼이 있으면 IN 목록은 filter로 남는다. Predicate Information에서 IN 목록이 access에 있는지 filter에 있는지부터 본다.
ACC-009와 견주어 본다. 그쪽은 범위 안의 값이 셋이고 값 하나에 항목이 10만 개씩이라 펼치는 쪽이 이겼다. 이쪽은 날짜가 92개이고 날짜 하나에 리프 4블록 남짓이라 펼치면 찾아 내려가는 비용만 쌓인다(ACC-010도 같은 이유로 줄지 않았다).
날짜를 펼칠 수 있었던 것도 T_ORD_BIG의 ORD_DT가 전부 자정이기 때문이다. 시분초가 있는 T_ORD였다면 날짜 목록으로 바꾸는 순간 결과가 0건이 된다.
바인드 변수라서 처음 실행한 기간으로 계획이 정해질 수 있다. 하루짜리 조회를 먼저 보고 기존 인덱스 계획이 굳으면 석 달 조회도 그 계획을 따른다. 이 화면은 기간을 거의 늘 석 달로 보내므로 걱정이 작지만, 기간 폭이 크게 갈리는 화면이라면 따로 확인해야 한다(PARSE 단원의 바인드 피킹).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 ACC-069와 같다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 ACC-069와 같다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황