테이블 엑세스 최소화

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

[인덱스 컬럼 추가로 튜닝]

• 인덱스 Range 탐색 결과집합의 대부분이 테이블 엑세스 단계에서 필터 조건에 의해 버려지는 레코드가 많을 때 → 인덱스 스캔 비효율이 있는 것이 아님

  • 인덱스 컬럼 순서를 조정해도 해결되지 않음
  • 인덱스 컬럼 추가 만이 해법❗️(새로운 인덱스를 생성하면 좋지만 추가하기 힘든 상황일때)

• SQL Trace 예시

  

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

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

[인덱스 클러스터링 팩터]

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

• 인덱스 정렬 순서와 테이블 정럴 순서가 비슷하면 인덱스 클러스터링 팩터가 좋은 상태이다

  • 정렬 순서가 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%미만에서 결정

• 손익분기점을 극복하기 위한 기능

  1. IOT : 테이블을 인덱스 구조로 생성하는 것, 테이블 자체작 인덱스 구조이므로 인덱스 리프 블록이 곧 데이터 블록이여서 수직 탐색후 추가적인 Random Access가 불필요
  2. 클러스터 테이블 : 키 값이 같은 레코드를 같은 블록에 모아 저장해 인덱스를 이용한 테이블 Random Access 시, 블록 I/O가 키 값별로 한번씩만 발생
  3. 파티셔닝 : 기준 키 값이 같은 데이터를 세그먼트 단위로 모아 Full Table Scan을 하더라도 일부 파티션만 읽고 멈추게 함