인덱스튜닝 - 기본원리

최적의 인덱스를 만들기 위한 능력

• 인덱스의 물리적 구조 이해

• 복잡한 SQL을 분해해서 이해

• 만들어진 인덱스가 어덯게 사용될지 예측

• 테이블 내 데이터 속성을 파악

• JOIN의 내부적인 처리방법(NESTED LOOP, MERGE, HASH)

  인덱스란

• 테이블 내 데이터를 찾을 수 있게 일부 데이터를 모아서 구성한 데이터 구조

• 오라클은 인덱스를 구성하는 모든컬럼이 NULL인 레코드는 인덱스에 저장안함 (인덱스에 저장안한 데이터를 조회하려고하면 INDEX RANGE SCAN불가)

  인덱스 종류

• 컬럼수에 따른 구분

  • 단일인덱스(Single Column Index) : 인덱스에하나의 컬럼만사용
  • 복합 인덱스 (Composite Index) : 인덱스에 두개 이상의 컬럼을 사용, 멀티컬럼인덱스, 결합인덱스라고도함

• 컬럼값 중복허용여부에 따른 구분

  • 유니크 인덱스 (Unique Index) : 인덱스 구성 컬럼들 값에 중복을 허용하지 않는다.
  • 비유니크인덱스 (Non-Unique Index) : 인덱스 구성 컬럼들 값에 중복을 허용한다.

• 물리적 구조에따른 구분

  • B트리 인덱스 : OLTP시스템은 대부분 B트리 구조 인덱스 사용
  • 비트맵 인덱스 : 값의 종류가 많지않은 컬럼에 사용, EX)주문유형 : 주문대기, 주문완료 2가지만있을때.

  B*트리 구조와 탐색 방법

• 인덱스생성시 별다른 옵션이 없다면 B*트리 구조 인덱스가 생성

• B는 BALANCED라는 의미. > 리프노드들이 같은수준(깊이)에 자리해있다는뜻

• *(STAR) 는 근접한 리프노드가 연결된 구조

• 인덱스 블록이 존재하는 위치와 담고있는 정보
  1) 루트블록
  : 최상위에 단 하나만 존재
  : 하위 브랜치 블록의 인덱스키 값과 주소를 가지고 있다.
  2) 브랜치 블록
  : 루트와 리프의 중간에 위치, 브랜치는 여러 층이 있을수 있다
  : 하위 브랜치의 인덱스 키 값과 주소 또는 하위 리프의 키 값과 주소를 가지고 있다.
  : 브랜치블록 각 로우의 키 값은 하위 블록이 갖는 값의 범위를 의미
  3) 리프 블록
  : 최하위에만 위치
  : 인덱스 키 값과 데이터 로우(ROWID)위치를 가지고 있다.
  : 리프블록은 인덱스 키 값 순으로 정렬되어있다.
  http://ojc.asia/bbs/view_img.php?img=https%3A%2F%2Flh5.googleusercontent.com%2FyaNbo3Hp-j878TTfps3aVGc9gSopYarsz24xtKo5BPntcyLKMtoybrY_ZCxytpy0gk5mQufUQAYyuLIeTe5GTOQY3vTHZi6XREx_kOjbFiNNvtsf0WDNlpAYODfWlO5vyKXUGmHC

  데이터 찾는법

  -오라클에서 데이터를 찾는방법은 3가지이다.
  1)테이블 전체 읽기(TABLE ACCESS FULL)
  : 조건에 활용할 인덱스가 없거나 인덱스보다 테이블 전체읽는것이 더 효율적이라고 판단될때 사용
  : 찾는데이터가많을때 효율적 EX)총 1000만건중 100만건을 찾는경우 인덱스를 쓰는것보다 풀스캔이 효율적일수있음
  : 무조건 성능이 나쁘다고 생각하면안됨
  2)인덱스 이용한 찾기(INDEX RANGE SCAN & TABLE ACCESS BY INDEX ROWID)
  : 종류 - INDEX RANGE SCAN, INDEX SKIP SCAN, INDEX FULL SCAN
  3)ROWID를 이용한 직접찾기(TABLE ACCESS BY INDEX ROWID)

  인덱스를 이용해 데이터를 찾는과정

  1) 루트에서 리프로(리프 블록찾기) : 루트블록에서 주어진 조건이 리프블록을 찾아가는 과정, 부하가없다고생각해도 될 정도로 매우빠름
  2) 리프 블록 스캔(RANGE SCAN)
  3) 테이블 접근 : 인덱스리프 블록의 ROWID값을 참조해 테이블의 데이터를 찾는다. 이과정은 테이블에 필요한데이터가있을때만 발생
  인덱스 컬럼만으로 처리할수있다면 이과정은 생략.

