JOIN-045

(+)로 쓴 POS 리포트를 ANSI 조인으로 옮겼더니 세 개 중 두 개의 숫자가 달라졌다

실전SELECT버거195 / 700

상황

본사가 리포트 도구를 바꾸면서 2012년에 만든 POS 리포트 SQL을 ANSI 조인으로 옮기고 있다. 시범으로 개발자 세 명이 하나씩 손으로 옮겼고, 옮기기 전과 후의 결과를 맞대 보니 숫자가 다른 리포트가 나왔다. 오과장은 "어느 것이 왜 틀렸는지 찾고, 앞으로 수백 개를 옮길 때 쓸 규칙으로 정리해 달라. 일량이 바뀐 것도 있는지 봐 달라"라고 했다. 아래 SQL은 원래 SQL과 옮긴 SQL이고, 실행계획은 2013-06-14로 돌린 것이다.

테이블과 인덱스

SHOP 200행 · 5블록 · PK / ITEM 92행 · 5블록 · PK · SALE_YN 'Y' 78개 · 'N' 14개(판매 중지)
ORD 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · 2013-06-14 주문 1,368건 · 그날 주문이 없는 매장 3곳
ORD_ITEM 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 2013-06-14 주문상품 3,381줄(하루치는 인덱스 리프 약 12블록, 테이블 약 80블록)
그날 팔린 상품 69개 · 판매 중인데 그날 안 팔린 상품 9개 · 통계 최신

원본 SQL

sql

-- [R1] 매장별 오늘 주문 수 (주문 없는 매장은 0)
--   원래 SQL
SELECT s.SHOP_NO, s.SHOP_NM, COUNT(o.ORD_NO) AS ord_cnt
  FROM SHOP s, ORD o
 WHERE o.SHOP_NO(+) = s.SHOP_NO
   AND o.ORD_DT(+)  = :v_dt
 GROUP BY s.SHOP_NO, s.SHOP_NM;
--   옮긴 SQL
SELECT s.SHOP_NO, s.SHOP_NM, COUNT(o.ORD_NO) AS ord_cnt
  FROM SHOP s
  LEFT JOIN ORD o ON o.SHOP_NO = s.SHOP_NO
 WHERE o.ORD_DT = :v_dt
 GROUP BY s.SHOP_NO, s.SHOP_NM;

-- [R2] 판매 중인 상품별 오늘 판매 수량 (안 팔린 상품은 0)
--   원래 SQL
SELECT t.ITEM_ID, t.ITEM_NM, NVL(SUM(i.ORD_ITEM_QTY), 0) AS qty
  FROM ITEM t, ORD_ITEM i
 WHERE i.ITEM_ID(+) = t.ITEM_ID
   AND i.ORD_DT(+)  = :v_dt
   AND t.SALE_YN    = 'Y'
 GROUP BY t.ITEM_ID, t.ITEM_NM;
--   옮긴 SQL
SELECT t.ITEM_ID, t.ITEM_NM, NVL(SUM(i.ORD_ITEM_QTY), 0) AS qty
  FROM ITEM t
  LEFT JOIN ORD_ITEM i ON i.ITEM_ID = t.ITEM_ID AND i.ORD_DT = :v_dt AND t.SALE_YN = 'Y'
 GROUP BY t.ITEM_ID, t.ITEM_NM;

-- [R3] 오늘 주문이 없는 매장 (점검 대상)
--   원래 SQL
SELECT s.SHOP_NO, s.SHOP_NM
  FROM SHOP s, ORD o
 WHERE o.SHOP_NO(+) = s.SHOP_NO
   AND o.ORD_DT(+)  = :v_dt
   AND o.ORD_NO IS NULL;
--   옮긴 SQL
SELECT s.SHOP_NO, s.SHOP_NM
  FROM SHOP s
  LEFT JOIN ORD o ON o.SHOP_NO = s.SHOP_NO AND o.ORD_DT = :v_dt
 WHERE o.ORD_NO IS NULL;

실행계획 학습용 예시

xplan

[R1 · 옮긴 SQL]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |      1 |        |    197 |00:00:00.01 |      45 |
|   1 |  HASH GROUP BY                        |         |      1 |    197 |    197 |00:00:00.01 |      45 |
|*  2 |   HASH JOIN                           |         |      1 |   1370 |   1368 |00:00:00.01 |      45 |
|   3 |    TABLE ACCESS FULL                  | SHOP    |      1 |    200 |    200 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   1370 |   1368 |00:00:00.01 |      38 |
|*  5 |     INDEX RANGE SCAN                  | ORD_X01 |      1 |   1370 |   1368 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------
   2 - access("O"."SHOP_NO"="S"."SHOP_NO")
   5 - access("O"."ORD_DT"=:V_DT)

