[Index] 쿼리 수정으로 성능 향상 시키기 (Full Scan -> Range Scan)

목차

1. like, substr
2. or
3. 산수
4. 부정형
5. 묵시적 형 변환
6. null

---

 

인덱스는 인덱스를 생성했다고 해서 조회 속도가 향상되는 것은 아닙니다.

인덱스로 지정한 컬럼은 B+Tree 구조로 생성되고 선언한 컬럼 순서대로 인덱스 노드가 정렬되어 Leaf 노드를 구성합니다.

조회시 사용된 조건을 토대로 Leaf 노드를 스캔하면서 조건에 부합하는 컬럼을 찾음으로써 빠르게 데이터를 조회할 수 있게 됩니다.

 

어떤 상황에서 인덱스 전체를 스캔(Index Full Scan)하고 어떻게하면 인덱스 범위 스캔(Index Range Scan)으로 바꿀수 있는지 알아보겠습니다.

[1] like, substr

Full Scan

--인덱스 ('부서')
SELECT * FROM 사원
WHERE 부서 LIKE '%캐피탈%'

위와 같은 like 조건절 사용시, 중간 값을 모르기 때문에 모든 인덱스 노드를 스캔해서 중간에 '캐피탈'이라는 단어 포함된 컬럼의 value를 조회하게됩니다.

--인덱스('부서')
SELECT * FROM 사원
WHERE SUBSTR(부서,1,4) = '렌트사업'

위와 같은 substr조건절도 마찬가지로, 입사날짜라는 컬럼의 value가 '2025'라는 값을 포함하는지 알 수 없기 때문에 모든 인덱스 노드를 스캔해서 단어를 조회하게 됩니다.

 

Range Scan

위의 예시와 같이 포함된 단어를 찾고자 할때는 like와 접두어 형식으로 사용하면 인덱스 범위 스캔을 사용할 수 있습니다.

--인덱스('부서')
SELECT * FROM 사원
WHERE 부서 LIKE '렌탈사업%'

접두어를 이용한 like 사용시, 옵티마이저는 시작 단어를 알고 수직 탐색이 가능하기 때문에 인덱스 스캔 시작점을 찾을 수 있습니다.

 

테스트하면서 SUBSTR(부서, 1, 4)는 시작지점부터 자른다음 조회하기 때문에 접두어처럼 사용해서 인덱스 스캔이 타지 않을까? 라는 의문이 있었습니다.

인덱스 사용시 주의할 점은, 인덱스를 타기 위해서는 조건절의 값이 가공되지 않는 값이어야 한다는 점입니다.

인덱스는 가공되기 전 컬럼으로 생성되어 있습니다. 그렇기 때문에 substr과 연산을 수행하게 되면 WHERE절이 수행되기 전에 가공이 일어나 인덱스로 생성한 컬럼으로 인식되지 않아 인덱스를 타지 않게 됩니다.

그렇기 때문에 substr시 like와 접두어 사용으로 인덱스를 활용하면 됩니다.

[2] or

--인덱스('전화번호'), ('고객명')
SELECT * FROM 고객
WHERE 전화번호 =:telNo OR 고객명 =:custNm

위와 같은 쿼리가 발생하는 경우 Full Scan으로 동작합니다.

각 조회 조건에 맞는 인덱스가 존재함에도 불구하고 옵티마이저는 해당 쿼리를 실행시킬때 두 번의 인덱스를 타는 비용 + 결과 병합 비용 > Full Scan비용이라 판단하고 Full Scan이 발생한다고 합니다.

--인덱스('전화번호'), ('고객명')
SELECT /*+ use_concat */ *
FROM 고객
WHERE 전화번호 =:telNo OR 고객명 =:custNm

OR절을 사용할때 인덱스를 태우고자 한다면, use_concat 힌트를 사용하면 Range Scan으로 동작합니다.

use_concat은 or조건을 union all로 변환하여 동작합니다.

옵티마이저가 위 쿼리를 변환한 결과는 아래와 같습니다.

SELECT * FROM 고객
WHERE 전화번호 =:telNo

UNION ALL

SELECT * FROM 고객
WHERE 고객명 =: custNm

 

한 가지 혼란스러웠던 점은 or조건만 사용했을때 옵티마이저는 두 번의 인덱스를 타는 비용 + 결과 병합 비용이 Full Scan비용보다 비효율적이라 판단하고 Full Scan을 선택한다고 했습니다. 그렇다면 union all도 결국 각 쿼리로 실행하고 결과를 병합하는 결과이기 때문에 동일한 동작이기 때문에 옵티마이저가 union all을 판단하지 못하는 이유가 의아했습니다.

그 이유는 각 쿼리는 옵티마이저의 실행 계획 최적화 방식이 다른 점이 었습니다.