INDEX RANGE SCAN VS. TABLE ACCESS FULL 성능비교

• 랜덤액세스
  : I/O작업 한번에 하나의 블록을 가져오는 접근방법을 뜻한다. 인덱스 리프블록에서 ROWID를 이용해 테이블에 접근할 때 랜덤 액세스가 발생한다.
  실행계획에는 'TABLE ACCESS BY INDEX ROWID'로 표시된다. 찾으려는 데이터가 많지 않으면, 랜덤엑세스가 효율적, 많으면 비효율적
• FULL SCAN
  : 많은양의 데이터를 읽어야할때는 성능이점이있을수있다. 데이터가 쌓일수록 성능이 점차나빠진다. 파티션전략이든 관리전략이 필요.
• 테이블접근 (=TABLE ACCESS BY INDEX ROWID)횟수를 줄이는것이 성능향상의 키, 테이블접근자체가 생략된다면 성능에 가장 좋다.

Predicate Information활용....

인덱스 ROWID구성요소

• 오브젝트 번호 + 데이터파일번호 + 블록번호 + 블록내 로우번호

  인덱스 스캔 종류(그림포함해서..)

• 인덱스 레인지 스캔 INDEX, INDEX_DESC
  선두절에 대한 조건이있으면 Range Scan가능, =조건일필요는없다.
  LIKE %조건%, 부정형비교조건(NOT), 인덱스컬럼 변형시에는 RANGE SCAN이 블가능하다
• 인덱스 패스트 풀스캔 INDEX_FFS
  Multi block I/O로 수행되며 결과의 순서 보장 안함
  인덱스에 포함된 컬럼으로만 조회할때 사용할 수 있다.
  병렬스캔도 가능
• 인덱스 스킵 스캔 INDEX_SS
  IN조건이 인덱스 액세스 조건일 때는 skip scan사용불가
• 인덱스 풀스캔 (별도의 힌트는 존재하지않는다.)
  선두컬럼이 조건절에 없으면 자동선택 실행된다.

  B*트리 인덱스에 생길수 있는 현상

• INDEX SKEW - 인덱스 엔트리가 한쪽으로 쏠림

• INDEX SPARSE - 인덱스 블록 전반에 밀도가 떨어짐

• 옵티마이저가 in조건을 강제로 추가할 순 없다.

  랜덤액세스 부담을 줄이는 기능

• IOT (INDEX-ORGANIZED TABLE)
  테이블을 인덱스 구조로 관리, 지정한 키 값 순으로 정렬상태를 유지 키값 외의 컬럼도 모두 인덱스 리프 블록에 저장하므로 테이블 랜덤액세스가 전혀발생하지 않는다.

• 클러스터
  값이 같은 레코드를 한 블록(데이터가 많을경우 연결된 여러 블록)에 모아서 저장하므로 인덱스를 이용한 테이블 랜덤액세스를 줄이는데 도움을 준다

• 테이블 파티션
  사용자 지정 기준에 따라 데이터를 세그먼트 단위로 모아서 저장한다. 따라서 특정조건을 만족하는 데이터를 인덱스를 이용한 랜덤액세스가 아닌 FULL SCAN 방식으로 빠르게 찾아갈 수 있다.

  리버스 인덱스

  키값을 REVERSE하게 저장함으로써 INDEX SKEW현상을 방지.
  인덱스 블록 경합 해소

  USE_CONCAT

  WHERE절에있는 OR(IN)을 UNION-ALL(중복허용) 연산을 이용하도록 실행계획을 세우는 역할
  select /+ USE_CONCAT/
  from table a
  where a.col1 in ('A','B','C')

  -내부적실행
  SELECT
  FROM TABLE
  WHERE A.COL1 = 'A'
  UNION ALL
  SELECT
  FROM TABLE
  WHERE A.COL1 = 'B'
  UNION ALL
  SELECT *
  FROM TABLE
  WHERE A.COL1 = 'C'