등급 재산정 UPDATE가 C 등급 고객 1,800명을 보는 데 주문 블록 90만 개를 읽는다

중급UPDATE한빛유통262 / 700

상황

매년 마지막 날 23시에 등급 재산정 배치가 돈다. 등급을 받은 날(GD_APL_DT)부터 지금까지의 주문 금액 합계가 기준(:v_amt, 4,500만 원) 이상인 C 등급 고객을 B로 올리는 UPDATE다. 올해 리허설에서 이 한 문장이 3.7초 걸렸다. 같은 시간대에 적재 배치가 돌아서 오과장은 "이 단계가 다른 배치의 버퍼를 다 밀어낸다"라고 걱정한다. SQL에는 힌트가 하나도 없는데 계획에 FILTER가 보인다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · C 등급 1,800명 · 이 문제에서는 등급 적용일 GD_APL_DT DATE 컬럼이 더 있다(고객마다 다르고, C 등급은 2023-07-01 ~ 2024-06-30 사이)
T_ORD_BIG 대용량 주문: 1,000,000행 · 5,826블록 · 고객마다 500건 · 한 고객의 주문은 테이블 전체에 흩어져 있다
인덱스 IX_ORD_BIG_CUS (CUS_ID): 높이 3 · 클러스터링 팩터 약 1,000,000 · 다른 인덱스는 없다
C 등급 고객의 적용일 이후 주문은 고객당 평균 약 167건 · 올해 B로 올라갈 고객 212명 · 통계 최신

원본 SQL

sql

UPDATE M_CUS c
   SET c.CUS_GD = 'B', c.GD_APL_DT = DATE '2025-01-01'
 WHERE c.CUS_GD = 'C'
   AND (SELECT SUM(o.ORD_AMT)
          FROM T_ORD_BIG o
         WHERE o.CUS_ID  = c.CUS_ID
           AND o.ORD_DT >= c.GD_APL_DT) >= :v_amt

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                       |                |      1 |        |      0 |00:00:03.71 |  906378 |
|   1 |  UPDATE                                | M_CUS          |      1 |        |      0 |00:00:03.71 |  906378 |
|*  2 |   FILTER                               |                |      1 |        |    212 |00:00:03.70 |  905430 |
|*  3 |    TABLE ACCESS FULL                   | M_CUS          |      1 |   1800 |   1800 |00:00:00.01 |      30 |
|   4 |    SORT AGGREGATE                      |                |   1800 |      1 |   1800 |00:00:03.68 |  905400 |
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG      |   1800 |     25 |    300K|00:00:03.55 |  905400 |
|*  6 |      INDEX RANGE SCAN                  | IX_ORD_BIG_CUS |   1800 |    500 |    900K|00:00:00.31 |    5400 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( (SELECT SUM("O"."ORD_AMT") FROM "T_ORD_BIG" "O" WHERE "O"."CUS_ID"=:B1 AND
              "O"."ORD_DT">=:B2)>=:V_AMT)
   3 - filter("C"."CUS_GD"='C')
   5 - filter("O"."ORD_DT">=:B1)
   6 - access("O"."CUS_ID"=:B1)

문제

4번에서 6번 줄의 Starts와 Buffers로 905,400블록이 어디서 나오는지 설명하시오. 힌트가 없는데도 옵티마이저가 이 서브쿼리를 펼치지 않은 이유도 설명하시오.
같은 고객 212명을 올리도록 UPDATE를 고치고, 인덱스로 푸는 방법과 비교해 어느 쪽을 택할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객과 주문을 먼저 조인(적용일 조건은 조인 조건으로)하고, 조인한 뒤 고객별로 묶어 기준 이상만 남긴다
UPDATE M_CUS c
   SET c.CUS_GD = 'B', c.GD_APL_DT = DATE '2025-01-01'
 WHERE c.CUS_GD = 'C'
   AND c.CUS_ID IN (SELECT x.CUS_ID
                      FROM M_CUS x, T_ORD_BIG o
                     WHERE x.CUS_GD  = 'C'
                       AND o.CUS_ID  = x.CUS_ID
                       AND o.ORD_DT >= x.GD_APL_DT
                     GROUP BY x.CUS_ID
                    HAVING SUM(o.ORD_AMT) >= :v_amt);
