오라클(Oracle) Index Scan 종류

오라클(Oracle)에서 Index Scan은 인덱스를 활용하여 데이터를 조회하는 방식이며, 다양한 방식으로 수행될 수 있습니다. 주요 Index Scan의 종류는 다음과 같습니다.

1. Index Unique Scan

• 특징: 인덱스의 고유한 값(UNIQUE, PRIMARY KEY)을 기준으로 한 건 또는 극소수의 데이터를 검색할 때 사용됨.
• 사용 조건:
  • 인덱스의 유니크 키(PK, UNIQUE INDEX)로 검색할 때
  • WHERE 절에서 완전한 키 값을 검색하는 경우
• 예제: SELECT * FROM EMP WHERE EMP_ID = 1001;
• 실행 계획 예시: INDEX UNIQUE SCAN (INDEX_NAME)

2. Index Range Scan

• 특징:
  • 인덱스의 특정 범위(조건)를 검색할 때 사용됨.
  • 일반적으로 BETWEEN, LIKE 'ABC%', <, >, IN 조건이 포함된 경우 발생.
  • 많은 결과를 반환할 가능성이 있어 효율적인 실행 계획이 필요함.
• 예제: SELECT * FROM EMP WHERE EMP_ID BETWEEN 1000 AND 2000; SELECT * FROM EMP WHERE NAME LIKE 'A%';
• 실행 계획 예시: INDEX RANGE SCAN (INDEX_NAME)

3. Index Full Scan

• 특징:
  • 인덱스의 전체 데이터를 순차적으로 조회하는 방식.
  • ORDER BY 절이 인덱스 정렬과 동일하면 테이블 액세스 없이 조회 가능.
  • 테이블 조회가 거의 없기 때문에, 특정 상황에서는 FULL TABLE SCAN보다 효율적일 수 있음.
• 사용 조건:
  • WHERE 절 없이 ORDER BY가 인덱스 순서와 일치하는 경우.
  • 필터링 없이 전체 인덱스를 읽어야 하는 경우.
• 예제: SELECT EMP_ID FROM EMP ORDER BY EMP_ID;
• 실행 계획 예시: INDEX FULL SCAN (INDEX_NAME)

4. Index Fast Full Scan

• 특징:
  • 인덱스 전체를 읽되 멀티 블록 읽기를 사용하여 성능을 향상시킴.
  • INDEX FULL SCAN과 다르게 인덱스 데이터를 병렬로 읽을 수 있음.
  • TABLE ACCESS가 불필요한 경우 매우 빠름.
• 사용 조건:
  • SELECT 절에서 인덱스에 포함된 컬럼만 사용하는 경우 (INDEX ONLY SCAN 최적화).
  • WHERE 절 없이 모든 인덱스 데이터를 읽어야 하는 경우.
• 예제: SELECT EMP_ID FROM EMP;
• 실행 계획 예시: INDEX FAST FULL SCAN (INDEX_NAME)

5. Index Skip Scan

• 특징:
  • 선행 컬럼이 없는 WHERE 조건을 사용할 때 인덱스를 활용할 수 있도록 함.
  • 복합 인덱스(Multi-Column Index)가 있는 경우, 선행 컬럼을 건너뛰고 인덱스를 스캔하는 방식.
• 사용 조건:
  • 선행 컬럼 없이 후행 컬럼만 조회하는 경우.
  • 인덱스의 선행 컬럼 값이 적고 후행 컬럼 값이 다양한 경우 효과적.
• 예제: -- 복합 인덱스 (DEPT_ID, EMP_NAME) 존재 SELECT * FROM EMP WHERE EMP_NAME = 'John';
• 실행 계획 예시 INDEX SKIP SCAN (INDEX_NAME)

6. Index Join Scan

• 특징:
  • 두 개 이상의 인덱스를 조합하여 테이블 조회를 최소화하는 방식.
  • 여러 개의 단일 컬럼 인덱스를 조합하여 AND 조건을 만족하는 데이터를 찾음.
• 사용 조건:
  • 여러 개의 단일 컬럼 인덱스가 존재하는 경우.
  • WHERE 조건이 인덱스 컬럼들을 동시에 포함할 때.
• 예제: SELECT * FROM EMP WHERE EMP_ID = 1001 AND DEPT_ID = 10;
• 실행 계획 예시: INDEX JOIN SCAN

7. Index Range Scan Descending

• 특징:
  • 인덱스를 역순(Descending)으로 스캔하는 방식.
  • ORDER BY column DESC 조건에서 발생.
• 예제: SELECT * FROM EMP ORDER BY EMP_ID DESC;
• 실행 계획 예시: INDEX RANGE SCAN DESCENDING (INDEX_NAME)

📌 정리

Index Scan 유형	특징	사용 조건
Index Unique Scan	고유(UNIQUE) 값 조회	WHERE PK = ?
Index Range Scan	특정 범위 조회	BETWEEN, <, LIKE
Index Full Scan	인덱스 전체 스캔	ORDER BY 정렬 시
Index Fast Full Scan	병렬 인덱스 전체 스캔	인덱스 컬럼만 조회
Index Skip Scan	선행 컬럼 없이 후행 컬럼 조회	복합 인덱스 후행 컬럼 사용
Index Join Scan	여러 인덱스를 조합하여 조회	다중 인덱스 활용
Index Range Scan Descending	인덱스 역순 검색	ORDER BY DESC

각 Index Scan은 쿼리 조건과 인덱스 구조에 따라 다르게 동작하므로, EXPLAIN PLAN 또는 AUTOTRACE를 통해 실행 계획을 확인하는 것이 중요합니다. 🚀

힌트 정리

Index Scan 유형	사용되는 힌트(Query Hint)	설명
Index Unique Scan	INDEX(TABLE_NAME INDEX_NAME)	유일한(PK, UNIQUE) 값을 조회할 때 사용.
Index Range Scan	INDEX(TABLE_NAME INDEX_NAME)	범위 검색 (BETWEEN, <, >, LIKE 'A%') 수행.
Index Full Scan	INDEX_FFS(TABLE_NAME INDEX_NAME)	인덱스를 전체 검색, 테이블 조회 최소화.
Index Fast Full Scan	INDEX_FFS(TABLE_NAME INDEX_NAME)	멀티 블록 읽기를 활용하여 인덱스 전체를 빠르게 스캔.
Index Skip Scan	INDEX_SS(TABLE_NAME INDEX_NAME)	복합 인덱스의 선행 컬럼 없이 후행 컬럼만 검색 가능.
Index Join Scan	INDEX_JOIN(TABLE_NAME INDEX1 INDEX2)	두 개 이상의 단일 컬럼 인덱스를 조합하여 테이블 조회를 줄임.
Index Range Scan Descending	INDEX_DESC(TABLE_NAME INDEX_NAME)	인덱스를 역순(Descending)으로 읽음.

📌 추가 참고 사항

• INDEX_FFS와 INDEX_SS는 성능 최적화에 중요한 역할을 하므로, 특정 상황에서 활용 가능성을 고려해야 합니다.
• INDEX_JOIN은 복합 인덱스를 생성하지 않고도 다중 인덱스를 활용하는 방법입니다.