[SQL 튜닝] Table Full Scan VS Index Range Scan VS Index Full Scan

Table Full Scan

- 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾음

- 시퀀셜 액세스 + Multi Block I/O

- Table Full Scan는 피해야 한다는 인식이 많지만, 오히려 인덱스를 사용하는 것이 성능을 떨어뜨리는 경우가 더 많음

- 캐시에서 못 찾으면 한 번의 I/O Call로 인접한 수십-수백 개의 블록을 한꺼번에 불러오는 것이 좋음

 

Index Range Scan

- 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 Index Range Scan이 가능

- 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾음

- 시퀀셜 액세스 + Multi Block I/O가 좋아도 소량의 데이터를 찾을 때 테이블 전체를 스캔하는  것은 비효율적임, 큰 테이블에서 소량의 데이터를 찾을 때는 반드시 인덱스를 사용해야 함

- 랜덤 액세스 + Single Block I/O

- 캐시에서 못 찾으면 레코드 하나를 읽기 위해 매번 I/O Call이 필요, 그러므로 많은 데이터를 읽을 때는 Table Full Scan보다 불리함

 

- 인덱스는 큰 테이블에서 아주 적은 일부의 데이터를 빨리 찾기 위한 도구일 뿐, 읽을 데이터가 일정 수량을 넘으면 인덱스보다는 Table Full Scan을 사용하는 것이 유리

- 인덱스를 안 타서 느린 경우보다 불필요하게 인덱스를 타서 느린 경우가 더 많더라...

- 예상 카디널리티가 일정량을 넘는 경우 인덱스 스캔을 하지 말도록... 힌트를 사용하여 Table Full Scan을 유도

 

Indes Full Scan

- 인덱스 선두 컬럼이 조건절에 없으면, Index Range Scan을 불가능하므로, 옵티마이저는 Table Full Scan을 고려함

- 그런데, 대용량 테이블이라 Table Full Scan에 대한 부담이 너무 크면, Index를 활용해야 할 필요가 있음

- 인덱스의 전체 크기는 테이블의 전체 크기보다 훨씬 적으므로, Index Range Scan을 할 수 없을 때, Table Full Scan보다는 Index Full Scan을 하는 것이 좋을 수 있음

- Index Scan을 통해 대부분의 레코드를 필터링하고 아주 일부만 테이블 액세스하는 상황이라면 크기가 큰 테이블을 Table Full Scan을 하기보다는 Index Full Scan 하는 것이 유리함

 

SELECT *
FROM  EMP
WHERE SAL > 9000
ORDER BY ENAME;

- 조건절 선두에 인덱스 선두 컬럼인 ENAME이 없어서 Index Range Scan은 불가능

- SAL > 9000인 레코드가 전체 테이블에서 아주 작은 일부라면 Table Full Scan보다는 Index Full Scan이 유리

- 하지만 이러한 Index Full Scan은 Index Range Scan을 하지 못한 차선책이므로, 수행 빈도가 낮은 SQL이라면 상관없지만, 그렇지 않다면 SAL칼럼이 인덱스 선두 컬럼인 인덱스를 생성하는 것이 좋음