JOIN-034

선물하기를 연 뒤로 CRM의 「고객별 월 주문」 추출이 0.03초에서 18초가 됐다

기초SELECT한빛유통184 / 700

상황

CRM팀은 매달 1일 고객 2,000명 모두에게 지난달 주문 건수와 금액을 붙인 파일을 받는다. 주문이 없는 고객도 0건으로 나와야 해서 아우터 조인으로 썼고, 지금까지는 0.03초 만에 끝났다. 지난달 「선물하기」를 열면서 선물로 받은 주문도 받은 고객의 실적으로 세기로 했다. 개발자는 ON 절의 조인 조건에 OR o.RCV_CUS_ID = c.CUS_ID를 더했고, 현업은 건수와 합계를 검수해 맞다고 했다. 그 뒤로 추출이 18초 걸린다. DB는 19c이고 옵티마이저 설정은 기본값이다. 아래는 2024년 6월로 돌린 실행계획이다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) 말고는 인덱스가 없다 · 통계 최신
이 문제의 T_ORD에는 RCV_CUS_ID(선물 받은 회원) 칸이 있다. 회원이 받는 주문에만 값이 있고, 본인이 받으면 CUS_ID와 같은 번호가 들어간다. 비회원이 주문하면 CUS_ID가 NULL이다
2024년 6월 주문 4,170건: 회원 주문 4,120건(본인 수령 3,700건 · 다른 회원에게 선물 300건 · 비회원에게 선물 120건) · 비회원이 회원에게 보낸 선물 50건
그달 주문했거나 선물을 받은 고객 1,752명 · 원본 결과의 건수 합계 4,470건

원본 SQL

sql

SELECT c.CUS_ID, c.CUS_NM,
       COUNT(o.ORD_SEQ)       AS ord_cnt,
       NVL(SUM(o.ORD_AMT), 0) AS ord_amt
  FROM M_CUS c
  LEFT JOIN T_ORD o
    ON (o.CUS_ID = c.CUS_ID OR o.RCV_CUS_ID = c.CUS_ID)
   AND o.ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)
 GROUP BY c.CUS_ID, c.CUS_NM
 ORDER BY c.CUS_ID

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |      1 |        |   2000 |00:00:18.42 |   1792K |
|   1 |  SORT GROUP BY         |                 |      1 |   2000 |   2000 |00:00:18.42 |   1792K |
|   2 |   NESTED LOOPS OUTER   |                 |      1 |   8340 |   4718 |00:00:18.40 |   1792K |
|   3 |    TABLE ACCESS FULL   | M_CUS           |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|   4 |    VIEW                | VW_LAT_5E1D7B42 |   2000 |      4 |   4470 |00:00:18.37 |   1792K |
|*  5 |     TABLE ACCESS FULL  | T_ORD           |   2000 |      4 |   4470 |00:00:18.35 |   1792K |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("O"."ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1)
              AND ("O"."CUS_ID"="C"."CUS_ID" OR "O"."RCV_CUS_ID"="C"."CUS_ID")))

문제

