JOIN-065

매장을 바꿔 골라도 「매장 일 판매 현황」의 숫자가 똑같다

중급SELECT유통215 / 700

상황

매장 관리 화면의 「매장 일 판매 현황」은 매장과 날짜를 고르면 그날 상품별 판매 수량과 금액을 금액 순으로 보여 준다. 지난주 화면 머리에 매장명을 띄워 달라는 요청이 있어, 개발자가 SQL에 CF07을 붙이고 매장 조건을 CF07 쪽으로 옮겼다. 오늘 아침 강남점 점장이 "우리 매장 숫자가 홍대점이랑 똑같다"라고 알려 왔다. DBA가 실행계획을 열어 보니 MERGE JOIN CARTESIAN이 있었는데, E-Rows와 A-Rows는 모두 맞아떨어졌다. 아래는 홍대점(SHP_NO = 1003)의 2023-06-15를 조회한 실행계획이다.

테이블과 인덱스

CF07 매장: 6행 · 4블록 · CF07_PK (SHP_NO) · 1000은 본사(판매 없음), 판매 매장은 1001 ~ 1005의 5곳
CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT)(하루치가 약 16블록에 모여 있다) · 2023-06-15 판매 687건, 그중 1003 매장 139건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) BLEVEL 2 · 그날 판매상품 1,512줄(상품 71개), 그중 1003 매장 305줄(상품 58개)
CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO) · 통계 최신

원본 SQL

sql

SELECT s.SHP_NM, b.ITM_NO, p.ITM_NM,
       SUM(b.SLE_QTY) AS sle_qty, SUM(b.SLE_QTY * b.SLE_UPRC) AS sle_amt
  FROM CF07 s, CF10 a, CF11 b, CF01 p
 WHERE s.SHP_NO = :v_shp
   AND a.SLE_DT = :v_dt
   AND b.SLE_NO = a.SLE_NO
   AND p.ITM_NO = b.ITM_NO
 GROUP BY s.SHP_NM, b.ITM_NO, p.ITM_NM
 ORDER BY sle_amt DESC

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                |      1 |        |     71 |00:00:00.03 |    2093 |           |
|   1 |  SORT ORDER BY                              |                |      1 |     71 |     71 |00:00:00.03 |    2093 |  9216 (0) |
|   2 |   HASH GROUP BY                             |                |      1 |     71 |     71 |00:00:00.03 |    2093 | 1123K (0) |
|*  3 |    HASH JOIN                                |                |      1 |   1507 |   1512 |00:00:00.02 |    2093 | 1321K (0) |
|   4 |     TABLE ACCESS FULL                       | CF01           |      1 |    109 |    109 |00:00:00.01 |       5 |           |
|   5 |     NESTED LOOPS                            |                |      1 |   1507 |   1512 |00:00:00.02 |    2088 |           |
|   6 |      MERGE JOIN CARTESIAN                   |                |      1 |    685 |    687 |00:00:00.01 |      22 |           |
|   7 |       TABLE ACCESS BY INDEX ROWID           | CF07           |      1 |      1 |      1 |00:00:00.01 |       2 |           |
|*  8 |        INDEX UNIQUE SCAN                    | CF07_PK        |      1 |      1 |      1 |00:00:00.01 |       1 |           |
|   9 |       BUFFER SORT                           |                |      1 |    685 |    687 |00:00:00.01 |      20 | 79872 (0) |
|  10 |        TABLE ACCESS BY INDEX ROWID BATCHED  | CF10           |      1 |    685 |    687 |00:00:00.01 |      20 |           |
|* 11 |         INDEX RANGE SCAN                    | IX_CF10_SLE_DT |      1 |    685 |    687 |00:00:00.01 |       4 |           |
|  12 |      TABLE ACCESS BY INDEX ROWID            | CF11           |    687 |      2 |   1512 |00:00:00.01 |    2066 |           |
|* 13 |       INDEX RANGE SCAN                      | CF11_PK        |    687 |      2 |   1512 |00:00:00.01 |    1376 |           |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("P"."ITM_NO"="B"."ITM_NO")
   8 - access("S"."SHP_NO"=:V_SHP)
  11 - access("A"."SLE_DT"=:V_DT)
  13 - access("B"."SLE_NO"="A"."SLE_NO")

문제

실행계획에 MERGE JOIN CARTESIAN이 생긴 이유와, 6번 줄과 9번 줄의 E-Rows와 A-Rows가 맞는다는 사실이 무엇을 알려 주는지 설명하시오. 화면에 나온 숫자가 무엇의 합인지도 쓰시오.
SQL을 고치고, 고친 뒤 일량과 결과가 어떻게 바뀌는지 숫자로 쓰시오. 같은 실수를 막을 SQL 작성 규칙도 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 테이블마다 ON 절에 조인 조건을 쓴다 : CF07 과 CF10 을 잇는 조건이 빠졌던 자리
SELECT s.SHP_NM, b.ITM_NO, p.ITM_NM,
       SUM(b.SLE_QTY) AS sle_qty, SUM(b.SLE_QTY * b.SLE_UPRC) AS sle_amt
  FROM CF07 s
  JOIN CF10 a ON a.SHP_NO = s.SHP_NO
  JOIN CF11 b ON b.SLE_NO = a.SLE_NO
  JOIN CF01 p ON p.ITM_NO = b.ITM_NO
 WHERE s.SHP_NO = :v_shp
   AND a.SLE_DT = :v_dt
 GROUP BY s.SHP_NM, b.ITM_NO, p.ITM_NM
 ORDER BY sle_amt DESC

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

