1. 실행계획

실행계획 : 테이블에서 데이터를 추출해서 쿼리가 원하는 결과를 생성해 가는 경로.

스캔(Scan) : 실행계획의 가장 기본적인 형태로 실제로 물리적인 데이터를 찾아서 액세스하는 것이다.

조인(Join) : 하나 이상의 테이블에서 스캔을 하여 서로 연결하는 처리 방식이며, 다양한 방법이 있다.

실행계획의 유형

• 스캔을 위한 실행계획
• 데이터 연결을 위한 실행계획
• 각종 연산을 위한 실행계획
• 기타 특수한 목적을 처리하는 실행계획

1. 스캔을 위한 실행계획( 스캔의 기본 유형 )

1. 유형

• 전체테이블 스캔(Full Table Scans)
• 로우식별자 스캔(Rowid Scans)
• 인덱스 스캔(Index Scans)
• 클러스터 액세스(Cluster Access)
• 해쉬 액세스(Hash Access)
• 표본 테이블 스캔(Sample Table Scans)

2. 전체 테이블 스캔 Full Table Scans

테이블에 있는 모든 로우를 읽어내는 방법

• 다중 블럭으로 읽음( 한번에 Access할 수 있는 블럭량 파라메터 : DB_FILE_MULTIBLOCK_READ_COUNT )
• WHERE 절에 기술된 조건들을 확인함
• 테이블의 최고 수위선(High Water Mark) 범위에서 데이타를 읽음
• 최고 수위선 : High Water Mark, 사용된 저장공간의 총합계나 데이터를 넣기 위해 포맷된 영역을 표시.

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

• 적용가능 인덱스 부재
• 넓은 범위의 데이터 액세스
• 소량의 테이블 액세스
• 병렬처리 액세스
• 'Full' 힌트를 적용했을 때

실행계획 예제

오라클 9i 이전과 이후의 실행계획 표시 방법이 틀리게 나온다.

실행계획의 각 단계별로 로우수가 표시되었지만, 9i 이후는 최종 결과 로우 수만 나타남

책에서는 이해를 돕기 위해 9i 이전 방법으로 설명.

단일 테이블

select * from tf02ngt where item_name like '%BF%';

Rows     Row Source Operation

-------- ---------------------

   51005 TABLE ACCESS (FULL) OF 'TF02NGT'

Rows : 이 테이블을 액세스한 로우의 개수( 전체 테이블의 총 로우수 )이고, where 절의 조건에 만족하는게 trace에 나오는 9건이다.

정렬처리를 했을때 나타나는 실행계획

Rows     Row Source Operation

-------- ---------------------

    3548 SORT ORDER BY

   51005  TABLE ACCESS (FULL) OF 'TF02NGT'

51001건의 전체 테이블 스캔으로 액세스하여 체크조건(where)을 적용하였더니, 3548건이 결과로 추출.

INSERT, UPDATE, DELETE

Rows     Row Source Operation

-------- ---------------------

       0 INSERT STATEMENT

  150842   TABLE ACCESS (FULL) OF 'BT_REG_BASE'

BT_REG_BASE 테이블에서 전체 테이블 스캔으로 150842건을 액세스하여 체크조건으로 2658건을 인서트를 하였다.

UPDATE, DELETE 동일

조인으로 인한 다중 테이블

Rows     Execution Plan

-------- ---------------------

4.  7701 NESTED LOOPS

1.148046   TABLE ACCESS (FULL) OF 'ITEM_BASE'

3.  7719   TABLE ACCESS (BY INDEX ROWID) OF 'CS_SPEC'

2.  7724      INDEX (UNIQUE SCAN) OF 'PK_CS_SPEC'

(1) 'ITEM_BASE' 전체테이블 스캔해서 148046건이고, 이 중에서 체크조건으로 7724건이 만족.

7724건을 (2) PK_CS_SPEC 으로 UNIQUE SCAN 한게 7719건이고, (3) CS_SPEC의 체크조건으로 

(4)의 7701 건이 결과

3. 로우식별자 스캔(Rowid Scans)

배치 작업에서 주로 사용하며, Bind 변수를 사용해서 처리하는 것이 올바른 방법이다.

rowid는 migration, chain, import, export 등의 작업에 따라 rowid 가 변경 된다.

Rows     Execution Plan

-------- ---------------------

    2000  TABLE ACCESS (BY INDEX ROWID) of 'bal_item'

    2001    INDEX (RANGE SCAN) OF 'pk_bal_item' (UNIQUE)

4. 인덱스 스캔(Index Scans)

실제적으로 가장 많이 발생하는 방식.

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

단 하나의 rowid를 추출한다.

기본키나, 유일 인덱스(unique index)으로 생성되어야 하고, =로 비교되어야 사용가능

특정 몇 경우는 'INDEX( table_alias index_name )' 힌트 부여로 사용 가능

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

가장 보편적인 데이터 액세스 형태

- 시작과 종료의 가진 경우와 하나 이상이 끝을 가지지 않은 경우

- 스캔을 경유하여 추출되는 로우는 인덱스 구성 컬럼의 정렬 순서와 동일하게 나타난다.

