인덱스 기본 - INDEX SCAN종류

K·2022년 6월 26일
2

친절한SQL튜닝

목록 보기
3/16

1. INDEX RANGE SCAN

  • B*Tree인덱스의 가장 일반적이고 정상적인 형태
  • 필요한 범위만 스캔
  • 실행계획 : INDEX (RANGE SCAN) OF '인덱스명' (INDEX)
  • 선두컬럼 가공하지않은 상태로 조건절에 사용
    - 성능은 인덱스 스캔범위, 테이블 엑세스 횟수를 얼마나 줄일수있느냐로 결정(인덱스탄다고끝이아님)

2. INDEX FULL SCAN

  • 수직탐색 없이 리프블록 처음부터 끝까지 수평적으로 탐색하는 방식
  • 실행계획 : INDEX (FULL SCAN) OF '인덱스명' (INDEX)
  • 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택
  • 마땅한 인덱스가없을때 조건절에서 필터후의 데이터량이 소량이라면
    인덱스풀스캔후 필터된 데이터를 대상으로 테이블에 엑세스하는것이 효율적이다.

3. INDEX UNIQUE SCAN

  • 수직적 탐색만으로 데이터를 찾는 방식
  • 등치(=)조건으로 탐색하는 경우에 작동
  • 실행계획 : INDEX (UNIQUE SCAN) OF '인덱스명' (UNIQUE)
  • UNIQUE INDEX라해도 범위검색조건 (BETWEEN, 부등호, LIKE)로 검색할때는 IDNEX RANGE SCAN으로 처리된다.

4. INDEX SKIP SCAN

  • 인덱스 선두컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 TABLE FULL SCAN을선택
  • TABLE FULL SCAN보다 I/O를줄이거나 정렬된 결과를 쉽게 얻을수 있다면 INDEX FULL SCAN사용
  • 9i버전부터 선두컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식을 선보임 > skip scan
  • 인덱스 선두컬럼의 DISTINCT VALUE개수가 적고 후행 컬럼의 DISTINCT VALUE갯수가 많을때 유용
    ex) 성별 - 고객번호 인덱스
  • 힌트 : index_ss, no_idex_ss(방지)

    -- index_ss 힌트 사용.
    select /+ index_ss(사원 사원_IDX) / *
    from 사원
    where 연봉 between 2000 and 4000

  • 1번 블록은 성별 남 보다 작은 성별이있는지 확인하기 위함
  • 남&1000>= 블록은 안타도되는거지만 컴퓨터는 성별이 남,여만있다는것을모르기때문에
    중간에 해당하는 값이 있는지 확인하기위해 접근한다. (10번블록도 마찬가지)

인덱스 skip scan이 작동하기 위한 조건

  • 인덱스 일별업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자
  • 아래 SQL처럼 선두컬럼에 대한 조건절은 있고, 중간컬럼(=업종코드)에 대한 조건이 없는 경우에도 SKIP SCAN사용할 수 있다.

    SELECT /*+ INDEX_SS(A 일별업종별거래_PK) */
    기준일자, 업종코드, 체결건수, 체결수량, 거래대금
    FROM 일별업종별 거래 A
    WHERE 업종유형코드 = '01'
    AND 기준일자 BETWEEN '20080501' AND '20080531'

  • Distinct Value 개수가 적은 두 개의 선두컬럼이 모두 조건절에 없어도 Skip Scan사용가능
  • 선두컬럼이 부등호, BETWEEN, LIKE같은 범위검색 조건일 때도 SKIP SCAN사용가능.

5. INDEX FAST FULL SCAN

  • INDEX FULL SCAN보다 빠르다 > 논리적 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 MULTIBLOCK I/O방식으로 스캔하기 때문
  • 힌트 : INDEX_FFS, NO_INDEX_FFS
  • INDEX FULL SCAN은 논리적 구조에따라 읽지만, FFS는 물리적으로 디스크에 저장된 순서대로 리프블록들을 읽어들인다.
  • 디스크로 부터 대량의 인덱스블록을 읽어야할 때 큰 효과를 발휘
  • 속도는 빠르지만, 인덱스 리프노드가 갖는 연결 리스트 구조를 무시한채 읽기 때문에 인덱스 키순서대로 정렬되지 않는다.
  • 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을때만 사용할 수 있다
  • 인덱스가 파티션 돼 있지않아도 병렬쿼리가능
  • 병렬쿼리시에는 DIRECT PATH I/O방식을 사용
  • 요약 : 세그먼트 전체 스캔, 순서보장 안됨, MULTIBLOCK I/O, 병렬스캔가능, 인덱스에 포함된컬럼으로만 조회할때 사용가능

6. INDEX RANGE SCAN DESCENDING

  • INDEX RANGE SCAN과 기본적으로 동일
  • 뒤에서부터 앞쪽으로 스캔하기때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만다르다.
  • 실행계획 : INDEX (RANGE SCAN DESCENDING) OF '인덱스명' (INDEX(UNIQUE))

    SELECT * FROM EMP
    WHERE EMPNO > 0
    ORDER BY EMPNO DESC

  • 아래처럼 MAX값을 구할때에도 해당컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한건만 읽고 멈추도록 실행계획이 자동으로 수립

    -- 쿼리
    SELECT DEPTNO, DNAME, LOC
            
    , (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)
    FROM DEPT D
    -- 실행계획
    FIRST ROW
      INDEX(RANGE SCAN (MIN/MAX)) OF '인덱스명' (INDEX)

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

0개의 댓글