주문 시각 인덱스가 있는데도 매시 정산 연계가 주문 테이블을 통째로 읽는다

기초SELECT한빛유통49 / 700

상황

한빛유통의 정산 시스템은 매시 정각에 지난 한 시간의 변경분을 받아 간다. 그 한 시간에 새로 들어온 주문과 결제가 끝난 주문이 변경분이고, 둘 중 하나에만 걸려도 보낸다. 연계 SQL은 한 문장이다. 조민우는 이렇게 말한다. "ORD_DT에 인덱스가 있는데 FULL로 돈다. OR라서 그런 것 같으니 UNION ALL로 나눠 보겠다." 아래는 2024-12-30 14:00 ~ 15:00 구간을 넘겼을 때의 실행계획이다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · 주문 순으로 쌓였다 · ORD_DT DATE(주문 시각, 시분초 있음, NOT NULL, 한 시간에 약 6건) · PAY_DT DATE(결제 시각, 주문 뒤 몇 분 ~ 이틀 안, 결제 전이면 NULL) · CUS_ID · ORD_ST · PAY_AMT
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT): BLEVEL 1 · 통계 최신 · PAY_DT에는 인덱스가 없다
2024-12-30 14시대: 이 시간에 들어온 주문 6건, 이 시간에 결제된 주문 7건, 그중 4건은 이 시간에 주문하고 결제까지 끝냈다(변경분 9건)
연계는 구간의 시작과 끝을 DATE 바인드(:v_from, :v_to)로 넘긴다

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, PAY_DT, ORD_ST, PAY_AMT
  FROM T_ORD
 WHERE (ORD_DT >= :v_from AND ORD_DT < :v_to)
    OR (PAY_DT >= :v_from AND PAY_DT < :v_to)

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows | A-Time      | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      9 | 00:00:00.01 |     896 |
|*  1 |  TABLE ACCESS FULL | T_ORD |      1 |     12 |      9 | 00:00:00.01 |     896 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO) OR ("PAY_DT">=:V_FROM AND "PAY_DT"<:V_TO)))

문제

IX_T_ORD_DT가 있는데도 1번 줄이 TABLE ACCESS FULL인 이유를 OR의 두 갈래로 나눠 설명하시오.
결과를 바꾸지 않고 변경분만 인덱스로 찾아가도록 DDL과 SQL을 작성하시오. 조민우처럼 SQL 두 개를 UNION ALL로 이어 붙일 때 주의할 점도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 결제 시각 갈래에도 찾아갈 곳을 만든다
CREATE INDEX IX_T_ORD_PAY ON T_ORD (PAY_DT);

-- SQL은 그대로 둔다 → 옵티마이저가 OR를 두 갈래로 나눈다 (OR-Expansion)
SELECT ORD_SEQ, CUS_ID, ORD_DT, PAY_DT, ORD_ST, PAY_AMT
  FROM T_ORD
 WHERE (ORD_DT >= :v_from AND ORD_DT < :v_to)
    OR (PAY_DT >= :v_from AND PAY_DT < :v_to);

-- (손으로 나눈다면) 둘째 갈래에서 첫 갈래에 걸린 행을 LNNVL로 뺀다
-- SELECT ... FROM T_ORD WHERE ORD_DT >= :v_from AND ORD_DT < :v_to
-- UNION ALL
-- SELECT ... FROM T_ORD WHERE PAY_DT >= :v_from AND PAY_DT < :v_to
--                         AND (LNNVL(ORD_DT >= :v_from) OR LNNVL(ORD_DT < :v_to))

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

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows | A-Rows | A-Time      | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |      9 | 00:00:00.01 |       8 |
|   1 |  VIEW                                  | VW_ORE_6A2C81F4 |      1 |     13 |      9 | 00:00:00.01 |       8 |
|   2 |   UNION-ALL                            |                 |      1 |        |      9 | 00:00:00.01 |       8 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD           |      1 |      6 |      6 | 00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN                   | IX_T_ORD_DT     |      1 |      6 |      6 | 00:00:00.01 |       2 |
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD           |      1 |      7 |      3 | 00:00:00.01 |       5 |
|*  6 |     INDEX RANGE SCAN                   | IX_T_ORD_PAY    |      1 |      7 |      7 | 00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO)
   5 - filter((LNNVL("ORD_DT">=:V_FROM) OR LNNVL("ORD_DT"<:V_TO)))
   6 - access("PAY_DT">=:V_FROM AND "PAY_DT"<:V_TO)

