SQL 튜닝 (접근경로)

옵티마이저가 각각의 실행계획에 따른 비용을 산정하는데 있어서 테이블이나 뷰의 데이터를 읽어오는 방식을 접근 경로(Acces Path)라 하는데, 이러한 접근 경로에는 다음과 같은 것들이 있다.

Full Table Scan

---

일반적으로 Full Table Scan은 인덱스를 사용한 검색에 비해 비효율적인 접근 방식으로 알려져 있고 많은 경우에 있어서 비효율적이다.

테이블에 데이터가 적은 경우에는 성능차이가 미미하게 느껴질 수 있지만, 수십 혹은 수백만 건의 데이터를 가진 테이블에서 100건 정도의 데이터를 검색하기 위해 테이블 전체를 검색하는 것은 비효율적일 수 밖에없다.

하지만 Full Table Scan방식이 항상 좋지 않은 것은 아니다.

인덱스를 사용한 방법은 일단 인덱스 정보를 먼저 검색해서 실제 데이터를 찾아가는 방식인데, 100만 건의 데이터 중에서 80만건을 선택하는 경우라면 인덱스를 사용하는 것보다 테이블 전체를 검색하는 것이 더 비용이 적기 들기 때문이다.

옵티마이저가 Full Table Scan으로 검색하는 경우

1. 인덱스가 존재하지 않거나 쿼리가 인덱스를 사용하지 못하는 경우
• 조건에 사용된 컬럼이 인덱스에 잡혀 있지 않거나 사용하지 못하는 경우(예를 들면, 인덱스 컬럼에 변형이 가해진 경우)에는 Full Table Scan 방식으로 검색
2. 대용량의 데이터를 조회하는 경우
3. 소용량의 테이블의 경우
• 테이블에 있는 데이터들은 실제로 데이터 파일에 저장되어 있다. 따라서 쿼리의 결과에 따른 데이터를 산출해 내기 위해서는 일단 데이터 파일에서 데이터를 읽어 메모리에 올린 다음 그 결과를 보여주게 된다.
  

그런데 오라클은 한 번에 일정 크기만큼의 블록을 데이터 파일에서 읽어서 메모리에 올리는데, 바로 테이블의 크기가 이 일정 크기보다 작은 경우에는 인덱스를 사용할 수 있더라도 옵티마이저는 Full Table Scan 방식이 더 비용이 낮다고 판단한다.

ROWID Scan

---

ROWID는 테이블에 저장된 로우에 대해 정확한 위치 정보를 가지고 있으므로, ROWID를 이용한 검색방법은 단일 로우를 검색하는 데에 있어서는 가장 빠른 접근 방법이다.

일반적으로 ROWID에의한 접근은 인덱스를 사용한 검색 후 두 번째 단계에서 사용된다. (인덱스 검색 후 ROWID를 추출해낸 다음에 ROWID를 사용한 검색을 하게됨)

Index Unique Scan

---

인덱스를 사용한 검색 방법으로 검색속도가 매우 빠르며 검색 결과로 산출되는 로우의 수는 단 한개 뿐이다.

Index Range Scan

---

인덱스를 사용해서 인덱스 컬럼에 대한 범위(Range)검색을 하는 방법이다.

COL1 컬럼이 인덱스 컬럼이며(Unique 인덱스가 아닐 경우), WHERE 조건에서 다음과 같은 형태로 사용될 경우 Index Range Scan 방식으로 탐색하게 된다.

COL = value

COL1 > value

COL1 < value

COL1 Like 'ABC%'

=> COL1 LIKE '%ABC' 형태가 되면 Index Range Scan을 타지 않는다.

(참고서적 : 뇌를 자극하는 오라클 프로그래밍)