인덱스를 통한 RowId로 테이블 액세스는 고비용연산 이다. -> Index Range Scan이 Table Full Scan보다 오래 걸릴 수 있다.
RowId가 메인 메모리 DB 처럼 물리적으로 데이터와 바로 연결된 포인터가 아니라, 디스크 상에서 테이블 레코드를 찾아가기 위한 위치정보를 담는 논리적 주소이기 때문.
포인터의 역할을 할 수 없는 이유는?
테이블 블록이 LRU에 의해 캐싱되었다, 밀려났다 다시 캐싱되기를 반복, 그때마다 다른 공간에 캐싱되므로, 인덱스에서 포인터로 연결 할 수 없다.
읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔 하는 것보다 오히려 느려진다.(인덱스 손익 분기점)
Table Full Scan은 추출 건수와 상관없이 조회 성능이 일정, but 인덱스를 이용해 테이블을 액세스할 때는 추출 건수가 많을 수록 느려진다.(테이블 랜덤 액세스 때문)
Table Full Scan은 시퀀셜 액세스, Index RowId를 이용한 테이블 액세스는 랜덤 액세스 방식임.
Table Full Scan은 Multiblock I/O, Index RowId를 이용한 테이블 액세스는 Single Block I/O 방식임.
일반적으로 10만건이내의 데이터에 대해 5%~20%정도의 추출에서 인덱스 손익분기점이 결정된다. 1000만건 수준의 큰 테이블에선 더 낮아진다.
이때 CF 수준에 따라 손익분기점이 5%미만이 될 수도, 90%수준이 될 수도 있다.
CF ?
Clustering Factor, 군집성계수로 이해.
특정 컬럼 기준으로 같은 값을 같는 데이터가 서로 모여 있는 정도 -> CF가 좋은 컬럼에 인덱스를 생성한다면, 검색효율이 매우 좋다. -> 인덱스 정렬 순서와 테이블 레코드 정렬 순서의 일치 수준이 높기 때문.
인덱스 레코드마다 테이블 레코드를 건건 블록 단위로 I/O 하는데, 왜 CF가 좋다고 블록 I/O가 적어지나?
인덱스 RowId로 테이블 액세스시 Oracle은 테이블 블록에 대한 포인터를 바로 해제 하지 않고 유지.(버퍼 Pinning)
유지 상태에서 다음 인덱스 레코드를 읽었는데 직전과 같은 테이블 블록을 가르킨다면, 래치 획득과 해시체인스캔과정 생략하고 바로 테이블 블록을 읽을 수 있음.
그럼 읽어야 할 데이터가 인덱스 손익분기점을 넘는 수준이라면? (예를 들어 배치프로그램)
Full Scan 사용할 것.(Full Scan을 빠르게 하기 위한 방법으로 파티셔닝, 해시조인 사용)
인덱스 튜닝 기법
인덱스 컬럼 추가(테이블 액세스 최소화를 위한 가장 일반적 방법)
조건절에 쓰인 컬럼이 인덱스로 잡혀있지 않아 인덱스 필터로 테이블을 액세스 하게 될 경우 기존 인덱스에 해당 컬럼을 추가.(기존 인덱스를 필요한 컬럼으로만 변경하면 기존 인덱스를 사용중인 쿼리에 영향을 미치고, 새로운 인덱스를 만드는 것은 인덱스 관리 비용 증가와 DML 부하에 따른 트랜잰션 성능 저하가 생길 수 있으므로)
인덱스 Scan 으로 얻은 Rows 수에 비해, 그 다음 Table Access로 얻은 Rows 수가 현저히 적은 경우 인덱스 컬럼을 추가해서 인덱스 스캔을 줄 일 수 있다.
조건절에 쓰인 컬럼 말고 쿼리에 쓰인 컬럼 모두를 인덱스에 넣어 성능을 개선할 수도 있다.
조건절 컬럼으로 인덱스 액세스 이후, 추출 데이터가 많은데, 인덱스에 쓰이지 않은 컬럼까지 추출하려면 느릴 수 밖에 없다. 이럴 때는 인덱스만 읽어서 처리할 수 있도록 인덱스에 컬럼을 추가 할 수 있다.(인덱스만 읽어서 처리하는 쿼리를 Covered 쿼리라고 하고, 그 인덱스를 Covered 인덱스라고 한다.)
Include 인덱스(SQL Server 2005 버전에 추가된 기능)
인덱스 외에 지정한 컬럼을 리프레벨에 추가 -> 인덱스 수직적 탐색에 쓰이지는 않지만, 수평적 탐색에 사용.
테이블 랜덤 액세스 생략 가능
인덱스 구조 테이블
인덱스를 통한 테이블 액세스가 랜덤 액세스로 인해 느리다면, 랜덤액세스가 발생하지 않도록 테이블을 인덱스 구조로 생성 -> Oracle의 IOT(Index-Organized Table), MS SQL Server의 클러스터형 인덱스
RowId를 갖는 인덱스와 달리 IOT는 인덱스 리프블록에 RowId 대신 테이블 데이터를 갖는다.
Freelist에서 할당받은 블록에 정해진 순서 없이 데이터를 입력하는 랜덤방식의 일반 힙 구조 테이블과 달리 정렬 상태를 유지하며 데이터 입력.
인위적으로 CF를 좋게 만드는 방법
같은 값을 가진 레코드가 정렬된 상태로 모여 있으므로, 랜덤 액세스가 아닌, 시퀀셜 방식으로 데이터를 액세스->Between, 부등호조건으로 넓은 범위를 읽을 때 유리
데이터 입력과 조회 패턴이 다른 테이블에도 유용. 조회 데이터 기준 정렬 컬럼을 지정하므로.
클러스터 테이블
인덱스 클러스터 테이블
클러스터 키 값이 같은 레코드를 한 블록에 저장
다른 여러 테이블 레코드를 같은 블록에도 저장(다중 테이블 클러스터)(일반 테이블은 블록을 다른 테이블과 공유 못함)
B*Tree 인덱스 구조이긴 하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫번째 데이버 블록을 가리킨다는 점이 다르다. 클러스터 인덱스는 테이블 레코드와 1:M 관계. 따라서 클러스터 인덱스 키 값은 Unique