테마
03화 · 파고들기
조회용 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다. 측정값은 모두ora-dist(pup)와ora-hanorder(sqlp)에서 실측한 값이다(2026-10-18 · Oracle AI Database 26ai Free 23.26.2.0.0).
| 찾는 것 | 바로 가기 |
|---|---|
| 어느 쪽이 변환되는가 (형변환 규칙) | §1 규칙 |
| 문자 날짜를 범위로 비교할 수 있는 조건 3가지 | §2 조건 |
| ☠️ 문자 날짜가 조용히 틀리는 자리 | §3 함정 |
| 못 고칠 때: FBI vs 가상 컬럼 | §4 구제책 |
| Predicate의 이동 네 가지 | §5 이동 |
| 10053으로 형변환 순간 보기 | §6 10053 |
| 날짜 조건 교정표 (02화 확장판) | §7 교정표 |
| 측정값 전부 | §8 측정 |
| 공식문서 좌표 | §9 좌표 |
1. 묵시적 형변환 — 어느 쪽이 변환되는가
타입이 다른 두 값을 비교하면 오라클은 한쪽을 다른 쪽 타입으로 맞춘다. 어느 쪽을 맞출지는 규칙으로 정해져 있다.
숫자는 문자를 이기고, 날짜도 문자를 이긴다. 즉 문자 쪽이 변환된다. 그 문자가 컬럼이면 컬럼이 가공되고, 인덱스는 원천 봉쇄된다.
| 비교 | 변환되는 쪽 | 컬럼은? | 실측 |
|---|---|---|---|
VARCHAR2 컬럼 = 숫자 리터럴 | 컬럼 → TO_NUMBER(col) | ☠️ 가공됨 | SLE_DT = 20230601 → 1,406 |
VARCHAR2 컬럼 = 문자 리터럴 | 없음 | ✅ 맨몸 | SLE_DT = '20230601' → 5 |
DATE 컬럼 >= 문자 리터럴 | 리터럴 → TO_DATE(lit) | ✅ 맨몸 | ORD_DT >= '01-JUN-24' → 91 |
DATE 컬럼 = TO_CHAR(col) 쪽에서 비교 | 컬럼(직접 가공) | ☠️ 가공됨 | TO_CHAR(ORD_DT,'YYYYMM') = :p_ym → 5,826 |
1.1 "컬럼이 변환된다"에는 두 얼굴이 있다
| 상황 | 결과 | 언제 발견되나 |
|---|---|---|
| 컬럼 값이 전부 숫자로 변환 가능 | 답은 맞고 느려지기만 한다 | 거의 발견되지 않는다 |
| 컬럼 값에 숫자로 못 바꾸는 게 섞여 있다 | ORA-01722 | 바로 드러난다. 그래서 오히려 낫다 |
ora-dist에서 실측한 결과:
sql
SELECT COUNT(*) FROM CF10 WHERE SLE_DT = 20230601; -- 706건. 맞다. 그런데 1,406 블록
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-000123ai에서는
ORA-03302가 함께 찍혀서 어느 값 때문인지까지 알 수 있다. 구버전에서는ORA-01722만 나온다.
SLE_DOW(VARCHAR2(1), 값 '1'~'7')도 같다. WHERE SLE_DOW = 7은 답이 맞고 에러도 없지만 filter(TO_NUMBER("SLE_DOW")=7)로 TABLE ACCESS FULL 4,511블록을 읽는다.
1.2 DATE 쪽이 이긴다는 것의 대가 — NLS
DATE 컬럼과 문자를 비교하면 컬럼은 멀쩡하다. 그러나 리터럴을 어떻게 해석할지가 세션의 NLS_DATE_FORMAT에 달려 있다.
ora-hanorder에서 실측한 결과(같은 테이블, 같은 코드):
세션 NLS_DATE_FORMAT | ORD_DT >= '01-JUN-24' | ORD_DT >= '2024-06-01' |
|---|---|---|
DD-MON-RR (기본) | ✅ 91 · 27,390건 | ☠️ ORA-01861 |
YYYY-MM-DD | ☠️ 1,000,000건 · 에러 없음 | ✅ 91 · 27,390건 |
sql
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
SELECT TO_CHAR(TO_DATE('01-JUN-24'),'SYYYY-MM-DD') FROM dual; -- 0001-06-24'01-JUN-24'가 서기 1년 6월 24일로 해석된다. 테이블의 최소 ORD_DT가 2022-01-01이므로 모든 행이 조건에 걸린다.ORA-01861은 에러가 나니까 고칠 수 있다. 하지만 이쪽은 아무 일 없다는 듯이 100만 건을 돌려준다.
규칙: 날짜를 변환할 때는 항상 포맷을 명시한다.
TO_DATE(x,'YYYYMMDD')처럼 쓴다. 배치 서버와 개발 PC의NLS가 다르면 같은 코드가 다른 답을 낸다.
2. 문자 날짜를 범위로 비교할 수 있는 조건 3가지
'20230601' <= x <= '20230630' 같은 문자 범위 비교가 날짜 범위 비교와 결과가 같은 것은 우연이 아니다. 사전순(binary) 비교가 시간순과 일치하려면 다음 세 가지를 모두 지켜야 한다.
| # | 조건 | 깨지면 |
|---|---|---|
| ① | 길이가 항상 같다 | 짧은 값이 엉뚱한 자리에 선다 |
| ② | 앞을 0으로 채운다 | '95959' > '180000'(§3) |
| ③ | 큰 단위가 왼쪽(YYYYMMDD) | DDMMYYYY면 정렬이 의미가 없다 |
CF10.SLE_DT는 세 가지를 모두 지킨다. 실측 결과 길이는 최소 8 = 최대 8, 비숫자 0건, 730일('20220101'~'20231231')이다.
sql
SELECT MIN(LENGTH(SLE_DT)), MAX(LENGTH(SLE_DT)),
COUNT(CASE WHEN TRANSLATE(SLE_DT,'x0123456789','x') IS NOT NULL THEN 1 END) AS 비숫자
FROM CF10;
TRANSLATE(col,'x0123456789','x')는 숫자를 전부 지운다. 남는 게 있으면 비숫자가 섞인 것이다. (TRANSLATE(col,'0123456789',' ')처럼 공백으로 바꾸면 결과가 항상 NOT NULL이라 판정할 수 없다.)
2.1 ③ 덕분에 LIKE를 교정형으로 쓸 수 있다
월 단위로 조회하려고 SUBSTR로 자르면 컬럼 가공이 된다. 반면 앞부분이 상수인 LIKE는 범위를 지목할 수 있다.
| 조건 | 계획 | Buffers |
|---|---|---|
SUBSTR(SLE_DT,1,6) = '202306' | INDEX FAST FULL SCAN | 1,406 |
SLE_DT LIKE '202306%' | INDEX RANGE SCAN | 62 |
SLE_DT >= '20230601' AND < '20230701' | INDEX RANGE SCAN | 62 |
2.2 그래도 추정은 날짜보다 못하다
같은 30일이라도 문자로 묻느냐 날짜로 묻느냐에 따라 E-Rows가 달라진다(실제는 20,953).
| 어떻게 물었나 | E-Rows | 실제 대비 |
|---|---|---|
SLE_DT BETWEEN '20230601' AND '20230630' | 2,673 | 7.8배 과소 |
SLE_DT >= '20230601' AND < '20230701' | 5,177 | 4.0배 과소 |
SLE_D(DATE 가상 컬럼) >= … AND < … | 21,261 | 1.5% 오차 |
오라클은 문자 컬럼의 범위 선택도를 문자열을 수치로 인코딩해서 추정한다. 날짜만큼 균일하지 않아서 추정이 어긋난다. 02화에서 배운 "컬럼을 가공하면 인덱스와 추정을 한꺼번에 잃는다"의 변형이다. 여기서는 가공을 하지 않았는데도 추정이 흐려져 있다.
3. 문자 날짜가 조용히 틀리는 자리
3.1 ☠️ 0 패딩이 깨지면 — 실측 재현
CF10.SLE_HMS는 VARCHAR2(6)에 담긴 시각인데, 앞자리 0이 날아간 행이 있다.
sql
SELECT LENGTH(SLE_HMS) AS 길이, COUNT(*), MIN(SLE_HMS), MAX(SLE_HMS)
FROM CF10 GROUP BY LENGTH(SLE_HMS) ORDER BY 1; 길이 건수 최소값 최대값
---------- ---------- ------ ------
5 33241 80000 95959 ← 8시대 16,622 · 9시대 16,619
6 466759 100000 225959'95959'(9:59:59)와 '180000'(18:00:00)을 왼쪽부터 비교하면 첫 글자가 9와 1이다. 따라서 문자 비교로는 '95959' > '180000' 판정이 나온다.
영업시간(09:00~18:00) 매출을 집계한 결과(CF10 × CF11):
| 건수 | 금액 | |
|---|---|---|
SLE_HMS BETWEEN '090000' AND '180000' | 841,623 | 6,920,497,000 |
TO_NUMBER(SLE_HMS) BETWEEN 90000 AND 180000 | 877,913 | 7,217,788,000 |
| 차이 (전부 9시대) | 36,290 | 297,291,000 |
2억 9,729만 1천 원이 1년 내내 빠졌다. 매일 09:00~09:59 한 시간이 통째로 빠진다. (08시대는 '80000' < '090000'이라서 애초에 범위 밖이다. 이건 조건이 의도한 대로다.)
3.2 ☠️☠️ 그리고 빠른 쪽이 틀렸다
SLE_HMS에 인덱스를 만들고 두 조건을 나란히 재 보면 다음과 같다. 이 화의 핵심 장면이다.
| 계획 | Predicate | Buffers | 결과 | |
|---|---|---|---|---|
SLE_HMS BETWEEN '090000' AND '180000' | INDEX RANGE SCAN | access | 966 | 383,578 (틀림) |
LPAD(SLE_HMS,6,'0') BETWEEN … | INDEX FAST FULL SCAN | filter | 1,264 | 400,197 (맞음) |
📌 02화의 명제가 여기서 뒤집혀 돌아온다
02화: "느린 코드를 빠르게 만드는 것이 튜닝이고, 맞는 코드를 틀리게 만드는 것은 장애다." 02화에서는 내가 빠르면서 틀린 코드를 쓰려다 걸렸다. 03화에서는 이미 운영에서 돌고 있는 코드가 그렇다.
Buffers만 보고 966을 통과시키면 정산이 조용히 틀리고,LPAD교정을 "느려졌다"는 이유로 반려하면 사고 난 코드로 되돌리는 셈이다. 그래서 T1 리포트에 정합성 회귀 검증 칸이 있다.
3.3 포맷이 섞이면
하이픈(0x2D)은 숫자(0x30~)보다 작다. 그래서 '2023-06-01'은 모든 8자리 값보다 앞에 온다.
sql
WITH t AS (
SELECT 'A' 라벨,'20230601' 값 FROM dual UNION ALL SELECT 'B','2023-06-01' FROM dual UNION ALL
SELECT 'C','20230615' FROM dual UNION ALL SELECT 'D','2023-6-15' FROM dual UNION ALL
SELECT 'E','20230630' FROM dual UNION ALL SELECT 'F','20230701' FROM dual)
SELECT 라벨, 값, CASE WHEN 값 BETWEEN '20230601' AND '20230630' THEN '포함' ELSE '제외' END
FROM t ORDER BY 값;B 2023-06-01 제외 ← 6월 1일인데 빠진다
D 2023-6-15 제외 ← 6월 15일인데 빠진다
A 20230601 포함
C 20230615 포함
E 20230630 포함
F 20230701 제외에러도 안 나고 경고도 없다. 그냥 빠진다.
3.4 교정의 세 갈래 — 어느 것도 공짜가 아니다
| 갈래 | 답 | 성능 | 대가 |
|---|---|---|---|
① SQL에서 컬럼을 감싼다(LPAD/TO_NUMBER) | ✅ 맞음 | FAST FULL SCAN 1,264 | 인덱스 RANGE SCAN을 잃는다 |
| ② 가상 컬럼 + 인덱스 | ✅ 맞음 | RANGE SCAN | §4의 대가 전부 |
| ③ 데이터를 고친다 | ✅ 맞음 | RANGE SCAN 그대로 | 운영 데이터 변경 |
③을 복사 테이블에서 실측했다. UPDATE … SET SLE_HMS = LPAD(SLE_HMS,6,'0') WHERE LENGTH(SLE_HMS)<6 → 33,241건 · redo 9,345,332 bytes (약 8.9MB). 고친 뒤에는 원래의 문자 BETWEEN이 400,197건을 돌려준다.
재발 방지 조치도 반드시 함께 한다.
sql
ALTER TABLE CF10 ADD CONSTRAINT CK_CF10_HMS CHECK (LENGTH(SLE_HMS)=6);SQL 튜닝으로 못 고치는 문제가 있다. ①②는 증상 처방이고 ③이 원인 처방이다. 다만 ③은 영향 분석, 백업, 적재 배치 수정이 따르는 승인 사안이지 튜너가 혼자 결정할 일이 아니다.
4. 구제책 — FBI와 가상 컬럼
전제: SQL을 고칠 수 있으면 SQL을 고친다. 아래는 고칠 수 없을 때 쓰는 수단이다.
sql
-- 함수 기반 인덱스 (FBI)
CREATE INDEX IX_CF10_SLE_D_FBI ON CF10(TO_DATE(SLE_DT,'YYYYMMDD'));
-- 가상 컬럼 + 일반 인덱스
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);4.1 둘 다 되는 것
| FBI | 가상 컬럼 | |
|---|---|---|
| SQL을 고치지 않아도 기존 가공 조건이 산다 | ✅ 59 | ✅ 59 |
| 뷰를 거치는 화면도 같이 산다 | ✅ 59 | ✅ 59 |
인덱스 크기 (LEAF_BLOCKS) | 1,327 | 1,327 |
| 생성 시간 (50만 행) | 0.8~1.6초 | 비슷함 + ALTER TABLE 한 번 (부하에 따라 흔들린다) |
| 테이블에 보이는 컬럼 | 숨은 컬럼 SYS_NC000nn$ (HIDDEN=YES) | 보이는 컬럼 SLE_D |
USER_TAB_COLS로 확인한다(USER_TAB_COLUMNS에는 숨은 컬럼이 나오지 않는다).
sql
SELECT internal_column_id, column_name, data_type, hidden_column, virtual_column
FROM user_tab_cols WHERE table_name='CF10' ORDER BY internal_column_id;
SELECT index_name, column_expression FROM user_ind_expressions WHERE table_name='CF10';4.2 ☠️ FBI의 첫 번째 대가 — 식이 글자 단위로 같아야 한다
| 조건 | 계획 | Buffers |
|---|---|---|
TO_DATE(SLE_DT,'YYYYMMDD') >= … AND < … | INDEX RANGE SCAN(FBI) | 59 |
TO_DATE(SLE_DT,'yyyymmdd') >= … AND < … | INDEX FAST FULL SCAN | 1,406 |
TRUNC(TO_DATE(SLE_DT,'YYYYMMDD')) >= … AND < … | INDEX FAST FULL SCAN | 1,340 |
포맷 문자열의 대소문자 하나로 59가 1,406이 된다. 사람이 보기에는 같은 뜻이고 결과도 같지만, 오라클에게는 다른 문자열이다. 가상 컬럼은 식이 컬럼 이름으로 치환되므로 이 함정은 덜하다. 대신 다른 대가가 있다.
4.3 ☠️ 가상 컬럼의 대가 — 운영 코드가 깨진다
USER_TAB_COLUMNS의 컬럼 수가 9 → 10으로 늘고, DESC와 SELECT * 결과가 바뀐다.
sql
INSERT INTO CF10 VALUES ('…',…아홉 개…); -- ORA-00947: not enough values
INSERT INTO CF10 (…,SLE_D) VALUES (…,SYSDATE); -- ORA-54013: INSERT operation disallowed on virtual columns모든 INSERT를 컬럼을 명시하는 형태로 고쳐야 한다. 값 목록만 쓰던 적재 배치는 그대로 깨진다.
4.4 두 번째 대가 — 적재 비용
복사 테이블에 50,000건을 INSERT하면서 redo size를 쟀다(3회 실행).
| 인덱스 수 | redo | 0개 대비 |
|---|---|---|
| 0개 | 약 3.7MB (3,872,372~3,876,896 bytes) | 1.00배 |
1개 (SLE_DT) | 약 9.2~9.5MB (9,616,792~9,969,488) | 2.5배 |
| 2개 (+ 가상 컬럼 인덱스) | 약 14.5~14.8MB (15,160,596~15,525,700) | 약 3.9~4.0배 |
인덱스 하나마다 약 5.7~5.8MB가 늘어난다. 6회 실행 기준이다. 시간은 1~64cs로 크게 흔들려서 지표로 쓰지 않았다(T0: 초는 보조 지표). 본격적인 청구서는 14화에서 받는다.
4.5 그래서 03화의 결론
| 대안 | 측정 | 채택 |
|---|---|---|
SQL 교정 (TO_DATE 제거, 문자끼리 비교) + 인덱스 1개 | 4,510 → 62 | ✅ 채택 |
| FBI | 1,406 → 59 | ❌ 포맷 한 글자에 무너진다 |
| 가상 컬럼 + 인덱스 | 1,406 → 59 | ❌ 모든 INSERT 수정 + redo 4배 |
62와 59의 차이는 3블록이다. 그 3블록을 위해
INSERT를 전부 고치고 적재를 느리게 할 이유는 없다. 재 보지도 않고 "안 씁니다"라고 쓰는 것과, 재 보고 안 쓰는 것은 다르다. 후자가 T1 리포트 6번 칸에 들어간다.
5. Predicate의 이동
Predicate Information에 적히는 조건은 내가 쓴 조건이 아니라 옵티마이저가 최종적으로 쓰기로 한 조건이다. 03화에서는 조건이 움직이는 네 가지 방식을 모두 봤다.
| # | 무엇이 일어나나 | 실측 Predicate | Buffers |
|---|---|---|---|
| ① | 타입을 맞추려고 함수가 붙는다 (FPD) | filter(TO_NUMBER("SLE_DT")=20230601) | 1,406 |
| ② | 조건이 뷰 안쪽으로 밀려 들어간다 | 내가 쓴 SLE_D >= … → filter(TO_DATE("SLE_DT",'YYYYMMDD')>=…) | 1,406 |
| ③ | 식이 인덱스의 숨은 컬럼으로 바뀐다 (FBI) | access("CF10"."SYS_NC00010$">=…) | 59 |
| ④ | 식이 가상 컬럼 이름으로 치환된다 | 내가 쓴 TO_DATE(SLE_DT,…) → access("CF10"."SLE_D">=…) | 59 |
②를 predicate pushing이라고 한다. 뷰를 통째로 만들고 거기서 거르는 게 아니라, 조건을 안쪽으로 밀어 넣어 가능한 한 일찍 거른다. 보통은 이득이지만, 밀어 넣는 순간 내 조건이 뷰의 식과 합쳐진다. 그래서 뷰는 가림막이지 변환이 아니다.
④는 SQL을 한 글자도 고치지 않았는데 실행계획이 바뀌는 경우다. 거꾸로 말하면, SQL은 그대로인데 실행계획이 바뀌었다면 테이블 쪽이 바뀌었는지를 의심해야 한다.
보조: FILTER 줄이 하나 더 생기는 경우
ora-hanorder의 Q1_rev03에서 본 실행계획이다.
|* 2 | FILTER | | 27390 |00:00:00.01 | 91 |
|* 3 | INDEX RANGE SCAN | IX_ORD_BIG_DT_AMT | 27390 |00:00:00.01 | 91 |
2 - filter(ADD_MONTHS(TO_DATE(:P_YM||'01','YYYYMMDD'),1)>TO_DATE(:P_YM||'01','YYYYMMDD'))리터럴이면 파싱할 때 "시작 < 끝"을 확정할 수 있지만, 바인드는 값이 들어와야 알 수 있다. 그래서 실행 시점에 확인하는 줄이 붙는다. Buffers에는 영향이 없다(91 → 91).
6. 10053으로 형변환 순간을 본다
Predicate Information에서는 결과를 보고, 10053 트레이스에서는 함수가 붙는 순간을 본다. T0 계기판 승급 사다리의 6단계이고, ALTER SESSION 권한(T3)이 필요하다.
sql
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT COUNT(*) FROM CF10 WHERE SLE_DT = 20230601;
ALTER SESSION SET EVENTS '10053 trace name context off';
SELECT value FROM v$diag_info WHERE name='Default Trace File';bash
docker exec -i ora-dist grep -n 'WCFPD: Current where clause\|finally:\|Final query after transformations' -A 1 <경로>실측 발췌:
WCFPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
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 ← 여기서 붙는다
finally: TO_NUMBER("CF10"."SLE_DT")=20230601
...
Final query after transformations: qb SEL$1 (#0)
SELECT COUNT(*) "COUNT(*)" FROM "PUP"."CF10" "CF10" WHERE TO_NUMBER("CF10"."SLE_DT")=20230601| 키워드 | 뜻 |
|---|---|
WCFPD / FPD | Filter Push Down. 조건을 안쪽/아래로 미는 단계. 여기서 타입이 맞춰진다 |
finally: | 그 단계가 끝난 뒤의 조건 |
Final query after transformations | 모든 변환이 끝난 최종 질의. 옵티마이저가 실제로 비용을 매기는 문장 |
10053 트레이스 파일은 수십만 줄까지 늘어난다. 위 세 키워드만 찾아 읽고, 전부 읽으려 하지 않는다. 비용 계산 자체를 읽는 법은 10화(통계·카디널리티)에서 다룬다.
7. 날짜 조건 교정표 — 02화 확장판
02화 §3.5는 DATE 컬럼만 다뤘다. 여기서는 VARCHAR2(8) 컬럼을 더한다.
| 컬럼 타입 | ✅ 올바른 형태 | ☠️ 하면 안 되는 것 | 이유 |
|---|---|---|---|
DATE | ORD_DT >= DATE '2024-06-01'AND ORD_DT < DATE '2024-07-01' | BETWEEN … AND DATE '2024-06-30' | 끝값이 30일 00:00에서 잘린다 |
DATE + 문자 바인드 | ORD_DT >= TO_DATE(:ym||'01','YYYYMMDD')AND ORD_DT < ADD_MONTHS(TO_DATE(:ym||'01','YYYYMMDD'),1) | TO_CHAR(ORD_DT,'YYYYMM') = :ym | 컬럼 가공. 91 → 5,826 |
DATE | 포맷을 명시한 TO_DATE | ORD_DT >= '01-JUN-24' | NLS에 의존한다. §1.2 |
VARCHAR2(8) | SLE_DT BETWEEN '20230601' AND '20230630'또는 >= '20230601' AND < '20230701' | TO_DATE(SLE_DT,'YYYYMMDD') >= … | 컬럼 가공. 62 → 1,406 |
VARCHAR2(8) | SLE_DT LIKE '202306%' | SUBSTR(SLE_DT,1,6) = '202306' | 컬럼 가공. 62 → 1,406 |
VARCHAR2(8) | 리터럴에 따옴표를 붙인다 | SLE_DT = 20230601 | 컬럼에 TO_NUMBER. 5 → 1,406 |
★ 여기서 BETWEEN 판정이 뒤집힌다
| 무대 | 컬럼 | BETWEEN을 쓰면 | 왜 |
|---|---|---|---|
02화 T_ORD | ORD_DT DATE (99,999행에 시분초) | 137건 누락 | 끝값이 자정으로 잘린다 |
03화 CF10 | SLE_DT VARCHAR2(8) (시각은 별도 컬럼) | 누락 없음 (실측 차이 0) | 잘릴 시분초가 없다 |
규칙을 외우기 전에 컬럼을 본다. "
BETWEEN금지"는 규칙이 아니라 끝 경계에 잘릴 성분이 있을 때 생기는 결과다. 확인 쿼리:sqlSELECT COUNT(*) FROM <T> WHERE <날짜컬럼> <> TRUNC(<날짜컬럼>); -- DATE: 0이 아니면 시분초가 있다 SELECT COUNT(DISTINCT LENGTH(<날짜컬럼>)) FROM <T>; -- VARCHAR2: 1이 아니면 길이가 깨졌다
8. 이 화의 측정값 전부
8.1 무대 — ora-dist (pup)
| 값 | |
|---|---|
| 테이블 | 18개 (CF01~CF18) · 보조 인덱스 0개(PK만) |
CF10(판매) | 500,000행 · 4,583블록 · 36MB · CF10_PK LEAF 1,673 / CF 9,340 |
CF11(판매상품) | 1,096,708행 · 8,204블록 |
| 문자 날짜 컬럼 | 6개: CF05.APL_DT CF10.SLE_DT CF12.ORD_DT CF14.INOUT_DT CF17.INV_BS_DT CF18.INV_BS_DT (전부 VARCHAR2(8)) |
SLE_DT | '20220101'~'20231231' · 730일 · 길이 8 고정 · 비숫자 0 |
| 2023년 6월 | 20,953행 = 4.19% (CF11 조인 시 45,935행 / 377,266,500원) |
IX_CF10_SLE_DT | BLEVEL 2 · LEAF 1,393 · CF 9,921 · 12MB |
8.2 CF10 단독 — 2023년 6월 (A-Rows 20,953)
| # | 조건 | 계획 | Buffers | E-Rows |
|---|---|---|---|---|
| A | 인덱스 없이 SLE_DT BETWEEN '20230601' AND '20230630' | TABLE ACCESS FULL | 4,510 | 2,673 |
| B | 인덱스 + 같은 조건 | INDEX RANGE SCAN | 62 | 2,673 |
| B2 | SLE_DT >= '20230601' AND < '20230701' | INDEX RANGE SCAN | 62 | 5,177 |
| C | SLE_DT BETWEEN 20230601 AND 20230630 (숫자) | INDEX FAST FULL SCAN | 1,406 | 1,250 |
| C2 | SLE_DT = 20230601 (A-Rows 706) | INDEX FAST FULL SCAN | 1,406 | 685 |
| C3 | SLE_DT = '20230601' (A-Rows 706) | INDEX RANGE SCAN | 5 | 685 |
| D | TO_DATE(SLE_DT,'YYYYMMDD') >= … AND < … | INDEX FAST FULL SCAN | 1,406 | 1,250 |
| E | SUBSTR(SLE_DT,1,6) = '202306' | INDEX FAST FULL SCAN | 1,406 | 5,000 |
| E2 | SLE_DT LIKE '202306%' | INDEX RANGE SCAN | 62 | 693 |
| F | 뷰 V_CF10_D의 SLE_D >= … AND < … | INDEX FAST FULL SCAN | 1,406 | 1,250 |
| P1 | FBI 생성 후 D와 같은 조건 | INDEX RANGE SCAN(FBI) | 59 | 21,261 |
| P2 | FBI 생성 후 F와 같은 뷰 조건 | INDEX RANGE SCAN(FBI) | 59 | 21,261 |
| Q1 | TRUNC(TO_DATE(SLE_DT,'YYYYMMDD')) >= … | INDEX FAST FULL SCAN | 1,340 | 1,250 |
| Q2 | TO_DATE(SLE_DT,'yyyymmdd') >= … | INDEX FAST FULL SCAN | 1,406 | 1,250 |
| R1 | 가상 컬럼 SLE_D >= … AND < … | INDEX RANGE SCAN | 59 | 21,261 |
| R2 | 가상 컬럼 생성 후 D와 같은 조건 (SQL 수정 없음) | INDEX RANGE SCAN | 59 | 21,261 |
| — | SLE_DOW = 7 (A-Rows 71,757) | TABLE ACCESS FULL | 4,511 | 71,429 |
| — | SLE_NO = 20220101 | — | ORA-01722 | — |
E-Rows1,250 = 500,000 × 0.25%. 02화 rev00의 2,500 = 1,000,000 × 0.25%와 같은 고정 추정값이다. (표현식 범위 조건은 0.25%, 표현식 등치 조건은 1%다.SUBSTR의 5,000 = 500,000 × 1%)
8.3 조인 — 현업 월 매출 쿼리 (CF10 × CF11, 45,935행 / 377,266,500원)
| 조건 형태 | 전체 Buffers | CF10 쪽 | CF11 쪽 |
|---|---|---|---|
TO_DATE(a.SLE_DT,…) >= … AND < … | 12,585 | TABLE ACCESS FULL 4,510 | FULL 8,075 |
a.SLE_DT BETWEEN '20230601' AND '20230630' | 8,552 | INDEX RANGE SCAN 62 → ROWID 478 | FULL 8,074 |
〃 + 커버링 (SLE_DT, SLE_NO) | 8,177 | INDEX RANGE SCAN 102 | FULL 8,075 |
커버링을 더해도 전체는 8,552 → 8,177(375 감소)에 그친다. 가장 큰 비용이
CF11FULL 8,075로 옮겨 갔기 때문이다. 그쪽은 조인 문제이므로 06화로 넘긴다. 03화에서는 건드리지 않았다.
8.4 SLE_HMS 정합성 사고
| 항목 | 값 |
|---|---|
| 길이 5인 행 | 33,241 (8시대 16,622 · 9시대 16,619) |
SLE_HMS BETWEEN '090000' AND '180000' (CF10 단독) | 383,578 · INDEX RANGE SCAN 966 |
LPAD(SLE_HMS,6,'0') BETWEEN … (CF10 단독) | 400,197 · INDEX FAST FULL SCAN 1,264 |
TO_NUMBER(SLE_HMS) BETWEEN … (인덱스 없음) | 400,197 · TABLE ACCESS FULL 4,522 |
| 조인 기준 누락 | 36,290건 / 297,291,000원 |
| 데이터 교정 비용 | 33,241건 UPDATE · redo 9,345,332 bytes |
8.5 ora-hanorder (한빛유통) — Q1 rev02 → rev03
| # | 조건 | 계획 | Buffers | E-Rows |
|---|---|---|---|---|
| rev02 | ORD_DT >= DATE '2024-06-01' AND < DATE '2024-07-01' | INDEX RANGE SCAN | 91 | 28,336 |
| 오답 | TO_CHAR(ORD_DT,'YYYYMM') = :p_ym | TABLE ACCESS FULL | 5,826 | 10,000 |
| rev03 | ORD_DT >= TO_DATE(:p_ym||'01','YYYYMMDD')AND ORD_DT < ADD_MONTHS(…,1) | FILTER + INDEX RANGE SCAN | 91 | 28,336 |
| — | ORD_DT >= '01-JUN-24' AND < '01-JUL-24' (NLS DD-MON-RR) | FILTER + INDEX RANGE SCAN | 91 | 28,336 |
| — | ORD_DT >= '2024-06-01' (NLS DD-MON-RR) | — | ORA-01861 | — |
| — | ORD_DT >= '01-JUN-24' (NLS YYYY-MM-DD) | — | 1,000,000행 반환 | — |
A-Rows 27,390 · 회귀 검증 rev02 대비 건수차 0 / 금액차 0 · 경계 2024-06-01 ~ 2024-06-30.
금액은 고정되어 있다
생성 스크립트가 난수 시드를 고정하므로 누가 언제 설치해도 금액까지 같다. 6월 합계는 6,982,065,200, 02화
T_ORD경계값 사고의 누락액은 34,159,700이다. 내 화면의 금액이 이와 다르면, 데이터를 이 교재의 생성 스크립트(02_신시스템_스키마B.sql)로 만들었는지부터 확인해야 한다.
9. 원천 좌표
전체 지도는
00_자료준비/06_원서와_공식문서_지도.md에 있다. PDF 페이지 = 책 페이지 − 2 ·성능고도화II_목차색인.md(공개 사이트에는 없음) 이 화의 무대는00_자료준비/고객사/L3_ora-dist/README.md에서 설명한다.
성능고도화 (주 원천)
| 주제 | 권-장-절 |
|---|---|
| 함수 기반 인덱스 · 가상 컬럼 ★ 이 화의 본줄기 | II권 Ch1 04절 |
| 인덱스 사용이 불가능한 경우 (묵시적 형변환 포함) | II권 Ch1 03절 |
| 인덱스 기본 원리 · B*Tree 수직/수평 탐색 | II권 Ch1 01절 |
| 클러스터링 팩터 | II권 Ch1 05절 (→ 04화) |
| 선택도·카디널리티 추정 | II권 Ch3 (→ 10화) |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| 데이터 타입 비교 규칙 (어느 쪽이 변환되는가) | sqlrf | Data Type Comparison Rules |
| 묵시적 형변환 매트릭스 | sqlrf | Data Type Conversion |
| 함수 기반 인덱스 생성·제약 | admin | Managing Indexes |
| 가상 컬럼 | cncpt / admin | Tables and Table Clusters / Managing Tables |
| 뷰 머지와 Predicate Pushing | tgsql | Query Transformations |
NLS_DATE_FORMAT과 날짜 리터럴 | nlspg | Setting Up a Globalization Support Environment |
이 화에서 꼭 확인할 뷰
sql
USER_TAB_COLUMNS -- data_type · data_length (날짜가 정말 문자인가)
USER_TAB_COLS -- ★ 숨은 컬럼(FBI)·가상 컬럼까지 보인다
USER_IND_EXPRESSIONS -- 함수 기반 인덱스의 식 전문
USER_INDEXES -- index_type = 'FUNCTION-BASED NORMAL'
V$MYSTAT / V$STATNAME -- redo size (적재 비용)
V$DIAG_INFO -- Default Trace File (10053)다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 04 | CF10의 CF 9,921(테이블 4,583블록) ↔ 02화 T_ORD_BIG의 CF 1,000,000. 같은 100만 vs 50만인데 왜 이렇게 다른가 |
| 06 | 조인 쿼리에 남은 CF11 FULL 8,075. 03화는 CF10 쪽만 고쳤다 |
| 09 | ora-dist 재방문. SLE_HMS 데이터 교정 승인과 그 뒤의 정렬·페이징 |
| 10 | E-Rows 고정 추정값(0.25% / 1%)과 문자 컬럼 범위 선택도가 부정확한 문제 |
| 14 | 인덱스 1개마다 redo 약 5.7MB (50,000건 기준) |
| 18 | 2019년 표기 규약이 왜 그때는 옳았는가: 판단의 유효기간 |