SQL Tuning 2
SQL 튜닝
인덱스 기본
인덱스 구조 및 탐색
데이터를 찾는 두 가지 방법
-
테이블 전체 스캔
-
인덱스 이용
OLTP 시스템 => 소량 데이터 주로 검색
인덱스 튜닝이 중요.
인덱스 스캔 후 액세스 방식은 랜덤 I/O 방식
랜덤 액세스를 최소화
어느 것이 시스템에 더 큰 영향을 줄까?
인덱스 스캔 < 랜덤액세스
데이터베이스 성능이 느린 이유: 디스크I/O
디스크 I/O중에서 랜덤 I/O가 특히 중요
- ROWID = 데이터 블록 주소 + 로우 번호
- 데이터 블록주소 = 데이터 파일번호 + 블록 번호
- 블록 번호 = 데이터파일 내에서 부여한 상대적 순번
- 로우 번호 = 블록 내 순번
=> ROWID = 데이터 파일 번호 + 블록 번호 + 로우 번호
수직적 탐색: 조건을 만족하는 첫 번째 레코드를 찾는 과정
수평적 탐색: 찾고자 하는 데이터가 더 안 나타날 때까지 데이터를 찾는 과정
인덱스 선두컬럼을 모두 ‘=’ 조건으로 검색할 때는 어느 컬럼을 인덱스 앞 쪽에 두든 블록I/O가 같다.
인덱스 기본 사용법
인덱스를 Range Scan할 수 있는 이유
=> 데이터가 정렬되어 있기 때문
인덱스를 Range Scan할 수 없는 이유
=>인덱스 스캔 시작점을 찾을 수 없기 때문.
- 인덱스 컬럼을 가공했을 경우
- LIKE로 중간 값을 검색할 경우
- OR 조건으로 검색하는 경우
- IN 조건절로 검색하는 경우
단, OR조건, IN조건절은 옵티마이저의 쿼리변환 기능을 통해 검색 가능할 수도 있다. (OR Expansion,IN-List Iterator)
인덱스 Range Scan 하기 위한 가장 첫번째 조건
=> 인덱스 선두 컬럼이 조건절에 있어야한다.
정리하자면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan이 무조건 가능하다.
인덱스 리프 블록은 양방향 연결 리스트 구조
정,역 방향 스캔 가능
ORDER BY, SELECT-LIST에서 컬럼을 가공하여 인덱스를 정상적으로 사용할 수 없는 경우도 있음.
SELECT *
FROM(
SELECT TO_CHAR(A.주문번호,'FM000000') AS 주문번호, A.업체번호
FROM 주문 A
WHERE A.주문일자 =:dt
ORDER BY 주문번호(#or 1)
)
WHERE ROWNUM <= 30
#여기서 ORDER BY의 주문 번호(or 1)는 가공된 (ALIAS)주문번호
ORDER BY A.주문번호
테이블의 가공되지 않은 순수한 컬럼으로 order by 사용
이렇게 수정하면 인덱스 사용가능.
기존 컬럼의 자료형을 변경하고 최소 최대를 찾는 경우도 정렬 연산을 생략 불가.
자동 형변환 주의.
형변환이 인덱스 컬럼을 가공하게 되어 인덱스 Range Scan을 사용하지 못함.
decode(a,b,c,d)
# a=b일때, c 또는 d를 반환
#이때 데이터 반환 타입은 c에 의해 결정되며, c가 문자,d가 숫자 일때 d는 문자형으로 변환된다.
#그러니 c의 값으로 null을 집어넣지 말고 to_number(null), 0 등을 집어 넣어 자동형변환이 일어나지 않게 자료형을 명시하자.
인덱스 확장기능 사용법
/*+ first_rows * /를 활용하여 index full scan하여 성능 개선하는 경우도 있다. 물론 끝까지 fetch하면 성능이 저하
Index Unique Scan
Unique 인덱스를 ‘=’조건으로 탐색하는 경우 작동.
Index Skip Scan
인덱스 선두 컬럼의 Distinct Value 가 적고, 후행 컬럼의 Distinct Value가 많을 때 유용.
- 선두 컬럼이 조건절에 없을 때.
- 중간 컬럼에 대한 조건절이 없을 때.
- 선두 컬럼이 부등호, BETWEEN, LIKE 같은 범위검색 조건일 때.
=> skip scan 가능
하지만 이들 스캔방식이 최선책일 수는 없다.
인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계.
Index Fast Full Scan
- 세그먼트 전체 스캔
- 결과집합 순서 보장 X
- 멀티블록 I/O
- 병렬스캔 가능
- 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능