( order by 절과 동일할 경우, 추가적인 정렬 작업을 안할수도 있다 )

- 최초의 시작점만 랜덤 액세스로 그 다음부터는 스캔

- =, <, <=, >, >=, BETWEEN, LIKE 등 사용 가능 ( LIKE '%d' 형태는 안됨 )

• 인덱스 역순 범위 스캔( Index Range Scans Descending )

역순으로 데이터를 액세스한다는 것을 제외하고 인덱스 범위 스캔과 동일

ORDER BY .. DESC 혹은 'INDEX_DESC( table_alias index_name )' 힌트로 사용

Rows     Execution Plan

-------- ---------------------

     526  TABLE ACCESS (BY INDEX ROWID) OF 'stn_code'

   12150   INDEX (FULL SCAN DESCENDING) OF 'stn_code_pk' (UNIQUE)

index_desc힌트 사용으로 12150건으로 찾았지만, 526건으로 나오는건 결합인덱스를 사용하였지만,

선행 컬럼이 조건을 부여받지 못하였고, 다른 인덱스의 체크로 526건만 엑세스 되었다.

Rows     Execution Plan

-------- ---------------------

       5 TABLE ACCESS (BY INDEX ROWID) of 'sbj_prgs'

       6   INDEX (RANG SCAN DESCENDING) OF 'sbj_prgs_idx1' (NON-UNIQUE)

인덱스에 구성된 컬럼이 적어도 하나 이상 선행컬럼부터 연속해서 조건을 부여 받았기 때문에 인덱스

스캐ㅔㄴ을 한 후에 버리는 것이 하나도 없다는 것을 의미.

테이블 액세스 보다 1건이 많은 것은 마지막 건을 읽어 보아야 범위가 끝났다는 것을 알 수 있디 때문.

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

결합 인덱스 사용시, 선행 컬럼에 조건을 부여하지 않아도 결합 인덱스로 사용가능하도록 하는 것.

단, 조건을 부여하지 않은 선행 컬럼은 카디널리티가 높아야 한다.

• 인덱스 전체 스캔( Full Scan )

조건절에서 그 인덱스의 컬럼이 적어도 하나 이상 사용되었을때 적용 가능.

- 쿼리 내에 사용된 어떤 테이블들의 모든 컬럼들이 그 인덱스에 모두 존재하거나

- 인덱스 컬럼 중에 최소한 not null 인 컬럼이 하나는 존재할때

인덱스 전체 스캔 사용 가능하고, 테이블을 대신하여 사용 될 수 있다.

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

쿼리에 사용된 모든 컬럼이 인덱스에 포함되어 있을때 사용 가능.

비트맵 인덱스에서는 사용 안됨

'INDEX_FFS( table_alias index_name )' 힌트로 사용

'NO_INDEX_FFS( table_alias index_name )' 힌트로 해제

• 인덱스 조인( Index Join )

- 별도의 장

• 비트맵 인덱스( Bitmap Index )

- 별도의 장

5. 클러스터 액세스(Cluster Access)

1:M 관계를 가진 다중 테이블 클러스터에서 M 쪽에서 액세스한 실행계획의 예

Rows     Execution Plan

-------- ---------------------

    1593 NESTED LOOPS

     145  TABLE ACCESS (BY INDEX ROWID) of 'obj$'

     146    INDEX (RANG SCAN) OF 'I_OBJ2' (UNIQUE)

    1593  TABLE ACCESS (CLUSTER) OF 'col$'

     145    INDEX (UNIQUE SCAN) OF 'I_OBJ#' (CLUSTER)

단일 테이블 클러스터링을 사용한 경우의 실행계획

Rows     Row Source Operation

-------- ---------------------

     318 NESTED LOOPS

    3207  TABLE ACCESS (CLUSTER) OF 'BPM500T'

      10    INDEX (RANG SCAN) OF 'BPM500T_CLX' (CLUSTER)

     318  TABLE ACCESS (BY ROWID) OF 'BPM01T'

     318    INDEX (UNIQUE SCAN) OF 'BPM01T_PK' (UNIQUE)

6. 해쉬 액세스(Hash Access)

로우의 길이의 편차가 심하지 않고 크기에 대한 예측이 가능한 테이블에 아주 빈번하게 랜덤 액세스가 발생한다면

해쉬 클러스터를 사용할 수 있다.

하나 이상의 컬럼으로 구성된 결합 해쉬 인덱스를 지정한 경우에는 항상 2개의 조건으로 조회를 실시 해야 한다.

1개의 조건만 사용한 것은 'like'을 사용한 것과 동일한 개념이 되어 전체테이블 스캔을 수행한다.

NESTED LOOPS

  TABLE ACCESS (BY INDEX ROWID) of 'emp'

    INDEX (RANGE SCAN) OF 'DEPT_IDX' (NON-UNIQUE)

  TABLE ACCESS (HASH) OF 'emp_hash'

해쉬 액세스는 인덱스를 경우하지 않기 때문에 바로 테이블을 해쉬로 액세스했다는 표현이다.

7. 표본 테이블 스캔(Sample Table Scans)

SELECT STATEMENT

  TABLE ACCESS (SAMPLE) OF 'EMP'