운영본과 보관본의 MINUS 대사가 0행으로 끝났는데, 한 문장에 20초와 TEMP 400MB가 든다

실전SELECT버거274 / 700

상황

14화의 파티션 전환을 앞두고 박팀장이 주문했다. "운영본 ORD_RANDOM이 이관 전 보관본 ORD와 한 줄도 다르지 않다는 것부터 보여라." 조민우가 MINUS 대사 두 문장을 낮 시간에 돌렸다. 두 문장 다 0행이었고 건수도 1,000,000 = 1,000,000이었다. 대신 한 문장에 20초씩 걸렸고, 그동안 TEMP를 400MB씩 써서 다른 배치가 TEMP 부족 경고를 받았다. 오과장은 서버를 바꿀 때 ORD_RANDOM 적재가 중간에 한 번 끊겼다가 다시 돌았다고 기억한다. 그런데 ORD_RANDOM에는 PK가 없다.

테이블과 인덱스

ORD(이관 전 보관본): 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO)
ORD_RANDOM(운영본): 1,000,000행 · 22,414블록 · PK 없음(인덱스는 ORD_RANDOM_X01 (ORD_DT, ORD_HMS)뿐) · 서버 교체 때 ORDER BY 없이 다시 적재
두 표의 컬럼 9개: ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE, ALPHABET(100자), ALPHABET_NUMERIC(100자) · 한 행 약 190바이트 · ORD_PK 말고는 NOT NULL 제약이 없다
PGA는 자동 관리(정렬이나 해시 하나에 약 20MB까지 메모리) · 통계 최신

원본 SQL

sql

-- ① 보관본에만 있는 행
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE, ALPHABET, ALPHABET_NUMERIC
  FROM ORD
MINUS
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE, ALPHABET, ALPHABET_NUMERIC
  FROM ORD_RANDOM;

-- ② 운영본에만 있는 행
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE, ALPHABET, ALPHABET_NUMERIC
  FROM ORD_RANDOM
MINUS
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE, ALPHABET, ALPHABET_NUMERIC
  FROM ORD;

실행계획 학습용 예시

xplan

[① — ② 도 같은 모양 · 19.84초 · 44,836블록 · TEMP 약 400MB]
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |      1 |        |      0 |00:00:19.84 |   44836 | 51210 |  51210 |          |          |
|   1 |  MINUS              |            |      1 |        |      0 |00:00:19.84 |   44836 | 51210 |  51210 |          |          |
|   2 |   SORT UNIQUE       |            |      1 |   1000K|   1000K|00:00:09.71 |   22422 | 25605 |  25605 |   21M (1)|   201M   |
|   3 |    TABLE ACCESS FULL| ORD        |      1 |   1000K|   1000K|00:00:00.31 |   22422 |     0 |      0 |          |          |
|   4 |   SORT UNIQUE       |            |      1 |   1000K|   1000K|00:00:09.69 |   22414 | 25605 |  25605 |   21M (1)|   201M   |
|   5 |    TABLE ACCESS FULL| ORD_RANDOM |      1 |   1000K|   1000K|00:00:00.30 |   22414 |     0 |      0 |          |          |
-----------------------------------------------------------------------------------------------------------------------------------

[① 결과 0행 · ② 결과 0행 · 두 문장 합계 89,672블록 · 약 40초]

문제

MINUS 양방향 0행과 건수 일치만으로는 두 표가 같다고 단정할 수 없는 경우를 MINUS의 뜻으로 설명하시오. NULL이 든 컬럼을 MINUS가 어떻게 비교하는지도 설명하시오.
두 표를 한 번씩만 읽고 중복과 내용 차이를 함께 잡는 대사 SQL을 작성하고, 원래 대사와 일량(Buffers, TEMP, 시간)을 비교하시오.
키(ORD_NO)로 양쪽에 NOT EXISTS를 돌리자는 안과 FULL OUTER JOIN으로 컬럼을 비교하자는 안의 장단점을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 두 표를 한 번씩 읽어 9 개 컬럼 전부로 묶고, 표마다 몇 번 나왔는지 센다
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE,
       ALPHABET, ALPHABET_NUMERIC,
       COUNT(a) AS cnt_ord, COUNT(b) AS cnt_random
  FROM (SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE,
               ALPHABET, ALPHABET_NUMERIC, 1 AS a, TO_NUMBER(NULL) AS b
          FROM ORD
        UNION ALL
        SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE,
               ALPHABET, ALPHABET_NUMERIC, TO_NUMBER(NULL), 1
          FROM ORD_RANDOM)
 GROUP BY ORD_NO, ORD_DT, ORD_HMS, SHOP_NO, UPPER2, UPPER_CASE, LOWER_CASE,
          ALPHABET, ALPHABET_NUMERIC
HAVING COUNT(a) <> COUNT(b);
-- 0 행이면 두 표는 행의 내용과 개수까지 같다

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

xplan

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |      0 |00:00:11.62 |   44836 | 27104 |  27104 |          |          |
|*  1 |  FILTER              |            |      1 |        |      0 |00:00:11.62 |   44836 | 27104 |  27104 |          |          |
|   2 |   HASH GROUP BY      |            |      1 |   2000K|   1000K|00:00:11.55 |   44836 | 27104 |  27104 |   21M (1)|   212M   |
|   3 |    VIEW              |            |      1 |   2000K|   2000K|00:00:00.92 |   44836 |     0 |      0 |          |          |
|   4 |     UNION-ALL        |            |      1 |        |   2000K|00:00:00.71 |   44836 |     0 |      0 |          |          |
|   5 |      TABLE ACCESS FULL| ORD       |      1 |   1000K|   1000K|00:00:00.25 |   22422 |     0 |      0 |          |          |
|   6 |      TABLE ACCESS FULL| ORD_RANDOM|      1 |   1000K|   1000K|00:00:00.26 |   22414 |     0 |      0 |          |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(COUNT("A")<>COUNT("B"))

