JOIN-040

6월 주문 고객과 상품평 고객을 맞대는 표가 두 테이블을 두 번씩 읽는다

중급SELECT한빛유통190 / 700

상황

CRM팀의 「구매 고객과 평가 고객 대조」는 한 달 동안 주문한 고객과 상품평을 남긴 고객을 한 표로 맞대어 '주문만', '평가만', '둘 다'를 나눈다. 기간을 바꿔 가며 하루 300번쯤 돌린다. 개발자는 처음에 두 인라인 뷰의 조인 조건 양쪽에 (+)를 붙였다가 ORA-01468이 나자, 한쪽씩 아우터 조인한 두 SQL을 UNION으로 이었다. 아래는 2024년 6월의 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)만 있다 · 한 달 주문은 테이블 전체에 흩어져 있다 · 2024년 6월 4,172건(고객 1,752명)
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) · EVL_DT에는 인덱스가 없다 · 2024년 6월 211건(고객 200명)
6월에 주문과 평가가 모두 있는 고객 180명 · 평가만 있는 고객 20명 · 통계 최신

원본 SQL

sql

SELECT o.CUS_ID, o.ord_cnt, o.ord_amt, NVL(e.evl_cnt, 0) AS evl_cnt
  FROM (SELECT CUS_ID, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
          FROM T_ORD
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to
         GROUP BY CUS_ID) o,
       (SELECT CUS_ID, COUNT(*) AS evl_cnt
          FROM T_ITM_EVL
         WHERE EVL_DT >= :v_from AND EVL_DT < :v_to
         GROUP BY CUS_ID) e
 WHERE e.CUS_ID(+) = o.CUS_ID
UNION
SELECT e.CUS_ID, NVL(o.ord_cnt, 0), NVL(o.ord_amt, 0), e.evl_cnt
  FROM (SELECT CUS_ID, COUNT(*) AS evl_cnt
          FROM T_ITM_EVL
         WHERE EVL_DT >= :v_from AND EVL_DT < :v_to
         GROUP BY CUS_ID) e,
       (SELECT CUS_ID, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
          FROM T_ORD
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to
         GROUP BY CUS_ID) o
 WHERE o.CUS_ID(+) = e.CUS_ID

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |   1772 |00:00:00.05 |    1888 |
|   1 |  SORT UNIQUE             |           |      1 |   1952 |   1772 |00:00:00.05 |    1888 |
|   2 |   UNION-ALL              |           |      1 |        |   1952 |00:00:00.04 |    1888 |
|*  3 |    HASH JOIN OUTER       |           |      1 |   1752 |   1752 |00:00:00.02 |     944 |
|   4 |     VIEW                 |           |      1 |   1752 |   1752 |00:00:00.02 |     896 |
|   5 |      HASH GROUP BY       |           |      1 |   1752 |   1752 |00:00:00.02 |     896 |
|*  6 |       TABLE ACCESS FULL  | T_ORD     |      1 |   4170 |   4172 |00:00:00.01 |     896 |
|   7 |     VIEW                 |           |      1 |    200 |    200 |00:00:00.01 |      48 |
|   8 |      HASH GROUP BY       |           |      1 |    200 |    200 |00:00:00.01 |      48 |
|*  9 |       TABLE ACCESS FULL  | T_ITM_EVL |      1 |    208 |    211 |00:00:00.01 |      48 |
|* 10 |    HASH JOIN OUTER       |           |      1 |    200 |    200 |00:00:00.02 |     944 |
|  11 |     VIEW                 |           |      1 |    200 |    200 |00:00:00.01 |      48 |
|  12 |      HASH GROUP BY       |           |      1 |    200 |    200 |00:00:00.01 |      48 |
|* 13 |       TABLE ACCESS FULL  | T_ITM_EVL |      1 |    208 |    211 |00:00:00.01 |      48 |
|  14 |     VIEW                 |           |      1 |   1752 |   1752 |00:00:00.02 |     896 |
|  15 |      HASH GROUP BY       |           |      1 |   1752 |   1752 |00:00:00.02 |     896 |
|* 16 |       TABLE ACCESS FULL  | T_ORD     |      1 |   4170 |   4172 |00:00:00.01 |     896 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."CUS_ID"(+)="O"."CUS_ID")
   6 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))
   9 - filter(("EVL_DT">=:V_FROM AND "EVL_DT"<:V_TO))
  10 - access("O"."CUS_ID"(+)="E"."CUS_ID")
  13 - filter(("EVL_DT">=:V_FROM AND "EVL_DT"<:V_TO))
  16 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))

문제

