[SQL 튜닝] 테이블 랜덤 액세스, ROWID, 인덱스 손익분기점

- 인덱스를 사용했는데 왜 느릴까?

- 인덱스를 사용하면 데이터가 많아도 금방 조회가 되는 경우가 있는데, 대량 데이터를 조회할 때 인덱스를 사용하니 테이블 전체를 스캔할 때 보다 훨씬 느리다

 

ROWID

- 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빠르게 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값인 ROWID를 얻기 위함임

- ROWID는 데이터파일 번호, 오브젝트 번호, 블록 번호와 같은 물리적인 요소로 구성되어 있긴 하지만, 물리적이라기 보단 논리적인 주소에 가까움

→ 물리적으로 직접 연결되지는 않고, 디스크 상의 실제 테이블 레코드를 찾아가기 위한 논리적인 주소를 담고 있음

 

I/O 매커니즘

- DBA는 디스크 상에서 블록을 찾기 위한 주소 정보임

- 매번 디스크에서 블록을 읽을 수 없으므로, I/O성능을 높이려면 버퍼캐시를 활용해야 한다. 그래서 블록을 읽을 때는 바로 디스크로 가기 전에 버퍼캐시 부터 찾음

- 인덱스 스캔 → 리프블록 → ROWID → DBA → 해시함수(해싱알고리즘) → 버퍼헤더(메모리상 주소 = 포인터) →버퍼블록 접근

 

=> ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고, 못 찾을 때만 디스크에서 블록을 읽음

=> ROWID를 이용하는 테이블 액세스는 고비용임

 

 

클러스터링 팩터

- 클러스터링 팩터, CF, 군집성 계수

- 특정 컬럼을 기준으로 값을 값을 가지는 데이터가 서로 모여있는 정도

- CF가 좋은 컬럼에서 생성한 인덱스를 검색 효율이 매우 좋음

- 데이터들이 물리적으로 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠름 (근접해 있으면 블록 I/O과정 없이 포인터로 바로 액세스 가능)

 

인덱스 손익분계점

- 인덱스 ROWID를 사용한 테이블 액세스는 고비용 구조임

- 읽어야 하는 데이터의 개수가 일정량을 넘는 순간, Index Range Scan보다 그냥 Table Full Scan이 더 빠름

- Table Full Scan은 항상 테이블 전체를 읽기 때문에, 한 테이블에서 몇 건을 조회하든 성능이 일정함

- 인덱스를 이용한 조회에서는 전체 데이터 중에서 몇 건을 조회하는냐에 따라 성능이 크게 달라짐

- 추출 건수가 늘어나면 인덱스 스캔량도 늘고, 테이블 랜덤 엑세스가 늘어나기 때문

 

- Table Full Scan은 시퀀셜 액세스, 인덱스는 랜덤 액세스

- Table Full Scan은 멀티블록I/O, 인덱스는 싱글블록I/O

 

인덱스 손익분기점

 

 

=> CF에 따라 달라지지만, 인덱스의 손익 분기점을 5~20%(전체데이터 대비 추출 건수)의 낮은 수준임

 

- 여기서 말하는 5~20% 수준의 손익 분기점을 10만건~100만건 사이의 테이블에나 적용되는 수치임

- 1000만건 수준의 테이블에서는 손익분기점이 더 낮아짐

- 10만건~100만건 테이블을 조회할 때에는 버퍼캐시에서 데이터를 찾을 가능성이 있지만.... 1000만건 테이블을 조회하는 경우 버퍼캐시에서 데이터를 찾을 가능성이 매우매우매우 낮아서, 거의 모든 데이터를 디스크에서 읽기 때문에, 손익분기점 자체가 의미가 없어져서, 인덱스를 이용하는 것 보다 그냥 Table Full Scan이 더 빠름