[R2 · 옮긴 SQL]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |      1 |        |     92 |00:00:00.03 |    7183 |
|   1 |  HASH GROUP BY                            |                 |      1 |     92 |     92 |00:00:00.03 |    7183 |
|   2 |   NESTED LOOPS OUTER                      |                 |      1 |   3381 |   3404 |00:00:00.03 |    7183 |
|   3 |    TABLE ACCESS FULL                      | ITEM            |      1 |     92 |     92 |00:00:00.01 |       7 |
|   4 |    VIEW                                   | VW_LAT_4D1E92A6 |     92 |     37 |   3381 |00:00:00.03 |    7176 |
|*  5 |     FILTER                                |                 |     92 |        |   3381 |00:00:00.03 |    7176 |
|*  6 |      TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM        |     78 |     37 |   3381 |00:00:00.03 |    7176 |
|*  7 |       INDEX RANGE SCAN                    | ORD_ITEM_X01    |     78 |   3381 |    264K|00:00:00.01 |     936 |
---------------------------------------------------------------------------------------------------------------------
   5 - filter("T"."SALE_YN"='Y')
   6 - filter("I"."ITEM_ID"="T"."ITEM_ID")
   7 - access("I"."ORD_DT"=:V_DT)

[원래 SQL과 R3 요약]
  R1 원래   HASH JOIN OUTER   45블록  200줄
  R2 원래   HASH JOIN OUTER   87블록   78줄  (ITEM 78행이 build, 그날 주문상품 범위는 한 번)
  R3 원래 · 옮긴 SQL  HASH JOIN ANTI   45블록  3줄 (두 계획이 같다)

문제

세 리포트마다 옮긴 SQL이 원래 SQL과 결과가 같은지 판정하고, 다르면 몇 줄이 어떻게 달라지는지 쓰시오.
틀린 것을 바르게 고친 ANSI SQL을 작성하고, R2를 잘못 옮겼을 때 일량이 87블록에서 7,183블록으로 늘어난 이유를 계획으로 설명하시오.
(+) 조건을 ANSI로 옮기는 규칙을 조건의 종류(두 테이블을 잇는 조건, inner 쪽 상수 조건, 보존할 쪽 조건, IS NULL 조건)별로 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- [R1] inner(ORD) 쪽 상수 조건은 ON 으로 : WHERE 에 두면 주문 없는 매장 3곳이 사라진다
SELECT s.SHOP_NO, s.SHOP_NM, COUNT(o.ORD_NO) AS ord_cnt
  FROM SHOP s
  LEFT JOIN ORD o ON o.SHOP_NO = s.SHOP_NO AND o.ORD_DT = :v_dt
 GROUP BY s.SHOP_NO, s.SHOP_NM;

-- [R2] 보존할 쪽(ITEM) 조건은 WHERE 로 : ON 에 두면 판매 중지 14개가 0 으로 끼어든다
SELECT t.ITEM_ID, t.ITEM_NM, NVL(SUM(i.ORD_ITEM_QTY), 0) AS qty
  FROM ITEM t
  LEFT JOIN ORD_ITEM i ON i.ITEM_ID = t.ITEM_ID AND i.ORD_DT = :v_dt
 WHERE t.SALE_YN = 'Y'
 GROUP BY t.ITEM_ID, t.ITEM_NM;

-- [R3] 옮긴 SQL 이 맞다 : IS NULL 은 조인 뒤에 거르는 조건이라 WHERE 에 남긴다 (HASH JOIN ANTI)
SELECT s.SHOP_NO, s.SHOP_NM
  FROM SHOP s
  LEFT JOIN ORD o ON o.SHOP_NO = s.SHOP_NO AND o.ORD_DT = :v_dt
 WHERE o.ORD_NO IS NULL;

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

xplan

