Oracle Execute Plan에서 사용하는 Operation

ORACLE Execute Plan에 대한 Operation 

1. Index and Table Access

INDEX UNIQUE SCAN 

B-tree traversal 만으로 엔트리를 찾는다.
이는 unique제약 조건이 걸려 있는 경우에 사용된다.
이는 엔트리에서 오직 유일한 하나의 엔트리만 존재함을 의미한다.

INDEX RANGE SCAN

B-tree traversal과 leaf 노드 체인을 순회하면서 엔트리들을 찾는다.

INDEX FULL SCAN

전체 인덱스를 순서대로 모두 읽는다.
이는 시스템 통계 데이터나 인덱스 순서대로 모든 row들이 필요한경우 수행된다.
Optimizer는 INDEX FAST FULL scan을 수행하고 추가적인 sort 오퍼레이션을 수행할 수 있다.

INDEX FAST FULL SCAN 

디스크에 저장된 모든 인덱스를 읽어 들인다.
이는 일반적으로 요구되는 칼럼들이 인덱스에 존재하는 경우 Full table scan대신에 사용된다.
TABLE ACCESS FULL 과 유사하게 INDEX FAST FULL SCAN은 multi-block 읽기 연산을 수행한다.

TABLE ACCESS BY INDEX ROW ID

사전에 인덱스에서 찾아낸 row id를 기준으로 테이블 row를 읽어 들인다.

TABLE ACCESS FULL

이는 full table scan으로 알려져 있으며, 전체 테이블의 모든 row나 column을 읽어 들인다.
비록 풀 테이블 스캔을 수행할때 멀티블록 읽기를 수행함으로 해서 성능을 올리기는 하지만 여전히 가장 비용이 비싼 오퍼레이션이다.
높은 IO rate에서 풀테이블 스캔은 모든 테이블을 검사해야하기 때문에 매우 큰 량의 CPU 시간도 소비하게 된다.

2. JOIN

일반적으로 Join연산은 한번에 2개의 테이블만 조인이 가능하다.
쿼리가 더 많은 조인이 이루어 져야하는 경우에는 순차적으로 조인이 수행된다.
처음 두 테이블이 조인하고, 중간 결과를 이용하여 다음 테이블을 조인하는 방식으로 수행된다.
조인의 단위는 테이블이며 이는 결과적으로 중간 결과도 테이블 형태임을 의미한다.

NESTED LOOPS JOIN

하나의 테이블에서 다른 테이블 row각각을 조인한다.
for loop 2개를 사용한 것과 같음.

HASH JOIN 

한쪽에서 조인을 위한 대상을 로드하여 해시 테이블을 만든다. 그리고 다른 쪽 테이블에서 각 row를 probe 하는 방식으로 조인을 수행한다.

MERGE JOIN 

머지 조인은 2개의 정렬된 리스트를 상호 머지 하는 형태의 조인이다.
양쪽은 조인전에 반드시 정렬이 먼저 되어 있어야 한다.

3. Sorting and Grouping

SORT ORDER BY

order by절에 기술된 사항에 따라 소트를 한다. 이 처리는 많은 양의 메모리를 필요로 하게 되며 이 메모리는 중간 결과를 저장히기 위한 공간이다. (pipelined되지 않음)

SORT ORDER BY STOPKEY

order by 절의 결과의 서브 셋을 소트한다.
pipelined 처리가 불가능 한경우 top N쿼리를 위해 사용된다.

SORT GROUP BY

group by 칼럼들에 대한 결과셋을 sort한다. 그리고 sort된 결과를 다음 스텝에 집계한다.
이 오퍼레이션은 많은 량의 메모리를 중간 결과 저장을 위해 사용하게 된다. (pipelined 되지 않는다.)

SORT GROUP BY NOSORT

group by 절에 의해서 서전 분류된 집계를 수행한다.
이 처리는 중간 결과를 위한 버퍼를 필요치 않는다.
이는 pipelined에서 처리가 수행된다.

HASH GROUP BY 

tash table를 이용한 결과 그룹을 찾아낸다.
이 처리는 많은 양의 메모리를 결과셋을 저장하기 위해서 사용된다. (pipelined 안됨)
출력은 정렬되어 있지 않다.

4. Top-N Queries

top-N쿼리의 효과는 아래 오퍼레이션에 따라서 결정된다.
SORT ORDER BY와 같은 non-pipelined 을 중단할때 매우 비효율 적이다.

COUNT STOPKEY

원하는 개수의 row가 fetch되면 오퍼레이션을 정지한다.

WINDOW NOSORT STOPKEY

window함수를 이용하는 경우 원하는 row 개수만큼 fetch되면 오퍼레이션을 정지 시킨다.


from : http://use-the-index-luke.com/sql/explain-plan/oracle/operations

Oracle Predicate

Oracle Predicate 

오라클 Predicate에는 3가지 종류가 있음

1. Access predicate ("access")

- 인덱스의 leaf노드를 검사하는 경우를 지칭한다.

2. Index filter predicate ("filter" 이며 인덱스에서 이루어짐)

- 인덱스의 leaf노드를 검사하며, 인덱스의 검색 시작에서 끝이 지정되지 않는 경우 발생한다. 즉 인덱스를 다 검사하고 해당 내역이 아닌경우 필터 처리를 한다.
- 안전성 면에서 좋지 않음.
- 인덱스가 사용된다고 하더라도 테이블의 데이터 볼륨이 증가되거나, 시스템 로드가 빠른 속도로 증가되는 경우 급격히 성능 저하가 발생 될 수 있다.

3. Table level filter predicate("filter"이며 테이블 엑세스 처리)

- 필터 처리 대상이 인덱스에 존재하지 않고 테이블에 존재할 경우 발생한다.
- filter predicate가 발생하더라도 access predicate와 함께 발생하는 경우에는 성능에 대한 이슈가 크지 않다.

예제)

------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 | 1445 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  4485 | 1445 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=:A AND "ID2"=:B)
       filter("ID2"=:B)

상기 예제는 2번 항목에 대해서 ID2와 SECTION이 인덱스로 검색 되어 access predicate가 걸렸음을 나타내고 있다.
그리고 ID2는 filter 처리 되었음을 보여주고 있다.

즉, SECTION = :A에 의해서 range 스캔을 수행하고, 각 row에 대해서 ID2 = :B로 필터링을 적용하였음을 보여준다.