월초 정산 검수 대상 조회가 결제 완료 주문 3,800건을 보는 데 9초가 걸린다

실전SELECT한빛유통303 / 700

상황

정산팀은 매달 초 지난달 결제 완료 주문 가운데 검수할 주문을 뽑는다. 검수 대상은 (가) 단가 50만 원 이상 상품이 들어 있는 주문이거나 (나) 정산 금액이 결제 금액과 다르게 적재된 주문이다. 조회는 9초가 걸리고, 정산팀은 화면이 멈춘 줄 알고 몇 번씩 다시 누른다. 조민우는 OR를 UNION ALL로 나눠 보겠다며 고친 SQL을 시험해 봤는데, 결과가 302건이 아니라 305건이 나왔다. 박팀장은 고친 SQL과 함께, 인덱스로 해결하자는 DBA 의견도 비교해 달라고 했다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 2024년 7월 결제 완료 주문 3,812건
T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3건 · 단가 50만 원 이상인 줄 9,960개
T_ORD_STLM 정산: 100,000행 · 640블록 · 인덱스 없음 · 정산 배치가 밤마다 T_ORD에서 적재한다 · STLM_AMT NOT NULL
7월 결과: (가)에 해당하는 주문 274건 · (나)에 해당하는 주문 31건 · 둘 다 해당하는 주문 3건 · 통계 최신

원본 SQL

sql

SELECT o.ORD_SEQ, o.CUS_ID, o.PAY_DT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.PAY_DT >= :d1 AND o.PAY_DT < :d2
   AND o.ORD_ST = 'COMPLETE'
   AND (EXISTS (SELECT 1
                  FROM T_ORD_DET d
                 WHERE d.ORD_SEQ = o.ORD_SEQ
                   AND d.UNT_PRC >= :v_prc)            -- (가) 고가 상품
        OR EXISTS (SELECT 1
                     FROM T_ORD_STLM s
                    WHERE s.ORD_SEQ  = o.ORD_SEQ
                      AND s.STLM_AMT <> o.PAY_AMT))    -- (나) 정산 금액 불일치

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        |    302 | 00:00:09.12 |   2276K |
|*  1 |  FILTER                                |              |      1 |        |    302 | 00:00:09.12 |   2276K |
|*  2 |   TABLE ACCESS FULL                    | T_ORD        |      1 |   3790 |   3812 | 00:00:00.01 |     896 |
|*  3 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T_ORD_DET    |   3812 |      1 |    274 | 00:00:00.03 |   11436 |
|*  4 |    INDEX RANGE SCAN                    | PK_T_ORD_DET |   3812 |      2 |   7240 | 00:00:00.01 |    7624 |
|*  5 |   TABLE ACCESS FULL                    | T_ORD_STLM   |   3538 |      1 |     31 | 00:00:09.05 |   2264K |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(( EXISTS (SELECT 0 FROM "T_ORD_DET" "D" WHERE "D"."ORD_SEQ"=:B1 AND "D"."UNT_PRC">=:V_PRC) OR
               EXISTS (SELECT 0 FROM "T_ORD_STLM" "S" WHERE "S"."ORD_SEQ"=:B2 AND "S"."STLM_AMT"<>:B3)))
   2 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."PAY_DT">=:D1 AND "O"."PAY_DT"<:D2))
   3 - filter("D"."UNT_PRC">=:V_PRC)
   4 - access("D"."ORD_SEQ"=:B1)
   5 - filter(("S"."ORD_SEQ"=:B1 AND "S"."STLM_AMT"<>:B2))

문제

9초가 어디서 나오는지 실행계획의 줄 번호와 Starts, Buffers로 설명하시오.
OR를 UNION ALL로 나눠 결과(302건)가 바뀌지 않게 SQL을 고치시오. 조민우의 SQL이 305건을 낸 이유와, 둘째 갈래에서 첫 갈래를 뺄 때 LNNVL을 쓸 수 없는 이유를 설명하시오.
T_ORD_STLM에 (ORD_SEQ) 인덱스를 만들고 SQL은 그대로 두는 안, 두 서브쿼리를 하나의 조인으로 합치는 안과 비교해 일량과 부작용을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (가) 갈래 : 고가 상품이 든 주문 (해시 세미 조인)
SELECT o.ORD_SEQ, o.CUS_ID, o.PAY_DT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.PAY_DT >= :d1 AND o.PAY_DT < :d2
   AND o.ORD_ST = 'COMPLETE'
   AND EXISTS (SELECT 1 FROM T_ORD_DET d
                WHERE d.ORD_SEQ = o.ORD_SEQ
                  AND d.UNT_PRC >= :v_prc)
