CH01.인덱스 원리와 활용 - 02. 인덱스 기본 원리

B*Tree 인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프블록까지의 수직적 탐색 과정을 거쳐야 합니다. 만약 인덱스 선두 컬럼이 조건절에 사용되지 않으면 범위 스캔을 위한 시작점을 찾을 수 없어 옵티마이저는 인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 선택합니다. 또한 인덱스 선투 컬럼이 조건절에 사용되어도 인덱스를 사용 못하거나 범위 스캔이 불가능한 경우가 있습니다.

(1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
- 조건절 인덱스 컬럼의 가공.(FBI 정의 하지 않는 경우)
select * from 업체 where substr(업체명,1,2) = ‘대한’

-부정형 비교
select * from 고객 where 직업 <> ‘학생’

-is not null (위와 같이 부정형 비교에 해당)
select * from 사원 where 부서코드 is not null

위의 조건들 모두 정상적인 인덱스 범위 스캔이 불가능할 따름이지 인덱스 사용 자체가 불가능하지는 않습니다. Index Full Scan은 가능합니다.

마지막 is not null의 경우 Index Full Scan을 사용할 수 있습니다. 오라클은 단일 컬럼 인덱스에 null값을 저장하지 않기 때문에 전체 스캔을 하면서 얻은 레코드는 모두 is not null 조건을 만족하게 되는 것입니다. 결합인덱스일때는 구성 컬럼 중 하나라도 null이 아닌 레코드는 인덱스에 포함되지만 필터링을 통해 부서코드 is not null 조건에 해당하는 레코드를 찾을 수 있습니다.

아예 인덱스 사용이 불가능한 경우도 있는데 , is null 조건만으로 검색할 때가 그렇습니다. null 일 경우 인덱스만 뒤져서는 완전한 결과집합을 얻을 수 없기 때문입니다.
select * from 사원 where 연락처 is null

is null 조건을 사용하더라도 다른 인덱스 구성 컬럼에 is null 이외의 조건식이 하나라도 있으면 아래와 같이 Index Range Scan이 가능합니다. (물론 인덱스 선두 컬럼이 조건절에 누락되지 않아야 합니다.) 인덱스 구성 컬럼 중 하나라도 null 값이 아닌 레코드는 인덱스에 저장되기 때문입니다. 참고로, 오라클은 null 값을 맨 뒤에 저장합니다.
create index emp_idx on emp(job, deptno);
select * from emp where job is null and deptno = 20;

(2) 인덱스 컬럼의 가공
아래는 흔한 인덱스 컬럼 가공사례에 대한 튜닝 방안입니다.

튜닝 Tip1
인덱스

일별지수업종별거래및시세_PK : 지수구분코드+지수업종코드+거래일자
튜닝 전 쿼리
where 지수구분코드 || 지수업종코드 in (‘1001’,’2003’) 의 경우에는 인덱스 선두 컬럼을 가공하여서 인덱스를 사용할 수 없게되고 Table Full Scan을 타게 됩니다.
튜닝 후 쿼리
where (지수구분코드,지수업종코드) in ((‘1’,’001’),(‘2’,’003’)) 이런식으로 구성하면 PK 인덱스를 정상적으로 이용할 수 있습니다.

튜닝 Tip2
인덱스
접수정보파일_PK : 수신번호
접수정보파일_X01 : 정정대상접수번호 + 금감원접수번호

튜닝 전 쿼리
where decode(정정대상접수번호, lpad(‘ ’,14),금감원접수번호,정정대상접수번호) = :접수번호
위의 쿼리는 인덱스 컬럼을 가공하는 바람에 Table Full Scan을 타게 됩니다.

튜닝 후 쿼리
where 정정대상접수번호 in (:접수번호,lpad(‘ ‘,14)) and 금감원 접수번호 = decode ( 정정대상접수번오, lpad(‘ ‘,14), :접수번호, 금감원접수번호)
위 decode문을 분석해 논리적으로 동일한 결과를 리턴하도록 아래와 같이 조건절을 재구성해주면 정상적인 인덱스 스캔이 가능해집니다.

(3) 묵시적 형변환
인덱스
월별품목실적_PK : 대상연월 + 영업조직ID + 물품지원품목코드
모든 조인 컬럼의 데이터 타입은 varchar2입니다.

튜닝 전 쿼리
select * from x,y
where y.물품지원품목코드 (+) = x.물품지원품목코드
and y.영업조직id(+) = x.물품원신청조직id
and y.대상연월(+) = substr(x.파트너지원요청일자, 1, 6) -1

튜닝 후 쿼리
NL Outer 조인은 조인순서가 고정돼 항상 Outer 테이블이 먼저 드라이빙됩니다. 위 쿼리는 따라서 x쪽 집합이 먼저 읽히고 y쪽 조인 컬럼에 값을 제공하게 됩니다.
그런데 위 조인절에서 대상연월, 파트너지원요청일자 모두 varchar2컬럼입니다. 즉 x쪽 파트너지원요청일자 컬럼에서 앞 6자리 연월을 취하고 1을 차감하는 과정에서 숫자형으로 묵시적 형변환이 일어나는 것입니다.
그리고 나서 이번에는 y쪽 대상연월 컬럼이 숫자형으로 형변환 됩니다. 왜냐하면 숫자형과 문자형이 비교될 때는 숫자형이 우선시되기 때문입니다. 내부적으로 아래와 같은 쿼리로 변환되서 수행됩니다.
and to_number(y.대상연월(+)) = to_number(x.파트너지원요청일자, 1,6)) -1

