꿈꾸는 개발자, DBA 커뮤니티 구루비

1. 전체스캔
1. 1.1 전체 스캔과 다중 블록 읽기 계수
2. 1.2 전체 스캔과 최고 수위선(HWM)
2. ROWID 액세스
3. 인덱스 스캔

1. 전체스캔

단순히 모든 블록을 순차적으로 읽는 것.
다중블록읽기 채용
초대형작업에서 전체스캔은 인덱스를 사용하는 것 보다 훨씬 빠르다.

1.1 전체 스캔과 다중 블록 읽기 계수

<실습>

1.2 전체 스캔과 최고 수위선(HWM)

전체스캔은 항상 세그먼트의 최고수위선(HWM) 아래에 있는 모든 블록을 읽는다.
데이터 삭제(delete)시에도 HWM까지 읽게 되므로 결과건수는 적더라도 실제 논리적인I/O는 삭제되기 전과 동일하다.
이를 방지하려면 테이블을 재편성 해야 한다.
( 테이블을 재편성하는 방법으로는 table move, export/import, rename/ctas 등이 있음 )
혹은 주삭제 대상 데이터 단위별로 파티션을 구성한 후 파티션별로 TRUNCATE PARTITION 혹은 DROP PARTITION을 한다.

2. ROWID 액세스

ROWID : 데이터의 물리적인 주소로서 파일에 관한행, 블록, 블록의 행에 관한 정보를 갖고 있다.
인덱스 스캔이 이루어진 후 테이블액세스 시에 사용된다.

ROWID FORMAT

Data Object Number : DB Segmemet식별정보(해당 row가 속해있는 Object 번호)
Relative File Number : Tablespace에 상대적인 Data file 번호(해당 row가 속해있는 데이터파일번호)
Block Number : Row를 포함하는 Data Block번호(해당 row가 속해있는 데이터 파일의 데이터블록 주소값)
Row Number : Block에서의 Row Slot(데이터 블록내의 해당 로우의 주소값)

2.1 ROWID 활용으로 검색속도 높이기

ROWID 액세스는 언제 발생하는가?

Rowid가 조건으로 공급된 경우
인덱스를 사용하여 Table 을 access 한경우

ROWID 액세스의 특징

Rowid를 이용해서 해당테이블의 특정 Block의 특정 Row를 찾아간다.
가장 빠른 Access 방식이다.
주로 max/min 일자를 찾아서 그일자에 해당하는 값을 select 할때 유용하게 사용된다.

ROWID 액세스를 하기 위한 힌트

/*+ rowid(테이블) */

2.2 ROWID 범위

ROWID의 범위를 사용하면 하나의 테이블에 대한 모든 작업을 병렬로 처리가능
(각 프로그램이 하나의 테이블에 대해서 물리적으로 뭉쳐있는 데이터에 대하여 수행되도록 보장하기 때문)
각 프로그램이 같은 공유 디스크 리소스로 다투지 않음

3. 인덱스 스캔

3.1 B*트리구조

이진트리

이진 트리(Binary tree)는 모든 노드의 차수가 2이하인 트리(Tree)로 하나의 노드는 두 개의 자식 노드(child)를 가질 수 있다.
부모가 왼쪽, 오른쪽 두개의 자식을 가지는 트리.
인덱스를 조직하는 방법으로 가장 많이 사용되는 것.
검색은 루트에서부터 시작한다.

Root : 인덱스의 다음 레벨을 가리키는 엔트리 포함
Branch : 인덱스의 다음 레벨을 가리키는 엔트리 포함
Leaf : 테이블의 행을 가리키는 인덱스 엔트리를 포함. 양방향 Linked List로 연결

이진탐색 트리

이진 탐색 트리는 이진 트리의 일종으로 모든 노드는 유일한 키 값을 가짐.
루트 노드의 왼쪽 서브 트리에 있는 노드는 모두 루트 노드보다 작은 키 값을 가짐.
루트 노드의 오른쪽 서브 트리에 있는 노드는 모두 루트 노드보다 큰 키 값을 가짐.
왼쪽 자식 < 부모 < 오른쪽 자식

