Full Table Scan, Index Scan 차이점

Full Table Scan, Index Scan 비교

1. Full Table Scan

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

- 오라클의 경우 HWM(High Water Mark, 고수위 마크) 아래의 모든 블록을 읽는다.

- 일반적으로 블록들은 서로 인접되어 있기 때문에 Full Table Scan은 한 번의 I/O에 여러 블록을 옮긴다. 즉, 한 번의 I/O에 데이터를 다중 블록 단위로 메모리에 가져오기 때문에, row 당 소요되는 입출력 비용이 인덱스 스캔에 비해 적다.

- 읽은 블록들은 재사용성이 떨어진다. 그렇기 때문에 Full Table Scan 방식으로 읽은 블록들은 메모리에서 곧 제거될 수 있도록 관리된다.

 

1.1 HWM(High Water Mark)

Full Table Scan과 HWM

- 고수위 마크라고도 불린다.

- 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치를 의미한다.

- 삭제 등으로 인해 데이터가 존재하지 않을 수도 있다.

 

1.2 옵티마이저가 Full Table Scan을 선택하는 경우

구분	설명
적용 가능한 인덱스가 없는 경우	- 결합 인덱스의 선두 칼럼이 존재하지 않을 때 Full Table Scan을 선택한다. - 적용할 인덱스가 있지만 컬럼이 수정되어 해당 인덱스를 적용할 수 없을 때 Full Table Scan을 선택한다.
넓은 범위의 데이터를 다룰 경우	적용 가능한 인덱스가 존재하더라도 처리 범위가 넓어서 오히려 Full Table Scan이 적은 비용이 드는 경우가 존재한다.
소량의 테이블을 다룰 경우	HWM 내에 있는 블록이 DB_FILE_MULTIBLOCK_READ_COUNT 이내에 있다면 Full Table Scan이 일어날 수 있다. 하지만 항상 그런 것은 아니다.
병렬처리를 다룰 경우	병렬처리는 Full Table Scan을 더욱 효과적으로 수행하게 한다. 그렇기 때문에 병렬처리로 수행되는 실행 계획을 수립할 때는 항상 Full Table Scan을 선택한다.
FULL 힌트를 적용한 경우	- FULL 힌트를 사용했을 경우 Full Table Scan을 선택한다. - 물론 FULL 힌트가 적절하지 않다면 옵티마이저는 이를 무시한다.

 

1.3 DB_FILE_MULTIBLOCK_READ_COUNT

- 한 번의 I/O 작업으로 읽어 들이는 최대 블록 수를 의미한다.

- 이 값이 크면 한 번 의 I/O로 더 많은 수의 블록을 읽어오므로 Full Table Scan 시에는 성능이 증가한다.

- 특정 블록만 필요한 경우, 불필요한 블록까지 함께 읽어오므로 디스크를 읽는 시간이 증가할 뿐만 아니라 크기가 한정된 Buffer Pool을 많이 차지함으로써 자주 쓰이는 데이터를 밀어내어 오히려 Cache 효율을 떨어뜨리는 결과를 가져올 수도 있다. 따라서 작업 특성에 따라 적절한 값을 세팅해야 한다.

- 오라클의 Optimizer는 이 값이 크면 작업의 효율성을 위해서 Full Table Scan을 선택할 가능성이 커지게 된다.

 

2. Index Scan

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

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

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

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

 

참고

- https://hoon93.tistory.com/53