2.2 인덱스 기본 사용법

2.2.1 인덱스를 사용한다는 것

인덱스 기본 사용법은 인덱스를 Range Scan 하는 방법을 의미한다. 인덱스 컬럼(정확히 선두컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다. 정상적으로 사용하지 못할 경우 전체를 스캔하는 Index Full Scan 방식으로 작동한다.

---

2.2.2 인덱스 가공 시 Range Scan 할 수 없는 이유

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

<인덱스 스캔 시작점을 찾을 수 없는 경우>

• where substr(생년월일, 5, 2) = '05'
• where nvl(주문수량, 0) < 100
• where 업체명 like '%대한%'

<옵티마이저의 쿼리변환 기능을 통해 Index Range Scan으로 처리되기도 하는 경우>

• where (전화번호 = :tel_no OR 고객명 = :cust_nm)
  : OR Expansion 을 통해 옵티마이저가 OR 조건식을 union all로 변환하여 시작첨을 찾아 수행할 수 있다.
• where 전화번호 in ( :tel_no1, :tel_no2)
  : IN-List Iterator 방식을 통해 IN-List 개수만큼 index Range Scan을 반복하여 시작점을 찾아 수행할 수 있다.

---

2.2.3 인덱스 사용 조건

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다 는 것이다. 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다. 문제는, 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은 건 아니라는 사실이다. 자세한 건 3장 3절에서 살펴보자.

---

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

인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문이다.
PK인덱스가 <장비번호, 변경일자, 변경순번> 으로 구성돼 있을 경우

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20220222'
-- ORDER BY 변경순번 은 생략되어짐.

위 쿼리문의 실행계획에서 ORDER BY가 생략된다. 장비번호, 변경일자가 '=' 조건으로 검색되면 결과집합은 자동으로 변경순번으로 출력되기 때문이다. 인덱스 리프 블록은 양방향 연결 리스트 구조이기때문에 당연히 내림차순(Desc)도 가능하다.

---

2.2.5 ORDER BY 절에서 컬럼 가공

조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.

SELECT * 
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번

---

2.2.6 SELECT-LIST에서 컬럼 가공

인덱스가 <장비번호+변경일자+변경순번>인 경우

SELECT    NVL(MAX(TO_NUMBER(변경순번)),0)
FROM    상태변경이력
WHERE    장비번호 = 'C'
AND        변경일자 = '20180316'

위와 같은 쿼리문은 변경순번을 가공한 것의 MAX 값이기 때문에 정렬연산을 수행할 수 없다.

SELECT    NVL(TO_NUMBER(MAX(변경순번)),0)
FROM    상태변경이력
WHERE    장비번호 = 'C'
AND        변경일자 = '20180316'

위와 같이 변경순번의 MAX값을 먼저 구한 후 변환한다면 정렬연산 생략으로 쉽게 찾을 수 있다. 물론 변경순번이 숫자형이 아닐때의 가정이다.
(또 다른 예는 102 page 참고)

---

2.2.7 자동 형변환

SELECT * FROM 고객
WHERE    생년월일 = 19920627

위 쿼리문의 경우 실행계획을 보면

filter(TO_NUMBER("생년월일")= 19920627

처럼 인덱스 컬럼이 가공되어 인덱스 Range Scan을 탈 수 없게 되고 테이블 전체 스캔을 하게 된다. 문자형인 생년월일이 숫자형과 비교 됐기 때문이다.
오라클에서 숫자형->문자형 이고 날짜형->문자형 으로 문자형은 숫자형과 날짜형을 만나면 형변환이 일어나게 되어 Range Scan을 할 수 없다.

 SELECT * FROM 고객
 WHERE 가입일자 = '01-JAN-2018';

의 경우 좌변 컬럼 기준으로 우변이 변환되기 때문에 인덱스 사용에 문제가 없다. 하지만 NLS_DATE_FORMAT 파라미터가 다르게 설정된 환경에서 수행하면 컴파일 오류가 나거나 결과집합이 틀려질 수 있기 때문에

 SELECT * FROM 고객
 WHERE 가입일자 = TO_DATE('01-JAN-2018','DD-MON-YYYY')

로 날짜 포맷을 정확히 지정해 주는 코딩 습관이 필요하다.
숫자형과 문자형이 만나면 숫자형이 이긴다고 했지만, 연산자가 LIKE 일 때는 다르다. LIKE 자체가 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 컬럼이 변환된다.

자동 형변환 주의

Decode(a, b, c, d)를 처리할 때 'a=b'일 경우 c를 반환하고, 아니면 d를 반환하다. 이때 반환 값의 데이터 타입은 세 번째 인자 c에 의해 결정된다. 따라서 c가 문자형이고 d가 숫자형이면, d는 문자형으로 변환된다. 또 하나의 내부 규칙은 세번 째 인자(c)가 null 값이면 varchar2로 취급된다는 사실이다.(예는 107 page 참고)