[SQL 튜닝 실습 1-1] 인덱스 튜닝 개념

1. 인덱스 스캔 튜닝

1) B-Tree 인덱스

오라클은 디스크 소트와 테이블 풀 스캔을 회피하기 위해 인덱스 사용

디스크 소트 : 정렬작업을 메모리 내에서 완료못할정도로 용량이 커서 디스크 공간까지 활용한 정렬 연산
테이블 풀 스캔 : 테이블 스페이스에 저장된 테이블을 처음부터 끝까지 전체 검색

위의 두 개로 인해 DBMS에 부하를 주는 경우가 많은데 인덱스 스캔을 활용한다면, 이런 부하를 줄일 수 있다

오라클은 다양한 인덱스 유형을 갖고 있는데 일반적으로 널리 사용되는 인덱스는 B-Tree 인덱스이다
인덱스를 이용한 SQL튜닝을 하려면 B-Tree 인덱스를 이해하는게 제일 중요하다!
B-Tree 인덱스는 데이터가 정렬된 상태로 저장되기때문에 부하가 큰 소트 연산을 회피하는 데 유용하게 사용된다

인덱스는 한 개 이상의 컬럼으로 구성될 수 있는데, 단일 컬럼 인덱스와 복합 컬럼 인덱스가 있다
단일 컬럼 인덱스 : 한 개의 컬럼으로만 구성된 인덱스
복합 컬럼 인덱스 : 2개 이상의 컬럼으로 구성된 인덱스

또한, 정렬순서를 오름차순 또는 내림차순으로 설정할 수 있다. 

B-Tree 인덱스 생성 (ENAME : 내림차순, SAL : 오름차순)

CREATE INDEX EMP_IDX01
ON EMP (ENAME ASC, SAL DESC);

 

B-Tree 인덱스는 성별과 같이 선택도가 높은 컬럼보다는 주민등록번호나 이름과 같은 선택도가 낮은 컬럼에 생성하는게 유리하다.
인덱스가 생성된 컬럼을 NVL 또는 TRIM 과 같은 내장함수로 감싸면 해당 인덱스 사용이 불가능하다. 

B-Tree 인덱스의 구성도를 보면, 루트 블록을 시작으로 리프 블록까지 수직으로만 탐색하는 기법을 인덱스 수직 탐색 이라고 한다
인덱스의 리프블록을 인덱스의 논리적 순서에 따라 수평으로 탐색하는 것을 인덱스 수평 탐색이라고 한다.  

 

인덱스의 구조

---

2) 인덱스와 테이블의 관계

인덱스와 테이블은 각각의 객체이다.
리프블록에는 ROWID를 저장하고 있다.
인덱스 스캔을 성공하면, 해당 ROWID를 이용해서 테이블 액세스를 하게 되는데, 이런 연산을 테이블 랜덤 액세스 라고 한다.
테이블 랜덤 액세스가 많아지면 시스템에 많은 부하를 주므로 결국, 테이블 랜덤 액세스를 줄이는 것이 인덱스 스캔 튜닝의 핵심이슈가 된다.

 

<그림>인덱스&테이블 : 테이블 랜덤 액세스

 

---

3) 인덱스 스캔 튜닝

적절한 인덱스를 생성하고 해당 인덱스를 사용함으로써 테이블 풀 스캔을 회피하거나 소트 연산을 생략한다.
이렇게 인덱스 스캔을 한다면, 대용량의 테이블에서 원하는 데이터를 빠르게 검색할 수 있다.

 

4) 인덱스 스캔 튜닝 관련 힌트

[INDEX] : 사용자가 지정한 테이블과 인덱스를 선택해서 인덱스 스캔을 유도하는 힌트이다. 
인덱스 스캔을 유도할 테이블과 인덱스를 입력한다. 

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

성별(SEX) 컬럼은 선택도가 50%인 컬럼으로 오라클의 옵티마이저는 선택도가 높은 컬럼에 대해 인덱스 스캔보다는
테이블 풀 스캔이 유리하다고 판단하지만, 다음과 같이 인덱스 힌트를 줘서 인덱스 스캔을 유도할 수 있다.

 

SELECT /*+ INDEX(PATIENTS SEX_INDEX) */			
	NAME,		
	HEIGHT,		
	WEIGHT		
FROM PATIENTS			
WHERE SEX = 'M';			

 

[FULL] : 특정 테이블에 대해 인덱스 스캔이 아닌 테이블 풀 스캔을 하도록 유도하고, 테이블 풀 스캔을 유도할 테이블을 입력한다.

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

 

예제로, ACCNO 컬럼은 계좌번호 항목으로 선택도가 아주 낮은 컬럼이다.
오라클의 옵티마이저는 선택도가 낮은 컬럼에 대해 인덱스 스캔이 유리하다고 판단하지만, 다음와 같이 FULL 힌트를 사용해서 테이블 풀 스캔을 유도할 수 있다.

SELECT /*+ FULL(A) */		
     ACCNO,		
     BAL		
FROM ACCOUNTS A		
WHERE ACCNO = 7086854;