인덱스의 특징
- 테이블과 마찬가지고 인덱스 저장하는 최소단위는 블록
- 인덱스로 지정된 컬럼의 값과 해당데이터의 원본을 찾아갈수있는 주소로 구성
- 컬럼수와 데이터 사이즈마다 인덱스블록에 저장되는 건수는 다를 수 있음
- 모든컬럼이 NULL인경우 인덱스를 만들지 않음.
오라클에서만 사용하는 ROWID
- 데이터마다 가지는 위치값을 ROWID
- 오브젝트번호+데이터파일번호+블록번호+블록내ROW번호
- 인덱스는 테이블 컬럼값과 ROWID를가짐
- ROWID를 알고있으면 인덱스통하지않고 바로 데이터 접근가능
- ROWID순으로 정렬하면 같은 블록끼리 모여있어 버퍼 피닝 효과를 만들수있다.
인덱스 스캔방식
- 인덱스 유니크 스캔
- 루트블록 -브랜치 블록 - 리프블록까지내려가 최정데이터가 저장된 테이블블록을 읽어 단한건을 찾는다.
- 결합 인덱스의 경우 일부 컬럼만으로 검색할때는 INDEX RANGE SCAN, INDEX FULL SACN으로 변형 되기도함.
- 인덱스 레인지 스캔
- 가장 일반적으로 사용되는 인덱스 스캔
- 인덱스를 수직탐색하다가 리프블록에 도달하여 시작지점을찾고 필요한 범위를 순차적으로탐색
- 조건에 만족하지않는 첫번째컬럼까지 액세스한후 종료
- PK인덱스 범위검색에도 사용
- 선두절에 대한 조건이있으면 가능 =조건일 필요는없다.
- LIKE %조건%, 부정형비교조건(NOT, <>), 인덱스컬럼변형(NVL(컬럼,0) = '조건') 등은 RANGE스캔 불가
- 인덱스 풀스캔
- 인덱스 처음부터 마지막 리프 블록까지 순차적으로 스캔
- 오라클은 인덱스 모든컬럼이 NULL일 경우 인덱스에 저장안됨 (MS SQL Server는 저장됨)
- 인덱스 스킵 스캔 (INDEX_SS)
- 선두컬럼이 조회조건에 없을경우에 사용
- 생략된 선두컬럼 값의 종류가 적을때 유용
EX)성별+생년월일>효율좋음, 생년월일+성별>효율안좋음 - IN조건이 인덱스 액세스 조건일때는 SKIP SCAN사용불가
- 인덱스 패스트 풀스캔(INDEX_FFS)
- SELECT나 WHERE에 포함된 모든컬럼이 인덱스에 포함될경우 사용
- MULTI BLOCK I/O로 수행되며 결과의 순서 보장 안함
- 병렬스캔도 가능
- 컬럼조건때문에 쓸기회가많지는 않지만, 인덱스를 멀티블록으로 읽을수 있는장점때문에 성능극적개선 가능성큼
- INDEX COMBINE
- 한테이블에서 두개의 인덱스를 사용할 수 있게 해 주는 기능.
- 두개의 인덱스를 사용해 각각의 결과 집합으로 ROWID를 만들어 놓고 조건절에 따라 AND또는OR연산해서 결과집합을 만듬.
인덱스 스캔 유도방식(힌트)
- INDEX : 인덱스스캔유도, 스캔종류는 오라클이선택
- INDEX_DESC : 인덱스 거꾸로 스캔
- INDEX_RS : 인덱스 범위 스캔 유도, 범위스캔 불가능판단시 힌트무시
- INDEX_SS : 인덱스 스킵 스캔을 유도, 스킵 스캔이 불가능판단시 힌트무시
인덱스 스캔 동작원리
-
WHERE SUBSTR(ITEM_NM,1,2) = '한우' 처럼 컬럼을가공하면 함수기반인덱스가 아니면 작동하지않음
WHERE ITEM_NM LIKE '한우%' 로바꿀수있음 -
묵시적 형변환 : SQL문에서 컬럼가공하지않았는데 내부적으로 형번환되는 경우
컬럼 타입에 맞게 사용하는것을 습관화 해야함.-
ORD_DT는 VARCHAR(8)로 되어있어, 인덱스스캔하지않고 전체슼내함.
WHERE ORD_DT = TO_DATE('20120101') -
WHERE ITEM_ID = '123' 여기서는 ITEM_ID가 NUMBER형임
-
늙어가면서 기억을 남기는 개발자