인덱스 스캔 효율화

인덱스 스캔 효율성을 설명하기에 앞서 이해해야 할 용어 두가지가 있다.

바로 '액세스 조건','필터 조건'이다.

인덱스 액세스 조건 : 인덱스 스캔 범위를 결정하는 조건절, 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캐하다가 어디서 멈출지를 결정하는 데 열향을 미치는 조건절
인덱스 필터 조건 : 테이블로의 액세스할지를 결정하는 조건절, 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

 

* 테이블과 달리 인덱스에는 '같은 값'을 갖는 레코드들이 서로 군집해 있다. '같은 값'을 찾을 때 '='연산자를 사용하므로 인덱스 컬럼을 앞쪽부터 누락없이 '='연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여 있다.

┖  선행컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

┖  인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치(=)조건으로 사용할 때 가장 좋다. (리프 블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 인덱스 스캔 단계에서의 비효율은 전혀 없다)

인덱스를 "아파트시세코드 + 평형 + 평형타입 + 인터넷매물"순으로 구성, 조건절 모두 인덱스 액세스 조건을 사용된다.

인덱스를 "아파트시세코드 + 평형 + 평형타입 + 인터넷매물"순으로 구성

┖ 인터넷 매물이 BETWEEN조건이지만 선행컬럼들이 모두 '='조건이기 때문에 전혀 비효율없이 조건을 만족하는 데이터를 찾는다.

┖ 인덱스 선행 컬럼이 모두 '='조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.

┖ 하지만 범위검색 컬럼이 맨 뒤에 있지 않은 경우가 대다수이다. 이럴 때는 BETWEEN조건을 아래와 같은 IN-List로 바꿔주면 큰 효과를 얻을 수 있다.

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 in ('1','2','3')
and 아파트시세코드='A01011350900056'
and 평형='59'
and 평형타입='A'
order by 입력일 desc

인덱스 수직적 탐색이 세번 발생

┖ IN-List 개수만큼 UNION ALL 브랜치가 생서오디고 각 브랜치마다 모든 컬럼을 '='조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다.

 

*주의사항

┖ IN-List 개수가 많지 않아야 한다. 많으면 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다.

┖ 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.

 

[ BETWEEN 과 LIKE 스캔 범위 비교 ]

월별로 집계된 테이블에서 2019년 1월부터 12월 데이터를 조회하고자 할 때

# LIKE 사용
select * from 월별고객별판매집계
where 판매월 like '2019%'

# BETWEEN사용
select * from 월별고객별판매집계
where 판매월 between '201901' and '201912'

개발자들이 LIKE연산을 더 선호하지만 LIKE보다 BETWEEN을 사용하는게 더 낫다.

예를들어, 인덱스를 '판매월 + 판매구분' 순으로 구성하고, 'A','B'값이 각각 90% 10% 비중을 차지하는 상황에서 두 조건절을 비교해보자

where 판매월 BETWEEN '201901' and '201912'
and 판매구분 = 'B'

where 판매월 LIKE '2019%'
and 판매구분 = 'B'

혹시라도 판매월이 201900이 저장돼 있다면  LIKE는 그 값도 읽어야하므로 인덱스 스캔량이 비효율적이 된다.

 

[ PL/SQL 함수의 성능적 특성 ]

PL/SQL 사용자 정의 함수는 개발자들이 일반적으로 생각하는 것보다 매우 느리다.

느린데는 아래 3가지 이유가 있다.

1) 가상머신(VM)상에서 실행되는 인터프리터 언어

2) 호출 시마다 컨텍스트 스위칠 발생

3) 내장 SQL에 대한 Recursive call 발생

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