튜닝하여 아래와 같이 변경할 수 있습니다.
and y.대상연월(+) = to_char(add_months(to_date(x.파트너지원요청일자, ‘yyyymmdd’), -1), ‘yyyymm’)

묵시적 형변환 사용시 주의사항
숫자형 컬럼(n_col)과 문자형 컬럼(v_col)을 비교하면 문자형 컬럼이 숫자형으로 변환되는데 만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 들어 있으면 쿼리 수행 도중 에러가 발생합니다.
where n_col = v_col
ORA-01722 : 수치가 부적합합니다.
참고로, like로 비교할 때만큼은 아래와 같이 숫자형이 문자형으로 변환되므로 위와 같은 에러는 발생하지 않습니다.
where n_col like v_col || ‘%’ -> where to_char(n_col) like v_col||’%’

에러가 발생하지 않더라도 묵시적 형변환 때문에 결과 오류가 생기는 사례도 있습니다.
오라클이 decode 함수를 처리할 때 내부적으로 사용하는 묵시적 형변환 규칙 때문에 발생할 수 있습니다.
decode(a,b,c,d)를 처리할 때 a=b이면 c를 반환하고 아니면 d를 반환합니다. 이때 출력된 값의 데이터 타입은 세번 째 인자 c에 의해 결정됩니다. 따라서 c가 문자형이고 d가 숫자형이면 내부적으로 d가 문자형으로 변환됩니다. decode 함수가 가진 또 한 가지 규칙은 c가 NULL이면 varchar2로 취급한다는 사실입니다.

묵시적 형변환은 사용자가 코딩을 쉽게 도울 목적으로 DBMS가 제공하는 기능이지만 위의 기능때문에 가급적 이 기능에 의존하지 말 것을 권고합니다.
문자형과 숫자형이 만나면 숫자형,문자형과 날짜형이 만나면 날짜형으로 변하는 등의 데이터 타입간 우선순위 규칙이 존재합니다.

함수기반 인덱스(FBI) 활용
성능 이슈가 발생했는데, 원인이 묵시적 형변환에 있었고 프로그램을 일일이 바꿀 만큼 시간적 여유가 없다면 아래처럼 함수기반 인덱스(FBI)를 이용해 급한 불을 끌수 있습니다. 하지만 권장하는 방법은 아닙니다.
create index emp_x01 on emp(to_number(v_deptno)