[친절한 SQL 2장] 인덱스 기본

2.1.1 미리보는 인덱스 튜닝

 SQL 튜닝은 랜덤 I/O와의 전쟁

 -. 데이터베이스 성능이 느린이유는 디스크 I/O 때문이다.

 -. Disk I/O중에 랜덤 I/O가 중요

 

2.1.2 인덱스 구조

 -. DBMS는 일반적으로 B*Tree 인덱스를 사용

 

 ※인덱스 탐색 과정

 -. 수직적탐색 : 인덱스 스캔 시작점을 찾는 과정

 -. 수평적탐색 : 데이터를 찾는 과정

 

2.1.5 결합 인덱스 구조와 탐색

 -. 인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수는 같으므로 성능도 같다.

 -. Excel fillter 기능으로 착각하면 안된다. 

 -. B-Tree 구조로 되어 있는 Index는 루트에서 리프 블록까지 높이(dept)는 항상 같다.

 

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

 -. 인덱스 컬럼을 가공하면 스캔의 시작점과 끝점을 찾을 수 없으므로 Range Scan을 할 수 없다.
  -> ex> where nvl(주문수량, 0) < 100, where 생년월일 '20000101' and '20000201' , where 업체명 like '%대한%'

 -. Or 구문(Where in 절도 마찬가지)도 마찬가지로 수직적탐색을 통해 어느 한 지점을 시작지점으로 바로 찾을 수 없다. 따라서 인덱스를 어떤 방식으로 구성해도 Range Scan을 할 수 없다.

 -. where (전화번호 = :tel_no OR 고객명 = :cust_nm)을 아래와 같은 쿼리하면 Index Range Scan이 가능하다.

select * from 고객 where (전화번호 = :tel_no OR 고객명 = :cust_nm)	select * from 고객 where 고객명 = :cust_nm union all select * from 고객 where 전화번호 = : tel_no and (고객명 <> : cust_nm or 고객명 is null);
-- Hint를 사용할 경우 select/*+ use_concat */  * from 고객  where (전화번호 = :tel_no OR 고객명 = :cust_nm)

 

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

 -. 선행조건에 대한 이해 필요

 -. ex) 인덱스 : [소속팀 + 사원명 + 연령] 순으로 구성

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

 -. 위 인덱스의 구성은, 이름이 같은 사원이더라도 소속팀이 다르면 서로 Leap Block이 떨어져있게 된다. 이 조건으로 검색하면, 인덱스 스캔 시작점을 찾을 수 없고, 어디서 멈춰야 할지도 알 수 없다.

 -. 인덱스를 Range Scan 하기 위한 가장 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야한다. 

 

 

2.2.7 자동 형변환

select * from 고객 where 생년월일 = 19821225         (문자형타입)  (숫자형타입) -> 플랜에 보면  filter(TO_NUMBER("생년월일")= 19821225) -> 인덱스 컬럼을 가공했기 때문에 INDEX Range Scan을 사용할 수 없다.

- Oracle에서 숫자형 vs 문자형 = 숫자형이 이김

- Oralce에서 날짜형 vs 문자형 = 날짜형이 이김

select * from 고객 where 가입일자 = '01-JAN-2018';          (날짜형타입)  (문자형타입) -> 이 경우에는 날짜형 타입이 이기므로 인덱스를 사용하는데 문제 없다.

select * from 고객 where 고객번호 like '%9410%'; -> like 자체가 문자열 비교 연산자이므로 이떄는 문자형 기준으로 숫자형 컬럼이 변환된다.

 

2.3.2 Index Full Scan (first_rows)

 

-> 소트연산을 생략함으로써 전체 집합중 처음 일부를 빠르게 출력할 목적 -> first_rows(n) : 가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근  -> n개의 row를 가져오는데 가장 response time이 작은것 , 페이징처리시 유용 -> SQL 문장에서 MAX, SUM, COUNT등과 같은 집합 함수를 사용하게 된다면 FIRST_ROWS 힌트는 무시된다

 

2.3.3 Index Unique Scan

-. 수직적 탐색으로 데이터를 찾는 스캔방식 -. Unique 인덱스라고 해도 범위검색 조건(between, 부등호, like)으로 검색할때는 Index Range Scan이 나타난다.

 

2.3.4 Index Skip Scan

-. 인덱스 선두컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용 -. index_ss -> Skip Scan 유도 -. no_index_ss -> Skip Scan 방지

 

2.3.5 Index Fast Full Scan

논리적 block 배치

물리적 block 배치

-. Index Full Scan은 논리적으로 1 ~ 10 번 Block을 차례대로 읽음 -. Index Fast Full Scan은 물리적으로 디스크에 저장된 순서데로 읽음 1->2->10->3->9 ... -> 6 -. Multiblock I/O방식으로 읽음으로 디스크로 부터 대량의 인덱스 블록을 읽어야 할떄 큰 효과 -. 인덱스 키 순서대로 정렬 X -. 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을때만 사용

 

2.3.5 Index Range Scan Descending

-. 인덱스를 거꾸로 읽어서 값을 바로 가져온다.