3.2 인덱스 고유 스캔

인덱스 조회로부터 번환되는 행은 1개.
고유인덱스에서는 오직 인덱스 키값에 따라 정렬된다.
( 고유하지 않은 인덱스에서는 인덱스 키값에 의해 정렬된 다음 동일한 값에 대해서는 ROWID에 따라 또다시 정렬됨)

인덱스 고유스캔을 하기 위한 힌트 : /*+ index(테이블) */

3.3 인덱스 범위 스캔

행이 하나도 반환되지 않거나, 하나 또는 그 이상의 행이 반환됨.

인덱스 범위스캔을 하기 위한 힌트

/*+ index(테이블) */
/*+ index_desc(테이블) */

참고-Index Range Scan

3.4 인덱스 건너뛰기 스캔 ( INDEX SKIP SCAN )

테이블의 데이터 분포에 대한 통계정보가 정상적으로 생성되어져 있고 결합인덱스를 가진 테이블에 질의를 할 때 선행하는 컬럼에 대한 조건절이 오지 않더라도 인덱스 스캔을 하는 스캔.(오라클9i 이상)
Oracle 옵티마이저를 비용기반 옵티마이져(CBO)로 사용해야만 한다.
(규칙기반 옵티마이져(RBO)가 사용되고 있다면, Index Skip Scan은 사용할 수 없음)
Index Skip Scan을 이용하기 위해서는 INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC 힌트를 사용함.
Index Skip Scan을 수행 했을 경우 실행 계획은 다음과 같이 나타난다.


   SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
     0 SORT (AGGREGATE)
          1 INDEX (SKIP SCAN) OF 'EMP_ID' (NON-UNIQUE)

인덱스 건너뛰기 스캔을 하기 위한 힌트

/*+ index_ss(테이블) */
참고 Index Skip Scan

3.5 인덱스 전체 스캔

인덱스의 모든 잎블록을 처리한다.
테이블 전체스캔과는 달리 인덱스 구조의 모든 블록을 읽지는 않는다. (첫블록을 찾을때 까지만 가지블록을 처리함)
이중 연결리스트를 이용하여 인덱스 구조를 앞, 뒤로 순회함.
그러므로 단일블록I/O를 사용함. (<= 테이블 전체 스캔과의 차이점)
별도의 정렬작업을 피하기 위하여 인덱스 전체스캔이 사용될 수도 있음.
직접적으로 full scan 을 유도하는 힌트는 없음.
참고 Index Full Scan

3.6 빠른 인덱스 전체 스캔

내부 가지 블록을 포함하여 인덱스 구조의 모든 블록을 읽음.
다중블록읽기를 사용함.
데이터를 정렬된 순서대로 검색하지 않음.

빠른 인덱스 전체 스캔을 하기 위한 힌트

/*+ index_ffs(테이블) */
참고 Index Fast Full Scan

3.7 인덱스 조인


create table t
as
select * from all_objects;

create index t_idx1 on t(object_id);

create index t_idx2 on t(owner, object_type);

exec dbms_stats.gather_table_stats ('SYS','T',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');

select object_id, owner, object_type
  from t
 where object_id between 100 and 2000
   and owner = 'SYS';

select /*+ index(t t_idx1) */ object_id, owner, object_type
  from t
 where object_id between 100 and 2000
   and owner = 'SYS';

select /*+ index(t t_idx2) */ object_id, owner, object_type
  from t
 where object_id between 100 and 2000
   and owner = 'SYS';

TEST

테이블 및 인덱스 생성, 통계정보 수집

인덱스 조인

t_idx1 인덱스 사용

t_idx2 인덱스 사용

결과를 비교해 보면 인덱스 조인일 경우 논리적인 I/O가 가장 적음을 알 수 있음.
참고 Index Join