xplan

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                |      1 |        |     58 |00:00:00.01 |     448 |           |
|   1 |  SORT ORDER BY                             |                |      1 |     58 |     58 |00:00:00.01 |     448 |  7168 (0) |
|   2 |   HASH GROUP BY                            |                |      1 |     58 |     58 |00:00:00.01 |     448 | 1123K (0) |
|*  3 |    HASH JOIN                               |                |      1 |    301 |    305 |00:00:00.01 |     448 | 1321K (0) |
|   4 |     TABLE ACCESS FULL                      | CF01           |      1 |    109 |    109 |00:00:00.01 |       5 |           |
|   5 |     NESTED LOOPS                           |                |      1 |    301 |    305 |00:00:00.01 |     443 |           |
|   6 |      NESTED LOOPS                          |                |      1 |    137 |    139 |00:00:00.01 |      22 |           |
|   7 |       TABLE ACCESS BY INDEX ROWID          | CF07           |      1 |      1 |      1 |00:00:00.01 |       2 |           |
|*  8 |        INDEX UNIQUE SCAN                   | CF07_PK        |      1 |      1 |      1 |00:00:00.01 |       1 |           |
|*  9 |       TABLE ACCESS BY INDEX ROWID BATCHED  | CF10           |      1 |    137 |    139 |00:00:00.01 |      20 |           |
|* 10 |        INDEX RANGE SCAN                    | IX_CF10_SLE_DT |      1 |    685 |    687 |00:00:00.01 |       4 |           |
|  11 |      TABLE ACCESS BY INDEX ROWID           | CF11           |    139 |      2 |    305 |00:00:00.01 |     421 |           |
|* 12 |       INDEX RANGE SCAN                     | CF11_PK        |    139 |      2 |    305 |00:00:00.01 |     280 |           |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("P"."ITM_NO"="B"."ITM_NO")
   8 - access("S"."SHP_NO"=:V_SHP)
   9 - filter(("A"."SHP_NO"=:V_SHP AND "A"."SHP_NO"="S"."SHP_NO"))
  10 - access("A"."SLE_DT"=:V_DT)
  12 - access("B"."SLE_NO"="A"."SLE_NO")

모범 답안 서술

CF07을 붙이면서 매장 조건을 s.SHP_NO = :v_shp로 옮겼는데, CF07과 CF10을 잇는 조인 조건(a.SHP_NO = s.SHP_NO)이 빠졌다. CF07은 다른 어느 테이블과도 조건으로 이어지지 않으므로, 옵티마이저는 매장 1행과 그날 판매 687건을 조건 없이 곱할 수밖에 없다. 그것이 6번 줄 MERGE JOIN CARTESIAN이다. 9번 줄 BUFFER SORT가 판매 687건을 담아 두고 매장 1행마다 내보낸다. 6번 줄과 9번 줄의 E-Rows(685)와 A-Rows(687)가 맞는다는 것은 옵티마이저가 잘못 추정해서 카티션을 고른 것이 아니라는 뜻이다. SQL이 카티션 곱을 시켰다. 매장이 1행이라 행 수는 늘지 않았지만, 판매 687건이 매장으로 걸러지지 않은 채 다음 조인으로 넘어갔다. 그래서 화면의 숫자는 그날 다섯 매장 전체의 판매(1,512줄, 상품 71개)이고, 어느 매장을 골라도 같은 숫자에 고른 매장의 이름만 붙는다. 조인 조건을 되살리면 옵티마이저가 s.SHP_NO = :v_shp를 a.SHP_NO에도 옮겨 적용하고(조건 전이), 판매 687건 중 1003 매장의 139건만 남긴다. CF11_PK를 찾아가는 횟수가 687 → 139번이 되어 Buffers는 2,093 → 448이고, 결과는 305줄, 상품 58개로 바로잡힌다. 같은 실수를 막으려면 조인 조건을 ON 절에 쓰는 ANSI 조인으로 쓴다. 테이블을 하나 붙일 때마다 그 테이블의 ON 절이 비어 있으면 바로 눈에 띈다. 실행계획 점검에서 MERGE JOIN CARTESIAN이 보이면 먼저 FROM 절의 테이블마다 조인 조건이 있는지 확인하고, 그다음에 E-Rows가 틀렸는지 본다.

해설

카티션이 늘 나쁜 것은 아니다: 1행짜리 기준 테이블이나 작은 차원 몇 개를 일부러 곱하는 계획도 있다. 판단 기준은 '곱한 결과가 몇 행인가'와 '그 곱이 SQL의 뜻대로인가'다. 이 문제는 행 수는 멀쩡한데 뜻이 틀렸다.
결과 검증: 매장 다섯 곳의 결과를 더하면 그날 전체와 같아야 한다. 고치기 전 SQL은 매장 하나의 결과가 이미 전체와 같았다. 이런 대조는 숫자가 맞는지 확인하는 가장 싼 방법이다(교재 08화 §10).
추정이 틀린 카티션과 구분: 카티션 아래 입력들의 E-Rows가 1인데 A-Rows가 크다면 오추정이 원인이다(JOIN-069). 이 문제처럼 추정이 맞으면 SQL의 조인 조건부터 본다.
일량은 덤이다: 2,093블록은 작아 보여도 매장 다섯 곳이 하루 수백 번 열면 다섯 배를 읽는다. 하지만 이 문제의 핵심은 성능보다 틀린 숫자가 일주일 동안 화면에 나간 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황