[비교]  원래 : 두 문장 89,672블록 · 정렬 4번 · TEMP 400MB × 2 · 약 40초
        고친 : 한 문장 44,836블록 · 해시 집계 1번 · TEMP 약 210MB · 약 12초

모범 답안 서술

MINUS는 집합 연산이라 양쪽 결과를 SORT UNIQUE로 정렬하며 중복을 먼저 지운 뒤 비교한다(2번과 4번 줄). 그래서 같은 행이 몇 번 들어 있는지는 보지 않는다. 적재가 끊겼다 다시 돌아 운영본에 어떤 주문이 두 줄 들어가고 다른 주문이 빠졌다면 빠진 주문은 ① 문장에 나타난다. 하지만 보관본에도 같은 모양의 차이가 있으면, 예를 들어 {x, x, y}와 {x, y, y}처럼 건수가 같고 행의 종류도 같으면 양방향 0행에 건수 일치까지 나온다. ORD_RANDOM에는 PK가 없어서 이런 중복을 DB가 막아 주지 않는다. 0행/0행과 1,000,000 = 1,000,000은 '행의 종류가 같다'까지만 증명한다. NULL은 MINUS에 유리한 점이다. 집합 연산은 NULL끼리 같은 값으로 비교하므로, 두 표의 SHOP_NO가 똑같이 NULL인 행은 차이로 잡히지 않는다. 조인의 =는 NULL끼리 참이 아니어서, 조인으로 같은 일을 하려면 DECODE(a.SHOP_NO, b.SHOP_NO, 0, 1) 같은 NULL 안전 비교를 컬럼마다 써야 한다. 원래 대사는 두 문장이 각각 두 표를 통째로 읽고(44,836블록씩, 합계 89,672블록) 1,000,000행짜리 정렬을 네 번 한다. 정렬 하나가 약 200MB라 메모리(약 20MB)를 넘어, 문장마다 TEMP를 400MB 쓰고 약 20초가 걸린다. 고친 대사는 두 표를 UNION ALL로 한 번씩 읽어(44,836블록) 9개 컬럼 전부로 GROUP BY하고, 표마다 몇 번 나왔는지 세어 개수가 다른 묶음만 남긴다(HAVING COUNT(a) <> COUNT(b)). 한쪽에만 있는 행, 내용이 다른 행(양쪽에 한 번씩 서로 다른 묶음으로 나온다), 같은 행의 개수 차이(중복)를 한 문장으로 모두 잡는다. GROUP BY도 NULL끼리 같은 묶음으로 보므로 MINUS의 NULL 처리를 그대로 지킨다. 정렬 네 번이 해시 집계 한 번이 되어 TEMP는 약 210MB 한 번, 시간은 약 12초다. 결과가 0행이면 두 표는 행의 내용과 개수까지 같다. 키로 양쪽에 NOT EXISTS를 돌리는 안은 ORD_NO만 해시 안티 조인하므로 빠르지만, 키가 있는지만 보고 컬럼 값은 비교하지 못한다. 컬럼 조건을 NOT EXISTS 안에 넣으면 NULL끼리 같지 않다고 보아 NULL이 든 행을 모두 차이로 내놓는다. FULL OUTER JOIN 안은 키 누락과 컬럼 차이를 한 번에 볼 수 있지만 컬럼마다 NULL 안전 비교를 적어야 하고, ORD_RANDOM처럼 키가 유일하지 않은 쪽에서는 조인이 행을 불려서 키별 건수를 따로 세지 않으면 중복을 잡지 못한다.

해설

폭을 줄이는 변형: 비교할 컬럼이 넓으면 GROUP BY ORD_NO, STANDARD_HASH(ORD_DT || '|' || ORD_HMS || '|' || …)처럼 행을 해시값으로 줄여 묶는다. 집계 폭이 줄어 TEMP를 거의 쓰지 않는다. 대신 해시 충돌의 가능성이 아주 작게나마 남으므로, 감사용 증빙에는 전체 컬럼 비교를 남긴다.
구분자를 넣는 까닭: 오라클에서는 ''이 NULL이라 이어 붙인 문자열 안의 NULL은 빈 칸이 된다. 구분자가 없으면 ('AB', NULL)과 ('A', 'B')가 같은 문자열이 된다.
대사도 부하다: 운영 시간에 100만 행짜리 정렬을 네 번 하는 대사는 TEMP를 같이 쓰는 다른 배치를 위협한다. 한 번 읽는 문장으로 바꾼 뒤에도 야간에 돌리고, 결과(0행, 건수, 실행 시각)를 리포트에 남긴다(교재 08화 정합성 회귀 체크리스트).
MINUS 양방향은 키가 유일한 작은 결과(리비전 전후 리포트 수백 행)를 대조할 때 여전히 간단하고 좋은 도구다. 중복이 뜻을 갖는 대상이면 건수를 함께 보거나 이 문제의 GROUP BY 방식을 쓴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황