이 SQL이 두 테이블을 몇 번씩 읽는지 계획의 줄 번호로 짚고, 1번 줄 SORT UNIQUE가 무엇을 하는지 설명하시오.
두 테이블을 한 번씩만 읽도록 SQL을 고치시오. 결과의 고객 번호 칸이 비지 않게 하는 방법도 쓰시오.
개발자가 처음 만난 ORA-01468이 왜 났는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 두 뷰를 FULL OUTER JOIN 으로 한 번 맞댄다 : 짝이 없는 쪽의 키는 NULL 이므로 고객 번호는 COALESCE 로
SELECT COALESCE(o.CUS_ID, e.CUS_ID) AS CUS_ID,
       NVL(o.ord_cnt, 0) AS ord_cnt,
       NVL(o.ord_amt, 0) AS ord_amt,
       NVL(e.evl_cnt, 0) AS evl_cnt
  FROM (SELECT CUS_ID, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
          FROM T_ORD
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to
         GROUP BY CUS_ID) o
  FULL OUTER JOIN
       (SELECT CUS_ID, COUNT(*) AS evl_cnt
          FROM T_ITM_EVL
         WHERE EVL_DT >= :v_from AND EVL_DT < :v_to
         GROUP BY CUS_ID) e
    ON e.CUS_ID = o.CUS_ID

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |   1772 |00:00:00.02 |     944 |
|   1 |  VIEW                    | VW_FOJ_0  |      1 |   1772 |   1772 |00:00:00.02 |     944 |
|*  2 |   HASH JOIN FULL OUTER   |           |      1 |   1772 |   1772 |00:00:00.02 |     944 |
|   3 |    VIEW                  |           |      1 |    200 |    200 |00:00:00.01 |      48 |
|   4 |     HASH GROUP BY        |           |      1 |    200 |    200 |00:00:00.01 |      48 |
|*  5 |      TABLE ACCESS FULL   | T_ITM_EVL |      1 |    208 |    211 |00:00:00.01 |      48 |
|   6 |    VIEW                  |           |      1 |   1752 |   1752 |00:00:00.02 |     896 |
|   7 |     HASH GROUP BY        |           |      1 |   1752 |   1752 |00:00:00.02 |     896 |
|*  8 |      TABLE ACCESS FULL   | T_ORD     |      1 |   4170 |   4172 |00:00:00.01 |     896 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."CUS_ID"="O"."CUS_ID")
   5 - filter(("EVL_DT">=:V_FROM AND "EVL_DT"<:V_TO))
   8 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))

모범 답안 서술

UNION의 두 갈래가 저마다 두 인라인 뷰를 새로 만들기 때문에, T_ORD는 6번과 16번 줄에서 두 번(896 × 2), T_ITM_EVL은 9번과 13번 줄에서 두 번(48 × 2) 읽혔다. 1,888블록 중 절반이 같은 일을 되풀이한 몫이다. 1번 줄 SORT UNIQUE는 두 갈래의 결과 1,952줄을 정렬해 겹친 180줄(주문과 평가가 모두 있는 고객)을 지운다. 이 SQL은 UNION의 중복 제거에 기대어 맞는 답을 낸다. (+)는 한 조건에서 한쪽 테이블에만 붙일 수 있다. e.CUS_ID(+) = o.CUS_ID(+)처럼 양쪽에 붙이면 ORA-01468이 난다. 양쪽을 모두 보존하는 조인은 ANSI의 FULL OUTER JOIN으로만 쓸 수 있다. 두 뷰를 FULL OUTER JOIN으로 한 번 맞대면 옵티마이저가 HASH JOIN FULL OUTER(VW_FOJ_0)로 처리해 두 테이블을 한 번씩만 읽는다. Buffers는 1,888 → 944이고 SORT UNIQUE도 없어진다. 하루 300번이면 약 57만 → 28만 블록이다. 짝이 없는 행은 반대쪽 컬럼이 NULL이므로 고객 번호는 COALESCE(o.CUS_ID, e.CUS_ID)로 낸다. o.CUS_ID만 쓰면 평가만 남긴 고객 20명의 고객 번호가 비어 나온다. 결과는 1,772줄(주문만 1,572명, 평가만 20명, 둘 다 180명)로 원본과 같다.

해설

다른 정답: 두 집합을 UNION ALL로 쌓고 고객별로 묶어도 된다(SELECT CUS_ID, SUM(ord_cnt), SUM(ord_amt), SUM(evl_cnt) FROM (... UNION ALL ...) GROUP BY CUS_ID). 조인 없이 두 테이블을 한 번씩 읽고 키를 합칠 일도 없다. 집합이 셋 이상이면 이 모양이 더 간단하다 (JOIN-046).
10g까지의 FULL OUTER JOIN: 예전 옵티마이저는 FULL OUTER JOIN을 "왼쪽 아우터 조인 UNION ALL 오른쪽에만 있는 행(안티 조인)"으로 풀어서 두 테이블을 두 번씩 읽었다. 11g부터는 HASH JOIN FULL OUTER가 한 번에 처리한다. 계획에 UNION-ALL과 ANTI가 보이면 NO_NATIVE_FULL_OUTER_JOIN 힌트나 관련 파라미터가 남아 있는지 확인한다.
UNION의 중복 제거에 기대지 않는다: 두 갈래에서 같은 고객 행이 한 칸이라도 다르게 계산되면(예: 한쪽은 NVL로 0, 다른 쪽은 NULL) SORT UNIQUE가 지우지 못해 고객이 두 줄로 나온다. 결과가 맞더라도 맞는 이유가 우연이면 고쳐 둔다.
대사: 고친 뒤 고객 수(1,772명), 주문 금액 합계, 평가 건수 합계(211건)를 원본과 맞대어 확인한다 (교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황