인덱스 사용을 방해하는 조건

#핵심 요약

Index Range Scan의 첫째 조건은 인덱스 선두 컬럼이 가공되지 않은 채 조건절에 있는 것임
선두 컬럼이 없거나 가공되면 스캔 시작점을 찾을 수 없음

함수 기반 인덱스가 아니면 인덱스는 컬럼 값을 담을 뿐이므로, 인덱스 컬럼에 함수를 적용한 조건은 인덱스 사용을 막음
문자 컬럼을 숫자와 비교하면 암시적 TO_NUMBER가 컬럼에 적용됨

결합 인덱스 두 번째 이후 컬럼이 가공되면 Range Scan은 가능하지만 스캔 범위를 줄이지 못해 비용이 높을 수 있음

집계 함수 안에서 인덱스 컬럼을 가공하면(MAX(TO_NUMBER(col))) 인덱스에서 한 건만 읽는 FIRST ROW 최적화가 사라짐
가공은 집계 함수 밖에서 함

날짜 컬럼과 문자열을 비교할 때 세션의 날짜 형식에 기대지 말고 TO_DATE로 명시함

#개념과 동작 원리

함수 기반 인덱스가 아니면 데이터베이스는 함수를 적용한 값이 아니라 컬럼 값을 인덱싱함

문자 컬럼 char_col을 WHERE char_col=1로 조회하면 상수 1에 암시적으로 TO_NUMBER를 적용하는데, 이것이 인덱스 사용을 막는 흔한 실수라고 공식 문서는 적음

조건이 B-tree 인덱스 선두 컬럼을 쓰지 않으면 인덱스를 고르지 않을 수 있고, Index Skip Scan을 고를 수는 있음

공식 문서 문장이 "상수 1에 TO_NUMBER를 적용한다"로 되어 있어, 설명(숫자와 문자를 비교하면 문자 컬럼 쪽이 숫자로 바뀜)과 표현이 다름
어느 쪽에 변환이 적용되는지는 공식 문서의 데이터 타입 변환 규칙과 대조하지 않았음

#필수 명령어 또는 SQL

#결합 인덱스 선두 컬럼이 없거나 가공된 경우

CREATE INDEX emp_job_ename ON emp (job, ename);

-- 선두 컬럼 없음: Full Table Scan 또는 Index Skip Scan (답)
SELECT ename, sal, job FROM emp WHERE ename = 'MARTIN';

-- 선두 컬럼 가공
SELECT ename, sal, job FROM emp
 WHERE SUBSTR(job, 2, 4) = 'ALES' AND ename = 'MARTIN';

-- 두 번째 컬럼 가공: Range Scan은 가능하나 비용이 높을 수 있음 
SELECT ename, sal, job FROM emp
 WHERE job = 'SALESMAN' AND SUBSTR(ename, 2, 5) = 'ARTIN';

결과 해석:

실습에서는 이 실습 중 DBMS_STATS.DELETE_TABLE_STATS로 통계를 지우고 ALTER SYSTEM FLUSH SHARED_POOL로 공유 풀을 비웠음
공유 풀 비우기는 인스턴스 전체의 파싱된 SQL을 지우므로 실습 DB에서만 씀
연습 문제(ename LIKE '%TI%'로 두 번째 컬럼 중간 검색)의 답은 기재 없음

#LIKE로 가공 없애기

-- 튜닝 전
SELECT * FROM 주문상품
 WHERE 주문일자 = TO_DATE('2026-06-01', 'RRRR-MM-DD')
   AND SUBSTR(상품번호, 1, 4) = 'PING';

-- 튜닝 후
SELECT * FROM 주문상품
 WHERE 주문일자 = TO_DATE('2026-06-01', 'RRRR-MM-DD')
   AND 상품번호 LIKE 'PING%';

결과 해석:

인덱스는 주문상품_N1 (주문일자, 상품번호)임
실습 환경 준비 스크립트는 DROP TABLE IF EXISTS 주문상품;으로 시작함
Oracle Database 19c에서는 19.28부터 DDL의 IF EXISTS를 지원하며 DROP TABLE도 대상에 포함됨
19.28보다 이전 RU의 구문 지원 여부는 별도 확인이 필요함
실습의 세부 RU와 해당 문장의 실제 실행 결과는 확인 불가
설명: 대한%처럼 앞부분이 고정된 LIKE는 값이 인덱스의 한 구간에 모여 있어 시작 지점을 찾을 수 있음

