[Oracle Tunning] INDEX Scan 방식

 이번에는 인덱스의 스캔 방식에 어떤 종류들이 있는 지 알아보자.

Index Unique Scan

• 루트 블록에서 시작하여 브랜치 블록을 거쳐 리프 블록까지 내려가서 최종 데이터가 저장된 테이블 목록을 읽어 단 한 건을 찾는 방식이다.
• 이 스캔 방식은 오직 수직적 탐색으로만 데이터를 찾는다.
• Unique Scan을 사용하려면 Unique 인덱스를 = 조건으로 탐색해야 하며, 중복 값이 없어야 한다.

출처: https://developer-guide.com/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%8A%A4%EC%BA%94-%EB%B0%A9%EC%8B%9D-index-range-full-unique-scan/

예시:

SELECT * FROM ITEM WHERE ITEM_ID = 11; -- PK 컬럼의 ID로 탐색 (Unique Scan)
SELECT * FROM UITEM WHERE ITEM_ID = 89; -- FK 컬럼이기 때문에 Range Scan

 

Index Range Scan

• 인덱스 스캔 중 가장 일반적으로 사용되는 방식이다. 인덱스를 수직 탐색하다가 리프 블록에 도달하여 시작 지점을 찾고, 필요한 만큼의 범위를 순차적으로 탐색한다.
• Index Range Scan 과정을 거쳐 생성된 결과 집합은 인덱스 컬럼 순으로 정렬된 상태가 되므로, Sort 연산을 생략하거나 min/max 값을 빠르게 출력할 수 있다.

출처: https://developer-guide.com/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%8A%A4%EC%BA%94-%EB%B0%A9%EC%8B%9D-index-range-full-unique-scan/

예시: 

SELECT * FROM ITEM WHERE ITEM_NM LIKE '밀크 쉐이크%'; 
-- 밀크 쉐이크로 시작하는 지점까지 수직 탐색 후 밀크 쉐이크 바로 다음 지점까지 수평적 탐색

 

Index Full Scan

• 범위가 정해진 Index Range Scan이라고 할 수 있다.
• 해당 인덱스의 첫 번째 리프 블록으로 이동하여 마지막 리프 블록까지 순차적으로 인덱스를 탐색한다.
• 일반적으로 데이터 검색을 위한 최적의 인덱스가 없을 때 차선책으로 사용된다.

출처: https://developer-guide.com/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%8A%A4%EC%BA%94-%EB%B0%A9%EC%8B%9D-index-range-full-unique-scan/

예시:

SELECT * FROM ITEM WHERE ITEM_NM IS NOT NULL;

 

Index Skip Scan

• 인덱스 스캔에 필요한 선두 컬럼이 조건절에 없을 때 사용 가능한 스캔 방식이다.
• 예를 들어, CREATE INDEX ITEM_X01 ON ITEM(ITEM_TYPE_CD, ITEM_NM); 인 인덱스가 ITEM 테이블에 걸려 있을 때, 아래와 같은 질의에서 Index Skip Scan이 발생할 수 있다.
• 생략된 컬럼 값의 종류가 적을 경우 매우 유용할 수 있지만, 생략된 컬럼의 값의 종류가 많을 경우 성능이 매우 느려질 수 있다.

출처: https://developer-guide.com/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%8A%A4%EC%BA%94-%EB%B0%A9%EC%8B%9D-index-range-full-unique-scan/

예시:

SELECT * FROM ITEM WHERE ITEM_NM LIKE '한우%';
-- Index의 선두 컬럼인 ITEM_TYPE_CD가 WHERE 절에 없기 때문에 Index Skip Scan이 발생

---

인덱스 스캔 유도 방법

Oracle은 기본적으로 실행 계획을 만들 때 통계 정보를 활용한다. 그러나 이 통계 정보가 실제 적재된 테이블의 상황과 다르다면, Oracle이 엉뚱한 실행 계획을 만들 수 있다. 이러한 경우 원하는 실행 계획을 유도하기 위해 힌트를 사용할 수 있다.

• INDEX: 인덱스 스캔을 유도하며, 어떤 인덱스 스캔을 유도할지는 Oracle이 선택한다.
• INDEX_DESC: 인덱스를 거꾸로 스캔한다.
• INDEX_RS: 인덱스 범위 스캔을 유도하며, 불가능하다고 판단될 시 힌트가 무시된다.
• INDEX_SS: 인덱스 스킵 스캔을 유도하며, 스킵 스캔이 불가능하다고 판단될 경우 힌트가 무시된다.

예시: 

SELECT /*+ INDEX(a EMP_IDX02) */ 
    a.empno,
    a.ename, 
    a.hiredate
FROM emp a;

Index Fast Full Scan

• 인덱스 스캔 중 유일하게 멀티블록 I/O 방식으로 스캔하는 방법이다.
• Index Fast Full Scan에서는 인덱스 스캔 시 정렬 순서가 보장되지 않는다.
• Index Fast Full Scan을 위해서는 SELECT 절과 WHERE 절에 있는 컬럼이 모두 인덱스에 포함되어 있어야 한다.
• 대용량의 인덱스 블록을 읽어야 할 때 효과적이다.

예시:

CREATE INDEX EMP_DEPTNO_ENAME_IDX ON emp(DEPTNO, ENAME);

SELECT /*+ index_ffs(EMP_DEPTNO_ENAME_IDX) */ * 
FROM emp 
WHERE deptno >= 20 
AND ename <= 'JONES';

SELECT /*+ no_index_ffs(EMP_DEPTNO_ENAME_IDX) */ * 
FROM emp 
WHERE deptno >= 20 
AND ename <= 'JONES';

Index Combine

• 한 테이블에서 두 개의 인덱스를 사용할 수 있게 해주는 기능이다. 두 개의 인덱스를 사용해 각각의 결과 집합으로 ROWID를 만들고, 조건절에 따라 AND 또는 OR 연산을 해서 결과 집합을 만든다.
• 새로운 인덱스를 추가하기 힘든 상황이나 기존 인덱스에 컬럼을 추가하기 부담스러운 상황에서 두 개의 인덱스를 사용하는 것으로 개선 효과를 기대할 수 있다.

예시:

회원_X01 -> 회원명
회원_X02 -> 거주지

SELECT /* INDEX_COMBINE(A 회원_X01 회원_X02) */
    *
FROM 회원A
WHERE 회원명 LIKE '박%'
AND 거주지 = '서울';

---

주의 사항

• 인덱스 컬럼은 가공하지 않는다.
  예를 들어, ITEM_NM이 인덱스로 잡혀 있을 때 WHERE SUBSTR(ITEM_NM, 1, 2) = '한우'로 쿼리를 실행하면 인덱스를 사용하지 않는다.
• 인덱스로 잡힌 컬럼의 자료형에 맞는 조건으로 검색해야 한다.
  묵시적으로 형 변환이 되거나 데이터 형을 잘못 사용했을 경우에는 인덱스를 타지 않는다.