-- (다른 답) FILTER 를 남긴다면 서브쿼리가 테이블에 가지 않게: 고객마다 4~5블록
-- CREATE INDEX IX_ORD_BIG_CUS_DT_AMT ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_AMT);

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |           |      1 |        |      0 |00:00:00.31 |    6834 |          |
|   1 |  UPDATE                  | M_CUS     |      1 |        |      0 |00:00:00.31 |    6834 |          |
|*  2 |   HASH JOIN RIGHT SEMI   |           |      1 |     90 |    212 |00:00:00.30 |    5886 | 1410K (0)|
|   3 |    VIEW                  | VW_NSO_1  |      1 |     90 |    212 |00:00:00.30 |    5856 |          |
|*  4 |     HASH GROUP BY        |           |      1 |     90 |    212 |00:00:00.30 |    5856 | 1531K (0)|
|*  5 |      HASH JOIN           |           |      1 |    300K|    300K|00:00:00.21 |    5856 | 1479K (0)|
|*  6 |       TABLE ACCESS FULL  | M_CUS     |      1 |   1800 |   1800 |00:00:00.01 |      30 |          |
|   7 |       TABLE ACCESS FULL  | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.06 |    5826 |          |
|*  8 |    TABLE ACCESS FULL     | M_CUS     |      1 |   1800 |   1800 |00:00:00.01 |      30 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="CUS_ID")
   4 - filter(SUM("O"."ORD_AMT")>=:V_AMT)
   5 - access("O"."CUS_ID"="X"."CUS_ID")
       filter("O"."ORD_DT">="X"."GD_APL_DT")
   6 - filter("X"."CUS_GD"='C')
   8 - filter("C"."CUS_GD"='C')

모범 답안 서술

2번 줄 FILTER 아래 4번에서 6번 줄의 Starts가 1,800이다. C 등급 고객 1,800명마다 서브쿼리를 따로 실행했고, 고객번호는 고객마다 하나라 FILTER의 캐시도 듣지 않는다. 한 번 실행할 때마다 IX_ORD_BIG_CUS로 그 고객의 주문 500건을 찾고(3블록), 적용일 이후 주문인지 보고 금액을 더하려고 테이블을 500번 찾아갔다. 한 고객의 주문은 테이블 전체에 흩어져 있어(클러스터링 팩터 약 100만) 500번이 거의 500블록이다. 1,800 × 503 ≈ 905,400블록이 여기서 나온다. 이 서브쿼리는 옵티마이저가 펼치지 못한다. 상관 조건에 o.ORD_DT >= c.GD_APL_DT라는 부등호가 있어서, 주문을 고객별로 미리 묶어 둘 수 없기 때문이다(묶을 범위가 고객마다 다르다). 상관 조건이 o.CUS_ID = c.CUS_ID 하나뿐이었다면 옵티마이저가 고객별 집계 뷰(VW_SQ_1)로 펼쳐 해시 조인했을 것이다. 그래서 손으로 바꾼다. 고객과 주문을 먼저 조인하면서 적용일 조건을 조인 조건으로 걸고, 조인한 뒤에 고객별로 묶어 기준 이상인 고객만 남긴다(GROUP BY … HAVING). 이 집합을 IN으로 받으면 비상관 서브쿼리라 한 번만 실행된다. 주문 테이블을 한 번(5,826블록) 읽고 해시 조인과 해시 집계로 끝나므로 Buffers는 906,378 → 6,834로 준다. 올라가는 고객은 212명 그대로다. FILTER를 남기려면 서브쿼리가 테이블에 가지 않게 (CUS_ID, ORD_DT, ORD_AMT) 인덱스를 만든다. 고객마다 적용일 이후 범위만 인덱스에서 읽어 한 번에 4~5블록, 합계 약 9,000블록이다. 하지만 100만 행짜리 인덱스가 하나 더 생겨 주문을 적재할 때마다 비용이 든다. 1년에 한 번 도는 배치라면 SQL을 고치는 쪽을 택한다.

해설

펼칠 수 있는 모양과 없는 모양: 상관 조건이 등치뿐이면 옵티마이저는 주문을 고객별로 묶은 뷰를 만들어 조인한다. 부등호 상관 조건이 하나라도 끼면 묶을 범위가 바깥 행마다 달라져 그렇게 할 수 없다. 힌트가 없는데 FILTER가 보이면 서브쿼리의 상관 조건부터 본다.
MERGE로 써도 된다: MERGE INTO M_CUS c USING (고객별 합계) s ON (s.CUS_ID = c.CUS_ID) WHEN MATCHED THEN UPDATE …도 같은 일량이다. IN으로 쓴 UPDATE는 서브쿼리가 M_CUS를 한 번 더 읽지만 30블록이라 차이가 없다.
읽기 일관성: 서브쿼리가 갱신 대상인 M_CUS의 CUS_GD를 읽어도 문장이 시작된 시점의 값을 본다. 갱신하는 도중에 B로 바뀐 고객이 집합에서 빠지는 일은 없다.
버퍼: 90만 블록은 대부분 같은 블록을 거듭 읽은 논리 읽기다. 물리 읽기가 적어도 CPU와 래치를 쓰고, 다른 배치와 겹치면 서로의 블록을 밀어낸다(교재 07화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황