JOIN-035

A 등급 고객 스무 명의 상품평 현황에 일반 고객 1,980명이 0건으로 섞여 나온다

기초SELECT한빛유통185 / 700

상황

CRM팀의 「VIP 상품평 활동」은 A 등급 고객 20명마다 1년 동안 남긴 상품평 건수와 평균 평점을 보여 준다. 평가를 하나도 남기지 않은 VIP도 0건으로 보여야 해서 아우터 조인으로 썼다. 화면을 연 CRM 담당자가 "VIP는 20명인데 목록이 2,000줄이고, 평가가 0건인 VIP가 일반 고객 사이에 묻혀서 안 보인다"라고 했다. 아래는 2023-07-01 ~ 2024-06-30으로 조회한 실행계획이다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 1%(20명) · B 9% · C 90%
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) · EVL_DT는 2023-01-01 ~ 2024-12-31에 고르게 분포
인덱스 IX_ITM_EVL_CUS (CUS_ID, EVL_DT): 고객 상담 화면용으로 작년에 만들었다 · 통계 최신
조회 기간에 A 등급 고객이 남긴 평가 27건(VIP 15명) · 평가가 없는 VIP 5명

원본 SQL

sql

SELECT c.CUS_ID, c.CUS_NM,
       COUNT(e.EVL_LST_NO)     AS evl_cnt,
       ROUND(AVG(e.EVL_PT), 1) AS avg_pt
  FROM M_CUS c
  LEFT JOIN T_ITM_EVL e
    ON e.CUS_ID = c.CUS_ID
   AND c.CUS_GD = 'A'
   AND e.EVL_DT >= :v_from
   AND e.EVL_DT <  :v_to
 GROUP BY c.CUS_ID, c.CUS_NM
 ORDER BY evl_cnt DESC, c.CUS_ID

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |   2000 |00:00:00.02 |      97 |
|   1 |  SORT ORDER BY                           |                 |      1 |   2000 |   2000 |00:00:00.02 |      97 |
|   2 |   HASH GROUP BY                          |                 |      1 |   2000 |   2000 |00:00:00.02 |      97 |
|   3 |    NESTED LOOPS OUTER                    |                 |      1 |   2000 |   2012 |00:00:00.02 |      97 |
|   4 |     TABLE ACCESS FULL                    | M_CUS           |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|   5 |     VIEW                                 | VW_LAT_C83A7ED5 |   2000 |      1 |     27 |00:00:00.01 |      67 |
|*  6 |      FILTER                              |                 |   2000 |        |     27 |00:00:00.01 |      67 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_ITM_EVL       |     20 |      1 |     27 |00:00:00.01 |      67 |
|*  8 |        INDEX RANGE SCAN                  | IX_ITM_EVL_CUS  |     20 |      1 |     27 |00:00:00.01 |      40 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("C"."CUS_GD"='A')
   8 - access("E"."CUS_ID"="C"."CUS_ID" AND "E"."EVL_DT">=:V_FROM AND "E"."EVL_DT"<:V_TO)

문제

화면에 2,000줄이 나온 이유를 실행계획 4번 줄과 6번 줄로 설명하시오. ON 절의 c.CUS_GD = 'A'가 실제로 무엇을 정했는지도 설명하시오.
A 등급 20명만 나오고 평가가 없는 VIP는 0건으로 나오도록 SQL을 고치시오. 아우터 조인에서 조건마다 ON과 WHERE 중 어디에 두어야 하는지 규칙으로 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT c.CUS_ID, c.CUS_NM,
       COUNT(e.EVL_LST_NO)     AS evl_cnt,
       ROUND(AVG(e.EVL_PT), 1) AS avg_pt
  FROM M_CUS c
  LEFT JOIN T_ITM_EVL e
    ON e.CUS_ID = c.CUS_ID          -- inner 쪽(상품평) 조건은 ON : 붙일 상품평을 고른다
   AND e.EVL_DT >= :v_from
   AND e.EVL_DT <  :v_to
 WHERE c.CUS_GD = 'A'               -- 보존할 쪽(고객) 조건은 WHERE : 남길 고객을 고른다
 GROUP BY c.CUS_ID, c.CUS_NM
 ORDER BY evl_cnt DESC, c.CUS_ID
