[Oracle] 스캔(Full Scan, Index Scan) 종류

Table Full Scan 

Table Full Scan

• 테이블에 존재하는 모든 테이터를 읽어 조건에 맞는 결과를 추출
• 테이블의 고수위 마크(HWM) 아래 모든 블록을 읽음
• 전체 데이터를 읽으므로, 검색시간이 과도할 수 있고, 메모리에서 빨리 제외

 

옵티마이저가 'Index Scan'이 아닌 'Table Full Scan'을 하는 이유

• SQL문 조건절에 검색을 위한 조건이 하나도 없을때
  • 사용 가능한 인덱스가 존재하지 않는 경우
  • 조건이 변형되어진 경우(함수 사용)
• 조건에 만족하는 데이터가 많아 테이블의 대부분 블록을 액세스하여 Full Scan 보다 효율이 낮다 판단하면 인덱스를 버리고 Full Scan을 선택 
• 병렬 처리 방식(Parallel)으로 처리하는 경우
• 블록이 몇개 안될 경우(인덱스 스캔이 비효율적일 때)
• 전체 테이블 스캔 방식의 힌트를 사용한 경우
• 부정형 연산자를 사용한 경우

---

Index Scan

• 인덱스를 구성하는 컬럼의 값과 ROWID를 기반으로 데이터를 추출하는 액세스 기법
• 인덱스의 리프 블록은 인덱스 구성하는 컬럼(Index Key)과 레코드 식별자(ROWID)로 구성
• 필요로 하는 모든 컬럼이 인덱스 구성 안에 해당 컬럼이 포함되있다면 테이블 액세스는 발생하지 않음
• 인덱스를 구성할 때의 컬럼 순서로 정렬

---

Index Scan 종류

1. 인덱스 유일 스캔(Index Unique Scan)
2. 인덱스 범위 스캔(Index Range Scan)
3. 인덱스 전용 스캔(Index Only Scan)
4. 인덱스 스킵 스캔(Index Skip Scan)
5. 인덱스 전체 스캔(Index Full Scan)
6. 인덱스 고속 전체 스캔(Index Fast Full Scan)
7. 인덱스 역순 범위 스캔(Index Range Scan Descening) 
8. 인덱스 ROWID에 의한 테이블 엑세스(Table Access By Index Rowid)

 

1. Index Unique Scan(인덱스 유일 스캔)

• OLTP 작업에 많이 사용하여 단 하나의 데이터를 추출하는 방식
• Unique Scan 구성 컬럼들의 조합이 중복되지 않음
• Unique Index 나 Primary Key 가 설정된 컬럼을 ' = ' 조건으로 조회할 때만 작동

 

2. Index Range Scan(인덱스 범위 스캔)

• 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식
• Unique Scan과 반대로 인덱스 구성 컬럼 모두에 대해 ' = '로 값이 주어지지 않은 경우
• 비유일 인덱스(Non-Unique Index)를 이용하는 모든 액세스 방식은 인덱스 범위 스캔 방식

 

3. Index Only Scan(인덱스 전용 스캔)

• 인덱스에 포함된 컬럼으로만 데이터를 추출하는 방식
• 유저가 요청한 모든 컬럼이 인덱스 구성 컬럼에 이미 다 포함되어 있을 경우
• 테이블 블록을 아예 방문하지 않고, 리프 블록만 읽고 바로 결과를 반환하므로 I/O를 획기적으로 줄일 수 있음

 

4. Index Skip Scan(인덱스 스킵 스캔)

• 결합 인덱스에서 선행 컬럼을 건너뛰고 후행 컬럼 조건으로 탐색하는 방식
• 인덱스의 첫 번째 컬럼이 조건절에 없지만, 그 컬럼의 종류가 적을 경우

👇결합 인덱스란?

보통 인덱스는 컬럼 하나에만 걸지만, 결합 인덱스는 [부서번호 + 이름] 처럼 여러 컬럼을 묶어서 하나의 리프 블록에 저장합니다.

 

5. Index Full Scan(인덱스 전체 스캔)

• 리프 블록의 처음부터 끝까지 양방향 링크를 따라 모든 데이터를 읽는 방식
• ORDER BY가 있거나, 테이블 전체를 읽어야 하는데 인덱스 구성 컬럼만으로도 읽을 수 있는 경우
• 인덱스는 정렬된 상태로 추출되므로 별도의 정렬 부하가 없음

 

6. Index Fast Full Scan(인덱스 고속 전체 스캔)

• 인덱스 트리 구조를 무시하고, 인덱스 세그먼트 전체를 Multiblock I/O로 덩어리째 읽어버리는 방식
• 결과의 정렬 순서가 중요하지 않을 때, 인덱스에 포함된 데이터만 빨리 가져오고 싶을 때 사용
• 결과가 정렬되어 있지 않음

 

7. Index Range Scan Desending(인덱스 역순 범위 스캔)

• 인덱스의 리프 블록의 양방향 링크를 이용하여 내림차순으로 데이터를 읽는 방식(Index Range Scan의 역방향 전개)
• 인덱스의 정렬 특성을 활용하여, 별도의 Sort 연산 없이도 데이터를 내림차순으로 빠르게 추출할 수 있는 스캔 기법

 

8. Table Acess By Index Rowid(인덱스 ROWID에 의한 테이블 액세스)

• 인덱스 리프 블록에서 찾은 ROWID를 가지고 테이블 블록을 하나씩(Single Block I/O) 읽는 방식
• 필요한 데이터가 소량일 때 효율적이지만, 읽어야 할 행이 많아지면 랜덤 액세스 부하가 급증하여 성능이 떨어짐

