3.2 실행계획의 유형

실행계획의 유형 목차

3.2.1 스캔을 위한 실행계획

3.2.1.1 전체테이블스캔

• 테이블에 있는 모든 로우들을 읽어내는 방법
• 다중 블록단위로 메모리에 옮겨지며, 이블록들을 순차적으로 읽혀진다.
• 한번에 엑세스 하는 블록의 양을 DB_FILE_MULTIBLOCK_READ_COUNT파라메터에서 지정한다.

<전체테이블스캔을 하는 경우>

• 적용가능 인덱스의 부재
• 넓은 범위의 데이터 액세스
• 소량의 테이블 액세스
• 병렬처리 액세스
• FULL힌트 사용

p188~192

3.2.1.2 로우식별자(ROWID)스캔

• 단 하나의 로우를 테이블에서 추출하는 가장 빠른 방법.
• 로우의 이주(Migration)나 체인(Chain), import/export 에 의해서 변경 될 수 있으므로 리터럴 값으로 직접사용하지 말고 바인드 변수를 사용할 것.
  로우식별자 통계정보

3.2.1.3 인덱스스캔

가)인덱스 유일스캔 (Index Unique Scan)

• 단 하나의 ROWID를 추출함.
• 인덱스가 기본키나 Unique Index로 생성되어 있어야 함.
• 구성된 모든 컬럼이 조건절에서 '='로 비교되어야 함.

나)인덱스 범위스캔 (Index Range Scan)

• 가장 보편적인 데이터 액세스 형태.
• 시작점을 찾을땐 랜덤엑세스, 그 이후부터 종료시까지는 스캔을 함.

다)인덱스 역순범위스캔(Index Range Scan Descending)

• 인덱스 범위스캔과 엑세스 형태는 동일하나 역순으로 데이터를 액세스 한다.
• ORDER BY ~ DESC 를 사용하거나 INDEX_DESC 힌트를 사용하여 적용함.

라)인덱스 스킵스캔 (Index Skip Scan)

• 인덱스의 선행 컬럼이 사용되지 않더라도 상위의 각각 분기된 가지별로 주어진 조건 컬럼을 스캔.
  인덱스스킵스캔 예제

마)인덱스 전체스캔 (Index Full Scan)

• 조건절에서 그 인덱스 컬럼이 적어도 하나 이상 사용되었을때 적용이 가능.
• 쿼리내에 사용된 어떤 테이블들의 모든 컬럼들이 그 인덱스에 모두 존재하고,
• 인덱스 컬럼 중에서 최소한 NOT NULL인 컬럼이 하나는 존재할때
  인덱스전체스캔 예제

바)인덱스 고속 전체스캔 (Index Fast Full Scan)

• 쿼리를 위해 사용한 어떤 테이블의 컬럼이 모두 그 인덱스에 포함되어 있을때 전체 테이블스캔의 대안으로 사용한다.
• 비트맵 인덱스에는 적용할 수 없다.
• INDEX_FFS / NO_INDEX_FFS 힌트로 조절 가능하다.
  인덱스고속스캔 예제

인덱스 조인

• p235 에서 다룸

비트맵 인덱스

3.2.1.4 B-Tree클러스터액세스(Cluster access)

3.2.1.5 해쉬클러스터액세스(Hash cluster access)

3.2.1.6 표본테이블액세스(Sample table scan)

3.2.2 데이터 연결을 위한 실행계획

참조: 조인의 최적화

^join_optimize.ppt

3.2.2.1 내포조인(Nested loops Join)

• 가장 기본적인 조인

<특징>

• 먼저 수행되는 집합의 처리범위가 전체의 일량을 좌우한다.
• 나중에 반복수행되는 연결작업이 랜덤액세스로 발생한다.
  => 소량의 범위처리를 할땐 유리하지만 대량의 범위는 부하를 많이 준다.

<수행절차>

• 옵티마이져는 먼저 수행될 외측집합을 결정한다(Driving). 선행집합의 처리 범위에 있는 각 로우에 대해 내측 집합을 연결하게 된다.
• 선행집합이 액세스 되면 그들의 모든 컬럼은 상수값을 가지게 된다. 나머지 집합들 중에서 다음 수행할 내측 집합을 선택한다.
• 위의 방법으로 나머지 순서도 결정한다.
• 실제로 조인이 수행될 때는 외측집합의 각각의 로우에 대해 내측 집합의 대응되는 모든 로우가 액세스 된다.
  내포조인예제

3.2.2.2 정렬병합조인(Sort Merge Join)

<특징>

연결을 위해 랜덤 액세스를 하지 않고 스캔을 하면서 수행
정렬을 해야함(Sort Area Size)
연결고리의 비교연산자가 '='가 아닌 경우에는 Nested Loops조인보다 유리한 경우가 많다.
정렬을 해야하는 단점을 극복하기 위해 Hash조인이 사용됨.
use_merge(table1,table2) 힌트를 사용하여 유도할 수 있다.
정렬병합조인예제

