Where 조건 절에서 Between 과 in 의 차이

조시형님의 친절한 SQL 튜닝을 읽고 개인적으로 공부한 내용을 정리한 글입니다.

---

Between 과 In 조건절의 인덱스 스캔 범위 비교해보기

 

위와 같이, DEPTNO 와 JOB 으로 인덱스를 구성했다고 가정해보자.

 

SELECT
	*
FROM
	TABLE
WHERE
	DEPTNO BETWEEN 10 AND 30
AND
	JOB = 'B'

위와 같은 쿼리가 실행되면 어떻게 될까?

 

 

먼저, DEPTNO가 10이면서 JOB이 'B' 인 레코드에서 탐색을 시작하여

 

DEPTNO 가 30이면서 JOB이 'B'가 아니게 되는 레코드 까지 탐색할 것이다.

 

수직적 탐색은 한번 이루어지지만, 수평적 탐색이 많은 레코드를 탐색하게된다.

 

반면에,

 

SELECT
	*
FROM
	TABLE
WHERE
	DEPTNO IN (10,20,30)
AND
	JOB = 'B'

 

❓ 위와 같은 쿼리를 작성하면 어떻게 될까?

 

💡 IN 조건을 사용하면 옵티마이저는 쿼리를 여러개로 분리한 뒤, UNION 하여 실행시킨다고 하였다.

 

왜냐하면, OR 로 연결되어 있는 경우 인덱스 스캔 시작점을 찾을 수 없으므로 인덱스 스캔이 불가능하기 때문이다.

 

따라서,

 

SELECT * FROM TABLE WHERE DEPTNO = 10 AND JOB = 'B'
UNION ALL
SELECT * FROM TABLE WHERE DEPTNO = 20 AND JOB = 'B'
UNION ALL
SELECT * FROM TABLE WHERE DEPTNO = 30 AND JOB = 'B'

 

위와 같은 쿼리를 처리하듯이, 실행계획을 세운다.

 

따라서, 스캔 범위는

 

위와 같이 처리하게 되어, 인덱스 스캔 효율을 향상시킬 수 있게된다.

 

따라서, 쓸모없는 레코드를 읽는 비효율을 피할 수 있게된다.

 

❓ 그렇다면, BETWEEN 을 IN 조건으로 바꾸면 항상 효율적일까?

🚨 그렇지 않다.

만약, IN 에 들어가는 파라미터가 너무 많아지면, 수직적 탐색이 더욱 많아진다.

 

따라서, 조건을 만족하는 레코드가 서로 많이 떨어져 있는 경우는

 

중간에 필요없는 레코드들을 생략하고 수직적 탐색으로 이동하는게 효율적이지만

 

두 레코드 간 사이가 가깝다면, IN 방법이 더 비효율적일 수 있다.

 

💡 따라서, 데이터 분포나 수직적 탐색 비용을 따져보고 결정해야한다.

 

✍🏻 in 조건절을 사용하는데 인덱스가 여러개이고, 원하는 인덱스까지 엑세스 조건으로 사용하고 싶은 경우 인덱스 힌트를 사용하면 된다.

num_index_keys ( table index num ) : num번째 인덱스까지 인덱스 엑세스 조건으로 사용한다.

그 이후, 인덱스는 필터 조건으로 사용되게된다.

💡 즉, 인덱스 엑세스 조건으로 사용되는 인덱스는 수직적 탐색을 한다는 의미이다.

따라서, in 조건절을 사용할 때,

in 에 이용되는 파라미터 비교 대상이 많거나 선두 인덱스 간 거리가 크지 않을 때에는

필터 조건으로 수평 탐색하는게 효율적이므로 인덱스 엑세스 조건에 제한을 두는 것이 효율적일 수 있다.