-- (+) 표기 : WHERE e.CUS_ID(+) = c.CUS_ID AND e.EVL_DT(+) >= :v_from AND e.EVL_DT(+) < :v_to AND c.CUS_GD = 'A'

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |     20 |00:00:00.01 |      97 |
|   1 |  SORT ORDER BY                         |                |      1 |     20 |     20 |00:00:00.01 |      97 |
|   2 |   HASH GROUP BY                        |                |      1 |     20 |     20 |00:00:00.01 |      97 |
|   3 |    NESTED LOOPS OUTER                  |                |      1 |     20 |     32 |00:00:00.01 |      97 |
|*  4 |     TABLE ACCESS FULL                  | M_CUS          |      1 |     20 |     20 |00:00:00.01 |      30 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ITM_EVL      |     20 |      1 |     27 |00:00:00.01 |      67 |
|*  6 |      INDEX RANGE SCAN                  | IX_ITM_EVL_CUS |     20 |      1 |     27 |00:00:00.01 |      40 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C"."CUS_GD"='A')
   6 - access("E"."CUS_ID"(+)="C"."CUS_ID" AND "E"."EVL_DT"(+)>=:V_FROM AND "E"."EVL_DT"(+)<:V_TO)

모범 답안 서술

4번 줄은 M_CUS 2,000행을 모두 읽었고, 이 2,000행이 그대로 결과가 됐다. ON 절은 보존할 쪽(M_CUS)의 행을 거르지 않는다. ON은 "어떤 상품평과 짝을 지을 수 있는가"만 정한다. 그래서 c.CUS_GD = 'A'는 A 등급이 아닌 고객에게 짝을 하나도 허락하지 않는 조건이 됐을 뿐이고, 일반 고객 1,980명은 상품평 컬럼이 NULL인 행으로 결과에 남았다. 옵티마이저는 이 조건을 래터럴 뷰(VW_LAT) 안의 6번 줄 FILTER로 처리했다. FILTER는 고객 2,000명마다 실행되지만(Starts 2,000) 등급이 A일 때만 아래 인덱스로 내려가므로 8번 줄의 Starts는 20이다. 읽는 양은 97블록으로 작다. 문제는 화면이 받은 행이 2,000줄이고, 평가가 0건인 VIP 5명이 0건인 일반 고객 1,980명 사이에 섞여 보이지 않았다는 것이다. 등급 조건은 결과에 남길 고객을 고르는 조건이므로 WHERE로 옮긴다. 그러면 4번 줄에서 20명만 남기고 그 20명에게만 상품평을 붙인다. 결과는 2,000줄 → 20줄(평가한 VIP 15명, 0건인 VIP 5명)이고 Buffers는 97 → 97로 같다. 규칙은 이렇다. NULL로 채워질 쪽(inner, T_ITM_EVL)의 조건은 ON에 두어 짝을 지을 때 쓰고, 보존할 쪽(M_CUS)의 조건은 WHERE에 두어 결과에 남길 행을 고른다. (+) 표기로 쓰면 inner 쪽 조건에만 (+)를 붙이고 c.CUS_GD = 'A'에는 붙이지 않는다.

해설

반대 실수와 짝을 이룬다: inner 쪽 조건을 WHERE에 두면 NULL 행이 버려져 이너 조인이 된다 (JOIN-005, JOIN-045). 보존할 쪽 조건을 ON에 두면 아무것도 걸러지지 않는다. 두 실수 모두 Buffers로는 드러나지 않고 결과 줄 수로만 드러난다.
12c 이후의 계획: ANSI 아우터 조인의 ON에 보존할 쪽만 참조하는 조건이 있으면 (+)로는 표현할 수 없어서, 옵티마이저가 VW_LAT_로 시작하는 래터럴 뷰와 FILTER를 만든다. 계획에서 이 이름이 보이면 ON 절 조건의 자리부터 의심해 본다.
다른 정답: FROM (SELECT ... FROM M_CUS WHERE CUS_GD = 'A') c LEFT JOIN ...처럼 보존할 쪽을 먼저 걸러 둔 인라인 뷰로 써도 같다.
화면이 2,000줄을 받아 앱에서 A 등급만 골라 보여 주도록 고치면 결과는 맞지만, 쓸데없는 1,980줄을 매번 네트워크로 보낸다. 거르는 일은 SQL 안에서 끝낸다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황