정보계 재전송 배치가 1,000건을 보낼 때마다 주문 테이블을 다 읽는다

기초SELECT버거10 / 700

상황

버거원 본사 정보계(DW) 적재가 장애로 비어서 주문 100만 건을 처음부터 다시 보내야 한다. 연동 배치는 마지막으로 보낸 주문번호(:v_last)를 기억해 두고, 그다음 1,000건씩 한 번에 받아 보낸다. 1,000번 돌아야 끝나는데 한 번 도는 데 걸리는 시간이 줄지 않는다. 오과장이 한 번 도는 SQL의 실행계획을 떠 왔다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록(블록당 약 45행) · ORD_NO NUMBER 1 ~ 1,000,000(빈 번호 없음, 주문 일시 순으로 매겼다) · 테이블도 주문번호 순서로 쌓였다
인덱스 ORD_PK (ORD_NO): BLEVEL 2 · 리프 약 2,090블록 · ORD_X01 (ORD_DT, ORD_HMS) · 통계 최신

원본 SQL

sql

SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 WHERE ORD_NO - :v_last BETWEEN 1 AND 1000
 ORDER BY ORD_NO

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000 |00:00:00.21 |   22239 |          |
|   1 |  SORT ORDER BY     |      |      1 |   2500 |   1000 |00:00:00.21 |   22239 |59392  (0)|
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |   2500 |   1000 |00:00:00.20 |   22239 |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_NO"-:V_LAST>=1 AND "ORD_NO"-:V_LAST<=1000))

문제

ORD_PK가 있는데도 1,000건을 가져올 때마다 테이블 전체를 읽는 이유를 설명하시오. 재전송 전체(1,000번)의 일량도 어림하시오.
같은 1,000건을 가져오면서 PK 범위만 읽도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 WHERE ORD_NO BETWEEN :v_last + 1 AND :v_last + 1000      -- 계산은 바인드 쪽에서
 ORDER BY ORD_NO

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

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |   1000 |00:00:00.01 |      29 |
|*  1 |  FILTER                      |        |      1 |        |   1000 |00:00:00.01 |      29 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORD    |      1 |   1000 |   1000 |00:00:00.01 |      29 |
|*  3 |    INDEX RANGE SCAN          | ORD_PK |      1 |   1000 |   1000 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:V_LAST+1<=:V_LAST+1000)
   3 - access("ORD_NO">=:V_LAST+1 AND "ORD_NO"<=:V_LAST+1000)

모범 답안 서술

2번 줄 조건은 filter(("ORD_NO"-:V_LAST>=1 AND "ORD_NO"-:V_LAST<=1000))이다. ORD_PK에는 ORD_NO 값이 정렬되어 있는데, 조건은 컬럼에서 :v_last를 뺀 값을 묻는다. 뺄셈도 컬럼 가공이라 정렬 위에서 범위를 지목하지 못하고, 100만 행을 다 읽은 뒤 1,000건만 남긴다(Buffers 22,239). E-Rows 2,500은 식에 대한 양쪽 범위 조건의 기본 선택도 0.25%다. 재전송 전체로는 1,000번 × 22,239 = 약 2,224만 블록을 읽는다. 계산을 바인드 쪽으로 옮긴다(이항). ORD_NO - :v_last BETWEEN 1 AND 1000은 ORD_NO BETWEEN :v_last + 1 AND :v_last + 1000과 같다. 이제 3번 줄이 ORD_PK의 INDEX RANGE SCAN이 되어 항목 1,000개(리프 3블록 남짓)만 읽고, 테이블도 주문번호 순서로 쌓여 있어 약 23블록이면 된다. 인덱스가 ORD_NO 순서로 행을 내주므로 SORT ORDER BY도 사라진다. 한 번에 22,239 → 29블록, 재전송 전체로는 약 2,224만 → 약 2만 9천 블록이다.

해설

번호에 빈 곳이 생길 수 있는 테이블(삭제, 시퀀스 캐시 유실)이라면 ORD_NO > :v_last ORDER BY ORD_NO FETCH FIRST 1000 ROWS ONLY처럼 '마지막 번호 다음부터 1,000건'으로 쓰는 쪽이 안전하다. 범위로 자르면 빈 번호만큼 덜 가져온다. 이 모양도 PK 범위를 읽다가 멈춘다.
키 범위로 일을 나누는 방식은 대량 작업을 여러 세션에 나눠 맡길 때도 쓴다. 나누는 기준 컬럼에 가공이 없어야 각 조각이 자기 범위만 읽는다.
ORD_NO + 0처럼 일부러 인덱스를 피하려고 넣는 가공도 있다. 옛 코드에서 이런 모양을 보면 의도였는지부터 확인한다(IDX-020).
교재 02화 무력화 ①의 컬럼 가공은 함수만이 아니다. 산술 연산도 컬럼 값을 바꾸므로 결과가 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황