인덱스를 만들었는데 실행계획에 FULL SCAN이 찍히는 경우가 있다. 인덱스가 없어서가 아니라 쿼리가 그 인덱스를 쓸 수 없는 모양이어서인 경우가 대부분이다. 자주 나오는 다섯 가지를 정리한다.
1. WHERE 절에서 컬럼을 가공했다
인덱스는 컬럼의 원래 값을 정렬해 둔 자료구조다. 컬럼에 함수를 씌우거나 연산을 하면 그 결과값은 인덱스에 없는 값이므로 인덱스를 탈 수 없다.
-- 인덱스를 못 탄다
select * from orders where to_char(ord_dt, 'YYYYMMDD') = '20261006';
select * from orders where substr(cust_no, 1, 4) = '1234';
select * from orders where amt * 1.1 > 100000;해결은 컬럼을 그대로 두고 반대편을 가공하는 것이다.
-- 인덱스를 탄다
select * from orders
where ord_dt >= to_date('20261006','YYYYMMDD')
and ord_dt < to_date('20261007','YYYYMMDD');
select * from orders where amt > 100000 / 1.1;날짜 컬럼에 시분초가 들어 있는 경우 TRUNC를 쓰고 싶어지는데, 위처럼 범위 조건으로 바꾸는 편이 낫다. 가공이 불가피하면 함수 기반 인덱스를 만드는 방법도 있다.
create index ix_orders_dt on orders (to_char(ord_dt, 'YYYYMMDD'));다만 함수 기반 인덱스는 쿼리에 쓴 표현식과 문자 단위로 일치해야 쓰인다. 포맷 문자열이 조금만 달라도 안 탄다.
2. 암묵적 형변환이 일어났다
이건 눈에 안 보여서 더 까다롭다. 컬럼과 비교값의 자료형이 다르면 오라클이 알아서 맞춰 주는데, 그 과정에서 컬럼 쪽이 변환되면 1번과 같은 상황이 된다.
-- cust_no가 varchar2인데 숫자로 비교한 경우
select * from orders where cust_no = 1234;
-- 오라클 내부에서는 이렇게 처리된다
select * from orders where to_number(cust_no) = 1234;문자와 숫자를 비교하면 오라클은 문자 쪽을 숫자로 바꾼다. 즉 컬럼이 가공된다. 반대로 숫자 컬럼에 문자를 비교하면 비교값이 변환되므로 인덱스를 탄다.
실행계획의 Predicate Information에 쓰지도 않은 TO_NUMBER나 INTERNAL_FUNCTION이 보이면 이 경우다. 애플리케이션에서 바인드 타입을 잘못 넘길 때도 똑같이 발생하므로, 자료형을 맞춰서 넘기는 것이 해법이다.
3. 복합 인덱스의 선두 컬럼을 안 썼다
복합 인덱스는 전화번호부와 같다. 성과 이름 순으로 정렬돼 있으면 성을 알 때는 빠르게 찾지만, 이름만 알고 찾으려면 처음부터 끝까지 봐야 한다.
create index ix_ord_01 on orders (cust_id, ord_dt, status);
-- 선두 컬럼이 없으니 효율이 떨어진다
select * from orders where ord_dt = date '2026-10-06';이 경우 인덱스를 아예 못 타는 건 아니다. 오라클은 인덱스 전체를 훑는 INDEX FULL SCAN이나 INDEX SKIP SCAN을 선택할 수 있다. 다만 선두 컬럼으로 범위를 좁힌 것과는 비교가 안 된다. 특히 스킵 스캔은 선두 컬럼의 값 종류가 아주 적을 때만 효과가 있다.
또 하나, 범위 조건 뒤의 컬럼은 범위를 좁히는 데 쓰이지 못한다. 등치 조건을 앞에, 범위 조건을 뒤에 두는 것이 복합 인덱스 설계의 기본이다.
4. 부정 조건이나 앞이 열린 LIKE를 썼다
select * from orders where status != 'C';
select * from orders where status not in ('C','X');
select * from orders where cust_nm like '%김%';인덱스는 “이 값부터 저 값까지”를 찾는 데 최적화돼 있다. “이 값이 아닌 것 전부”는 결국 대부분의 행을 읽으라는 뜻이라 인덱스로 얻을 게 없다.
LIKE도 마찬가지다. 앞부분이 고정된 like '김%'는 인덱스를 타지만, 앞에 와일드카드가 붙으면 시작 지점을 특정할 수 없어 못 탄다. 중간 검색이 꼭 필요하다면 일반 인덱스가 아니라 텍스트 검색 인덱스를 검토해야 한다.
부정 조건은 가능하면 긍정 조건으로 바꿔 쓴다. 상태 코드가 몇 개 안 된다면 status in ('P','E','W')가 훨씬 낫다.
5. NULL을 찾고 있다
오라클의 일반 B-tree 인덱스는 모든 키 컬럼이 NULL인 행을 저장하지 않는다. 그래서 IS NULL 조건은 단일 컬럼 인덱스로 찾을 수 없다.
select * from orders where close_dt is null;미처리 건을 찾는 조건으로 흔히 쓰이는 모양인데, 정작 인덱스가 안 먹는다. 우회 방법은 두 가지다. 상수를 붙여 복합 인덱스로 만들거나,
create index ix_orders_close on orders (close_dt, 1);아예 NULL 대신 의미 있는 값을 쓰도록 설계를 바꾸는 것이다. 미처리를 NULL이 아니라 'N'으로 표시하면 인덱스도 잘 타고 조건도 명확해진다. NOT NULL 컬럼으로 만들 수 있으면 그쪽이 낫다.
옵티마이저가 일부러 안 쓰는 경우
위 다섯 가지에 해당하지 않는데도 안 타는 경우가 있다. 이때는 오라클이 인덱스를 쓰는 게 더 느리다고 판단한 것이다.
인덱스로 찾은 행은 테이블에서 한 건씩 다시 읽어야 한다. 전체의 10~20%를 넘게 읽어야 한다면 그냥 테이블을 순서대로 읽는 편이 빠르다. 이 판단은 대개 옳다.
판단이 틀리는 건 통계정보가 실제 데이터와 어긋났을 때다. 이 경우 인덱스를 새로 만들 게 아니라 통계부터 다시 수집한다.
begin
dbms_stats.gather_table_stats(user, 'ORDERS', cascade => true);
end;
/정리
“인덱스를 걸었는데 안 탄다”는 말의 대부분은 인덱스 문제가 아니라 조건식 문제다. 실행계획을 열고 Predicate Information을 보면 내 조건이 access에 있는지 filter에 있는지, 쓰지도 않은 변환 함수가 붙어 있는지가 바로 드러난다.
순서로 정리하면 이렇다. 먼저 컬럼이 가공됐는지 본다. 다음으로 자료형이 맞는지 본다. 그다음 복합 인덱스라면 선두 컬럼을 썼는지 본다. 여기까지 문제가 없으면 옵티마이저의 판단이고, 그때는 통계정보를 확인할 차례다.