1,792K 블록이 어디서 나오는지 실행계획 4번과 5번 줄의 Starts로 설명하시오. OR를 더하기 전처럼 해시 조인을 쓰지 못한 이유를 조인 조건으로 설명하고, 19c 옵티마이저가 이 OR를 스스로 두 갈래로 나누지 않았다는 것을 계획의 어디에서 알 수 있는지도 쓰시오.
결과가 바뀌지 않게, 해시 조인을 쓸 수 있는 등치 조인 조건 하나로 SQL을 고치시오. 같은 주문이 한 고객에게 두 번 세어지거나 빠지지 않게 하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT c.CUS_ID, c.CUS_NM,
       NVL(x.ord_cnt, 0) AS ord_cnt,
       NVL(x.ord_amt, 0) AS ord_amt
  FROM M_CUS c
  LEFT JOIN (SELECT k.CUS_KEY, COUNT(*) AS ord_cnt, SUM(k.ORD_AMT) AS ord_amt
               FROM (SELECT o.CUS_ID AS CUS_KEY, o.ORD_AMT            -- 주문한 고객
                       FROM T_ORD o
                      WHERE o.ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')
                        AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)
                        AND o.CUS_ID IS NOT NULL
                     UNION ALL
                     SELECT o.RCV_CUS_ID, o.ORD_AMT                    -- 선물 받은 고객
                       FROM T_ORD o
                      WHERE o.ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')
                        AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)
                        AND o.RCV_CUS_ID IS NOT NULL
                        AND LNNVL(o.RCV_CUS_ID = o.CUS_ID)) k          -- 본인 수령은 첫 갈래에서 셌다
              GROUP BY k.CUS_KEY) x
    ON x.CUS_KEY = c.CUS_ID                                            -- 등치 조건 하나 : 해시 조인을 쓸 수 있다
 ORDER BY c.CUS_ID

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |      1 |        |   2000 |00:00:00.03 |    1822 |          |
|   1 |  SORT ORDER BY            |        |      1 |   2000 |   2000 |00:00:00.03 |    1822 |  160K (0)|
|*  2 |   HASH JOIN OUTER         |        |      1 |   2000 |   2000 |00:00:00.03 |    1822 | 1452K (0)|
|   3 |    TABLE ACCESS FULL      | M_CUS  |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   4 |    VIEW                   |        |      1 |   1980 |   1752 |00:00:00.02 |    1792 |          |
|   5 |     HASH GROUP BY         |        |      1 |   1980 |   1752 |00:00:00.02 |    1792 | 1290K (0)|
|   6 |      VIEW                 |        |      1 |   4330 |   4470 |00:00:00.02 |    1792 |          |
|   7 |       UNION-ALL           |        |      1 |        |   4470 |00:00:00.02 |    1792 |          |
|*  8 |        TABLE ACCESS FULL  | T_ORD  |      1 |   4130 |   4120 |00:00:00.01 |     896 |          |
|*  9 |        TABLE ACCESS FULL  | T_ORD  |      1 |    200 |    350 |00:00:00.01 |     896 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"."CUS_KEY"(+)="C"."CUS_ID")
   8 - filter(("O"."CUS_ID" IS NOT NULL AND "O"."ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND
              "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1)))
   9 - filter(("O"."RCV_CUS_ID" IS NOT NULL AND LNNVL("O"."RCV_CUS_ID"="O"."CUS_ID") AND
              "O"."ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1)))

모범 답안 서술

4번 줄 VW_LAT_로 시작하는 뷰의 Starts가 2,000이다. 고객 2,000명마다 이 뷰를 다시 열었고, 그때마다 5번 줄에서 T_ORD 896블록을 처음부터 끝까지 읽으며 조건에 맞는 주문을 골랐다. 2,000 × 896 = 1,792,000블록이 1,792K다. 행으로 치면 주문 10만 행을 2,000번, 모두 2억 번 비교했으므로 시간도 18초가 걸렸다. OR를 더하기 전의 조인 조건 o.CUS_ID = c.CUS_ID는 등치 조건 하나였다. 해시 조인은 이 키 값을 해시 함수에 넣어 짝이 있을 자리를 바로 찾는다. 그런데 o.CUS_ID = c.CUS_ID OR o.RCV_CUS_ID = c.CUS_ID에서는 주문 한 건의 키가 하나로 정해지지 않는다. 주문은 CUS_ID 자리로도, RCV_CUS_ID 자리로도 짝을 찾아야 한다. 이렇게 OR로 이은 조인 조건에는 해시 조인(소트 머지도 마찬가지)이 쓸 등치 키가 없다. 게다가 아우터 조인의 ON 절에 있는 OR는 (+)로 옮길 수 없어서, 옵티마이저는 이 조인을 고객마다 다시 실행되는 래터럴 뷰(4번 줄)로 바꿨다. 남은 방법은 고객 한 명마다 주문을 다 읽는 NL이었다. 19c에는 OR 조건을 UNION ALL 갈래로 펼치는 비용 기반 OR 확장이 있어서, 이너 조인의 WHERE 절에 쓴 OR라면 옵티마이저가 갈래마다 해시 조인하는 계획을 고를 수도 있다. 하지만 이 계획에는 VW_ORE_로 시작하는 뷰도, UNION-ALL도 없다. 이 SQL에서는 펼쳐지지 않았다는 뜻이다. 변환은 옵티마이저가 SQL 모양과 비용을 보고 그때그때 정하므로, 일어나리라 기대하지 말고 계획으로 확인해야 한다. 그래서 키를 SQL에서 한 칸으로 만든다. 주문을 '주문한 고객' 줄과 '선물 받은 고객' 줄로 펼쳐(UNION ALL) 고객 번호 한 칸(CUS_KEY)에 모으고, 고객별로 묶은 뒤 M_CUS에 등치 조건 하나로 아우터 조인한다. 둘째 갈래에서는 본인 수령 주문 3,700건을 뺀다. 원본의 OR는 두 조건이 함께 참이어도 한 번만 세기 때문이다. 이때 o.RCV_CUS_ID <> o.CUS_ID로 빼면 비회원이 보낸 선물 50건도 함께 빠진다. CUS_ID가 NULL이면 <>의 결과가 참이 아니기 때문이다. LNNVL(o.RCV_CUS_ID = o.CUS_ID)는 조건이 거짓이거나 NULL일 때 참이므로 이 50건을 남긴다. 펼친 줄은 4,120 + 350 = 4,470줄로 원본의 건수 합계와 같다. 고친 SQL은 T_ORD를 두 번 FULL로 읽고(896 × 2) M_CUS와 해시 아우터 조인한다. Buffers는 1,792K → 1,822, 시간은 18.42 → 0.03초로 준다.

해설

UNION ALL만 쓰면 3,700건이 두 번 세어진다: 본인이 받은 주문은 CUS_ID와 RCV_CUS_ID가 같아서 두 갈래에 모두 걸린다. 원본의 OR는 한 번만 센다. 둘째 갈래에서 첫 갈래에 걸린 주문을 빼야 결과가 바뀌지 않는다. 고친 뒤에는 건수 합계(4,470건)와 금액 합계를 원본과 맞대 본다 (교재 08화 §10).
<>가 아니라 LNNVL인 이유: 비교하는 칸에 NULL이 있으면 <>는 참이 아니라 UNKNOWN이 되어 그 행을 버린다. (o.RCV_CUS_ID <> o.CUS_ID OR o.CUS_ID IS NULL)로 써도 같다. 19c의 OR 확장도 둘째 갈래에 LNNVL을 붙여 같은 일을 한다 (교재 08화 §4의 OR 표).
다른 정답: 주문을 한 번만 읽는다: UNPIVOT이나 2행짜리 복제용 테이블로 주문 한 건을 키 두 줄로 펼치면 T_ORD를 한 번만 읽어 926블록 안팎이 된다. 데이터 복제 기법은 SUBQ 단원에서 다룬다.
인덱스로 풀면 어떨까: T_ORD에 CUS_ID와 RCV_CUS_ID 인덱스를 만들면 고객마다 두 인덱스로 찾아갈 수는 있다. 하지만 고객 2,000명을 모두 내는 추출에서는 NL이 고객 수만큼 왕복한다. 전부를 한 번에 내는 일에는 해시가 맞다 (교재 06화).
VW_LAT_는 JOIN-035에서도 나왔다. ON 절에 (+)로 옮길 수 없는 조건이 있다는 신호다. 계획에서 이 이름이 보이면 ON 절부터 읽는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황