3.2.2.3 해쉬조인(Hash Join)

• 해슁함수 기법을 활용하여 조인을 수행하는 방식
• 조인의 범위가 넓어질때 랜덤액세스의 부담을 줄이기 위해서 Sort Merge조인을 선택했으나
  정렬에 대한 부담이 많음.
• 조인의 어느 한쪽 집합이 해쉬영역보다 작아서 인메모리(In-memory) 해쉬조인이 가능하다면 수행속도는 매우 빨라짐.
  해쉬조인예제

3.2.2.4 세미조인(Semi Join)

• 각종 다양한 비교연산자에 의해서 사용된 서브쿼리가 메인쿼리와 연결되는 모든 경우.
  세미조인예제

3.2.2.5 카티젼조인(Cartesian Join)

• 두개의 집합간에 연결고리 조인이 전혀 없는 경우

<사용예>

• 쿼리 생성 사용자 실수
• 특별한 목적을 가지고 고의적으로 만드는 경우
• 3개 이상의 집합을 조인할 때 조인순서의 잘못으로 인해 연결고리 부재현상이 발생하는 경우.
  카티션조인예제

3.2.2.6 아우터조인(Outer Join)

Nested Loops 아우터 조인

• 기준 집합이 외측 루프로써 먼저 수행되어야 하며 내측 루프가 수행될 때 연결에 실패하더라도 외측루프의 로우를 탈락 시키지 않는 방식으로 수행된다.

해쉬아우터 조인

• Nested Loops 조인으로는 부담이 되는 대량의 데이터이거나, Nested Loops 조인으로는 수행에 문제가 있을때 선택될 수 있다.

Sort Merge 아우터 조인

• Nested Loops 조인으로는 부담이 되는 대량의 데이터이거나, Nested Loops 조인으로는 수행에 문제가 있을때 선택될 수 있다.
• 조건연산자로 인해 해쉬조인이 불가능할 때이거나 이미 다른 처리에 의해 조인을 위한 정렬이 선행되어 있어서 더 유리해질때 사용.

전체 아우터 조인

• 양쪽 집합이 모두 기준 집합이면서 대응집합이 되는 아우터 조인

아우터조인예제

3.2.2.7 인덱스조인(Index Join)

• 특정 테이블에 사용된 모든 컬럼이 하나 이상의 인덱스들이 존재할 때 그 인덱스들 간의 해쉬조인을 통해 액세스 하는 기법.
• 하나 이상의 인덱스 들을 결합하여 모든 처리를 할 수 있을때 테이블을 액세스하지 않고 인덱스들로만 처리하는 방법.

인덱스조인 예제

인덱스 조인의 특징

• 사용된 모든 컬럼이 어떤 인덱스에라도 존재해야 한다.
• 비교연산자가 '='이 아니어도 된다.
• 반드시 인덱스 선두 컬럼이 아니어도 인덱스 조인에 참여할 수 있다.
• 조건절을 기준으로 인덱스 조인을 결정한다.
• 비용산정을 통해 충분히 효율적이라고 판단할 때만 인덱스 조인으로 수행된다.
• 실행계획을 보면 해쉬조인으로 수행됨을 알 수 있다.

3.2.3 각종 연산을 위한 실행계획

3.2.3.1 IN-List 탐침(Iterator) 실행계획

• 선분(BETWEEN)과 점(IN)의 개념
• B-Tree 인덱스에서는 중간에 위치한 컬럼이 '='로 사용되지 않으면 해당 범위를 모두 스캔해야 한다.

탐침 예제

3.2.3.2 연쇄(Concatenation)실행계획

• 'OR'로 연결된 서로 다른 컬럼을 사용한 조건을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결하는 실행계획
• USE_CONCAT 힌트를 사용할 수 있음.
• NO_EXPAND로 해제할 수 있음.

<바람직하지 않은 사용예>

• 조인의 연결고리가 'OR'조건을 가질때 조인의 상대방이 넓은 범위를 가질때.
• 동일컬럼의 OR조건. <= IN-List탐침이 유리하다.
• 보다 효율적으로 처리범위를 줄일 수 있는 다른 액세스 경로가 있을때.
• 'OR'조건들 중에서 너무 넓은 처리범위를 가진 것들이 존재할 때.

3.2.3.3 원격(Remote)실행계획

• 다른 데이터베이스의 테이블을 '데이터베이스링크(Database Link)'로 액세스하는 액세스 형태.

피해야 할 상황

