테마
03화 · 날짜가 문자로 박혀 있다
3월 18일 월요일, 02화에서 일주일 뒤다. 무대가
ora-dist로 바뀐다. 지난주에는TO_CHAR를 지워서 64배를 얻었다. 오늘은 지울 수 없는 곳으로 간다.
오전 8시 50분
지하철에서 메일을 열었다. 발신자가 우리 회사 사람이 아니었다.
───────────────────────────────────────────────────────────
[협력요청] 레거시 유통 마스터 — 월 마감 조회 지연
회신처: 본사 유통지원팀
───────────────────────────────────────────────────────────
월 매출 조회가 느립니다. 10년 넘은 시스템이고 담당이 없습니다.
설계 문서는 2019년 이후로 갱신이 안 됐습니다.
※ 추가 건: 작년 영업시간 매출 집계가 회계 숫자와 맞지 않습니다.
2억 9천만 원가량 차이가 납니다. 같이 봐 주시면 좋겠습니다.
───────────────────────────────────────────────────────────아래쪽 문장이 더 무서웠다. 느린 건 기다리면 되지만, 숫자가 안 맞는 건 기다린다고 맞춰지지 않는다.
사무실에 도착하니 선배가 벌써 노트북을 켜 놓고 있었다. 화면에는 ora-dist 컨테이너가 올라와 있었다.
"나도 처음 보는 시스템이야." 선배가 말했다. "2주 전에 네가 한빛유통에 처음 들어갔을 때랑 똑같은 상황이야. 그때 뭐부터 했지?"
"…무엇이 얼마나 있는지부터요."
"그럼 그것부터 하면 되겠네."
오전 9시 30분 — 지도부터
01화에서 배운 순서 그대로 user_tables와 user_indexes를 훑었다.
TABLE_NAME NUM_ROWS BLOCKS
-------------- ---------- ----------
CF11 1096708 8204 ← 판매상품
CF10 500000 4583 ← 판매
CF15 51497 349
... (18개)테이블 이름은 전부 CF에 번호를 붙인 것이었다. 다행히 한글 주석은 달려 있었다. 인덱스는 CF01_PK부터 CF18_PK까지 18개였고, 전부 UNIQUE였다. 보조 인덱스는 0개였다.
"또 0개네요. 한빛유통도 그랬잖아요."
"그래. 그리고 너는 지난주에 그걸 보고 뭐라고 했지?"
"…인덱스를 만들면 된다고요." 웃음이 나왔다. 지난주에 인덱스를 만들고도 달라진 게 없어서 5,826이 5,826 그대로 남아 있던 화면이 아직 눈에 선했다. "오늘은 안 그럴 거예요."
"그럼 소스부터 봐."
오전 10시 10분 — 지울 수가 없다
유통지원팀이 보내 준 월 매출 집계 쿼리는 이랬다.
sql
SELECT COUNT(*), SUM(b.SLE_QTY * b.SLE_UPRC)
FROM CF10 a, CF11 b
WHERE a.SLE_NO = b.SLE_NO
AND TO_DATE(a.SLE_DT,'YYYYMMDD') >= DATE '2023-06-01'
AND TO_DATE(a.SLE_DT,'YYYYMMDD') < DATE '2023-07-01';거의 반사적으로 말이 나왔다. "찾았어요. 컬럼 가공이요. TO_DATE를 지우면 돼요."
"지워 봐."
sql
WHERE a.SLE_DT >= DATE '2023-06-01'쓰고 나서 손이 멈췄다. 왼쪽은 SLE_DT고 오른쪽은 DATE '2023-06-01'이다. 그러면 SLE_DT가 날짜여야 말이 되는데, 날짜라면 애초에 TO_DATE를 왜 씌웠을까?
sql
DESC CF10 Name Null? Type
----------- -------- -------------
SLE_NO NOT NULL VARCHAR2(15)
SLE_DT NOT NULL VARCHAR2(8) ← 날짜
SLE_HMS NOT NULL VARCHAR2(6) ← 시각
SLE_DOW NOT NULL VARCHAR2(1) ← 요일
...
REG_DTT NOT NULL DATE ← 등록일시는 DATE 다"…문자네요."
"응."
"그럼 TO_DATE를 지우면 안 되는 거잖아요. 지우면 문자하고 날짜를 비교하는 게 돼요."
"그래. 지난주랑 뭐가 달라?"
천천히 정리해 봤다. 지난주에는 컬럼은 원래 날짜인데 코드가 문자로 바꿔 놓은 상황이었다. 그래서 바꾸는 부분을 지우면 원래대로 돌아갔다. 지금은 컬럼 자체가 문자다. 지울 게 없다.
"…지울 수가 없어요."
"그게 오늘의 출발점이야." 선배가 의자를 끌어왔다. "지난주는 쉬운 경우였어. 현장에는 이쪽이 더 많아. 그리고 이것도 봐 둬. REG_DTT는 DATE야. 같은 테이블인데 어떤 건 날짜고 어떤 건 문자야. 왜 그랬을 것 같아?"
오전 10시 40분 — 그럼 컬럼을 바꾸면 되잖아요
"컬럼 타입을 바꾸면 되는 거 아니에요? ALTER TABLE CF10 MODIFY SLE_DT DATE 하면…"
선배는 자세를 바꾸지 않은 채 되물었다. "세 가지만 물어볼게. 첫째, SLE_DT를 참조하는 코드가 이 회사에 몇 개야?"
알 리가 없었다. 세어 볼 수조차 없었다. 화면, 배치, 리포트에 외부로 나가는 인터페이스 파일까지 있었다.
"둘째. 저 날짜는 SLE_NO 안에도 들어 있어. 봤어?"
SLE_NO SLE_DT
---------------- --------
20220101-0308 20220101
20220101-0309 20220101판매번호가 날짜 여덟 자리 + 하이픈 + 일련번호였다. 컬럼 타입만 바꾼다고 끝날 문제가 아니었다.
"셋째. 우리는 남의 집에 들어온 외부 인력이야. 2주 만에 처음 보는 테이블의 컬럼 타입을 바꾸자고 하면 저쪽이 우릴 어떻게 보겠어?" 선배가 말했다. "타입 변경이 틀린 답이라는 게 아니야. 그건 1년짜리 과제지 오늘 할 일이 아니야. 오늘 네 일은 '이 구조를 그대로 둔 채 뭘 할 수 있나'야."
그러고는 받아 온 자료를 하나 띄웠다. 2019년에 만든 테이블 정의서였다.
[표준] 일자 컬럼 표기 규약 (2019-04 개정)
- 일자는 VARCHAR2(8) 'YYYYMMDD' 로 통일한다.
- 사유: 외부 연계 10개 중 7개가 고정길이 텍스트 파일이다.
DATE 로 주고받을 때 장비/도구마다 포맷이 달라 월·일이 뒤바뀌는
사고가 2018년에 두 번 있었다. 문자로 고정하면 뒤바뀌지 않는다.
- 단, 시스템 내부 감사 컬럼(REG_DTT, MOD_DTT)은 DATE 를 쓴다.2주 전에 본 문장이 겹쳐 떠올랐다. "날짜 타입 비교에서 시분초 때문에 하루가 누락되는 버그 수정. 문자로 통일. (2019.03 강도현)"
"……같은 사람이에요?"
"다른 회사야. 근데 같은 해에 같은 결론을 냈지." 선배가 말했다. "그때 그 결론은 틀리지 않았어. 월·일이 뒤바뀌어서 두 번 터졌는데 문자로 고정하니까 안 터졌거든. 그 사람들은 실제로 있던 사고를 막은 거야."
"근데 지금은 문제잖아요."
"그래서 이 일이 어려운 거야. 판단에는 유효기간이 있어. 네가 할 일은 그 사람을 욕하는 게 아니라 유효기간이 지났다는 걸 숫자로 보여 주는 것이야."
오전 11시 00분 — 문서고
선배가 일어섰다. "따라와 봐."
지난주에 갔던 안내 데스크 캐비닛을 지나 더 안쪽에 있는 문서고였다. 선반에 종이 상자가 줄지어 꽂혀 있었다.
┌──────────┐┌──────────┐┌──────────┐┌──────────┐
│ 20230531 ││ 20230601 ││ 20230602 ││ 20230603 │ …
└──────────┘└──────────┘└──────────┘└──────────┘"전표 보관함이야. 라벨 글자 순서대로 꽂혀 있어. 자, 6월 1일부터 30일까지 꺼내 와."
쉬웠다. 20230601을 찾아서 20230701이 나오기 전까지 쭉 빼면 됐다. 앞쪽 1~5월 상자는 건드리지도 않았다.
"지난주 출입대장이랑 똑같지. 그럼 질문 하나." 선배가 상자 하나를 집었다. "이 라벨은 날짜야, 글자야?"
매직으로 쓴 숫자 여덟 개였다. 날짜처럼 보이지만 그냥 글자였다. "…글자요."
"그런데 글자를 글자 순서로 꽂았는데 날짜 순서가 됐어. 왜 그렇지?"
라벨을 다시 보니 세 가지가 눈에 들어왔다. 첫째, 전부 여덟 칸이었다. 둘째, 빈칸을 0으로 채웠다. 6월은 6이 아니라 06이었다. 셋째, 큰 단위가 왼쪽에 있었다.
"길이가 같고, 0으로 채웠고, 큰 게 왼쪽에 있으니까, 왼쪽부터 한 글자씩 비교하면 날짜를 비교하는 거랑 똑같아져요."
"그래." 선배가 상자를 제자리에 꽂았다. "문자로 적힌 날짜를 범위로 비교할 수 있는 건 우연이 아니야. 저 세 가지를 지킨 설계 덕분이지."
그러고는 상자를 다시 꺼냈다. "규칙을 하나 바꿔 볼게. 이번엔 라벨을 숫자로 읽어서 비교해. 20230601 이상 20230630 이하인 상자를 가져와."
"…그거 똑같은 거 아니에요?"
"라벨은 글자야. 숫자로 읽으려면 뭘 해야 돼?"
멈칫했다. 1월 상자부터 전부 꺼내서 라벨을 읽고, 머릿속으로 숫자로 바꿔 봐야 했다.
"……전부 꺼내야 하네요."
"지난주 출입대장이랑 같은 얘기야." 선배가 상자를 탁 꽂았다. "모양을 바꾸는 순간 정렬은 쓸모가 없어져. 지난주엔 날짜를 글자로 바꿔서 그랬고, 오늘은 글자를 숫자로 바꿔서 그래. 방향만 반대지 같은 일이야."
💡 라벨 상자: 문자로 적힌 날짜로 "여기부터 여기까지"를 지목할 수 있는 건 길이가 같고, 0으로 채웠고, 큰 단위가 왼쪽에 있기 때문이다. 셋 중 하나만 깨져도 글자 순서는 날짜 순서가 아니다. (01화 = 커피 캐리어 · 02화 = 출입대장 · 03화 = 라벨 상자)
오전 11시 40분 — 문자를 문자로 비교한다
자리로 돌아오자 할 일이 분명해졌다. TO_DATE를 지울 수는 없다. 대신 반대쪽을 문자로 맞추면 SLE_DT는 가공 없이 맨몸으로 남는다.
먼저 베이스라인부터 쟀다. 보조 인덱스가 없는 상태에서 2023년 6월 판매 건수를 센다.
sql
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)
FROM CF10
WHERE SLE_DT BETWEEN '20230601' AND '20230630';|* 2 | TABLE ACCESS FULL | CF10 | 1 | 2673 | 20953 |00:00:00.01 | 4510 |
2 - filter(("SLE_DT">='20230601' AND "SLE_DT"<='20230630'))4,510블록. 20,953건을 세려고 50만 행을 전부 읽는다. 이어서 인덱스를 만들었다.
sql
CREATE INDEX IX_CF10_SLE_DT ON CF10(SLE_DT); -- 1초 남짓같은 SQL을 다시 돌렸다.
|* 2 | INDEX RANGE SCAN | IX_CF10_SLE_DT | 1 | 2673 | 20953 |00:00:00.01 | 62 |
2 - access("SLE_DT">='20230601' AND "SLE_DT"<='20230630')62. INDEX RANGE SCAN이었고 조건도 access였다. 힌트를 주지 않았는데도 한 번에 탔다.
4,510 → 62. 72배.
지난주에는 두 번 틀리고 나서야 64배가 나왔는데 오늘은 한 번에 나왔다. "선배님, 이거 너무 쉽게 되는데요."
"쉬운 게 아니라 지난주에 네가 배운 거야." 선배가 말했다. "지난주의 너는 '인덱스를 만들면 된다'고 생각했고, 오늘의 너는 '조건이 인덱스의 정렬 순서에서 범위를 지목할 수 있나'를 먼저 봤어. 그게 달라진 거야." 그러고는 내 SQL을 짚었다. "근데 너 지금 뭘 썼는지 봐."
BETWEEN이었다. 지난주에 선배가 한 말이 떠올랐다. "날짜 범위에 BETWEEN을 쓰지 마." 공책에까지 적어 뒀는데 방금 그걸 쓰고 있었다.
"아… 고쳐야겠네요. >= AND <로."
"고쳐 봐. 근데 왜 고치는지 먼저 말해 봐."
"BETWEEN은 끝값이 6월 30일 자정이라서, 30일 낮에 들어온 건이 빠지니까요."
"그럼 SLE_DT에 '30일 낮'이 어떻게 들어가 있어?"
입을 열었다가 도로 닫았다. SLE_DT는 '20230630', 여덟 글자다. 시분초가 들어갈 자리가 없다. 시각은 옆 컬럼 SLE_HMS에 따로 있다. 잘려 나갈 게 없었다. 둘 다 돌려 보니 BETWEEN '20230601' AND '20230630'도, >= '20230601' AND < '20230701'도 똑같이 20,953건, 똑같이 62블록이었다.
"이게 오늘 가져갈 것 중에 제일 중요한 거야." 선배가 말했다. "지난주에 나는 'BETWEEN 쓰지 마'라고 했고 너는 그걸 규칙으로 외웠어. 그런데 그건 규칙이 아니라 DATE 컬럼에 시분초가 붙기 때문에 생긴 결과였어. 여기는 시분초가 없으니까 결론이 반대야."
"그럼 규칙을 외우면 안 되는 거예요?"
"외워도 돼. 근데 외운 규칙을 적용하기 전에 컬럼을 봐. 규칙은 컬럼의 성질에서 나오는 거지 그 반대가 아니야."
공책의 그 줄 밑에 한 줄을 더 적었다.
BETWEEN금지: 끝 경계에서 잘려 나갈 성분이 있을 때.DATE에는 시분초가 있고VARCHAR2(8)날짜에는 없다.
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 "느린 것"에 대한 답이다(4,510 → 62). 이어서 읽으면 "숫자가 안 맞는 것"으로 들어간다. 오늘의 본론은 그쪽이다.
오후 1시 20분 — 따옴표 하나
점심을 먹고 화면 소스를 더 뒤졌다. 날짜 조건이 수십 군데 있었는데, 그중 하나가 눈에 걸렸다.
sql
WHERE SLE_DT = 20230601따옴표가 없었다. "선배님, 이거 에러 안 나요?"
"돌려 봐."
잘 돌았다. 답도 맞았다. 따옴표를 붙였을 때와 같은 706건이었다. 그런데 실행계획이 달랐다.
[따옴표 없음]
|* 2 | INDEX FAST FULL SCAN | IX_CF10_SLE_DT | 706 |00:00:00.02 | 1406 |
2 - filter(TO_NUMBER("SLE_DT")=20230601)
[따옴표 있음]
|* 2 | INDEX RANGE SCAN | IX_CF10_SLE_DT | 706 |00:00:00.01 | 5 |
2 - access("SLE_DT"='20230601')1,406과 5. 따옴표 두 개 차이였다. 게다가 나는 TO_NUMBER를 쓴 적이 없었다.
"오라클이 끼워 넣은 거야. 문자하고 숫자를 비교하면 오라클은 문자 쪽을 숫자로 바꿔. 규칙이 그래. 근데 그 문자가 컬럼이잖아. 그러면 컬럼이 가공되는 거고…"
"라벨을 숫자로 읽으라고 한 거네요." 아침에 본 상자가 떠올랐다.
"그래. 상자를 전부 꺼내게 되는 거야."
같은 실수인데 장애가 되는 자리
"하나 더. SLE_NO로 똑같이 해 봐. 따옴표 없이."
sql
SELECT COUNT(*) FROM CF10 WHERE SLE_NO = 20220101;ORA-01722: unable to convert string value containing '-' to a number: SLE_NO
ORA-03302: (ORA-01722 details) invalid string value: 20220101-0001에러가 났다. 메시지에는 어떤 값 때문인지까지 찍혀 있었다.
| 같은 실수(숫자 리터럴로 문자 컬럼 비교) | 결과 | 언제 발견되나 |
|---|---|---|
SLE_DT = 20230601 (값이 전부 숫자) | Buffers 5 → 1,406 · 답은 맞음 | 아무도 모른다. 조용히 느려진다 |
SLE_NO = 20220101 (값에 하이픈) | ORA-01722 | 바로 드러난다. 그래서 오히려 낫다 |
"위쪽이 더 무서워." 선배가 말했다. "아래쪽은 터지니까 고치잖아. 위쪽은 아무것도 안 터진 채로 3년이 가."
요일 컬럼 SLE_DOW(VARCHAR2(1), 값은 '1'~'7')도 마찬가지였다. WHERE SLE_DOW = 7은 에러도 없고 답도 맞는데, filter(TO_NUMBER("SLE_DOW")=7)로 4,511블록을 읽었다.
오후 2시 10분 — 뷰로 가리면 되지 않나
"컬럼을 못 바꾸면 뷰를 만들어서 DATE로 보이게 하면 되지 않아요? 화면 코드는 깔끔해지잖아요."
"만들어 봐."
sql
CREATE OR REPLACE VIEW V_CF10_D AS
SELECT SLE_NO, TO_DATE(SLE_DT,'YYYYMMDD') AS SLE_D, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO FROM CF10;
SELECT COUNT(*) FROM V_CF10_D WHERE SLE_D >= DATE '2023-06-01' AND SLE_D < DATE '2023-07-01';|* 2 | INDEX FAST FULL SCAN | IX_CF10_SLE_DT | 20953 |00:00:00.04 | 1406 |
2 - filter((TO_DATE("SLE_DT",'YYYYMMDD')>=TO_DATE(' 2023-06-01 00:00:00', ...) AND ...))1,406. Predicate에 TO_DATE("SLE_DT",'YYYYMMDD')가 다시 나타나 있었다.
"제 조건이 뷰 안으로 들어갔네요?"
"그걸 Predicate가 밀려 내려갔다고 해. 오라클은 뷰를 통째로 만들어 놓고 거기서 거르는 게 아니라, 조건을 뷰 안쪽으로 밀어 넣어 가능한 한 일찍 거르려고 해. 보통은 그게 이득이야." 선배가 말했다. "근데 밀어 넣는 순간 네 조건이 뷰의 식이랑 합쳐져. 그래서 SLE_D >= ...가 TO_DATE(SLE_DT) >= ...로 되돌아온 거야."
"뷰는 가림막이지 변환이 아니네요."
"맞아. 하나 더 보여 줄게. 오늘은 계기판을 한 단계 올려 볼 거야."
10053 트레이스를 켜고 따옴표 없는 그 조건을 한 번 돌린 뒤, 트레이스 파일에서 몇 줄을 찾았다.
WCFPD: Current where clause predicates "CF10"."SLE_DT"=20230601 ← 내가 쓴 그대로
FPD: Considering simple filter push in query block SEL$1 (#0)
TO_NUMBER("CF10"."SLE_DT")=20230601 ← 여기서 붙는다
Final query after transformations:
SELECT COUNT(*) FROM "PUP"."CF10" WHERE TO_NUMBER("CF10"."SLE_DT")=20230601"윗줄은 제가 쓴 거고 아랫줄은 오라클이 고친 거네요."
"그래. FPD는 Filter Push Down이야. 조건을 밀어 내리면서 타입을 맞추고, 그 과정에서 함수가 붙어." 선배가 말했다. "지난주엔 Predicate Information에서 결과를 봤고, 오늘은 붙는 순간을 본 거야."
10053 읽는 법과 계기판을 한 단계 올리는 법은
2_파고들기.md §6에 있다.
오후 3시 00분 — 못 고치는 코드라면
"선배님, 화면이 수십 개예요. 그걸 전부 고칠 수 있을지 모르겠는데요."
"그럴 때 쓰는 게 있어. 지난주에 이름만 듣고 넘어간 거." 함수 기반 인덱스였다. 가공된 값 자체를 인덱스로 만드는 방법이다.
sql
CREATE INDEX IX_CF10_SLE_D_FBI ON CF10(TO_DATE(SLE_DT,'YYYYMMDD'));SQL은 한 글자도 고치지 않고 원래의 가공 조건 그대로 돌렸다.
|* 2 | INDEX RANGE SCAN | IX_CF10_SLE_D_FBI | 20953 |00:00:00.01 | 59 |
2 - access("CF10"."SYS_NC00010$">=TO_DATE(' 2023-06-01 00:00:00', ...) AND ...)1,406 → 59. 아까 만든 뷰로 돌려도 59였다. 화면 코드를 한 줄도 고치지 않았는데 양쪽 다 살아났다.
"이거 되게 좋은데요?"
"좋지. 그래서 위험해. 이거 쳐 봐." 선배가 포맷 문자열만 소문자로 바꾼 조건을 불러 줬다. TO_DATE(SLE_DT,'yyyymmdd') >= DATE '2023-06-01' AND ... < DATE '2023-07-01'.
1,406. 안 탔다. 두 SQL을 나란히 놓고 한참 비교했다.
'YYYYMMDD' ← 탄다. 59
'yyyymmdd' ← 안 탄다. 1,406대소문자였다.
"……이게 다예요?"
"그게 다야. 함수 기반 인덱스는 식이 글자 단위로 같아야 타. 오라클한테 'YYYYMMDD'하고 'yyyymmdd'는 다른 문자열이야. 같은 뜻으로 보이는 건 사람 눈에만 그래." TRUNC로 한 번 더 감싼 조건도 안 탔다(1,340).
그럼 가상 컬럼은
"식에 이름을 붙이는 방법도 있어."
sql
ALTER TABLE CF10 ADD (SLE_D DATE GENERATED ALWAYS AS (TO_DATE(SLE_DT,'YYYYMMDD')) VIRTUAL);
CREATE INDEX IX_CF10_SLE_D ON CF10(SLE_D);SLE_D로 조회하면 59였다. 놀라운 건 그다음이었다. 원래의 TO_DATE(SLE_DT,'YYYYMMDD') 조건으로도 59가 나왔다.
2 - access("CF10"."SLE_D">=TO_DATE(' 2023-06-01 00:00:00', ...) AND ...)Predicate를 보니 TO_DATE(SLE_DT,...)가 "SLE_D"로 바뀌어 있었다.
"옵티마이저가 네 식을 보고 '이건 저 가상 컬럼이네' 하고 바꿔치기한 거야." 선배가 말했다. "조건이 또 이동했지. 아까는 뷰 안쪽으로 밀려 들어갔고, 이번엔 컬럼 이름으로 치환됐어. 같은 자리에 적힌 게 계속 바뀌는 거 보여?"
E-Rows도 눈에 띄었다.
문자 컬럼으로 물으면 SLE_DT >= '20230601' AND < '20230701' E-Rows 5,177
DATE 가상컬럼으로 SLE_D >= ... AND < ... E-Rows 21,261
실제 A-Rows 20,953"문자로 물으면 4배 과소추정이에요. 날짜로 물으면 거의 맞고요."
"그게 문자 날짜의 숨은 비용이야. 오라클은 문자 컬럼의 범위 선택도를 글자를 숫자로 인코딩해서 추정해. 날짜만큼 균일하지가 않아." 선배가 말했다. "지난주에 배운 거 기억나? 컬럼을 가공하면 인덱스와 추정을 한꺼번에 잃는다. 여기선 가공을 안 했는데도 추정이 흐려져 있어."
그리고 청구서
"자, 그럼 쓸 거야?" 쓰고 싶었다. 59는 좋은 숫자였다. "세 가지만 재 보고 결정해."
첫째, 기존 적재 배치를 그대로 돌렸다. INSERT INTO CF10 VALUES (…아홉 개…).
ORA-00947: not enough values컬럼이 9개에서 10개가 됐다. 값 9개를 넣던 INSERT는 모두 깨진다. 그럼 값을 10개로 맞춰 주면?
ORA-54013: INSERT operation disallowed on virtual columns가상 컬럼에는 값을 넣을 수 없다. 모든 INSERT를 컬럼을 일일이 명시하도록 고쳐야 한다.
둘째, 인덱스가 늘면 적재가 얼마나 느려질까. 복사 테이블에 5만 건을 넣으며 redo를 쟀다.
| 인덱스 수 | redo (50,000건 INSERT) |
|---|---|
| 0개 | 약 3.7MB |
1개 (SLE_DT) | 약 9.2~9.5MB |
| 2개 (+ 가상컬럼) | 약 14.5~14.8MB |
인덱스 하나마다 약 5.7MB씩 늘었다. 2개면 0개일 때의 약 4배다.
"……안 쓰는 게 맞겠네요."
"왜?"
"SQL을 고칠 수 있으면 고치는 게 낫잖아요. TO_DATE를 빼고 문자로 비교하면 인덱스 하나로 62가 나와요. 62를 59로 줄이자고 INSERT를 전부 고치고 적재까지 느리게 할 이유는 없어요."
"그게 오늘 리포트의 6번 칸이야. '채택하지 않은 대안과 그 이유.'" 선배가 말했다. "재 보지도 않고 '안 씁니다'라고 쓰면 그건 게으른 거야. 재 보고 안 쓰는 거랑은 달라."
가상 컬럼을 지웠다.
오후 4시 20분 — 2억 9천만 원
그제야 아침 메일에 있던 두 번째 건이 떠올랐다. 문제의 쿼리를 받아 봤다.
sql
SELECT COUNT(*), SUM(b.SLE_QTY * b.SLE_UPRC)
FROM CF10 a, CF11 b
WHERE a.SLE_NO = b.SLE_NO
AND a.SLE_HMS BETWEEN '090000' AND '180000'; -- 오전 9시 ~ 오후 6시깔끔해 보였다. 컬럼도 가공하지 않았다. 아침에 배운 그대로였다. "이거 문제없어 보이는데요."
"SLE_HMS를 봤어?"
아침에 본 문서고가 떠올랐다. 길이가 같고, 0으로 채웠고, 큰 단위가 왼쪽. 셋 중 하나가 깨졌을까. 길이별로 세어 봤다.
길이 건수 최소값 최대값
---------- ---------- ------ ------
5 33241 80000 95959
6 466759 100000 225959길이가 5인 행이 33,241건 있었다. '80000'은 8시 0분 0초, '95959'는 9시 59분 59초였다. 앞자리 0이 날아가 있었다.
"…0으로 안 채웠네요."
"그럼 어떻게 되지?"
머릿속으로 문자 비교를 해 봤다. '95959'와 '180000'을 왼쪽부터 비교하면 첫 글자가 9와 1이다. 9가 크다.
"9시 59분이 오후 6시보다 크다고 판정돼요. 그래서 BETWEEN '090000' AND '180000'에서 빠져요."
"몇 건?" 조인해서 금액까지 뽑았다.
| 건수 | 금액 | |
|---|---|---|
현업 쿼리 (문자 BETWEEN) | 841,623 | 6,920,497,000 |
제대로 (TO_NUMBER로 비교) | 877,913 | 7,217,788,000 |
| 차이 | 36,290 | 297,291,000 |
2억 9,729만 1천 원. 메일에 적힌 "2억 9천만 원"이 바로 이것이었다.
사라진 36,290건을 추적해 보니 전부 9시대였다. 8시대는 애초에 범위 밖이니 의도대로 빠졌지만, 9시대는 범위 안에 들어와야 하는데 위쪽으로 튕겨 나갔다.
"매일 아침 9시부터 10시까지 한 시간이 1년 내내 빠진 거야." 선배가 말했다.
그리고 여기가 고약한 지점
"자, 저거 고쳐 봐."
sql
WHERE LPAD(a.SLE_HMS,6,'0') BETWEEN '090000' AND '180000'답이 맞았다. 877,913건. "됐죠?"
"인덱스를 만들고 둘 다 재 봐."
| 계획 | Buffers | 결과 | |
|---|---|---|---|
원본 SLE_HMS BETWEEN … | INDEX RANGE SCAN · access | 966 | 383,578건 (틀림) |
교정 LPAD(SLE_HMS,6,'0') BETWEEN … | INDEX FAST FULL SCAN · filter | 1,264 | 400,197건 (맞음) |
한참 그 표를 봤다.
빠른 쪽이 틀렸다.
지난주에 선배가 한 말이 그대로 겹쳤다. "느린 코드를 빠르게 만드는 것이 튜닝이고, 맞는 코드를 틀리게 만드는 것은 장애다." 그때는 내가 빠르면서 틀린 코드를 쓰려다 걸렸다. 오늘은 이미 운영에서 돌고 있는 코드가 그랬다.
"제가 오늘 이걸 '튜닝 대상'으로만 봤으면, 966이면 빠르니까 괜찮다고 넘겼을 거예요."
"그래. 그리고 누가 LPAD를 붙여서 고치면 '느려졌다'고 반려당했겠지." 선배가 말했다. "빠른 게 맞는 거라고 착각하면 거기서 끝이야. 그래서 리포트에 정합성 검증 칸이 있는 거야."
진짜 교정은 어디에 있나
"그럼 LPAD로 가는 거예요? 1,264로?"
"그건 증상 처방이야." 선배가 고개를 저었다. "원인이 뭐야?"
"…데이터요. SLE_HMS에 0이 안 채워져 있는 거요."
"그래. SQL로 못 고치는 문제도 있어. 이건 데이터를 고쳐야 돼."
복사본을 만들어서 비용을 쟀다. UPDATE ... SET SLE_HMS = LPAD(SLE_HMS,6,'0') WHERE LENGTH(SLE_HMS) < 6.
>> UPDATE 33241건 redo = 9,345,332 bytes (약 8.9 MB) 27 cs데이터를 고치고 나니 원래의 문자 BETWEEN이 맞는 답(400,197건)을 냈다. 이때는 인덱스도 RANGE SCAN으로 탄다.
"운영에서는 할 일이 더 붙어. 영향 분석, 백업, 적재 배치 수정, 그리고 재발 방지 제약인 CHECK (LENGTH(SLE_HMS)=6)까지. 이걸 안 걸면 내일 또 다섯 글자가 들어와."
⛳ 오늘은 여기까지(중단 지점 2) 오늘의 클라이맥스는 966 vs 1,264다. 빠른 쪽이 틀린 답을 낸다. 여기까지만 해도 오늘 할 일은 다 했다. 이어지는 저녁 장면에서는 오늘 배운 규칙을 우리 쪽 코드(Q1)에 대 본다. 거기서 방향이 뒤집힌다.
오후 6시 40분 — 반대 방향
리포트를 쓰고 있는데 선배가 왔다. "오늘 배운 규칙, 한 줄로 말해 봐."
"문자 컬럼을 숫자하고 비교하면 컬럼 쪽이 변환돼요. 그래서 인덱스가 봉쇄되고요."
"그럼 DATE 컬럼을 문자하고 비교하면 어느 쪽이 변환돼?"
모르겠다고 답했다. 선배가 ora-hanorder 터미널, 그러니까 한빛유통 쪽을 열어 보라고 했다. "지난주에 Q1을 91까지 내렸지. 근데 그거 리터럴로 쟀잖아. 진짜 패키지는 뭘 받아?"
소스를 열어 보니 PKG_ORD_STLM.Q1은 조회월을 VARCHAR2(6)으로 받고 있었다. '202406' 같은 문자 바인드였다.
"그럼 어떻게 쓸래?"
바인드가 문자니까 컬럼도 문자로 맞추면 된다고 생각했다.
sql
VAR p_ym VARCHAR2(6)
EXEC :p_ym := '202406'
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), SUM(ORD_AMT)
FROM T_ORD_BIG
WHERE TO_CHAR(ORD_DT,'YYYYMM') = :p_ym;|* 2 | TABLE ACCESS FULL| T_ORD_BIG | 1 | 10000 | 27390 |00:00:00.07 | 5826 |
2 - filter(TO_CHAR(INTERNAL_FUNCTION("ORD_DT"),'YYYYMM')=:P_YM)5,826. 지난주에 본 바로 그 숫자였다. 91까지 내려놨던 게 바인드 하나 끼웠다고 원점으로 돌아갔다.
"……제가 또 그랬네요."
"방향을 거꾸로 적용한 거야. 오늘 pup에서는 컬럼이 문자였어. 그래서 반대쪽을 문자로 맞춰야 했지. 여기는 컬럼이 날짜야. 그럼 반대쪽을 뭐로 맞춰야 돼?"
"…날짜요. 컬럼이 아니라 바인드 쪽을 날짜로 바꿔야 해요."
sql
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), SUM(ORD_AMT)
FROM T_ORD_BIG
WHERE ORD_DT >= TO_DATE(:p_ym||'01','YYYYMMDD')
AND ORD_DT < ADD_MONTHS(TO_DATE(:p_ym||'01','YYYYMMDD'),1);|* 2 | FILTER | | 1 | | 27390 |00:00:00.01 | 91 |
|* 3 | INDEX RANGE SCAN | IX_ORD_BIG_DT_AMT | 1 | 28336 | 27390 |00:00:00.01 | 91 |
3 - access("ORD_DT">=TO_DATE(:P_YM||'01','YYYYMMDD') AND
"ORD_DT"<ADD_MONTHS(TO_DATE(:P_YM||'01','YYYYMMDD'),1))91. 조건도 access였다.
"이게 Q1_rev03이야. 근데 오늘 리비전은 빨라진 게 아니야. 91에서 91이잖아."
"그럼 뭐가 성과예요?"
"바인드를 되돌려 놓으면서 91을 잃지 않은 거야. 리포트에 '개선 없음'이 아니라 '유지'라고 써. 둘은 다른 말이야."
그런데 조용히 틀리는 길도 있다
"하나만 더. DATE 컬럼하고 문자를 그냥 비교하면 어떻게 될 것 같아? 아까 네가 물은 거."
sql
WHERE ORD_DT >= '01-JUN-24' AND ORD_DT < '01-JUL-24' 3 - access("ORD_DT">='01-JUN-24' AND "ORD_DT"<'01-JUL-24') -- Buffers 9191이었다. 27,390건, 멀쩡했다. "어? 되는데요?"
"DATE가 이겨. 문자 쪽이 TO_DATE로 변환돼. 컬럼은 맨몸으로 남고." 선배가 말했다. "규칙은 이거야. 숫자는 문자를 이기고, 날짜도 문자를 이긴다. 지는 쪽이 컬럼이면 망하는 거고."
"그럼 이렇게 써도 되는 거네요?"
"써 봐. 포맷만 바꿔서."
sql
SELECT COUNT(*) FROM T_ORD_BIG WHERE ORD_DT >= '2024-06-01';ORA-01861: literal does not match format string"'2024-06-01'은 안 되고 '01-JUN-24'는 되네요. 왜요?"
"네 세션의 NLS_DATE_FORMAT이 DD-MON-RR이라서 그래. 포맷을 안 적으면 세션 설정을 따라가. 그럼 세션 설정을 바꿔 볼까."
sql
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
SELECT COUNT(*) FROM T_ORD_BIG WHERE ORD_DT >= '2024-06-01' AND ORD_DT < '2024-07-01'; -- 91 · 27,390
SELECT COUNT(*) FROM T_ORD_BIG WHERE ORD_DT >= '01-JUN-24'; -- ?앞줄은 잘 돌았다. 91에 27,390건. 방금 터지던 코드가 멀쩡해졌다. 그런데 뒷줄은, 아까까지 잘 돌던 그 코드는…
COUNT(*)
----------
1000000숫자를 세 번 봤다. 100만. 전체였다. 27,390이 아니었다. 그런데도 에러 하나 나지 않았다.
sql
SELECT TO_CHAR(TO_DATE('01-JUN-24'),'SYYYY-MM-DD') FROM dual; -- 0001-06-24'01-JUN-24'를 'YYYY-MM-DD'로 읽으니 서기 1년 6월 24일이 됐다. 테이블에서 가장 오래된 주문도 2022년이니 전부 조건에 걸렸다.
"……차라리 에러라도 나면 좋겠는데요."
"지난주에도 같은 말 했지." 선배가 웃었다. "네가 틀려도 오라클은 틀렸다고 말해 주지 않아. 오늘은 그게 느린 게 아니라 틀린 답으로 나타난 거고."
"세션 설정 하나로 정산이 바뀌는 거네요."
"그래. 배치 서버랑 개발 PC의 NLS가 다른 회사가 생각보다 많아." 선배가 일어섰다. "날짜에는 포맷을 항상 적는다. TO_DATE(x,'YYYYMMDD'). 그게 오늘 가져갈 마지막 줄이야."
오늘 남은 것
| 무엇 | 값 |
|---|---|
pup 월 매출 조회 | TO_DATE 가공 제거 + 인덱스 1개. 4,510 → 62 (CF10 단독) · 조인 쿼리 12,585 → 8,552 |
Q1_rev03 | 바인드 복원. 91 유지 (개선이 아니라 유지) |
| 정합성 사고 1건 | 영업시간 집계에서 36,290건, 297,291,000원 누락. 원인은 SLE_HMS의 0 패딩 누락 |
| T1 리포트 2호 | 처음으로 "채택하지 않은 대안"을 숫자로 적는다 |
오늘 배운 두 문장
① 묵시적 형변환은 둘 중 한쪽에서 일어난다. 지는 쪽이 컬럼이면 인덱스가 봉쇄된다. 숫자는 문자를 이기고, 날짜도 문자를 이긴다. 그래서
문자컬럼 = 숫자는 망하고날짜컬럼 >= 문자는 산다.
② 문자로 적힌 날짜를 범위로 비교할 수 있는 것은 우연이 아니라 세 가지 규칙 덕분이다. 길이가 같고, 0으로 채웠고, 큰 단위가 왼쪽에 있어야 한다. 하나라도 깨지면 글자 순서는 더 이상 시간 순서가 아니다.
다음으로 넘기는 것
CF10의 CF는 9,921인데 왜 지난주T_ORD_BIG은 1,000,000이었나 → 04화- 조인 쪽에 남은
CF11FULL 8,075 → 06화 (오늘은 건드리지 않았다) - 인덱스 1개 때문에 늘어난 redo 5.7MB → 14화
SLE_HMS데이터 교정 승인 요청 → 유통지원팀 회신 대기 · 09화에서 이 시스템에 다시 온다
다음 →
3_실습과_기록.md에서 오늘 과정을 직접 재현하고 T1 리포트 2호를 쓴다. 개념이 막히면2_파고들기.md를 펼친다.