---

Table Full Scan과 Index Scan의 차이점

구분	Table Full Scan(전체 스캔)	Index Scan(인덱스 스캔)
읽는 범위	테이블의 모든 블록	인덱스 리프 블록 + 필요한 테이블 블록
I/O 단위	Multi-Block I/O(한 번에 여러개 블록)	Single Block I/O(한 번에 한 개의 블록씩)
데이터 추출	정렬되지 않은 상태로 추출	인덱스 키 컬럼 순으로 정렬되어 추출
효율성	대량의 데이터를 읽을 때 유리	소량의 데이터를 정교하게 찾을 때 유리
인덱스 유무	인덱스가 없어도 가능	인덱스가 반드시 존재해야 함

 

1. I/O 처리 방식

• Table Full Scan: 한 번에 여러 블록을 읽어 메모리로 올리기 때문에 블록 당 읽는 속도가 매우 빠름
• Index Scan: 블록을 하나씩 읽기 때문에 읽어야 할 블록이 많아지면 속도가 급격히 느려짐

2. 손익분기점(Selectivity)

• Index Scan이 유리하면 보통 전체 데이터의 약 15% 이내를 찾을 때여야 함
• 15% 이상의 데이터를 찾는다면, Table Full Scan으로 뭉텅이씩 읽는 것이 '랜덤 액세스' 부하를 줄일 수 있어 더 효율적

3. 정렬(Sort) 여부

• Index Scan은 인덱스 자체가 이미 정렬 되어 있어서 결과물도 정렬된 상태로 출력. ORDER BY 부하를 줄여주는 장점
• Table Full Scan은 데이터가 저장된 순서로 읽기 때문에 정렬을 보장하지 않음

---

💡시나리오로 감 잡기

1. "인덱스가 있는데 왜 자꾸 전체 스캔을 할까?"

• 상황: 사원 테이블(EMP)의 100만 건 데이터 중 급여(SAL)가 1,000원 이상인 사람을 조회합니다. SAL에 인덱스가 분명히 있는데, 실행 계획을 보니 Table Full Scan이 뜹니다.

👇원인과 해결

• 원인: 손익분기점(Selectivity) 때문입니다. 급여가 1,000원 이상인 사람이 전체의 90%라면, 인덱스를 타고 테이블을 90만 번 왔다 갔다(Single Block I/O) 하는 것보다, 그냥 테이블 전체를 뭉텅이로(Multi-Block I/O) 한 번에 읽는 게 훨씬 빠르다고 판단한 것입니다.
• 해결: 이건 오류가 아니라 옵티마이저의 현명한 선택입니다. 만약 강제로 인덱스를 타게 하면 '랜덤 액세스' 부하로 인해 시스템이 더 느려질 수 있습니다.

 

2. "로그인 처리가 0.001초 만에 끝나는 비결"

• 상황: 웹사이트 로그인 시 아이디(ID)를 조회합니다. 사용자가 수천만 명인데도 결과가 즉시 나옵니다.

👇원인과 해결

• 원인: ID 컬럼이 Primary Key로 설정되어 있어 Index Unique Scan이 작동했기 때문입니다. 수직적 탐색만으로 단 하나의 ROWID를 정확히 짚어내기 때문에 데이터 양에 상관없이 일정한 고속 성능을 보장합니다.
• 해결: 고유한 값을 찾는 쿼리에는 반드시 Unique 인덱스나 PK를 설정하여 Unique Scan이 유도되도록 설계해야 합니다.

 

3. "최근 게시글 10개를 가져오는데 ORDER BY가 사라졌다?"

• 상황: 게시판에서 가장 최근 글 10개를 보여주려고 ORDER BY 등록일 DESC 쿼리를 실행했습니다. 그런데 실행 계획에 SORT 연산이 보이지 않습니다.

👇원인과 해결

• 원인: Index Range Scan Descending 덕분입니다. 등록일(REGDATE) 인덱스의 리프 블록은 이미 정렬되어 있고 양방향으로 연결되어 있습니다. 오라클은 인덱스의 뒤쪽(최신값)부터 거꾸로 10개만 읽고 바로 멈췄기 때문에 별도의 정렬 작업이 필요 없었던 것이죠.
• 해결: 대량 데이터의 최신순 조회 시 인덱스를 활용하면 무거운 Sort 부하를 획기적으로 줄일 수 있습니다.

 

4. "왜 '아닌 것'을 찾을 때는 인덱스가 침묵할까?"

• 상황: WHERE 부서번호 <> 10 (10번 부서가 아닌 사람) 쿼리를 날렸더니 인덱스가 있음에도 Table Full Scan을 합니다.

👇원인과 해결

• 원인: 인덱스는 '있는 값'을 찾기 위해 정렬된 지도입니다. 부정형(<>, NOT IN, IS NOT NULL) 연산자는 "무엇이 아닌 것"을 찾아야 하므로 결국 거의 모든 데이터를 다 확인해야 합니다. 인덱스의 효율이 없다고 판단한 옵티마이저는 전체 스캔을 선택합니다.
• 해결: 가급적 긍정형 조건(IN, =)으로 쿼리를 작성하거나, 반드시 부정형을 써야 한다면 전체 스캔이 비효율적이지 않은 데이터 양인지 체크해야 합니다.

 

 

 

 

 

참조: 멋쟁이사자처럼 - 한 번에 합격라는 SQLP 과정