or조건만 사용하는 경우 쿼리는 하나의 SELECT 블록 구조이고 각 인덱스를 가지고 있는 조건을 통합하고 1개의 실행 계획을 세워야하기 때문에 옵티마이저는 Full Scan이 더 효율적이라 판단합니다.

use_concat을 사용하는 경우 쿼리는 두개의 SELECT 블록 구조이기 때문에 각 블록에서 실행 계획을 생성하기에 인덱스 사용여부를 독립적으로 판단하여 인덱스를 타도록 판단하게 되는 거였습니다.

 

즉, 두 컬럼에서는 옵티마이저가 실행 계획 최적화를 어떻게 판단하게끔 만드느냐의 차이었습니다.

[3] 산수

--인덱스('월급')
SELECT * FROM 사원
WHERE 월급 * 12 = 50000000

앞의 like, substr에서 설명했듯이 컬럼 value를 가공하는 경우 인덱스를 타지 못합니다. 그렇기 때문에 위의 쿼리와 같이 컬럼 value에 연산자를 이용하게 되면 가공이 되어 비교가 되기 때문에 인덱스가 타지않고 Full Scan으로 동작하게 됩니다.

 

--인덱스('월급')
SELECT * FROM 사원
WHERE 월급 = 50000000/12

위와 같이 인덱스를 태우기 위한 컬럼을 가공하지 않도록 하면 Range Scan으로 동작하게 됩니다.

[4] 부정형

--인덱스('사번')
SELECT * FROM 사원
WHERE 사번 <> '201'

인덱스는 부정형을 사용하는 경우 해당 값이 아닌 경우를 모두 찾아보기 위해 Full Scan으로 동작하게 됩니다.

--인덱스('사번')
SELECT * FROM 사원
WHERE 사번 IN ('202', '203')

부정형이 아닌 그 외의 값을 IN절로 사용한다면 Range Scan으로 동작하게 됩니다.

물론 INLIST에 너무 많은 값을 사용하게 되면 Full Scan과 다를바가 없습니다.

[5] 묵시적 형 변환

형변환이란 쿼리가 실행될때 서로 다른 타입과 비교 및 함수를 사용했을때 우선순위와 함수 타입에 맞게 옵티마이저가 실행시 값의 형변환을 시킨 후 쿼리를 실행하게 됩니다.

 

옵티마이저는 어떻게 형변환을 시키는지 알아보겠습니다.

형 변환 처리

• WHERE 문자 = 숫자
  정수형(숫자)이 문자형보다 우선순위가 높아 문자를 숫자로 형 변환.
• WHERE 문자 LIKE '숫자%'
  Like는 문자열 비교 연산자이기 때문에 숫자를 문자로 형 변환.
• WHERE 문자 = 날짜
  문자를 날짜로 형변환
• WHERE 날짜 = 숫자
  에러

--인덱스('고객번호')
--고객번호 타입 : 문자형
SELECT * FROM 고객
WHERE 고객번호 = 100

고객번호 컬럼은 문자형이기 때문에 고객번호 컬럼 value는 문자형에서 정수형으로 형변환 되면서 가공이 됩니다. 그렇기 때문에 인덱스를 타지 않게 되고 Full Scan으로 동작합니다.

--인덱스 ('고객번호')
--고객번호 타입 : 문자형
SELECT * FROM 고객
WHERE 고객번호 = '100'

인덱스인 고객번호 컬럼 value가 형변환이 일어나지 않도록 같은 문자형(문자형 = 문자형)으로 비교를 해줌으로써 인덱스가 타게 되고 Range Scan으로 동작하게 됩니다.

[6] null

Oracle은 인덱스 구조 구성시 null데이터는 인덱스에 저장하지 않습니다.(Leaf 블럭에 해당 데이터 row를 저장하지 않음)

--인덱스('코드')
SELECT * FROM 고객
WHERE 코드 IS NULL

SELECT * FROM 고객
WHERE 코드 IS NOT NULL

null값은 인덱스에 저장하지 않기 때문에 Full Scan으로 동작합니다.

--인덱스(NVL('코드','unknown'))
SELECT * FROM 고객
WHERE NVL(코드, 'unknown') = 'unknown'

 

 

CREATE INDEX 고객_코드_FUNC_IDX ON 고객(NVL(코드, 'unknown')) 과 같은 함수기반 인덱스를 생성한 경우 NVL을 통해 인덱스를 타게 할 수 있습니다.

함수기반 인덱스를 생성하지 않고 NVL을 사용하는 경우 옵티마이저는 함수가 적용된 컬럼은 인덱스를 탈 수 없다고 판단하기 때문입니다.

---

참고

https://maybetoday.tistory.com/entry/ORACLE-OR-expansion%EA%B3%BC-USECONCAT-NOEXPAND-%ED%9E%8C%ED%8A%B8