모범 답안 서술

1번 줄에는 (ORD_DT 구간) OR (PAY_DT 구간)이 통째로 filter에 들어 있고 access가 없다. OR는 둘 중 하나만 맞아도 되므로, IX_T_ORD_DT로 ORD_DT 갈래의 6건을 찾아도 PAY_DT 갈래에 맞는 행이 어디 있는지는 여전히 모른다. PAY_DT에는 인덱스가 없어서 그 갈래에 답하려면 테이블을 다 읽어야 하고, 어차피 다 읽을 바에는 옵티마이저가 FULL 한 번(896블록)으로 두 갈래를 함께 거른다. 한 갈래라도 인덱스로 찾을 수 없으면 OR 전체가 FULL이 된다. PAY_DT에 인덱스 IX_T_ORD_PAY를 만들면 갈래마다 찾아갈 곳이 생기고, 옵티마이저가 OR를 UNION ALL 갈래로 나눈다(OR-Expansion, 계획의 VW_ORE_). 첫 갈래는 IX_T_ORD_DT로 주문 6건을, 둘째 갈래는 IX_T_ORD_PAY로 결제 7건을 찾는다. 둘째 갈래의 5번 줄에는 LNNVL("ORD_DT">=:V_FROM) OR LNNVL("ORD_DT"<:V_TO)가 붙어서 첫 갈래에 이미 나온 4건을 빼고 3건만 남긴다. SQL은 고치지 않아도 되고 결과도 9건 그대로다(Buffers 896 → 8). 조민우처럼 손으로 UNION ALL을 쓰면 둘째 갈래에서 첫 갈래에 걸린 행을 직접 빼야 한다. 빼지 않으면 그 4건이 정산 시스템에 두 번 간다. 빼는 조건은 LNNVL로 쓴다. LNNVL은 조건이 거짓이거나 NULL 때문에 판정할 수 없을 때 참이므로, 빼는 쪽 컬럼에 NULL이 있어도 행을 잃지 않는다. 그리고 손으로 나눠도 PAY_DT 인덱스는 똑같이 필요하다. 인덱스가 없으면 둘째 갈래가 FULL이다. 대가도 있다. 결제가 끝날 때마다(하루 약 137번) PAY_DT를 UPDATE하면서 IX_T_ORD_PAY도 고쳐야 한다. 결제 전 주문은 PAY_DT가 NULL이라 이 인덱스에 들어가지 않으므로, 인덱스는 결제된 주문만큼만 커진다.

해설

함정: UNION ALL로 나누고 둘째 갈래에 아무것도 붙이지 않으면 그 시간에 주문하고 결제한 4건이 두 번 나간다. UNION으로 바꾸면 중복은 사라지지만 결과 전체를 정렬해 같은 행을 지우는 일이 붙는다.
ORD_DT는 NOT NULL이라 둘째 갈래에 AND NOT (ORD_DT >= :v_from AND ORD_DT < :v_to)를 써도 이 문제에서는 답이 같다. 순서를 바꿔 PAY_DT 갈래를 먼저 두고 NOT (PAY_DT ...)로 빼면, 결제 전 주문은 PAY_DT가 NULL이라 조건이 판정 불가(UNKNOWN)가 되어 새 주문이 빠진다. 옵티마이저가 LNNVL을 쓰는 이유다.
OR-Expansion은 비용으로 정한다. 갈래 하나가 테이블의 큰 몫을 돌려주면 펼치지 않고 FULL을 고른다. 한 시간 구간처럼 두 갈래가 다 좁을 때 이득이다.
서로 다른 컬럼을 OR로 묶은 조건은 BITMAP OR(두 인덱스를 비트맵으로 바꿔 합치는 방식)로 풀릴 때도 있다(교재 08화 OR 표). 어느 쪽이든 갈래마다 인덱스가 있어야 한다는 점은 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황