#집계 함수 안의 가공

CREATE INDEX emp_job_deptno_sal ON emp (job, deptno, sal);

SELECT MAX(sal) FROM emp WHERE job = 'SALESMAN' AND deptno = 30;            -- FIRST ROW 나옴
SELECT MAX(TO_NUMBER(sal)) FROM emp WHERE job = 'SALESMAN' AND deptno = 30; -- FIRST ROW 사라짐
SELECT TO_NUMBER(MAX(sal)) FROM emp WHERE job = 'SALESMAN' AND deptno = 30; -- 튜닝 후

결과 해석:

설명: 실행계획의 SORT (AGGREGATE)는 이름에 SORT가 붙었지만 실제로 행을 정렬하지 않고 집계 결과 한 건을 만드는 단계임

실습 실무 예(PK 장비번호 + 변경일자 + 변경순번, 변경순번은 6자리 0 채움 문자열):

-- 튜닝 전
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
  FROM 상태변경이력
 WHERE 장비번호 = 'A001' AND 변경일자 = '20180316';

-- 튜닝 후(답)
SELECT NVL(MAX(변경순번), '000000')
  FROM 상태변경이력
 WHERE 장비번호 = 'A001' AND 변경일자 = '20180316';

결과 해석: 6자리 0 채움이라 문자열 순서와 숫자 순서가 같으므로 함수를 빼도 결과가 같음
0 채움이 없으면('9'와 '10') 문자열 최댓값과 숫자 최댓값이 달라지므로 데이터 형식을 먼저 확인해야 함
실습 해설의 튜닝 전 SQL은 장비번호 = '20180316'으로 잘못 옮겨 적혀 있음
퀴즈 답에 따르면 MAX(변경일자 || 변경순번)처럼 두 컬럼을 연결해도 가공이므로 FIRST ROW를 쓸 수 없음

#자동 형변환

-- 숫자 컬럼에 LIKE: 컬럼이 문자로 바뀌어 인덱스를 못 씀
SELECT ename, sal FROM emp WHERE sal LIKE '30%';

-- 문자 컬럼을 숫자와 비교
SELECT * FROM emp05 WHERE sal = '3000';   -- 문자 = 문자
SELECT * FROM emp05 WHERE sal = 3000;     -- 문자 = 숫자

결과 해석:

emp05는 수업 자료가 sal을 VARCHAR2(10)로 만든 실습 테이블임
실습에서는 두 번째 SQL의 계획을 EXPLAIN PLAN과 DBMS_XPLAN.DISPLAY로 확인했음

-- 날짜 컬럼 = 문자: 세션 NLS_DATE_FORMAT이 맞으면 문자가 날짜로 바뀜
SELECT * FROM emp WHERE hiredate = '81/11/17';

-- 권장
SELECT * FROM emp WHERE hiredate = TO_DATE('81/11/17', 'RR/MM/DD');

-- 실무 문제
SELECT * FROM 고객 WHERE 생년월일 = 19821225;     -- 튜닝 전 (생년월일은 VARCHAR2(8))
SELECT * FROM 고객 WHERE 생년월일 = '19821225';   -- 튜닝 후

결과 해석:

이 변환은 고객번호가 모두 7자리일 때만 같은 결과를 냄(9410이나 8자리 94100000은 LIKE에만 걸림)
실습 샘플 데이터는 7자리뿐임

#DECODE와 NULL의 타입

SELECT MAX(DECODE(job, 'PRESIDENT', NULL, sal)) FROM emp;            -- 문자 비교로 최댓값
SELECT MAX(DECODE(job, 'PRESIDENT', TO_NUMBER(NULL), sal)) FROM emp; -- 숫자 비교

결과 해석:

설명: DECODE의 세 번째 인자가 문자형으로 인식되는 NULL이면 결과 전체가 문자형이 되어 최댓값이 문자 순서로 계산됨
실습에서는 이것을 금융권에서 실제 문제가 된 사례로 소개했음