인덱스 기본개념

K·2022년 5월 16일
0

인덱스의 특징

  • 테이블과 마찬가지고 인덱스 저장하는 최소단위는 블록
  • 인덱스로 지정된 컬럼의 값과 해당데이터의 원본을 찾아갈수있는 주소로 구성
  • 컬럼수와 데이터 사이즈마다 인덱스블록에 저장되는 건수는 다를 수 있음
  • 모든컬럼이 NULL인경우 인덱스를 만들지 않음.

오라클에서만 사용하는 ROWID

  • 데이터마다 가지는 위치값을 ROWID
  • 오브젝트번호+데이터파일번호+블록번호+블록내ROW번호
  • 인덱스는 테이블 컬럼값과 ROWID를가짐
  • ROWID를 알고있으면 인덱스통하지않고 바로 데이터 접근가능
  • ROWID순으로 정렬하면 같은 블록끼리 모여있어 버퍼 피닝 효과를 만들수있다.

인덱스 스캔방식

  1. 인덱스 유니크 스캔
    • 루트블록 -브랜치 블록 - 리프블록까지내려가 최정데이터가 저장된 테이블블록을 읽어 단한건을 찾는다.
    • 결합 인덱스의 경우 일부 컬럼만으로 검색할때는 INDEX RANGE SCAN, INDEX FULL SACN으로 변형 되기도함.
  2. 인덱스 레인지 스캔
    • 가장 일반적으로 사용되는 인덱스 스캔
    • 인덱스를 수직탐색하다가 리프블록에 도달하여 시작지점을찾고 필요한 범위를 순차적으로탐색
    • 조건에 만족하지않는 첫번째컬럼까지 액세스한후 종료
    • PK인덱스 범위검색에도 사용
    • 선두절에 대한 조건이있으면 가능 =조건일 필요는없다.
    • LIKE %조건%, 부정형비교조건(NOT, <>), 인덱스컬럼변형(NVL(컬럼,0) = '조건') 등은 RANGE스캔 불가
  3. 인덱스 풀스캔
    • 인덱스 처음부터 마지막 리프 블록까지 순차적으로 스캔
    • 오라클은 인덱스 모든컬럼이 NULL일 경우 인덱스에 저장안됨 (MS SQL Server는 저장됨)
  4. 인덱스 스킵 스캔 (INDEX_SS)
    • 선두컬럼이 조회조건에 없을경우에 사용
    • 생략된 선두컬럼 값의 종류가 적을때 유용
      EX)성별+생년월일>효율좋음, 생년월일+성별>효율안좋음
    • IN조건이 인덱스 액세스 조건일때는 SKIP SCAN사용불가
  5. 인덱스 패스트 풀스캔(INDEX_FFS)
    • SELECT나 WHERE에 포함된 모든컬럼이 인덱스에 포함될경우 사용
    • MULTI BLOCK I/O로 수행되며 결과의 순서 보장 안함
    • 병렬스캔도 가능
    • 컬럼조건때문에 쓸기회가많지는 않지만, 인덱스를 멀티블록으로 읽을수 있는장점때문에 성능극적개선 가능성큼
  6. INDEX COMBINE
    • 한테이블에서 두개의 인덱스를 사용할 수 있게 해 주는 기능.
    • 두개의 인덱스를 사용해 각각의 결과 집합으로 ROWID를 만들어 놓고 조건절에 따라 AND또는OR연산해서 결과집합을 만듬.

인덱스 스캔 유도방식(힌트)

  • INDEX : 인덱스스캔유도, 스캔종류는 오라클이선택
  • INDEX_DESC : 인덱스 거꾸로 스캔
  • INDEX_RS : 인덱스 범위 스캔 유도, 범위스캔 불가능판단시 힌트무시
  • INDEX_SS : 인덱스 스킵 스캔을 유도, 스킵 스캔이 불가능판단시 힌트무시

인덱스 스캔 동작원리

  • WHERE SUBSTR(ITEM_NM,1,2) = '한우' 처럼 컬럼을가공하면 함수기반인덱스가 아니면 작동하지않음
    WHERE ITEM_NM LIKE '한우%' 로바꿀수있음

  • 묵시적 형변환 : SQL문에서 컬럼가공하지않았는데 내부적으로 형번환되는 경우
    컬럼 타입에 맞게 사용하는것을 습관화 해야함.

    • ORD_DT는 VARCHAR(8)로 되어있어, 인덱스스캔하지않고 전체슼내함.
      WHERE ORD_DT = TO_DATE('20120101')

    • WHERE ITEM_ID = '123' 여기서는 ITEM_ID가 NUMBER형임

profile
늙어가면서 기억을 남기는 개발자

0개의 댓글