테마
08화 · 금액이 50배로 찍힌다
4월 22일 월요일, 01화에서 "가장 작은데 가장 위험하다"라고 적어 두고 7주 동안 미뤄 둔 Q3를 연다. 오늘은 네 번 틀린다. 그리고 "오라클이 알아서 고쳐 준다"는 믿음이 깨진다.
오전 8시 50분
메신저에 정산팀 조민우 씨 이름이 떴다. 01화 저녁에 선배가 "몇 달째 숫자가 회계랑 안 맞는다고 하는 사람"이라고 했던 바로 그 사람이다.
조민우 08:44
분기 마감 자료 건입니다.
'상품별 매출 TOP 5' 화면 캡처를 붙였는데 재무팀에서 반려됐습니다.
재무팀 원장이랑 금액이 안 맞는답니다. 1위만 봐도 이렇습니다.
화면 ITM00071 22,216,620,000
원장 ITM00071 444,332,400
예전부터 말씀드렸던 그 숫자 건이 이거였던 것 같습니다.
순위는 원장이랑 똑같은데 금액만 이상합니다.손가락으로 자릿수를 셌다. 위는 222억, 아래는 4억 4천만 남짓이었다.
01화에 내가 적은 원장이 떠올랐다.
| Q3 | 841 | 5 | 0.08% | 정합성 · 별도 트랙 최우선 |최우선. 내 손으로 그렇게 적었다. 그러고는 Q1을 고치고, Q2를 고치고, 히트율을 쟀다. 그렇게 7주가 지났다.
오전 9시 05분 — 7주 묵힌 조각
선배가 커피를 들고 와서 내 화면을 봤다. "Q3 왔구나."
"네. 최우선이라고 써 놓고 7주를 미뤘어요."
"알면 됐어. 오늘 하면 되지." 선배가 의자를 끌어왔다. "매주 처음 하는 일부터."
직전 리비전을 여는 일이다. 그런데 Q3에는 리비전이 없었다. 한 번도 고친 적이 없으니까. 화면 소스에서 SQL을 꺼내 Q3_rev00.sql로 저장했다.
sql
SELECT d.ITM_ID, SUM(d.ORD_QTY * d.UNT_PRC) AS amt, COUNT(*) AS cnt
FROM T_ORD_DET d, T_ITM_EVL e
WHERE e.ITM_ID = d.ITM_ID
GROUP BY d.ITM_ID
ORDER BY amt DESC
FETCH FIRST 5 ROWS ONLY;주문상세(T_ORD_DET)와 상품평가(T_ITM_EVL)를 상품번호로 붙이고, 상품별로 더해서 큰 순서로 다섯 개를 뽑는 SQL이다. 일단 쟀다. 이제는 습관이 됐다.
xplan
| Id | Operation | Name | Starts | A-Rows | Buffers |
| 0 | SELECT STATEMENT | | 1 | 5 | 839 |839. 01화 원장에는 841로 적혀 있었다. 몇 번 돌려 보니 839와 841 사이를 오갔다.
"성능은 그대로네요." 내 말에 선배가 고개를 끄덕였다. "그대로지. 그래서 아무도 안 고쳤어. 화면이 느리면 사람들이 전화를 해. 틀리면 아무도 전화 안 해. 재무팀이 원장을 대 보기 전까지는."
오전 9시 20분 — 첫 번째 답
"근데 원장 쪽이 틀렸을 수도 있잖아요." 내가 말했다. "재무팀은 반품이나 취소를 빼고 집계할 수도 있고요. 기준이 다른 거 아닐까요?"
"그럴 수도 있지. 근데 어느 쪽이 틀렸는지 넌 어떻게 알 건데? 원장을 계산하는 SQL은 뭐야?"
화면 SQL을 다시 봤다. 상품별 매출이라면 주문상세의 수량과 단가를 곱해 상품별로 더하면 된다. 매출을 정의하는 데 평가 테이블이 낄 자리는 없다. 평가 조인을 지우고 돌렸다.
ITM_ID AMT CNT
---------- ------------- ----------
ITM00071 444,332,400 2000
ITM00081 443,754,800 2000
ITM00056 441,123,900 2000
ITM00051 440,989,300 2000
ITM00066 439,486,400 2000444,332,400. 재무팀 원장과 한 푼도 다르지 않았다. 원장이 맞았다. 틀린 건 화면이었다.
화면 값을 원장 값으로 나눠 봤다. 22,216,620,000 ÷ 444,332,400 = 50.0000. 다섯 줄 모두 그랬고, 상품 100개를 전부 대 봐도 정확히 50배였다.
그러다 맨 오른쪽 CNT 열이 눈에 들어왔다. ITM00071의 건수가 화면 SQL에서는 100,000, 원장 SQL에서는 2,000이었다.
"건수도 50배예요. 주문상세는 2,000줄인데 10만 줄을 세고 있었어요."
"그 열만 봤어도 처음부터 알 수 있었어. 근데 화면에는 그 열이 안 나와. 금액만 나오지."
다섯 개를 더해 봤다.
| 금액 | |
|---|---|
| 화면의 TOP 5 합계 | 110,484,340,000 |
| 원장의 TOP 5 합계 | 2,209,686,800 |
회사 전체 매출 (T_ORD_DET 전체) | 30,272,557,700 |
상품 다섯 개의 매출이 회사 전체 매출의 3.6배였다.
"이게 몇 달 동안 화면에 떠 있었다고요?" "그러니까 묻는 거야." 선배가 말했다. "왜 아무도 몰랐을까?"
오전 9시 50분 — 50이라는 숫자
"평가를 붙여서 그런 거죠. 평가가 대충 두세 개씩 달려서 부풀었나…" "대충?" "…재 볼게요."
상품마다 평가 수를 세고, 평가 수별로 상품이 몇 개인지 다시 셌다.
EVL_CNT ITEMS
---------- ----------
50 100상품 100개 모두 평가가 정확히 50건이었다. 조인이 만드는 줄도 셌다. 주문상세 200,000줄이 평가와 짝지어지자 10,000,000줄이 됐다. 상품 하나에 2,000 × 50 = 100,000줄이다.
"그럼 아까 질문의 답이 나와." 선배가 말했다. "모든 상품이 똑같이 50배야. 그러면 순위는?"
"…안 바뀌네요. 다 같은 수를 곱했으니까."
"이 화면을 매일 보는 건 마케팅팀이야. 마케팅팀은 순위만 봐. '이번 달 1등이 뭐냐'만 보는 거지. 그 사람들한테 이 화면은 맞는 화면이었어. 금액을 원장이랑 한 줄씩 대 보는 건 재무팀뿐인데, 재무팀은 그걸 분기에 한 번 해. 오늘처럼."
오전 10시 10분 — 회식비 정산표
선배가 지갑에서 영수증 세 장을 꺼냈다.
"지난 금요일 정산팀 회식 거야. 우리 둘도 꼈었잖아. 오늘까지 총무팀에 정산서를 내야 해." 참석자 서명지도 한 장 있었다. 조민우 씨, 정산팀 김○○ 씨, 선배, 나까지 네 명이었다.
영수증 참석자 서명
1차 고깃집 184,000 조민우 · 김○○ · 윤세라 · (나)
2차 호프 62,000
3차 노래방 62,000"총무팀 엑셀은 이렇게 만들게 돼 있어." 선배가 빈 표를 그렸다. "참석자 서명지에서 한 명씩 짚어. 짚을 때마다 그 회식 영수증을 전부 옆에 옮겨 적고. 그다음에 금액 열을 더해."
시키는 대로 했다. 조민우 씨 앞에 184,000, 62,000, 62,000. 김○○ 씨 앞에 또 세 줄. 선배 앞에도, 내 앞에도 또 세 줄씩. 모두 열두 줄을 더했다.
1,232,000. 영수증 세 장을 그냥 더하면 308,000이다.
"…네 배요."
"영수증이 한 장이라도 늘어났어?" "아니요. 세 장 그대로예요. 근데 한 장이 네 번씩 적혔어요." "왜?"
표를 들여다봤다. 영수증은 회식에 딸려 있다. 참석자도 회식에 딸려 있다. 그런데 영수증과 참석자 사이에는…
"서로 아무 관계가 없어요. 둘 다 '그 회식'에 매달려 있을 뿐이에요."
"오늘 할 얘기는 그게 다야." 선배가 펜으로 표 가운데를 짚었다. "가운데 있는 하나에 여러 건이 양쪽으로 매달려 있으면, 그 하나를 다리 삼아 양쪽을 짝짓는 순간 곱하기가 돼. 이걸 M:1:M이라고 해."
| 회식비 정산표 | Q3 |
|---|---|
| 회식 한 건 (가운데 있는 하나) | 상품 ITM_ID |
| 영수증 (한쪽에 여럿) | 주문상세 T_ORD_DET: 상품당 2,000줄, 곧 매출 |
| 참석자 서명 (다른 쪽에 여럿) | 상품평가 T_ITM_EVL: 상품당 50줄 |
| 참석자마다 영수증을 전부 옮겨 적는다 | WHERE e.ITM_ID = d.ITM_ID 조인 |
| 영수증 3장 × 참석자 4명 = 12줄 | 주문상세 2,000 × 평가 50 = 100,000줄(상품 하나) |
| 회식비가 네 배 | 매출이 50배 |
"하나만 더." 선배가 회식을 하나 더 그렸다. "B 회식이야. 두 명이 갔고 영수증 합계는 50만 원. 정산표에서는 어느 회식이 1등이야?"
A는 308,000 × 4 = 1,232,000이고, B는 500,000 × 2 = 1,000,000이다. "A가 1등이에요. …돈은 B가 더 썼는데."
"곱하는 수가 회식마다 다르면 순위가 뒤집혀. Q3의 순위가 맞았던 건 평가 수가 우연히 전부 50이라서야. 운이 좋았던 거지."
그 운을 재 보고 싶었다. 평가 날짜를 보니 12월에 달린 평가만 세면 상품마다 3건, 4건, 5건으로 갈렸다. 화면 SQL과 같은 모양으로 '12월에 평가가 달린 상품'만 남겨 돌렸다.
| 조인 (화면 SQL의 모양) | EXISTS (평가가 있는지만) | |
|---|---|---|
| 1위 | ITM00041 2,197,346,000 (×5) | ITM00071 444,332,400 |
| 2위 | ITM00036 2,147,149,000 (×5) | ITM00081 443,754,800 |
| 3위 | ITM00071 1,777,329,600 (×4) | ITM00056 441,123,900 |
ITM00041은 원장 6위, ITM00036은 원장 18위다. 둘 다 원장 TOP 5에 없는 상품이다. 그 둘이 1위와 2위로 올라갔고, 12월 평가가 3건뿐인 원장 2위 ITM00081은 TOP 5에서 아예 사라졌다. 틀린 답은 '조금 큰 값'이 아니라 엉뚱한 상품을 1위에 올리는 값이었다.
📐 M:1:M이 왜 행을 불리는가 →
2_파고들기.md §1
오전 10시 50분 — 그때의 근거
"강도현 씨는 왜 평가를 붙였을까요? 매출이랑 상관없는 테이블인데."
"찾아봐. 이유 없이 붙이는 사람 아니야." 선배가 말했다.
형상관리 이력에서 Q3를 처음 넣은 커밋과 T_ITM_EVL을 마지막으로 건드린 커밋을 찾았다.
2019-05-14 hd.kang
상품별 매출 TOP5 : 평가가 등록된 상품만 노출
(마케팅 요청 - 판매 개시 전 테스트 상품 제외)
2020-02-03 hd.kang
앱 리뷰 오픈. T_ITM_EVL 다건 허용
PK (ITM_ID) -> (ITM_ID, EVL_LST_NO)첫 커밋에는 요구가 적혀 있었다. 평가가 등록된 상품만. 그때 T_ITM_EVL의 PK는 ITM_ID 하나였다. 상품마다 평가가 한 건뿐이었다.
"그때는 조인이 '평가가 있는 상품만 남기는' 필터였네요. 한 건이니까 곱할 것도 없고요." "그럼 그때를 재 봐."
평가 테이블의 복사본을 만들었다. 상품마다 가장 먼저 등록된 평가를 한 건씩만 남긴 100행짜리다. 원본에는 손대지 않았다. 그 복사본으로 화면 SQL을 그대로 돌리고, 원장과 양쪽으로 MINUS를 걸었다.
THEN_MINUS_LEDGER LEDGER_MINUS_THEN THEN_ROWS THEN_SUM LEDGER_SUM
----------------- ----------------- ---------- --------------- ----------------
0 0 100 30,272,557,700 30,272,557,7000과 0. 그 시절의 Q3는 원장과 한 푼도 다르지 않았다.
"그래. 02화의 TO_CHAR처럼 그때는 맞는 코드였어. 틀린 건 다음 커밋이야." 선배가 두 번째 커밋을 짚었다. "한 건이던 게 여러 건이 됐지. 이 커밋에서 빠진 줄이 뭘까?"
06화의 회의록이 떠올랐다. 틀린 줄은 하나도 없었는데, 빠진 줄이 있었다.
"…'이 테이블을 조인하고 있는 SQL 목록'이요."
"그거야. 강도현 씨가 게을렀다기보다는 그걸 적을 칸이 있는 양식이 없었던 거야." 선배가 커피를 마셨다. "상품당 한 줄이던 게 여러 줄로 바뀌면, 그 테이블을 다리로 쓰던 조인은 전부 곱하기가 돼. 그날부터 이 화면은 평가 수만큼 곱한 금액을 찍었어. 지금은 그 수가 50이고."
나는 수첩에 적었다. 카디널리티가 바뀌는 변경 = 그 테이블을 조인하는 SQL 전부가 회귀 대상.
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. 839블록으로 빠른데 금액은 50배인 화면과 그 이유(M:1:M), 그리고 그때는 맞았다는 것까지 봤다. 이어서 읽으면 드디어 고친다. 그리고 고치면서 두 번 더 틀린다.
오전 11시 30분 — 두 번째 답
고치는 건 쉬워 보였다. 같은 줄이 50번 적혔으면 한 번만 세면 된다.
"중복이니까 DISTINCT로 지우면 되겠네요."
sql
SELECT d.ITM_ID,
SUM(DISTINCT d.ORD_QTY * d.UNT_PRC) AS amt,
COUNT(DISTINCT d.ORD_SEQ || '-' || d.ORD_DET_NO) AS cnt
FROM T_ORD_DET d, T_ITM_EVL e
WHERE e.ITM_ID = d.ITM_ID
GROUP BY d.ITM_ID ORDER BY amt DESC FETCH FIRST 5 ROWS ONLY;ITM_ID AMT CNT
---------- ------------- ----------
ITM00006 286,527,900 2000
ITM00056 283,011,100 2000
ITM00051 280,520,500 2000
ITM00021 279,958,100 2000
ITM00071 279,878,000 2000CNT가 2,000으로 원장과 같았다. "건수 맞았어요!" "금액도 원장이랑 대 봐."
1위가 ITM00006, 286,527,900이었다. 원장으로는 12위인 상품이다. 원장 1위 ITM00071은 5위로 내려갔고, 금액은 279,878,000으로 원장의 0.63배였다. 원장 TOP 5에 없던 ITM00006과 ITM00021(원장 10위)이 들어와 있었다.
"회식비 정산표 다시 봐." 선배가 영수증을 가리켰다. "2차 호프 62,000, 3차 노래방 62,000. '같은 금액은 한 번만' 세면?"
"…한 장이 사라지네요." "SUM(DISTINCT 금액)은 같은 값을 하나로 쳐. 중복된 줄을 지우는 게 아니야."
세어 봤다. ITM00071의 주문상세 2,000줄 가운데 금액(수량 × 단가)이 서로 다른 건 1,276개뿐이었다. 나머지 724줄은 다른 줄과 금액이 같아서 사라졌다.
"CNT가 맞아서 고친 줄 알았어요."
"건수는 주문 키로 셌으니까 맞았지. 금액은 값으로 셌으니까 틀렸고. 50배를 0.6배로 바꿨어. 게다가 이번엔 순위까지 틀렸어. 아까보다 더 나빠."
오전 11시 50분 — 세 번째 답
"그럼 순서를 바꿀게요. 주문상세를 상품별로 먼저 더해서 100줄로 만들고, 그다음에 평가를 붙이면 곱할 게 없죠."
sql
SELECT d.ITM_ID, d.amt, d.cnt
FROM (SELECT ITM_ID, SUM(ORD_QTY * UNT_PRC) AS amt, COUNT(*) AS cnt
FROM T_ORD_DET GROUP BY ITM_ID) d,
T_ITM_EVL e
WHERE e.ITM_ID = d.ITM_ID
ORDER BY d.amt DESC FETCH FIRST 5 ROWS ONLY;ITM_ID AMT CNT
---------- ------------- ----------
ITM00071 444,332,400 2000
ITM00071 444,332,400 2000
ITM00071 444,332,400 2000
ITM00071 444,332,400 2000
ITM00071 444,332,400 2000금액은 맞았다. 그런데 TOP 5가 전부 ITM00071이었다. 100줄로 줄인 주문상세에 평가 50줄을 붙이면 상품 하나가 다시 50줄이 된다. 더하지 않았을 뿐, 줄 수는 여전히 50배로 불어나 있었다.
"…더하는 순서가 문제가 아니었네요. 평가 쪽이 '많은 쪽'이라는 게 문제예요. 참석자 명단을 그대로 둔 채로는 영수증을 먼저 합치든 나중에 합치든 명단 길이만큼 불어나요."
"그래서?"
"참석자 쪽을 한 줄로 만들어야 해요. '이 회식에 참석자가 있었나'만 묻든지, 참석자를 '4명'이라는 한 줄로 요약해서 붙이든지요." "전부 해 봐."
오후 1시 30분 — 축 두 개
점심을 먹고 와서 고치는 방법을 여섯 가지로 정리하고, 하나씩 원장과 대조했다. TOP 5가 아니라 상품 100개 전체로 건수와 합계를 맞춰 보고, 양쪽으로 MINUS를 걸었다.
| 고친 방법 | 정답 | Buffers |
|---|---|---|
| (원본) 평가를 그대로 조인 | ✗ 50배 | 839 |
SUM(DISTINCT …) | ✗ 작다 · 순위가 바뀐다 | 839 |
| 주문상세만 먼저 묶고 평가 원본에 조인 | ✗ 같은 줄이 50번 | 839 |
EXISTS: 평가가 있는지만 묻는다 | ○ | 839 |
IN 서브쿼리 | ○ | 839 |
| 평가를 상품별로 묶어 한 줄로 만들고 붙인다 | ○ | 839 |
| 평가 조인을 지운다 | ○ (지금은) | 807 |
"선배님, Buffers로는 하나도 안 갈려요. 틀린 것도 맞는 것도 다 839예요."
"T0에 뭐라고 적었어?" "'빨라졌다'는 말 금지. Buffers로 말한다." "그건 성능을 말할 때 규칙이야. Buffers는 일을 얼마나 했는지 재는 자지, 답이 맞는지는 안 재. 맞는지는 다른 자로 재야 돼." 선배가 맨 아래 줄을 가리켰다. "807. 제일 싸네. 이걸로 할 거야?"
"…지금은 답도 맞잖아요. 상품 100개에 전부 평가가 있으니까요."
"강도현 씨 커밋 다시 읽어 봐. 요구가 뭐였어? 평가가 등록된 상품만. 평가 없는 상품이 들어오는 날을 만들어 봐."
평가 테이블 복사본에서 ITM00071의 평가 50건을 뺐다. 막 들어온 신상품이라 아직 평가가 없다고 치는 것이다.
| 1위 | 2위 | 3위 | |
|---|---|---|---|
| 평가 조인을 지운 안 | ITM00071 444,332,400 | ITM00081 | ITM00056 |
EXISTS | ITM00081 443,754,800 | ITM00056 | ITM00051 |
조인을 지운 안은 평가 테이블을 아예 보지 않으니 ITM00071을 그대로 1위에 올렸다. 양쪽 MINUS는 1행과 0행이었다.
"02화의 BETWEEN이랑 똑같네요. 지금 데이터에서는 통과하는데, 그 조건이 실제로 뭔가를 거르는 날 터져요."
"지금 데이터에서 같다고 맞는 건 아니야. 807은 요구를 하나 지워서 얻은 숫자야. 32블록 아끼자고 마케팅팀이랑 한 약속을 지우면 안 되지."
나는 EXISTS를 골랐다. 요구는 그대로 두고 곱하기만 없애는 방법이다. 원본과 다른 곳은 FROM과 WHERE 두 줄뿐이다.
sql
-- Q3_rev01
SELECT d.ITM_ID, SUM(d.ORD_QTY * d.UNT_PRC) AS amt, COUNT(*) AS cnt
FROM T_ORD_DET d
WHERE EXISTS (SELECT 1 FROM T_ITM_EVL e WHERE e.ITM_ID = d.ITM_ID)
GROUP BY d.ITM_ID
ORDER BY amt DESC
FETCH FIRST 5 ROWS ONLY;839 → 839. 성능 리비전이 아니었다. 처음으로 답을 고친 리비전이었다.
📐 여섯 가지 방법의 실행계획과 정답 대조 전체 →
2_파고들기.md §2
오후 2시 10분 — 변환이 먼저, 비용이 나중
리포트를 쓰려고 rev00의 실행계획을 다시 열었다. 아침에는 Buffers만 보고 넘어갔던 표였다.
xplan
| Id | Operation | Name | Starts | A-Rows | Buffers |
|* 4 | HASH JOIN | | 1 | 100 | 839 |
| 5 | VIEW | VW_GBF_8 | 1 | 100 | 32 |
| 6 | HASH GROUP BY | | 1 | 100 | 32 |
| 7 | INDEX FAST FULL SCAN| PK_T_ITM_EVL | 1 | 5000 | 32 |
| 8 | VIEW | VW_GBC_7 | 1 | 100 | 807 |
| 9 | HASH GROUP BY | | 1 | 100 | 807 |
| 10 | TABLE ACCESS FULL | T_ORD_DET | 1 | 200K | 807 |"이상해요. 조인이 1,000만 줄을 만들어야 하잖아요. 근데 A-Rows에 1,000만이 없어요."
"Name 열을 봐. 너 VW_GBF_8이라는 뷰 만든 적 있어?" 없었다. VW_GBC_7도 마찬가지였다. 내 SQL에는 인라인 뷰가 하나도 없었다.
"그건 오라클이 만든 뷰야. 실행계획 아래에 Outline을 찍어 봐."
PLACE_GROUP_BY(@"SEL$1" ( "E"@"SEL$1" ) ( "D"@"SEL$1" ) 7)"Group-by Placement야. 집계를 조인 앞으로 옮기는 변환이지. 주문상세를 상품별로 먼저 더해서 100줄(VW_GBC_7), 평가를 상품별로 먼저 세서 100줄(VW_GBF_8)을 만들고, 둘을 붙인 다음에 곱해."
"…회식 정산표를 열두 줄 쓰지 않고 '영수증 합계 × 참석 인원 4명'으로 계산한 거네요."
"그래. 그래서 빨랐던 거야. 끄면 어떻게 되는지 봐."
NO_PLACE_GROUP_BY 힌트를 주자 해시 조인의 A-Rows가 10M이 됐다. 1,000만 줄을 정말로 만든 것이다. 시간은 0.01초에서 0.33초로 늘었고, Buffers는 거의 그대로였다. 그리고 결과는 ITM00071, 22,216,620,000. 똑같이 50배였다.
"오라클이 제 SQL을 고쳐 썼는데… 답은 안 고쳤어요."
"못 고치는 거야." 선배가 말했다. "옵티마이저는 SQL을 받으면 먼저 고쳐 쓰고, 그다음에 비용을 매겨. 서브쿼리를 펼치고, 뷰를 합치고, 집계 자리를 옮겨. 근데 어떤 변환이든 지켜야 하는 규칙이 하나 있어. 답을 바꾸면 안 된다. 네가 '평가 한 건마다 매출을 한 번씩 더하라'고 썼으면, 오라클은 그걸 더 빠르게 할 수는 있어도 안 할 수는 없어."
"틀린 뜻도 지키는 거네요." "틀린 뜻도 정확하게 지켜. 그래서 빠르면서 틀린 거야."
rev01의 실행계획도 다시 봤다. EXISTS로 쓴 서브쿼리는 실행계획에 보이지 않았다. 대신 HASH JOIN RIGHT SEMI 한 줄이 있었고, Outline에는 UNNEST(@"SEL$2")가 있었다. 서브쿼리를 펼쳐서 '있는지만 보는 조인(세미 조인)'으로 바꾼 것이다. IN으로 쓴 문장도 계획 해시가 똑같이 2231864090이었다.
그런데 표를 정리하다가 이상한 걸 발견했다. 아까 그 틀린 SUM(DISTINCT)의 계획 해시도 2231864090이었다.
"선배님, 틀린 거랑 맞는 거랑 계획이 같아요."
"Outline 끝줄 봐." 거기에 PARTIAL_JOIN(@"SEL$1" "E"@"SEL$1")이 있었다. "DISTINCT로만 세면 평가가 50건이든 1건이든 결과가 같지? 오라클도 그걸 알아. 그래서 조인을 '하나만 찾으면 멈추는' 세미 조인으로 바꿨어. 네가 쓴 DISTINCT의 뜻을 정확히 지키면서. 계획 해시는 연산의 모양만 봐. SUM 안에 DISTINCT가 있는지는 안 봐. 같은 계획에서 다른 답이 나오는 거야."
"그럼 '계획이 같으니까 답도 같다'는…" "리포트에 그렇게 쓰면 반려야."
궁금한 게 하나 더 있었다. "오라클이 평가 조인을 아예 지울 수는 없었어요? 평가 컬럼은 하나도 안 쓰잖아요."
"지울 수 있는 조인은 행 수를 안 바꾸는 조인뿐이야. 오라클은 그걸 PK랑 FK로 증명해. '상대 쪽에 반드시 있고(FK), 딱 하나만 있다(PK)'는 거지. 이 조인은 행 수를 50배로 바꾸니까 못 지워." 선배가 말했다. "오라클이 조인을 지울지 따질 때 묻는 거랑 오늘 우리가 따진 거랑 똑같은 질문이야. 이 조인이 행을 불리느냐."
📐 Unnesting · View Merging · JPPD · Group-by Placement · 조인 제거 · OR-Expansion: 실행계획에서 흔적 읽는 법 →
2_파고들기.md §3·§4📐 어떤 변환은 비용을 따지고 어떤 변환은 안 따지나(10053 트레이스) →§6
오후 3시 00분 — 맞다는 걸 어떻게 증명했어
"오늘 '맞다'를 몇 가지로 증명했어?" "건수, 합계, 양쪽 MINUS, 세 가지요." "하나만 했으면?"
"MINUS만으로도 되지 않아요? 양쪽 다 0이면 똑같은 거잖아요."
"아까 세 번째 답 있잖아. 'TOP 5가 전부 ITM00071'이었던 거. 그걸 원장이랑 MINUS 해 봐."
H_MINUS_G G_MINUS_H H_CNT G_CNT
---------- ---------- ---------- ----------
0 0 5000 1000과 0. 그런데 건수는 5,000 대 100이었다.
"…MINUS는 같은 줄이 몇 번 나오는지 안 봐요. 집합이니까."
"그래서 셋 다 하는 거야. 하나는 놓칠 수 있어도 셋이 한꺼번에 놓치기는 어려워. 그리고 오늘 걸렸던 것들 다시 꼽아 봐."
평가 수가 전부 50이라서 순위가 같았다. 모든 상품에 평가가 있어서 조인을 지워도 답이 같았다. 틀린 안과 맞는 안의 계획 해시가 같았다. 전부 '이 데이터에서는 같았다'였다.
"이거 02화랑 03화도 그래요. T_ORD_BIG은 전부 자정이라서 BETWEEN이 통과했고, SLE_HMS는…"
"그럼 그걸 한 장으로 정리해. 앞으로 모든 리포트에 붙일 거야. T1의 8번 칸이 '정합성 회귀 검증'이잖아. 지금까지는 거기에 '결과 같음' 한 줄만 썼지? 앞으로는 이 표를 붙여."
그렇게 「정합성 회귀 체크리스트」가 생겼다. 항목은 여덟 개다. 대조 기준을 먼저 정하고, 건수와 합계와 양방향 MINUS를 셋 다 보고, 잘라 내기 전의 전체 결과로 대조한다. 조인마다 '1쪽'이 정말 하나인지 확인하고, 경계 데이터는 복사본에 만든다. 계획 해시가 같아도 결과를 대조하고, 변환을 근거로 쓰지 않으며, 카디널리티를 바꾸는 변경이 있었는지 확인한다.
경계 데이터 칸을 채우는데 선배가 하나를 더 보여 줬다. 구 시스템의 EMP 테이블 14명 가운데 "누구의 관리자도 아닌 사원"을 찾는 두 문장이었다. NOT IN (SELECT MGR FROM EMP)는 0명, NOT EXISTS는 8명이었다.
"KING은 관리자가 없어. MGR이 NULL이야. NULL이 하나라도 끼면 NOT IN은 아무것도 안 돌려줘. 'NULL과 다르냐'는 질문의 답이 '모름'이니까." 선배가 실행계획의 HASH JOIN ANTI NA를 가리켰다. "NA는 Null-Aware라는 뜻이야. 오라클은 NOT IN을 NOT EXISTS로 함부로 안 바꿔. 뜻이 다르니까. 대신 NULL을 고려하는 조인으로 NOT IN의 뜻을, 그러니까 0명이라는 답을 지켜. NULL이 없는 날엔 둘이 같으니까 이것도 '이 데이터에서는 같다'가 될 수 있어. 그래서 경계 목록에 NULL이 들어가는 거야."
📐 NOT IN과 NULL →
2_파고들기.md §7· 체크리스트 양식 →40_보고서와_표준/템플릿/정합성_회귀_체크리스트.md
오후 3시 40분 — 네 번째 답
체크리스트의 D 항목을 쓰다가 07화에 적어 둔 메모가 눈에 걸렸다.
"진짜 질문은 따로 있어. 오라클은 왜 이 스칼라를 알아서 조인으로 안 바꿨나."
Q2의 MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)), Starts는 8인데 CPU의 절반을 쓰던 바로 그 스칼라 서브쿼리다. 오늘 배운 대로라면 답이 보이는 것 같았다.
"이건 바꿔도 되는 거 아니에요? M_RGN은 RGN_ID가 PK예요. 지역 하나에 한 줄이 보장되니까 행이 불어날 일이 없어요. 오라클이 바꿨어야 맞는 것 같은데요."
"재 봐."
Q2 rev01의 Outline에는 UNNEST가 없었다. 서브쿼리 안에 /*+ UNNEST */를 넣어 봤다. 계획 해시는 2292855265, 그대로였다. 02화 때처럼 따를 수 없는 힌트는 조용히 무시됐다.
"왜 못 따르는지 오라클한테 직접 물어봐." 선배가 말했다. "10053이라는 트레이스가 있어. 옵티마이저가 SQL을 고쳐 쓰면서 '이건 해 볼까, 이건 안 돼' 하고 혼잣말한 걸 파일로 남겨 줘."
트레이스를 켜고 Q2를 한 번 돌렸다. 파일은 컨테이너 안에 생겼다. docker exec로 꺼내서 SU:(Subquery Unnesting)로 시작하는 줄을 찾았다.
SU: Checking validity of scalar subquery unnesting for query block SEL$2 (#2)
SU: bypassed: Scalar subquery may return more than one row."한 행보다 많이 나올 수 있다. …PK가 있는데요?" "그 문장을 믿고 다음 걸 해 봐. '반드시 한 행'이라는 걸 문법으로 박아 넣으면 어떻게 될까."
스칼라 서브쿼리 안을 MAX()로 감쌌다. 집계 함수는 행이 몇 개든 한 줄을 돌려준다. 먼저 07화의 고객명 스칼라에 해 봤다. Starts 752,995, Buffers 779,832였던 바로 그 스칼라다.
SELECT ... (SELECT MAX(c.CUS_NM) FROM M_CUS c WHERE c.CUS_ID = o.CUS_ID) ...
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 1000K | 5856 |779,832 → 5,856. 서브쿼리가 펼쳐졌다. Outline에는 UNNEST, ELIM_GROUPBY, MERGE가 찍혔고, 트레이스에는 이런 줄이 남았다. Unnesting scalar subquery. Transforming subquery block into inline view. 오라클이 VW_SSQ_1이라는 뷰를 만들었다가 바깥 쿼리와 합친 것이다.
"됐어요! 그럼 Q2도…"
Q2에 똑같이 해 봤다. 실행계획은 그대로였다. M_RGN 쪽 Starts도 8 그대로였다.
SU: bypassed: Outer qurery has illegal group-by."바깥 쿼리에 허용되지 않는 GROUP BY가 있다." 철자도 틀려 있었다. qurery. 오라클 원문이 그랬다.
"자물쇠가 두 개였네." 선배가 말했다. "하나는 '한 행이라는 증명'이고, 다른 하나는 '그 스칼라가 GROUP BY 쿼리의 MAX 안에 들어 있다'는 거야. 첫 번째는 네가 MAX로 풀었고, 두 번째는 이 버전의 옵티마이저가 풀지 않는 모양이야."
트레이스에는 한 줄이 더 있었다. GBP: Bypassed: Unsupported argument to aggregate function. Q3에서 오라클이 해 줬던 Group-by Placement가 Q2에서는 집계 함수 안의 스칼라 서브쿼리 때문에 막혀 있었다.
내친김에 Q3 rev00의 트레이스도 떴다. 끝부분 Final query after transformations 아래에 오라클이 고쳐 쓴 SQL이 통째로 적혀 있었는데, 그 안에 이런 식이 있었다.
"VW_GBC_7"."ITEM_3" * "VW_GBF_8"."ITEM_3"주문상세의 상품별 합계에 평가의 상품별 건수를 곱한 식이다. 영수증 합계 × 참석 인원. 50배가 SQL로 적혀 있었다.
"오전에는 오라클이 너무 많이 고쳐 써서 놀랐지. 지금은 안 고쳐 써서 놀라고 있고. 근데 이유는 하나야. 뜻을 지킬 수 있다고 증명하지 못하면 안 바꿔."
"PK가 있는데도 증명이 안 된다는 건 좀 억울한데요." "그럼 오라클 입장에서 생각해 봐. 정말로 두 행이 나왔다면 무슨 일이 생겼을까?"
오후 4시 30분 — 두 배
복사본을 만들었다. 지역 코드 M_RGN을 PK 없이 복사하고, 같은 8행을 한 번 더 넣었다. 코드 테이블을 다시 적재하다가 같은 데이터가 두 번 들어가는 사고로, 운영에서 충분히 있을 법한 일이다. 그 복사본으로 두 문장을 돌렸다. 스칼라를 그대로 둔 rev01과, 스칼라를 외부조인으로 바꾼 문장이다. 외부조인부터 돌렸다.
K CUS_GD CNT AMT RGN
------------ ------ ---------- ------------------ --------
B_OUTER_DUP A 20000 5,128,644,200 SEOUL
B_OUTER_DUP B 180000 45,897,315,600 ULSAN
B_OUTER_DUP C 1800000 459,771,287,600 ULSANA 등급의 원래 값은 10,000건 / 2,564,322,100이다. 금액이 정확히 두 배로 찍혔다. 건수도 두 배였다. 경고도 에러도 없었다.
다음은 스칼라.
A_SCALAR_DUP -> ORA-01427: single-row subquery returns more than one row멈췄다. 한 줄도 내놓지 않았다.
"스칼라는 틀리면 멈추고… 조인은 틀리면 조용히 세요."
"그게 may return more than one row의 뜻이야." 선배가 말했다. "오라클이 스칼라를 조인으로 바꿨는데 두 행이 나오면, 원래 SQL이라면 에러를 냈어야 할 자리에서 두 배짜리 답을 내게 돼. 뜻이 바뀌는 거지. 그래서 증명이 안 되면 안 바꿔."
"Q3가 그랬어요. 평가 조인은 멈추지 않고 50배를 셌어요. 몇 달 동안."
"그래. 아침에 본 50배랑 지금 본 두 배는 같은 일이야. '1쪽'이 하나가 아닐 때 조인은 소리를 안 내."
반대쪽 경계도 쟀다. 고객 복사본에서 마지막 고객 20명의 지역을 지역 코드에 없는 RGN09로 바꿨다. 신설 지역을 코드 테이블에 늦게 등록한 경우다.
| 등급 | 스칼라 (rev01) | 외부조인 | 내부조인 |
|---|---|---|---|
| A | 10,000 / 2,564,322,100 | 10,000 / 2,564,322,100 | 9,500 / 2,439,050,900 |
| B | 90,000 / 22,948,657,800 | 90,000 / 22,948,657,800 | 89,500 / 22,823,283,100 |
| C | 900,000 / 229,885,643,800 | 900,000 / 229,885,643,800 | 891,000 / 227,598,402,100 |
스칼라와 외부조인은 양쪽 MINUS가 0과 0이었고, 내부조인은 3과 3이었다. 그 20명의 주문 10,000건이 내부조인에서만 사라졌다.
"그러니까 손으로 바꾼다면 외부조인이어야 하고, 그것도 M_RGN의 PK가 살아 있는 동안만 같은 거네요." "그래. 그럼 바꿀 거야?"
07화의 숫자가 떠올랐다. 외부조인으로 바꾸면 Buffers는 5,869 → 5,862로 7블록 줄고, CPU는 절반이 된다.
"…안 바꿀래요. T0로는 7블록인데, 바꾸면 '틀리면 멈추는' 성질이 '틀리면 조용히 센다'로 바뀌어요. PK가 지켜 주는 동안은 같지만, 그 PK가 없어지는 날엔 아무도 모를 거예요. 오늘 Q3처럼요."
선배가 웃었다. "리비전을 안 만드는 이유를 처음으로 성능 말고 다른 걸로 댔네."
Q2는 rev01 그대로 두기로 했다. 07화에서는 잴 게 없어서 안 고쳤고, 오늘은 바꾸면 잃는 게 있어서 안 고친다.
📐 스칼라 서브쿼리 Unnesting의 두 자물쇠와 경계 실험 →
2_파고들기.md §8
⛳ 오늘은 여기까지(중단 지점 2)
Q3_rev01이 나왔고 금액이 22,216,620,000 → 444,332,400으로 원장과 맞았다. 「정합성 회귀 체크리스트」도 생겼다. 여기까지만 해도 오늘 할 일은 다 했다. 이어지는 저녁에는 06화가 남긴 질문, 100만 행을 읽어 3행을 만드는 게 맞는 요구인가에 반쯤 답한다.
오후 5시 20분 — 100만 행을 읽어 3행
06화 리비전 주석 맨 끝에 내가 적어 둔 줄이 있었다. 이 쿼리는 100만 행을 전부 읽어 3행을 만든다. 그 자체가 맞는 요구인지는 아직 묻지 않았다 → 08화.
"반만 답해." 선배가 말했다. "나머지 반은 네가 혼자 답할 수 있는 게 아니야. 먼저 요구를 그대로 둔 채로 어디까지 갈 수 있는지 재 봐."
요구는 '전체 기간, 지금 이 순간의 등급별 매출'이다. 그러려면 주문 100만 건의 금액을 한 번씩은 읽어야 한다. 줄일 수 있는 건 읽는 블록과 행마다 하는 일, 두 가지뿐이었다.
블록부터 봤다. 주문 테이블을 한 번 훑는 값 5,826이 바닥이다. 더 작은 걸 훑게 하려고 고객번호와 금액만 담은 인덱스 (CUS_ID, ORD_AMT)를 잠깐 만들어 봤다. 리프 블록은 3,624개였다. Q2는 INDEX FAST FULL SCAN으로 갈아탔고 5,869 → 3,689로 줄었다. 재고 나서 바로 지웠다.
"1.6배요. 근데 T_ORD_BIG의 인덱스가 세 개가 돼요." "14화 청구서에 한 줄을 더 붙이는 거지. 1.6배 얻자고 그 값을 낼 거야?" "아니요."
다음은 행마다 하는 일이다. 오늘 오라클이 Q3에서 해 준 일, 곧 집계를 조인 앞으로 옮기는 일을 Q2에 손으로 해 봤다. 주문을 고객별로 먼저 묶어 2,000줄로 만들고, 고객과 붙인 다음 등급별로 다시 더한다. 스칼라 서브쿼리는 그대로 두었다. 예열한 뒤 10번씩 돌렸다(CPU는 1/100초 단위, 10회 합계).
| 문장 | Buffers / 회 | CPU (10회) |
|---|---|---|
| rev01 (스칼라 그대로) | 5,868 | 114~118 |
| 스칼라 → 외부조인 | 5,862 | 56~61 |
| 주문을 고객별로 먼저 묶고 붙인다 | 5,868 | 30~34 |
| (바닥) 주문 테이블을 한 번 읽어 더하기만 | 5,826 | 21~24 |
블록은 그대로인데 CPU가 3분의 1 아래로 줄었다. 해시 조인에서 나오는 줄이 100만에서 2,000으로 줄었고, 스칼라 서브쿼리가 붙는 줄도 그만큼 줄었다. rev01과의 양쪽 MINUS는 0과 0이었다. 지역 코드를 두 번 넣은 복사본에서는 이 문장도 ORA-01427로 멈췄다. 스칼라의 성질이 그대로 살아 있었다.
"이건 rev02로 올릴 만하지 않아요? 답도 같고, 멈추는 성질도 그대로고요."
"T0로 말해 봐." "…Buffers 5,869 → 5,868이요." "그럼 리비전은 아니야. 그리고 이 SQL이 지금 서버를 태우고 있다는 증거가 있어? 07화에서 rev01을 적용하고 나서 운영팀 CPU 그래프는 내려왔어. 고칠 이유가 숫자로 생기면 그때 올려. 리포트에 '보류안'으로 적고, 언제 다시 볼지 조건을 걸어 둬."
조건은 이렇게 걸었다. 시스템 전체를 재는 날(16화)에 이 SQL이 CPU 상위에 오르면 rev02로 재검토한다.
"나머지 반은요?"
"100만 건을 안 읽으려면 요구가 바뀌어야 해. '지금 이 순간'이 아니라 '어젯밤 마감 기준'이어도 되는지, '전체 기간'이 아니라 '최근 몇 달'이어도 되는지. 그러면 미리 더해 둔 숫자를 읽거나 범위를 좁힐 수 있지." 선배가 일어섰다. "그건 네가 혼자 정하면 안 돼. 튜닝이 아니라 약속을 바꾸는 일이야. 어떻게 묻고 어떻게 적는지는 따로 배울 거야. 오늘은 '요구를 그대로 두면 블록은 바닥이고, 줄일 수 있는 건 CPU뿐'이라는 걸 잰 걸로 충분해."
📐 요구를 바꾸지 않고 할 수 있는 것 / 바꿔야 하는 것 →
2_파고들기.md §9
오후 6시 10분
조민우 씨에게 보낼 회신을 썼다. 처음에는 이렇게 썼다. "화면 SQL의 조인 오류를 수정했습니다. 이제 원장과 일치합니다."
선배가 두 군데를 짚었다. "'일치합니다'라고만 쓰면 뭘로 확인했는지가 없잖아. 그리고 재무팀이 제일 먼저 물을 걸 안 썼어. 언제부터 틀렸냐. 고치는 건 우리 일이지만 지난 숫자를 바로잡는 건 재무팀 일이야. 그 판단을 할 수 있게 근거를 줘야지."
다시 썼다.
"상품별 매출 TOP 5 화면의 금액이 실제 매출의 정확히 50배로 표시되고 있었습니다. 화면 SQL이 매출(주문상세)에 상품평가를 붙이면서, 평가 한 건마다 매출을 한 번씩 다시 더했기 때문입니다. 지금은 모든 상품에 평가가 50건씩 있어 순위는 맞고 금액만 50배였습니다.수정한 SQL은 상품 100개 전체에서 원장과 건수와 합계가 같고, 양쪽으로 대조해도 다른 줄이 없습니다. 1위 ITM00071은 444,332,400원입니다.이 오류는 상품평가가 상품당 여러 건으로 바뀐 2020년 2월 변경 이후부터 생긴 것으로 보입니다. 그 뒤로 이 화면 캡처를 쓴 자료를 어디까지 정정할지는 재무팀에서 확인 부탁드립니다."
"됐어." 선배가 한마디 덧붙였다. "체크리스트는 지난 리포트에도 붙여. 1호부터 5호까지. 적어도 건수, 합계, MINUS 칸은 채워. 다음 주부터는 그 표 없는 리포트는 안 받아."
1호를 열었다. 02화의 Q1이다. rev00과 rev02는 27,390건, 6,982,065,200원으로 같았고 양쪽 MINUS는 0과 0이었다. 4호는 06화의 Q2다. 3행, 255,398,623,700원, 역시 0과 0이었다.
통과였다. 그런데 '통과'라는 글자가 아침과는 다르게 보였다. 이 데이터에서 같다는 뜻일 뿐이었다. 경계 칸은 아직 비어 있었다.
오늘 남은 것
| 무엇 | 값 |
|---|---|
| Q3의 정체 | Buffers 839로 빠르고, 금액은 정확히 50배. 평가 50건 × 상품 100개, 조인 행 200,000 → 10,000,000 |
Q3_rev01 | 평가 조인을 EXISTS로 바꿨다. Buffers 839 → 839. ITM00071 22,216,620,000 → 444,332,400 |
| 변환이 먼저, 비용이 나중 | rev00은 오라클이 이미 PLACE_GROUP_BY로 고쳐 쓰고 있었다. 끄면 1,000만 행. 답은 둘 다 50배 |
| Q2 3차 | 스칼라 Unnesting의 자물쇠 둘(may return more than one row · illegal group-by). 코드가 중복되면 조인은 두 배, 스칼라는 ORA-01427. rev01 유지 |
| 100만 행 → 3행 | 요구를 그대로 두면 블록은 바닥(5,826)이다. CPU만 줄일 수 있다(114~118 → 30~34, 보류안) |
| T1 리포트 6호 | 이 교재의 첫 정합성 리포트 |
| 「정합성 회귀 체크리스트」 ★ | 새 표준 문서. 이후 모든 리포트에 붙는다 |
오늘 배운 한 문장
옵티마이저는 SQL을 빠르게 고쳐 쓸 수는 있어도, 뜻을 고쳐 쓰지는 않는다. 변환은 답을 지킨다. 틀린 답도 그대로 지킨다. 그래서 빠르다는 것이 맞다는 증거가 되지는 않는다.
그리고 하나 더.
조인이 안전한 것은 '1쪽'이 정말 하나일 때뿐이다. 하나가 아니게 되는 순간 스칼라 서브쿼리는 에러를 내며 멈추고, 조인은 조용히 곱한다.
다음으로 넘기는 것
- 요구를 바꾸는 이야기를 어떻게 꺼내고 어떻게 적나 (100만 행 → 3행의 나머지 반) → 09화
E-Rows와A-Rows가 조인에서 어떻게 계산되나 (조인 카디널리티 추정) → 10화- 바인드 변수로 바뀌는 OR-Expansion의 갈래 → 11화
- 같은 3행을 매번 다시 계산하지 않는 법 (결과 재사용) → 15b화
- Q2 '먼저 묶고 붙이기' 보류안 재검토 (CPU 상위 여부) → 16화
다음 →
3_실습과_기록.md에서 오늘 과정을 직접 재현하고, 체크리스트를 채운 뒤 T1 리포트 6호를 쓴다. 개념이 막히면2_파고들기.md를 펼친다.