인덱스 스캔 튜닝

쿼리 튜닝에 대한 개념 익히기

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

인덱스 튜닝

B-Tree 인덱스

데이터가 정렬된 상태로 저장되어서 부하가 큰 소드 연산(Sort Operation)을 회피하는데 유용하게 이용된다.
오라클은 인덱스를 생성하는 데 있어서 다양한 옵션을 제공한다.
- 한 개(1) 또는 여러 개(N)의 컬럼으로 구성된 인덱스를 생성할 수 있는데, 한 개의 컬럼으로만 구성된 인덱스를 '단일 컬럼 인덱스'라 한다.
- 2개 이상의 컬럼으로 구성된 인덱스를 '복합 컬럼 인덱스'라 한다.
- 인덱스 마다 정렬되는 순서를 오름차순(ASC) 또는 내림차순(DESC)로 설정할 수 있다.
B-Tree 인덱스는 성별과 같이 선택도가 높은 컬럼보다는 주민등록번호나 이름 같이 선택도가 낮은 컬럼에 생성하는 것이 유리하다.
인덱스로 활용할 수 없는 상황
- 인덱스가 생성된 컬럼을 NVL, TRIM과 같은 내장 함수로 감싸는 경우 인덱스 사용이 불가능하다.

B-Tree 인덱스의 구성도

인덱스와 테이블은 각각의 객체이다.
- 인덱스와 테이블은 논리적/물리적 완전하게 분리되어 있다.
리프 블록에는 ROWID(테이블의 최우선 순위 인덱스)를 저장하고 있다.
인덱스 스캔이 성공하면 해당 ROWID를 이용하여 테이블액세스를 하게 되는데, 이러한 연산을 테이블 랜덤 액세스(Table Random Access)라 한다.
테이블 랜덤 액세스가 많아지면 시스템에 많은 부하를 주게 된다.
- 테이블 랜덤 액세스를 줄이는 것이 인덱스 스캔 튜닝의 핵심 이슈가 된다.

정리

인덱스 스캔 시 인덱스의 리프 블록에는 해당 테이블의 행을 가리키는 ROWID가 존재한다.
인덱스 스캔이 완료되면 해당 ROWID를 이용하여 테이블 액세스를 하게 되는데, 이러한 과정을 테이블 랜덤 액세스라 한다.
대량의 데이터를 인덱스 스캔 후, 테이블 랜덤 액세스 하는 횟수가 많아지면 시스템에 큰 부하를 주게된다.
테이블 랜덤 액세스의 횟수로 인덱스 스캔의 효율을 평가하기도한다.
특정 인덱스를 스캔하여 100건이 나왔고, 테이블 랜덤 액세스 후의 결과도 100건 이라면, 인덱스 스캔의 비효율은 없다고 평가한다.
인덱스를 스캔하여 100건이 나왔는데 테이블 랜덤 액세스후의 결과는 1건인 경우, 인덱스 스캔의 비효율이 크므로 다른 인덱스를 사용하거나 인덱스 순서 조정 및 인덱스 컬럼을 추가 해야 한다.
ROWID가 아무리 최우선 순위 인덱스라고 하더라도 각각의 행을 랜덤하게 가져오게 되므로 비용이 매우 크게 발생한다.

테이블 랜덤 액세스를 줄이기 위한 인덱스 스캔 튜닝

인덱스 스캔 튜닝 관련 힌트

SELECT /*+ INDEX(테이블 인덱스) */

선택도가 높은 컬럼에 대해서 오라클 옵티마이저는 인덱스 스캔보다는 테이블 풀 스캔이 유리하다고 판단한다.
- 하지만 INDEX 힌트를 사용하여 인덱스 스캔을 유도할 수 있다.

SELECT /*+ INDEX(테이블명 컬럼명) */

FULL
- 선택도가 낮은 컬럼을 오라클의 옵티마이저가 인덱스 스캔이 유리하다고 판단하지만 FULL 힌트를 사용하여 테이블 풀 스캔을 유도 할 수 있다.

SELECT /*+ FULL(테이블) */

Last updated 3 years ago