인덱스 컬럼은 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

인덱스를 정상적으로 사용

• 리프 블록에서 스캔 시작점을 찾아 스캔을 시작해서 중간에 멈출 수 있음을 의미
• Index Range Scan을 의미

인덱스를 가공하면 중간에 스캔을 멈출 수 없어서 일부가 아닌 전체를 스캔하게 됨. (Index Full Scan)

인덱스 가공하면 Range Scan을 못 쓰는 이유

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

일정 범위를 스캔하려면 ‘시작지점’과 ‘끝지점’이 있어야 한다.

인덱스 가공

-- 생년월일 인덱스 (Index Range Scan)
where 생년월일 between '20070101' and '20070131'

-- 인덱스를 가공 (Index Range Scan) 못 씀.
where substr(생년월일, 5, 2) = '05'

-- Index Range Scan 사용 불가
where nvl(주문수량, 0) < 100

위와 같이 인덱스를 가공하면 년도 순으로 정렬된 인덱스에 5월을 기준으로 찾아야 하니 띄엄띄엄 찾아야한다. 이것은 시작지점과 끝지점을 찾을수 없게된다.

그래서 Index Full Scan을 하게 된다.

Like 문을 사용

-- 업체명이 인덱스
where 업체명 like '%대한%'

LIKE로 검색해도 위와 같이 ‘대한’이라는 글자를 가진 인덱스를 중간 글씨에서 찾아야 하기 때문에, Range Scan이 불가능하다.

where 업체명 like '대한%' 은 Index Range Scan이 가능하다.

OR 조건

where (전화번호 = :tel_no OR 고객명 = :cust_nm) -- Range Scan 안됨

-- OR Expansion (Index Range Scan 사용 가능)
select * from 고객 where 고객명 = :cust_nm
union all
select * from 고객
where 전화번호 = :tel_no and (고객명 <> :cust_nm OR 고객명 is null)

OR 조건으로 검색할떄, 수직적 탐색을 통해 전화번호 ‘01012345678’이거나 고객명이 ‘홍길동’인 시작지점을 찾을 수 없다.

하지만 union all을 사용하면 각 쿼리당 다른 브랜치 블록을 타기 때문에, Index Range Scan 가능

IN 조건

where 전화번호 IN (:tel_no1, :tel_no2) -- Range Scan 안됨

-- Index Range Scan 사용 가능
select * from 고객 where 전화번호 = :tel_no1
union all
select * from 고객 where 전화번호 = :tel_no2

IN조건 또한 OR의 다른 표현이기 때문에, OR과 마찬가지로 Index Range Scan이 안된다.

하지만 union all을 사용하면 각 쿼리당 다른 브랜치 블록을 타기 때문에, Index Range Scan 가능

IN조건절에는 옵티마이저가 IN-List Iterator를 사용한다.
IN-List 갯수만큼 Index Range Scan을 반복한다.
그래서 UNION ALL으로 변환한 것과 같은 효과를 얻는다.

더 중요한 인덱스 사용 조건

만약 인덱스가 [소속팀 + 사원명 + 연령] 순으로 되어 있는데, 사원명으로 스캔하면 range scan이 될까?

답은 아니다. range scan을 하기 위해선 가장 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다.

반대로 말하면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 range scan이 무조건 가능하다.

인덱스를 이용한 소트 연산 생략

[장비번호 + 변경일자 + 변경순번] 등으로 구성한 인덱스에 아래와 같은 쿼리를 실행한다.

select *
from 상태 변경 이력
where 장비번호 = 'C'
	and 변경일자 = '20180316'
order by 변경순번

위의 쿼리는 인덱스 순으로 이미 정렬된 상태로 order by를 했다. 이것은 order by를 생략할 수 있다.

만약 이렇게 인덱스가 구성되어있지 않다면 SORT ORDER BY 연산이 실행계획에 추가된다.

select *
from 상태 변경 이력
where 장비번호 = 'C'
	and 변경일자 = '20180316'
order by 변경순번 desc

위의 쿼리처럼 desc를 사용하면 Index Range Scan Descending 을 실행한다.

Order by 절에서 컬럼 가공

조건절 뿐만 아니라 ORDER BY절이나 SELECT-LIST 에서 컬럼을 가공하여 인덱스를 정상적으로 못 사용하는 경우가 있다.

-- Index Range Scan 가능
select * 
from 상테변경이력 
where 장비번호 = 'C'
order by 변경일자, 변경순번

-- Index Range Scan 불가능 (ORDER BY 가공)
select * 
from 상테변경이력 
where 장비번호 = 'C'
order by 변경일자 || 변경순번

두번째 쿼리는 order by가 가공되었기 때문에 인덱스를 정상적으로 사용할 수 없다.

그리고, select 절에서 가공된 인덱스를 서브쿼리로 만들어 사용해도 인덱스를 정상적으로 사용할 수 없다.

SELECT-LIST 에서 컬럼 가공

select NVL(MAX(TO_NUMBER(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
	and 변경일자 = '20180316'

위와 같이 ‘변경순번’의 값을 숫자로 바꾸면 인덱스를 사용할 수 없다.

왜냐면 인덱스는 문자열 기준으로 정렬되어 있는데 숫자값으로 바꾸었기 때문이다.

select NVL(TO_NUMBER(MAX(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
	and 변경일자 = '20180316'

위와 같이 변경하면 인덱스를 사용하여 변경순번의 MAX값을 찾고 NUMBER로 변경하니 성능저하가 없다.

자동 형변환

인덱스 가공도 안했는데 가끔 옵티마이저가 테이블 전체 스캔하는 경우 있음.

그 이유는 자동 형변환 때문이다.

select * from 고객 where 생년월일 = 19920920

-- 이걸 옵티마이저가 이렇게 바꿈
select * from 고객 where TO_NUMBER(생년월일) = 19920920

조건을 찾기 위해 생년월일을 입력값과 같은 number로 형변환 했다.

DBMS마다 다른데 오라클은 자동 형변환 하는 방식을 채택한다. Postgresql은 에러를 내려줄거 같다.

형변환 우선순위

• 오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다.
• 날짜형과 문자형이 만나면 날짜형이 이긴다.
• 근데 LIKE가 출동하면 문자형이 이긴다. select * from 고객 where 고객번호 like '9410%'

인덱스 컬럼 기준으로 형변환 잘하자

형변환 당하지 않게 들어오는 값이 컬럼과 다른 값이 들어오지 않게 형변환을 잘 해야 인덱스를 정상적으로 사용할 수 있다.

인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 TO_DATE, TO_NUMBER, TO_CHAR 등을 사용하여 정확히 형변환하자.