이번장은 이론적인 내용보다는 옵티마이져의 세부 형태에대한 소개라
한번씩 책을 정독하시고 실습을 병행해보는게 좋을꺼같습니다. ^^
지겨운 내용..

3.2. 실행 계획의 유형

• 실행계획은 크게 나누어 네가지로 구분

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

    

3.2.1. 스캔(Scan) 의 기본유형

• 스캔의 기본유형

1. 전체 테이블 스캔(Full Table Scans)
2. 로우실별자 스캔(Rowid Scans)
3. 인덱스 스캔(Index Scans)
4. 클러스터 액세스(Cluster Access)
5. 해쉬 액세스(Hash Access)
6. 표본 테이블 스캔 (Sample Table Scans)

 

3.2.1.1. 전체테이블 스캔

High Water Mark(최고 수위선)

• 사용된 저장공간의 총합계나 데이터를 넣기 위해 포맷된 영역을 표시하는 것
• 전체 테이블 스캔은 다중 블록 단위(한번의 I/O)로 메모리에 옮겨진후 순차적으로 읽힘
• DB_FILE_MULTIBLOCK_READ_COUNT ; 한번에 액세스하는 블록의 양을 정의

 

EX)
소형은 인덱스를 경유하더라도 인덱스를 통한 랜덤 엑세스한 자료가 대부분 비슷한 블록이기 때문에

 FULL스캔이 유리할수도있음

대형은 이미 액세스한 블록을 공유할 확률이 낮아지므로 랜덤에 대한 부하가 증가 ,

DISK의 ACCESS ARM의 이동이 많아질수 있음

 

• 전체 테이블 스캔을 선택하게 되는 경우
• 적용가능 인덱스의 부재: 인덱스가 없을경우, 결합인덱스의 선두 컬럼이 존재 하지 않을때, 인덱스가 가공될경우
  (경우에 따라 인덱스 스킵 스캔이 적용되면 인덱스 사용이 가능)
• 넓은 범위의 데이터 액세스: 인덱스 사용시의 비용이 클 경우
• 소량의 테이블 액세스: 최고수위내의 블록이 db_file_multiblock_read_count 내에 있을 경우
• 병렬처리 액세스: 병릴처리는 전체테이블 스캔이 더욱효과적
• 'Full'힌트를 적용: 'full(table_alias)' 힌트 사용

실행계획상 oracle 9i 이전에는 해당단계에서 액세스한 로우수가 표기 됬지만 그이후부터는 최종결과만 표기
trace 관련 참고
http://develop.sunshiny.co.kr/130?category=10
http://blog.naver.com/themisoo/110004154686

 

3.2.1.2. Rowid 스캔

• Rowid 스캔은 인덱스를 경유하여 테이블을 액세스하는 과정에서 발생

 

3.2.1.3. 인덱스 스캔

• 분류

인덱스 유일 스캔 (Index Unique Scan)
인덱스 범위 스캔 (Index Range Scan)
인덱스 역순 범위 스캔(Index Range Scans Descending)
인덱스 스킵 스캔(Index Skip Scan)
인덱스 전체 스캔 (Full Scan)
인덱스 고속 전체 스캔(Fast Full Index Scan)
인덱스 조인(index Join)
비트맵 인덱스 (Bitmap Index)

 

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

•  인덱스가 기본키나 유일 인덱스(Unique Index)로 생성되어야 함 (조건절에서 = 비교)
• Index(table_alias index_name) hint 를적용하면 사용

 

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

• 브랜치 블록을 경유하여 시작 리프 블록을 찾은후 계속해서 연결된 리프 블록을 스캔하다가 종료점에서 멈춤
• = , < , <=, >, >=, BETWEEN, LIKE (%가 선두일경우 불가능)

 

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

• 역순으로 데이터를 액세스 (최대값으로 시작하여 최소값이 될때까지 수행)
• 가장최근의 자료를 먼저검색하는 등의 경우
• Orderby .. Desc를 할대 옵티마이저의 판단에 따르거나 'Index_desc (table_alias index_name)' 힌트 사용

 

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

• 인덱스의 선행컬럼이 사용되지않아도 후행인덱스를 사용하여 스캔
• 결합인덱스일때 상위에 카디널리티가 낮은 컬럼들이 위치할 경우 상위 값들로 브랜치 블록이 구성될수 있음

ex) col1,col2,col3 ||로 연결하여 col4 인덱스를 만들경우

 col1,col2가 낮은 카디널리티를 가지고

 col3이 높다면 브랜치 블록은 col1,과 col2가 차지

 

