테이블 엑세스 최소화

• 테이블 저장구조 개선 → 인덱스 구조 테이블(IOT, 클러스터형 인덱스)과 클러스터 테이블

인덱스 컬럼 추가로 튜닝

인덱스 Range 탐색 결과집합의 대부분이 **테이블 엑세스 단계에서 필터 조건에 의해 버려지는 레코드가 많을 때(**인덱스 스캔 비효율이 있는 것이 아님) → 인덱스 컬럼 순서를 조정해도 해결되지 않음 → 인덱스 컬럼 추가 만이 해법 (새로운 인덱스를 생성하면 좋지만 추가하기 힘든 상황일 때)

<SQL Trace 예시>

1. 인덱스 스캔 과정에서 메모리 block 1011개, 물리적 block 900, 결과 반환 266,476 row 반환
2. 266,476 row 만큼 테이블 랜덤 엑세스 발생, 랜덤 엑세스 과정에서 메모리 block 265,957(266,968-1011), 결과 반환 1,909 row 반환 → 1 row 읽는데, 약 1 block I/O 발생

(쿼리 전체 읽은 메모리 block 수가 266,968)

인덱스 클러스터링 팩터

• 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 모여있는 정도를 의미

• 인덱스 클러스터링 팩터가 좋으면 테이블 엑세스 과정에서 읽는 블록, 개수가 줄어듬 테이블 엑세스 과정에서 읽는 블록의 수가 줄어드니, DB 버퍼캐시와 디스크에서 읽는 블록의 개수도 줄어듬

• 인덱스 정렬 순서와 테이블 정럴 순서가 비슷하면 인덱스 클러스터링 팩터가 좋은 상태임 → 정렬 순서가 100% 일치할 필요는 없고, 다음 읽을 테이블 블록과 직전에 읽은 테이블 블록의 주소가 같으면 좋은 상태임

• 테이블 엑세스 양에 비해 블록 I/O가 적게 발생함으로 검색 효율 좋다.

  <aside> 💡

  인덱스 레코드마다 테이블 레코드를 건건히 블록 I/O를 하는데 CF가 달라도 블록 I/O 발생량 차이가 없지 않을까?

  • 오라클은 논리적인 블록 I/O 과정을 통해 찾아간 테이블 블록에 대한 포인트(메모리 주소값)를 바로 해제하지 않고 유지한다. (버퍼 Pinning) 그래서 다음 인덱스 레코드를 읽었는데, 직전 테이블 블록과 포인트가 같으면 논리적인 블록 I/O 과정없이 바로 테이블 블록 데이터를 읽는다. </aside>

• 인덱스를 rebuild 하는 것이 아니라, 테이블을 인덱스 컬럼 순으로 정렬해야 좋아진다.

인덱스 손익분기점

• Index Range Scan에 의한 테이블 엑세스가 Table Full Scan 보다 느려지는 지점을 의미한다.

  <aside> 💡

  참고

  • 인덱스 rowid에 의한 테이블 엑세스는 Random Access 방식, Full Table Scan은 Sequential 엑세스 방식
  • 인덱스 rowid에 의한 테이블 엑세스는 Single Block Read 방식, Full Table Scan은 Multiblock Read 방식 </aside>

• 일반적으로는 5%~20%대에 손익분기점이 존재. CF가 좋으면 90% 수준까지 올라가고, CF가 안 좋으면 5%미만에서 결정 → 인덱스로 100만 건 이상 엑세스 한다면 캐시 히트율이 극히 낮아 Table Full Scan이 더 효율적일 수 있음 (보통의 상황에서)