친절한 SQL 튜닝 2장

2장 인덱스 기본

2.1 인덱스 구조 및 탐색

인덱스 튜닝의 핵심요소

• 인덱스 스캔 효휼화 튜닝 : 정렬을 통한 소량 스캔
• 랜덤 액세스 최소화 튜닝 : 인덱스 스캔 후 테이블 레코드 액세스 최소화

 

2.1.1 인덱스 구조

B+ 트리

LMC : 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킴

리프 블록에 저장된 레코드는 ROWID(데이터 블록 주소 + 로우 번호)를 가짐.

 

2.1.2 인덱스 수직적 탐색

인덱스 스캔 시작지점을 찾는 과정

찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동

 

2.1.3 인덱스 수평적 탐색

조건절에 만족하는 모든 데이터의 ROWID를 얻기 위한 과정

 

2.1.4 결합 인덱스

인덱스 구성에 따라 성능 차이는 있지만, 어느 컬럼을 앞에 두든 일량의 차이는 없다.

 

2.2 인덱스 기본 사용법

인덱스 선두 컬럼이 조건절에 있어야 범위 스캔 가능

단, 인덱스 선두 컬럼을 가공하지 않아야 정상적으로 사용 가능 (인덱스 스캔 시작점 탐색)

 

2.2.1 컬럼 가공

WHERE, ORDER BY, SELECT-LIST에서 가공이 이루어진 경우 인덱스가 정상적으로 사용되지 않을 수 있음.

• SUBSTR()
• NVL()
• LIKE '%...%'
• WHERE ( ... OR ... )
• WHERE IN ( ..., ... )
• TO_CHAR( ..., 'FM...' )

* IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식 사용 : IN_List 개수만큼 Index Range Scan 반복

 

2.2.2 정렬 연산 생략

인덱스 사용 시 정렬돼 있기 때문에 ORDER BY가 있어도 정렬 연산 수행 생략

 

2.2.3 자동 형변환

자동 형변환에 의해 인덱스 컬럼이 가공되어 인덱스 스캔을 못하는 경우 존재

숫자형 + 문자형 = 숫자형

날짜형 + 문자형 = 날짜형

 

에러 )

• 실행 에러 : 숫자형 컬럼과 문자형 컬럼 비교 시 문자형 컬럼에 숫자로 변환할 수 없는 문자열을 입력하는 경우
• 결과 오류 : DECODE(A, B, C, D) 처리 중 데이터 타입이 C에 의해 결정

 

2.3 인덱스 확장기능 사용법

2.3.1 Index Range Scan

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위만 스캔

 

2.3.2 Index Full Scan

수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색

데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택

 

2.3.3 Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식

 

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성 관리

Unique 결합 인덱스에 대해 일부 컬럼만으로 검색 시 Index Range Scan 실행

 

2.3.4 Index Skip Scan

조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스하는 스캔 방식

조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용

 

2.3.5 Index Fast Full Scan

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔

결과집합이 인덱스 키 순서대로 정렬되지 않음.

쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용 가능

인덱스가 파티션 돼 있지 않더라도 병렬 쿼리 가능

 

2.3.6 Index Range Scan Descending

내림차순으로 정렬된 결과집합