UNION ALL
-- (나) 갈래 : 정산 금액 불일치 주문 가운데 (가)에 이미 나온 주문은 뺀다
SELECT o.ORD_SEQ, o.CUS_ID, o.PAY_DT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.PAY_DT >= :d1 AND o.PAY_DT < :d2
   AND o.ORD_ST = 'COMPLETE'
   AND EXISTS (SELECT 1 FROM T_ORD_STLM s
                WHERE s.ORD_SEQ  = o.ORD_SEQ
                  AND s.STLM_AMT <> o.PAY_AMT)
   AND NOT EXISTS (SELECT 1 FROM T_ORD_DET d          -- LNNVL 이 아니라 NOT EXISTS : EXISTS 는 참 · 거짓뿐이다
                    WHERE d.ORD_SEQ = o.ORD_SEQ
                      AND d.UNT_PRC >= :v_prc)

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

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |    302 | 00:00:00.05 |    4224 |
|   1 |  UNION-ALL             |            |      1 |        |    302 | 00:00:00.05 |    4224 |
|*  2 |   HASH JOIN SEMI       |            |      1 |    270 |    274 | 00:00:00.02 |    1792 |
|*  3 |    TABLE ACCESS FULL   | T_ORD      |      1 |   3790 |   3812 | 00:00:00.01 |     896 |
|*  4 |    TABLE ACCESS FULL   | T_ORD_DET  |      1 |   9850 |   9960 | 00:00:00.01 |     896 |
|*  5 |   HASH JOIN ANTI       |            |      1 |     30 |     28 | 00:00:00.03 |    2432 |
|*  6 |    HASH JOIN SEMI      |            |      1 |     38 |     31 | 00:00:00.02 |    1536 |
|*  7 |     TABLE ACCESS FULL  | T_ORD      |      1 |   3790 |   3812 | 00:00:00.01 |     896 |
|   8 |     TABLE ACCESS FULL  | T_ORD_STLM |      1 |   100K |   100K | 00:00:00.01 |     640 |
|*  9 |    TABLE ACCESS FULL   | T_ORD_DET  |      1 |   9850 |   9960 | 00:00:00.01 |     896 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   3 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."PAY_DT">=:D1 AND "O"."PAY_DT"<:D2))
   4 - filter("D"."UNT_PRC">=:V_PRC)
   5 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   6 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
       filter("S"."STLM_AMT"<>"O"."PAY_AMT")
   7 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."PAY_DT">=:D1 AND "O"."PAY_DT"<:D2))
   9 - filter("D"."UNT_PRC">=:V_PRC)

[대안 비교 · 같은 7월]
  원래 SQL (OR · FILTER)                      2,276K 블록 · 9.12초
  고친 SQL (UNION ALL + NOT EXISTS)             4,224 블록 · 0.05초 · 302건
  SQL 그대로 + T_ORD_STLM (ORD_SEQ) 인덱스      약 23,000 블록 · 0.1초 안팎 · 정산 적재 10만 건마다 인덱스 갱신
  주문별로 묶은 두 결과를 외부 조인한 한 문장     약 2,430 블록 · 세 테이블을 한 번씩

모범 답안 서술

