NL조인, 인덱스, 디스크 I/O

인덱스를 통해 rowid를 얻고 테이블액세스를 한다는 건 알고 있었다. 그러나 인덱스의 B-Tree 구조를 통해 수직탐색을 할 때도 I/O가 발생하는 건지 궁금했다. 이 부분을 정리해 보려고 한다.

1. 인덱스를 이용해 테이블을 액세스 한다는 의미

두 단계를 거친다

1. 인덱스 블록을 읽어 rowid를 얻는다
2. 획득한 rowid로 테이블 블록을 읽는다
   실행계획은 이렇게 작성된다

INDEX RANGE SCAN OF 사원_X1          ← 인덱스 블록 읽기
TABLE ACCESS BY INDEX ROWID OF 사원   ← rowid로 테이블 블록 읽기

별개의 동작이 아니라 하나의 세트다.

2. 디스크 I/O는 언제 발생하는가?

오라클은 인덱스나 디스크 블록이 필요할 때 다음 순서로 동작한다

1. 먼저 버퍼캐시를 확인
2. 버퍼캐시에 있으면 -> 디스크 I/O 없음 (논리적 읽기)
3. 버퍼캐시에 없으면 -> 디스크에서 읽어 캐시에 적재 (물리적 읽기 = 디스크 I/O)
   즉, 인덱스 블록이나 디스크 블록 모두 캐시에 없으면 디스크 I/O가 발생한다

3. 그럼 왜 인덱스를 사용하나?

인덱스의 목적은 디스크 I/O를 없애는 게 아니라 디스크 I/O양을 줄이는 데 있다

• 100만건 테이블에서 1건을 찾는 경우
  • FULL TABLE SCAN -> 디스크 I/O 횟수 : 테이블 전체 블록만큼
  • INDEX RANGE SCAN -> 인덱스 수직 탐색 횟수 3 ~ 4블록 + 해당 rowid로 테이블 스캔 1블록 = 약 4~5블록

1000페이지 책을 처음부터 끝까지 훑어 해당 단어를 찾아내는 것과 책의 맨 뒷 페이지 색인란에서 해당 단어가 있는 페이지로 바로 이동해 한 페이지만 훑어 보는 것과 같은 차이인거다. (전체블록 vs 해당 블록만)

4. 인덱스 수직 탐색 디스크 I/O

인덱스는 B-Tree 구조이므로 루트 -> 브랜치 -> 리프 순으로 내려간다. 이때 각 단계마다 블록을 읽으며 캐시에 없으면 디스크 I/O가 발생한다

루트 블록 읽기      ← 캐시 miss 시 디스크 I/O
  ↓
브랜치 블록 읽기    ← 캐시 miss 시 디스크 I/O
  ↓
리프 블록 읽기      ← 캐시 miss 시 디스크 I/O
  ↓
(rowid 획득)
  ↓
테이블 블록 읽기    ← 캐시 miss 시 디스크 I/O

이론상 1건 조회에 최대 4번의 디스크 I/O가 발생할 수있다. 하지만 보통은 다음과 같이 나타난다

• 루트 블록 : 모든 인덱스 블록이 거쳐가는 길목이라 거의 캐시 hit
• 브랜치 블록 : 자주 접근 되어 캐시에 잘 남음
• 리프 블록 : 데이터에 따라 양상이 달라짐. 디스크 I/O가 주로 발생하는 지점
• 테이블 블록 : 캐시 적중률에 따라 달라짐

5. 대량 조회에서 인덱스가 손해인 이유

테이블의 80%를 조회한다고 가정한다면

• FULL SCAN : 테이블 블록을 멀티블록I/O로 한번에 여러개를 읽는다
• INDEX RANGE SCAN : 테이블 블록을 싱글블록I/O로 하나씩 읽는다

인덱스로 가면 탐색 시 인덱스를 읽는 I/O비용이 추가 될 수도 있는데 여기에 테이블의 80프로를 싱글블록I/O로 하나씩 읽어야 한다면 오히려 비효율적이며 느리게 된다. 그래서 대량 데이터 조회시에는 FULL SCAN, 소량의 데이터 조회시는 INDEX SCAN이 유리하다

6.NL 조인 확장 매커니즘이 등장한 배경

Outer 테이블 처리(인덱스 수직 탐색 -> 테이블 액세스)로 조인대상 데이터를 추출한 다음 추출한 건마다 Inner 인덱스 수직탐색 -> 테이블 액세스를 반복한다 아우터는 한번만 수행하지만 이너는 아우터에서 추출된 건수만큼 반복되므로 NL조인 비용의 핵심은 Inner 반복I/O에 있다


다음 두 가지 방법은 오라클이 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안되었다

• 테이블 Prefetch : 인덱스로 테이블 액세스 중 디스크 I/O가 필요해지면 이어서 읽게 될 블록까지 미리 읽어 캐시에 적재
• 배치 I/O : 디스크 I/O Call을 미뤄뒀다가 읽을 블록의 일정량이 쌓이면 한꺼번에 처리