오라클 성능 고도화 원리2 - 인덱스 원리와 활용

Table Random Access 최소화 튜닝

• 인덱스 컬럼 추가
• 기존 인덱스에 컬럼 추가
• PK 인덱스에 컬럼 추가

⇒ 인덱스에 컬럼 추가했을 때, 클러스터링 팩터가 나빠질 수 있다.

→ 인덱스 내에서 키 값이 같은 레코드는 rowid 순으로 정렬됨. 근데, 여기서 변별력이 좋은 컬럼이 추가되면, 해당 컬럼 순으로 정렬되어 클러스터링 팩터를 나쁘게 만들 수 있음.

변별력이 좋지 않은 컬럼 뒤에, 변별력이 좋은 컬럼을 추가 할때는 유의해야할 필요가 있음.

IOT (Index Organized Table)

• 테이블을 인덱스 구조로 생성.

 

• 테이블 인덱스 구조로 만드는 구문

create table index_org_t (a number primary key, b varchar(10)) organization index;

 

• 힙 구조 테이블
  데이터 삽입 = Random 방식
• 인덱스 테이블
  정렬 상태를 유지하며 데이터 삽입
• 오라클 IOT는 PK 컬럼 순으로만 정렬 가능하다.
• 장/단점
  • 넓은 범위 액세스에 유리 (Random 액세스 보다 Sequential 방식 액세스 가능)
  • PK 인덱스를위한 별도의 세그먼트 생성 X, 저장공간의 이점
• 단점
  • 데이터 입력시 느리다
  • Direct Path Insert 작동 X
  • 인덱스 분할이 발생할 가능성이 높다.

언제 사용해야 하는가?

• 크기가 작고 NL조인으로 반복 룩업하는 테이블
  • 코드성 테이블
• 폭이 좁고 긴(row 수가 많은) 테이블
• 넓은 범위를 주로 검색하는 테이블
  • Between, like 등 으로 넓게 검색할때
  • PK 컬럼이 없는 통계성 테이블
• 데이터 입력 조회 패턴이 서로 다른 테이블
• Partitioned IOT
• Overflow 영역
  • PK 이외 컬럼이 많은 테이블인데 IOT를 구성해야 하는 경우 사용
  • Overflow 영역을 읽을 때에도 Random 액세스는 발생함 (Overflow 영역에도 버퍼 Pinning 효과 나타남)
• IOT_REDUNDANT_PKEY_ELIM

---

인덱스 클러스터 테이블

1. 인덱스 클러스터
   • 단일 테이블 인덱스 클러스터
   • 다중 테이블 인덱스 클러스터

• 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조 사용.
• (데이터를 물리적으로 한 군데 저장할 뿐, 정렬하는 것은 아님)
• 테이블 구성 방법
  • 클러스터 생성
  • 클러스터 인덱스 정의 (데이터 검색 용도 & 데이터 저장 위치 찾는 용도)
• B*Tree 구조이나, 해당 키값을 저장하는 첫번째 데이터 블록만을 가리킴.
• 클러스터 인덱스의 키 값은 항상 Unique하다.
• 테이블 레코드와 1:M 관계
• 넓은 범위 검색에 유리
• DML 부하로 인해 실무에서 자주사용되진 않음.
• Truncate Table 구문을 사용할 수 없음
• 테이블을 Drop 할 때도, 내부적으로 건건이 delete 수행됨
  → 전체 데이터 지울 때, Truncate Cluster, Drop Cluster 하는게 더 빠름

2. 해시 클러스터
   • 단일 테이블 해시 클러스터
   • 다중 테이블 해시 클러스터
   • 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장
     • 데이터 검색 : 클러스터 키 활용
     • 저장 위치 탐색 : 해시 함수 활용
       → 해시 함수가 인덱스 역할 대신
     • ‘=’ 검색만 가능하다는 제약이 있음.

---

인덱스 스캔 효율

I/O 튜닝의 핵심

1. Sequential 액세스의 선택도를 높인다.
   • 인덱스를 스캔한 건수 중 결과로 선택되는 비율
   • 같은 결과를 내기 위해 적은 양을 읽어야함.
2. Random 액셋스의 발생량을 줄인다.

---

인덱스 설계

가장 중요한 두가지 선택 기준

1. 조건절에 항상 사용되거나, 적어도 자주 사용되는 컬럼들을 선정
2. ‘=’ 조건으로 자주 조회되는 컬럼을 앞쪽에 둔다.

인덱스 스캔 효율성 고려

스캔 효율성 이외의 고려 대상

• 쿼리 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터 량
• DML 부하
• 저장 공간
• 인덱스 관리 비용

결합인덱스 컬럼 순서 결정 시 , 선택도 이슈

선택도 : 전체 레코드 중 조건절에 의해 선택되는 비율

→ 선택도가 충분히 낮은지가 중요한 판단 기준임.

Sort operation 생략을 위한 컬럼 추가

조건절에 포함되지 않더라도, 소트 연산을 대체할 목적으로 컬럼추가해서 성능 개선 가능

---

비트맵 인덱스

활용

• Distinct Value 개수가 적을 때 저장 효율이 좋음 (ex - 성별)
• 다양한 인덱스를 동시에 활용할 수 있어, 정형화되지 않은 임의 질의(ad-hoc query)가 많은 환경에 적합
• 읽기 위주의 대용량 DW에 적합

단점

• lock에 의한 DML 부하가 심하다