2번 줄에서 7월 결제 완료 주문 3,812건을 읽은 뒤, 1번 줄 FILTER가 주문마다 OR로 이은 두 서브쿼리를 차례로 연다. OR로 묶인 두 EXISTS가 세미 조인으로 펼쳐지지 않고 FILTER로 남았고, ORD_SEQ가 주문마다 달라 서브쿼리 캐시도 쓸모가 없다. (가)는 3번, 4번 줄에서 PK_T_ORD_DET로 3블록씩 찾아 3,812번에 11,436블록이다. (가)가 거짓인 주문 3,538건에서는 (나)를 열어야 하는데, T_ORD_STLM에는 인덱스가 없어 5번 줄이 열 때마다 정산 10만 행(640블록)을 처음부터 읽는다. 3,538 × 640 = 2,264K블록, 9.05초가 이 한 줄에서 나온다. OR를 UNION ALL 두 갈래로 나누면 각 서브쿼리가 세미 조인으로 펼쳐진다. 첫 갈래는 주문과 주문상세를 해시 세미 조인해 (가) 274건을, 둘째 갈래는 주문과 정산을 해시 세미 조인해 (나) 31건을 찾는다. 정산은 한 번만 읽고(640블록), 금액 비교(STLM_AMT <> PAY_AMT)는 해시 조인의 filter로 함께 걸린다. 조민우의 SQL은 둘째 갈래에서 첫 갈래를 빼지 않아 (가)와 (나)를 모두 만족하는 3건이 두 번 나왔다(274 + 31 = 305). OR는 두 조건 가운데 하나라도 맞으면 한 번이지만, UNION ALL은 두 결과를 그냥 더한다. 그래서 둘째 갈래에 NOT EXISTS ((가) 서브쿼리)를 붙여 첫 갈래에 나온 주문을 뺀다. 이 부분은 주문상세와의 해시 안티 조인이 되어 둘째 갈래는 28건, 합쳐서 302건으로 원래와 같다. LNNVL은 쓸 수 없다. LNNVL은 ORD_SEQ = :v처럼 단순 조건 하나를 받아 그 조건이 거짓이거나 UNKNOWN일 때 참을 돌려주는 함수라 서브쿼리를 넣을 수 없다. 그리고 필요하지도 않다. 단순 조건을 뺄 때 LNNVL이 필요한 것은 비교할 값이 NULL이면 조건이 UNKNOWN이 되어 NOT으로 뒤집어도 행이 사라지기 때문이다. EXISTS는 행이 있는지 없는지만 보므로 참이나 거짓만 돌려주고, NOT EXISTS가 정확한 반대다. 고친 SQL은 2,276K → 4,224블록, 9.12 → 0.05초다. 주문과 주문상세를 두 번씩 읽지만 모두 한 번씩의 FULL 스캔이다. T_ORD_STLM (ORD_SEQ) 인덱스를 만들고 SQL을 그대로 두면 (나)가 주문마다 인덱스로 3블록 남짓을 읽어 약 23,000블록, 0.1초 안팎이 된다. SQL을 고칠 수 없을 때는 좋은 답이지만 FILTER는 그대로 남고, 정산 배치가 밤마다 10만 건을 넣을 때 인덱스도 함께 고쳐야 한다. 화면 하나를 위해 적재 테이블에 인덱스가 생기는 셈이다. 두 서브쿼리를 '주문별로 미리 묶은 두 결과를 외부 조인'하는 한 문장으로 합치면 주문, 주문상세, 정산을 한 번씩 읽어 약 2,430블록이 된다. 가장 적게 읽지만 SQL이 길어지고 조건이 바뀔 때 고칠 곳이 많다. 한 달에 몇 번 쓰는 정산팀 화면이므로 읽기 쉬운 UNION ALL 안을 고른다.

해설

옵티마이저가 OR를 나눠 주지 않은 이유: OR-Expansion은 갈래마다 더 나은 접근 경로가 있을 때 고려된다. 서브쿼리가 OR로 묶이면 이 계획처럼 FILTER로 남는 경우가 많다. OR_EXPAND나 USE_CONCAT 힌트로 나눠지는지 시험해 볼 수는 있지만, 손으로 나눈 SQL이 계획을 더 확실하게 정한다(교재 08화 §4).
둘째 갈래에서 뺄 것은 갈래를 가르는 조건뿐이다: 기간과 상태 조건은 두 갈래가 같으므로 따로 뺄 필요가 없다. 빼야 하는 것은 첫 갈래를 고른 (가) 조건 하나다.
NOT EXISTS의 비용: 둘째 갈래의 해시 안티 조인은 주문상세를 FULL로 한 번 더 읽는다(896블록). 둘째 갈래 결과가 31건뿐이므로 PK_T_ORD_DET로 찾아가는 NL 안티 조인(31 × 3블록)이 더 싸다. 옵티마이저가 고르게 두되 계획으로 확인한다.
정산 금액이 NULL이라면: STLM_AMT <> PAY_AMT는 정산 금액이 NULL인 행에서 UNKNOWN이라, 원래 SQL도 고친 SQL도 그 정산을 (나)로 보지 않는다. 지금은 NOT NULL이지만 'NULL로 적재된 정산도 검수한다'가 요구라면 OR s.STLM_AMT IS NULL을 더해야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황