• 원격 테이블이 선행집합이 되어서 외측루프를 수행하고 로컬 테이블이 내측 루프를 수행해야만 비교적 양호한 수행속도를 얻을 수 있다. 그러나 원격테이블이 내측에서 수행이 된다면 심각한 문제가 발생할 수 있다.
• 부득이하게 원격테이블이 내측에서 수행이 되어야 한다면 Sort Merge조인이나 해시조인으로 나타나는 경우가 많다.
• 만약 하나 이상의 동일한 원격 테이블의 조인을 할 때엔 많은 문제를 일으킬 수 있다.
  =>원격 데이터베이스에 미리 조인된 뷰를 만들어서 해결하는 방법이 있음.

3.2.3.4 정렬처리(Sort Operation) 실행계획 p249~

SORT(UNIQUE)

• SELECT-List에 기술된 컬럼들로 구성된 해당 쿼리의 추출로우에 대한 유일한 집합을 생성하는 작업.
  => Distinct사용, 서브쿼리에서 제공자 역할을 할때 나타난다.

SORT(AGGREGATE)

• GROUP BY를 하지 않은 상태에서 전체 대상에 대해 그룹함수로 계산할 때 나타난다.

SORT(GROUP BY)

• GROUP BY를 사용한 구문에 의해서 발생한다. (10g는 X => GROUP BY NOSORT)

SORT(JOIN)

SORT(ORDER BY)

3.2.3.5 집합처리(Set Operations) 실행계획

합집합(Union, Union All)실행계획

• Union과 Union All은 반드시 구분해서 사용해야 한다.
  => Union은 불필요한 정렬작업을 하므로 꼭 필요한 경우에만 사용하도록 한다.

교집합(Intersection)실행계획

• Sort Merge조인과 유사한 방법을 사용하여 양쪽 집합을 유일하게 정렬한 다음 이들을 머지한다.

차집합(Minus)실행계획

• 어느 한쪽 집합을 기준으로 다른 집합의 요소들을 제거하는 방법.
  실행계획 예제

3.2.3.6 COUNT(STOPKEY) 실행계획

• 조건절에 ROWNUM을 사용했을때 나타남.
• 자세한 내용은 1.4.5 ROWNUM의 활용 참조.p442~449
  STOPKEY예제

3.2.4 비트맵(Bitmap) 실행계획 p261~

3.2.4.1 조건연산자별 비트맵 실행계획

• 조건연산자는 단지 비트연산의 방식만 다르게 할 뿐이기 때문에 조건연산자의 형태에 따라 처리방법이 크게 달라지지 않는다.
  가) 동치(Equal)비교 실행계획
• 하나의 컬럼을 '='로 비교한 경우.
• SINGLE VALUE로 나타난다.
  나) 범위(Range)비교 실행계획
• 범위를 나타내는 BETWEEN, LIKE, >, <, >=, <=연산자를 사용하여 비교한 경우.
• RANGE SCAN이 나타난다.
• number 타입으로 정의된 컬럼에 LIKE를 사용하였다면 인덱스 FULL SCAN으로 나타난다.
  (B-Tree인덱스에서는 형변환이 일어나기 때문에 인덱스 스캔을 하지 못한다.)
  다) AND조건 실행계획
• 각각 비트맵을 가지고 있는 컬럼들을 AND조건으로 사용하면 각각의 비트맵을 액세스하여 'AND연산'을 한다.
  라) OR조건 실행계획
• OR조건으로 연결된 각각의 컬럼들은 자신의 단위 액세스를 생성하고 그 결과의 비트맵으로 'OR연산'을 실시한다.
  마) 부등식(Not equal)비교 실행계획
• 비트맵 인덱스는 부등식이 사용되면 'BITMAP MINUS' 연산을 수행한다.
  바) NULL 비교 실행계획
• 'IS NULL' 이나 'IS NOT NULL'로 비교하면 NULL을 마치 정상적인 값들과 동일하게 취급하여 각종 비트맵 연산에 참여시킨다.

3.2.4.2 서브쿼리 실행계획

• B-Tree인덱스와 크게 다르지 않음.

3.2.4.3 B-Tree 인덱스와의 연합(Combine) 실행계획

• 비트맵을 ROWID로 전환할 수 있고, 다시 ROWID를 비트맵으로 전환할 수 있는 특성을 이용해 B-Tree인덱스를 비트맵 연산을 수행할 수 있다.
  .
  p272참조
  .
• B-Tree 인덱스를 비트맵으로 전환을 해서라도 인덱스를 이용하여 최대한 범위를 줄일 필요가 있을때 적용해야 한다.
  => 인덱스의 처리범위가 너무 넓다면 전환에 대한 오버헤드가 발생하고, 처리 범위또한 줄일 수 없으니 함부로 사용하지 말아야 한다.

3.2.5 기타 특수한 목적을 처리하는 실행계획 p274~

3.2.5.1 순환전개실행계획

3.2.5.2 UPDATE 서브쿼리 실행계획

3.2.5.3 특이한 형태의 실행계획

문서에 대하여

• 최초작성자 : 이현석
• 최초작성일 : 2008년 3월 21일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.