- sal_typ(매출유형) + item_cd(상품코드) + sal_dt(매출일자)' 로 구성되어있을때 조건으로

 item_cd와 sal_dt만 사용되고 sal_typ가 (D,E,L) 세종류일경우
조건절에 SAL_TYP IN ('D','E','L') 을 추가한것과 동일한 효과를 볼수 있음

 

논리적 서브 인덱스

 

결합인덱스에서 실제 조건절에 사용되지 않은 컬럼을 논리적으로 인덱스를 사용한 형태로 만들어진 INDEX_SS', 'INDEX_SS_ASC', 'INDEX_SS_DESC' _ 인덱스 스킵스캔사용 흰트 (정상,역순스캔) 사용이 필요없을경우 'NO_INDEX_SS'

 

 

 

마) 인덱스 전체 스캔(INDEX FULL SCAN)

• 테이블을 대체
• 쿼리 내에 사용된 어떤 테이블들의 모든 컬럼들이 그 인덱스에 모두 존재하고
• 인덱스 컬럼 중에서 최소한 NOT NULL 인 컬럼이 하나는 존재할때

 

ex) select count(*) from user_table 수행시 not null인컬럼이 있을경우 그 컬럼의

 인덱스를 전체스캔하여 결과 도출

 

 

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

• 쿼리를 위해 사용된 모든 컬럼이 인덱스에 포함되어있을 경우
• 인덱스만 스캔하고 테이블 액세스는 하지 않음
• table과 같이 I/O에 다중 블록 액세스를 함
• 비트맵 인덱스에서는 적용 불가
• INDEX_FFS(TABLE_ALIAS INDEX_NAME) 힌트 사용
• NO_INDEX_FFS( table_alias index_name) 힌트로 해제

 

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

단일 컬러스터링 : 대량의 범위 처리의 효율화조인의 효율적인 연결: 두개 이상의 테이블을 하나의 클러스터에 저장하는 방법

 

1:M 관계를 가진 두 테이블을 클러스터링
1쪽의 테이블을 클러스터 키로 액세스하면 하나의 로우가 나타 나고
M쪽을 액세스하면 여러개의 로우가 나타남

 

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

• 해쉬 함수를 통하여 데이터를 액세스 'like,<>,>,>=,<,<='등을 사용했다면 함수에 적용이 불가능
• = 이나 'between' ,'in'으로 적용가능한 테이블에 적용
• 자주 갱신이 없는 자료
• 로우의 길이의 편차가 심하지 않고 크기에 대한 예측이 가능한 테이블
• 인덱스를 경유 하지 않기 때문에 바로 테이블을 해쉬로 액스스

 

사용예
-우편번호 테이블
-시스템
-시스템 사용자 정보

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

 

• 테이블의 데이터 중에서 사용자가 부여한 비율 만큼의 데이터를 익고 그중에서 조건을 만족하는 로우를 리턴
• 자료 분석등의 작업에서 표본 자료가 필요할 경우 사용

 

select ....
from table_name sample { block option} (sample percent)
where ....
group by ....
having ....
order by ....

 

sample block (sample percent) : 전체 액세스 대상 블록에서 지정하 비율 만큼 읽은후 조건을 확인
sample (sample percent) : 모든 블록을 액세스하여 지정한 비율만큼의 로우를 읽음
sample percent 는 0,100을 제외한 0.000001 에서 99.999999 까지
CBO에서 지원 ' role'힌트를 사용해도 비용기준으로 수행

 

SELECT STATEMENT
TABLE ACCESS (SAMPLE) OF 'EMP'

 

데이터 마이닝에서 활용

- 데이터 정재를 위해 데이터의 오류 패턴을 찾을 경우 활용
- 테스트를 위한 표본 데이터를 생성할때 적용

데이터 마이닝

- 숨겨진 패턴과 관계를 찾아내어 의미 있는 정보를 발견해 내는 것
- 신뢰도가 높은 충분한 자료를 사용
- 너무 많을 경우 분석이 어려울수 있으므로 사용

 

옵티마이져의 실행계획을 보는 방법에 대한 내용들입니다.
CBO가 어떤 인덱스로 어떻게 액세스를 진행하느냐를 바로 해설할수 있도록
꾸준한 연습과 분석을 해야할거같습니다.

작성자: tofriend(남송휘)
작성일: 2009년 08월 02일
참고문헌 : 새로쓴 대용량데이터 베이스 1 - 이화식 , 기타 문서