[R2 · 고친 SQL]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |     78 |00:00:00.01 |      87 |
|   1 |  HASH GROUP BY                        |              |      1 |     78 |     78 |00:00:00.01 |      87 |
|*  2 |   HASH JOIN OUTER                     |              |      1 |   3381 |   3390 |00:00:00.01 |      87 |
|*  3 |    TABLE ACCESS FULL                  | ITEM         |      1 |     78 |     78 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   3381 |   3381 |00:00:00.01 |      80 |
|*  5 |     INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   3381 |   3381 |00:00:00.01 |      12 |
---------------------------------------------------------------------------------------------------------------
   2 - access("I"."ITEM_ID"(+)="T"."ITEM_ID")
   3 - filter("T"."SALE_YN"='Y')
   5 - access("I"."ORD_DT"(+)=:V_DT)

[R1 · 고친 SQL]  HASH JOIN OUTER  45블록  200줄
[R3]             HASH JOIN ANTI   45블록    3줄

모범 답안 서술

R1은 틀렸다: o.ORD_DT(+) = :v_dt는 inner(ORD) 쪽 상수 조건이라 짝을 지을 때 쓰는 조건이다. 옮긴 SQL은 이 조건을 WHERE에 두었고, WHERE는 조인 뒤에 걸리므로 주문이 없는 매장의 NULL 행이 버려진다. 계획 2번 줄이 OUTER 없는 HASH JOIN이 되었고 결과는 200줄 → 197줄, 오늘 주문이 없는 매장 3곳이 사라졌다. 읽는 양(45블록)은 같아서 속도로는 드러나지 않는다. R2도 틀렸다: t.SALE_YN = 'Y'는 보존할 쪽(ITEM) 조건이라 결과에 남길 상품을 고르는 조건이다. ON에 두면 아무것도 거르지 않고 짝을 지을지만 정하므로, 판매 중지 상품 14개가 수량 0으로 끼어들어 78줄 → 92줄이 된다. 일량도 늘었다. ON에 보존할 쪽 조건이 있으면 (+)로 표현할 수 없어서 옵티마이저가 래터럴 뷰(VW_LAT)를 만들고 NL로 붙였다. 판매 중인 상품 78개마다 ORD_ITEM_X01로 그날 주문상품 범위 전체를 다시 읽고 상품 번호로 걸렀다(7번 줄 Starts 78, A-Rows 264K). 그래서 87 → 7,183블록이 됐다. R3은 맞다: o.ORD_NO IS NULL에는 원래 (+)가 없었다. 조인한 뒤 짝이 없었던 행만 남기는 조건이므로 WHERE에 남겨야 하고, 옮긴 SQL도 그렇게 했다. 옵티마이저는 이 모양을 HASH JOIN ANTI로 바꾸어 3곳을 정확히 낸다(45블록). 옮기는 규칙: 두 테이블을 잇는 (+) 조건은 ON으로 옮긴다. (+)가 붙은 inner 쪽 상수 조건(o.ORD_DT(+) = :v_dt)도 ON으로 옮긴다. (+)가 없는 보존할 쪽 조건(t.SALE_YN = 'Y')은 WHERE에 둔다. (+) 없이 inner 쪽 컬럼에 건 IS NULL은 조인 뒤의 필터이므로 WHERE에 둔다. 한마디로 (+)가 붙은 조건은 ON으로, 붙지 않은 조건은 WHERE로 간다. 검증: 옮긴 SQL마다 원래 SQL과 결과를 MINUS 양방향으로 맞대고 줄 수를 비교한다. R1처럼 일량이 같아도 결과가 다를 수 있으므로 계획 비교만으로 합격시키지 않는다.

해설

원래 SQL에 (+)가 빠져 있었다면: 원래 SQL 자체에 (+)가 빠져 있었다면(JOIN-005처럼) 규칙대로 옮긴 ANSI도 같은 버그를 그대로 옮긴다. 옮기는 작업의 목표는 결과를 바꾸지 않는 것이므로, 버그는 옮긴 뒤에 따로 고치고 따로 승인받는다.
래터럴 뷰는 신호다: 계획에 VW_LAT_가 보이면 ON 절에 보존할 쪽만 참조하는 조건이 있는지 먼저 본다. 대개 그 조건은 WHERE로 가야 할 조건이다 (JOIN-035).
안티 조인의 다른 표기: R3은 NOT EXISTS로 써도 같은 HASH JOIN ANTI가 된다. 새로 쓰는 리포트라면 의도가 드러나는 NOT EXISTS가 읽기 쉽다.
대사를 자동으로: 리포트 도구 전환처럼 SQL을 대량으로 옮길 때는 리포트마다 옮기기 전과 후의 결과를 MINUS 양방향과 건수, 합계로 맞대는 스크립트를 함께 만든다 (교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황