인덱스 스캔(Index Scan)과 전체 테이블 스캔(Full Table Scan)

전체 테이블 스캔(Full Table Scan)

Full Table Scan과 high water mark

1) 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식이다.

2) Oracle의 경우, 테이블의 고수위 마크(HWM, High Water Mark) 아래의 모든 블록을 읽는다.

※고수위 마크(HWM): 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치(현재는 지워져서 데이터가 존재하지 않을 수도 있음)를 의미.

3) 위 그림과 같이 일반적으로 블록들은 서로 인접되어 있기 때문에, Full Table Scan는 한 번의 I/O에 여러 블록을 옮겨온다. 즉, 한 번의 I/O에 데이터를 다중 블록 단위로 메모리에 가져오기 때문에, Row 당 소요되는 입출력 비용이 인덱스 스캔에 비해 적다. 메모리에 옮겨진 블록들은 순차적으로 읽힌다.

4) 결과를 찾기 위해 꼭 필요해서 모든 블록을 읽은 것이다. 따라서 이렇게 읽은 블록들은 재사용성이 떨어진다. 그래서 전체 테이블 스캔 방식으로 읽은 블록들은 메모리에서 곧 제거될 수 있도록 관리된다.

그렇다면 옵티마이저가 '전체 테이블 스캔'을 선택하는 경우는 언제일까??

상황	설명
적용 가능한 인덱스가 없는 경우	① 결합 인덱스의 선두 칼럼이 존재하지 않을 때. ② 적용할 인덱스가 있지만 칼럼을 가공 or 연산하여 그 인덱스를 사용할 수 없을 때.
넓은 범위의 데이터 액세스	적용 가능한 인덱스가 존재하더라도 처리 범위가 넓어서 전체 테이블 스캔이 보다 적은 비용이 든다면 Full Table Scan을 적용할 수 있다.
소량의 테이블 액세스	고수위 마크(HWM) 내에 있는 블록이 DB_FILE_MULTIBLOCK_READ_COUNT(한번에 I/O 작업으로 읽어들이는 최대 블럭 수) 이내에 있다면 Full Tables Scan이 일어날 수 있다. (항상 그런것은 아님)
병렬처리 액세스	병렬처리는 Full Table Scan을 더욱 효과적으로 수행하게 되므로 병렬처리로 수행되는 실행계획을 수립할 때는 항상 Full Table Scan을 선택한다.
'FULL' 힌트를 적용한 경우	Full 힌트를 사용했을 때. 단, Full힌트가 적절하지 않다면 옵티마이저는 이를 무시할 수 있다.

※DB_FILE_MULTIBLOCK_READ_COUNT : 이 값이 크면 한 번의 I/O로 더 많은 수의 블록을 읽어오므로 Full Table Scan시에는 성능이 증가한다. 하지만 특정 블록만 필요한 경우에 불필요한 블록까지 함께 읽어들이므로 디스크를 읽는 시간이 증가할 뿐만 아니라 크기가 한정된 Buffer Pool을 많이 차지함으로써 자주 쓰이는 데이터를 밀어내어 오히려 Cache의 효율을 떨어뜨리는 결과를 가져올 수도 있다. 따라서 작업 특성에 따라 적절한 값을 세팅해야 한다. 오라클의 Optimizer는 이 값이 크면 작업의 효율성을 위해서 Full Table Scan을 선택할 가능성이 커지게 된다.

인덱스 스캔(Index Scan)

Full Table Scan

Index Scan

1) 인덱스(Index)를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법이다.

2) 인덱스에 존재하지 않는 칼럼의 값이 필요한 경우에는 현재 읽은 레코드 식별자를 이용하여 테이블을 액세스해야 한다. 그러나 SQL문에서 필요로 하는 모든 칼럼이 인덱스 구성 칼럼에 포함된 경우 테이블에 대한 액세스는 발생하지 않는다.

3) 인덱스의 구성 칼럼이 A+B라면 먼저 칼럼 A로 정렬되고 칼럼 A의 값이 동일할 경우에는 칼럼 B로 정렬된다. 그리고 칼럼 B까지 모두 동일하면 레코드 식별자로 정렬된다.

4) 인덱스가 구성 칼럼으로 정렬되어 있기 때문에 인덱스를 경유하여 데이터를 읽으면 그 결과 또한 정렬되어 반환됨. → 따라서 인덱스의 순서와 동일한 정렬 순서를 사용자가 원하는 경우에는 정렬 작업을 하지 않을 수 있다.

정리

대용량 데이터 중에서 극히 일부의 데이터를 찾을 때, 인덱스 스캔 방식(Index Scan)은 인덱스를 이용해 몇 번의 I/O만으로 원하는 데이터를 쉽게 찾을 수 있다. 그러나 전체 테이블 스캔(Full Table Scan)은 테이블의 모든 데이터를 읽으면서 원하는 데이터를 찾아야 하기 때문에 비효율적인 검색을 하게 된다. 그러나 반대로 테이블의 대부분의 데이터를 찾을 때는 한 블록씩 읽는 인덱스 스캔 방식 보다는 어차피 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리할 수 있다.