[튜닝] 튜닝 관점에서의 인덱스 개념

■ 인덱스의 역할
  - 튜닝의 가장 중요한 포인트
  - 오라클 옵티마이저가 실행계획을 만드는 가장 중요한 판단 기준(통계정보, 알고리즘 등)
  - 튜닝시 가장 Cost가 적게 드는 방법


■ 인덱스의 의미
  - 최소한의 데이터 블록 읽기를 통한 성능 향상
  - 대표 키와 Row의 주소 정보(ROWID)만 미리 정렬
  - 대표 키 값을 통해 미리 정렬된 인덱스 만을 검색하여 해당 Row만 바로 액세스(테이블의 경우는 입력 순서대로 데이터를 저장하므로 비순차적인 블록 구성)
  - ROWID를 통한 액세스가 가장 빠른 조회 경로
  - 블록 단위의 액세스 : 가장 작은 단위 디스크 I/O


■ 인덱스의 사용 여부
  ○ 인덱스를 사용할 수 있는 조건
    - equal(=)
    - in
    - like
    - between
    - <=, <, >, >=
    - not between
  ○ 인덱스를 사용할 수 없는 조건
    - 부정형 표현
    - not equal(!=)
    - not like
    - is null, is not null
    - index suppressing
  ○ 예외사항
    - is not null의 경우 옵티마이저의 판단으로 히스토그램을 이용하여 인덱스 사용 가능.
    - COL1+COL2 결합인덱스에서 COL1 is null and COL2 = 'abc' 조건은 사용 가능


■ 인덱스의 활용
  - 프로그램에서 Index suppressing이 발생하지 않는지 점검
  - 자주 액세스되는 테이블을 대상으로 모든 쿼리에 대해서 액세스 경로 조사표를 작성
  - 중복되는 인덱스가 있는지 검사 : COL1 인덱스와 COL1+COL2 인덱스가 동시에 있을 때
  - 각 컬럼들에 대해 Number of Distinct Values가 어느 정도인지 검사할 필요
  - 분포도가 어떻게 구성되어 있는지 검사할 필요(히스토그램 생성)
  - Range scan하는 쿼리들이 어느 정도의 넓은 범위로 액세스하는지 검사할 필요
  - 바인드SQL을 사용하는 경우 히스토그램 정보를 사용할 수 없으며, 실제 어떤 값들이 바인딩 되는지 조사
  - 자주 액세스되는 짧은 쿼리에서 인덱스만으로 처리할 수 있는지 조사


■ 결합 인덱스의 구성
  - 자주 같이 액세스되는 컬럼으로 구성
  - 항상 (=, in)으로 들어오고, Selectivity가 높은 컬럼을 선두 컬럼으로 구성
  - Selectivity가 떨어지더라도 테이블 액세스되는 범위를 줄일 수 있다면 결합인덱스에 추가
  - 대량의 테이블을 액세스할 때 일반적으로 단일인덱스보다 Selectivity 유리
  - 결합 인덱스만으로 결과를 조회할 수 있다면 유리


■ OLTP 시스템에서 인덱스가 필요한 경우
  - Nested loop join에서 조인조건에 사용되는 컬럼들은 인덱스가 있어야 한다.
  - 서브쿼리의 경우 대부분 특별한 힌트를 주지 않는 한 Filter, NL join처럼 풀리므로 연결조건에 인덱스가 반드시 필요하다.
  - Primary key, Unique constraint에는 오라클이 내부적으로 인덱스를 생성하나, FK constraint에는 오라클이 내부적으로 인덱스를 생성하지 않으므로 꼭 인덱스를 생성해야 한다.
  - 계층구조에서 Start with와 Connect by에는 양쪽 모두 인덱스가 있어야 한다.


■ 인덱스 사용시 문제점
  ○ 과다한 디스크 블록 액세스
    - 동일 값이 많은 인덱스
    - Range scan의 범위가 너무 넓은 경우
    - 건수가 너무 적은 테이블
  ○ 인덱스 관리 비용 부담
    - DML작업시 인덱스 정보도 같이 갱신
    - 비트맵이나 IOT의 경우 특히 심함