친절한 SQL 튜닝 - 2.2 인덱스 기본 사용법

---

2.2.1 인덱스를 사용한다는 것

인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용 가능

(=리프 블록에서 스캔 시작점을 찾아 스캔하다가 중간에 멈추는 것을 의미)

Index Range Scan : 리프 블록 일부만 스캔 (인덱스 컬럼 가공하면 Index Range Scan 불가능)

인덱스 컬럼을 가공해도 인덱스 사용 가능하지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 한다.

Index Full Scan : 리프 블록 전체 스캔

---

2.2.2 인덱스를 Range Scan 할 수 없는 이유

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용 (Range Scan)할 수 없다"

인덱스 컬럼을 가공하면 인덱스 스캔 시작점을 찾을 수 없기 때문

 

#1 인덱스에는 가공되지 않은 값이 저장되어 있음 : 생년월일

where substr(생년월일, 5, 2)='05' //생년월일을 substr 함수를 사용해서 가공함

가공된 값을 기준으로 검색하려면 스캔 시작점과 스캔 끝지점을 찾을 수 없다.

 

 

#2 가공하지 않은 주문수량으로 인덱스를 만듬

where nvl(주문수량,0) < 100 // nvl- 값이 NULL이면 0으로 치환한 값 즉, 가공함

 

#3 LIKE로 중간값을 검색할 때

where 업체명 like '%대한%' 
// '대한'을 포함하는 값은 전체 구간에 걸쳐 흩어져있으므로 range scan이 불가능

 

#4 or 조건으로 검색할 경우

where (전화번호 =: tel_no OR 고객명 =: cust_nm)
// 수직적 탐색을 통해 전화번호가 01012345678 이거나 
// 고객명이 홍길동인 어느 한 시작지점을 바로 찾을 수 없다.

 

#5 IN 조건으로 검색할 경우

where 전화번호 in (:tel_no1, :tel_no2)

IN과 OR은 같다. union all 방식으로 작성하면 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있다. (range scan이 가능) OR 과 IN 조건절은 옵티마이저의 쿼리 변환 기능을 통해 Index Range Scan(IS) 로 처리되기도 한다.

 

OR EXPANSION

select *
from 고객
where 고객명 =: cust_nm --고객명이 선두 컬럼인 인덱스 Range Scan
union all
select *
from 고객
where 고객명 =: tel_no --전화번호가 선두 컬럼인 인덱스 Range Scan
and (고객명<> : cust_nm or 고객명 is null)

OR 조건식을 SQL 옵티마이저가 위와 같은 형태로 변환 가능

/*use_concat*/ 힌트를 이용해서 OR EXPANSION 을 유도 가능

 

---

2.2.3 더 중요한 인덱스 사용조건

 

select     사원번호, 소속팀, 연령, 입사일자, 전화번호
from     사원
where     사원명 = ‘홍길동＇

인덱스 : [소속팀+사원명+연령]이다.

즉, "데이터를 소속팀 순으로 정렬하고 소속팀이 같으면 사원명 순으로 정렬하고 사원명이 같으면 연령 순으로 정렬한다" (이름이 같은 사원이라도 소속팀이 다르면 서로 멀리 떨어진다 => 그림과 같게됨)

인덱스를 Range Scan 하려면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다.

 

결론 : 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능

 

인덱스를 탄다 = 인덱스를 Range Scan 한다

---

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

 

인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문 => 인덱스를 사용하는 이유!

SQL에 ORDER BY 가 있어도 정렬 연산을 따로 수행하지는 않는다.

왜 ??? 인덱스를 스캔하면서 출력한 결과집합이 어차피 변경순번 순으로 정렬되기 때문이다!!

---

2.2.5 order by 절에서 컬럼 가공

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용 불가능"

여기서 인덱스 컬럼은 where절의 조건을 의미한다!!

그런데 조건절이 아닌 ORDER BY 나 SELECT-LIST에서도 컬럼을 가공함으로써 인덱스를 정상적으로 사용할 수 없는 경우 발생

 

#1 인덱스 : [장비번호 + 변경일자 + 변경순번]

SELECT ~~~
ORDER BY 변경일자, 변경순번 //ORDER BY 절 생략 가능 (자동으로 변경일자+변경순번 순으로 정렬되기 때문)


SELECT ~~~
ORDER BY 변경일자 || 변경순번 //ORDER BY 절 생략 불가 (|| : OR)

 

#2 인덱스 : [주문일자 + 주문번호]

SELECT *
FROM (
         SELECT TO_CHAR( A.주문번호, 'FM00000') AS 주문번호, A. 업체번호, A.주문금액
        FROM 주문 A
        WHERE A.주문일자 =: dt
                AND A.주문번호 > NVL(:next_ord_no, 0)
        ORDER BY 주문번호
        )
WHERE ROWNUM <=30

위 질의의 실행 계획을 확인하면 SORT ORDER BY 연산이 추가되어 있다.

인덱스 선두컬럼인 주문일자가 "=" 조건이므로 데이터가 다음 인덱스 컬럼인 주문번호 순으로 출력된다. ORDER BY 절이 있어도 정렬 연산을 생략할 수 있는 상태다.

 

Q : 그럼에도 불구하고, 왜 SORT ORDER BY 연산이 추가되었을까?

A : ORDERY BY 절에 기술된 주문번호는 순수한 주문번호가 아니라 TO_CHAR 함수로 가공한 주문번호이기 때문이다!!

solution : 진짜 주문번호를 가리키게 주문번호를 명시해준다.

 

SELECT *
FROM (
         SELECT TO_CHAR( A.주문번호, 'FM00000') AS 주문번호, A. 업체번호, A.주문금액
        FROM 주문 A
        WHERE A.주문일자 =: dt
                AND A.주문번호 > NVL(:next_ord_no, 0)
        ORDER BY A.주문번호
        )
WHERE ROWNUM <=30

 

다시 실행 계획을 확인해보면 sort order by가 출력되지 않는 것을 확인할 수 있다.

---

2.2.6 select-list 에서 컬럼 가공

 

인덱스 : [장비번호 + 변경일자 + 변경순번]

select min(변경순번)
from 상태변경이력
where 장비번호='C'
	and 변경일자='2180316';

정렬연산을 따로 하지 않음 , 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최소값

 

select max(변경순번)
from 상태변경이력
where 장비번호='C'
	and 변경일자='2180316';

정렬연산을 따로 하지 않음, 수직적 탐색을 통해 가장 오른쪽 지점으로 내려감

 

select nvl(max(to_number(변경순번)),0)
from 상태변경이력
where 장비번호='C'
	and 변경일자='2180316';.

인덱스에는 문자열 기준으로 정렬되어있지만, 숫자값으로 바꾼 값 기준으로 최종 변경 순번을 요구하면 정렬연산 발생! (select 절에서 to_number함수를 사용해 변경순번을 가공했기 때문에)

 

select nvl(to_number(max(변경순번)),0)
from 상태변경이력
where 장비번호='C'
	and 변경일자='2180316';

정렬연산없이 최종 변경순번을 쉽게 찾을 수있다. (max 값을 가장 먼저 찾았기 때문에)