인덱스 일체형 테이블

Index-Organized Table

테이블과 인덱스가 일체형으로 되어 있다는 것은 인덱스와 다른 일반 컬럼들이 모두 같은 위치에 저장되는 형태를 말한다. 따라서 인덱스만 액세스하면 따로 테이블을 액세스 할 필요가 없다.

일체형 테이블은 처리 범위가 넗어지더라도 분리형 테이블보다 부담이 적다. 즉, 인덱스 스캔 후 테이블 스캔 시 랜덤 액세스가 발생하기 때문이다.

1. 분리형과 일체형의 비교

구분	Ordinary Table	Index-Organized Table
로우의 유일 식별자	ROWID	기본키
기본키 미지정	허용	허용하지 않음  (반드시 기본키가 존재해야함)
Secondary 인덱스의 생성	ROWID	논리적 ROWID나 비트맵 인덱스
Row 액세스	ROWID로 액세스	기본키로 액세스
전체테이블 스캔	임의의 순서로 로우를 리턴함	기본키의 순서로 로우를 리턴함
클러스터링 가능여부	Cluster에 저장 가능	Cluster에 저장이 불가능
LONG,LONG RAW,LOB	LONG,LOB 중 하나포함	LOB는 가능하나 LONG은 불가능
분산 (Distributed) SQL	허용	버전에 따라 차이가 있음
데이터 이중화	허용	버전에 따라 차이가 있음
파티션 적용	허용	버전에 따라 차이가 있음
병렬 처리	허용	버전에 따라 차이가 있음

2. 일체형 테이블의 구조 및 특징 

• 테이블과 인덱스를 모두 가지고 있는 구조이다. 즉 분리형 테이블 처럼 인덱스를 찾고 ROWID를 이용 테이블 액세스가 필요 없다.
• 인덱스를 경유하지 않기 때문에 분리형 테이블에 비해 한번의 논리적 액세스가 줄어든다.
• 인덱스에 테이블 데이터도 포함되어 인덱스만 스캔하는 경우 더 많은 블럭을 스캔해야 한다.
• 저장 형태는 B-Tree와 같은 Leaf-node에 데이터를 같이 저장한다.
• 8i에서는 추가적인 인덱스(Secondary index)를 생성할 수 없었으나, 9i 부터는 추가적인 인덱스(Secondary index) 생성 가능하다.
• 추가적인 인덱스(Secondary index)의 경우 ROWID 대신 기본키(primary key)를 사용해야 한다.

장점

• 인덱스와 데이터가 같이 저장 되는 구조이기 때문에 저장 및 엑세스 효율이 증가한다.
• 랜덤 액세스가 없기 때문에 넘은 범위의 처리에 효과적이다.

단점

• 유연하지 못하고 배타적이다.
• 데이터 갱신시 오버플로우가 발생한다.

3. 논리적 ROWID와 물리적 주소(Physical guess)

논리적 ROWID의 개념

분리형 테이블에서는 인덱스의 분할이 발생하더라도 테이블의 ROWID는 변하지 않는데 반해 일체형은 인덱스자신이 테이블이므로 인덱스의 분할에 따라 ROWID가 변할 수 있으므로 어떤 키값이 동일한 노드에 지속적으로 저장될 수 없다.

• 키 분할 후 이 값으로 해당 로우를 찾아가면 원래의 로우는 다른 블록으로 이동하고, 거기에 다른 로우가 존재할 수 있다.
• 그 로우가 존재할 가능성이 높은 주소를 나타내기 때문에 이것을 Physical Guess 혹은 Guess라고 한다.
• 데이터를 액세스할 때 값(ROWID)이 부정확한 값이라고 판명되면 그 때는 기본키를 이용한 액세스를 하게 된다(부정확한 값이 높다면 아예 사용되지 않음).
• 물리적 위치정보와 기본키가 상호 보완 작용하여 논리적으로 완벽한 ROWID 역할을 수행
• Physical Guess가 완벽한 ROWID는 아니지만 극히 일부를 제외하고는 유효하다면 언제나 기본키로 액세스하는 것보다는 훨씬 유리하다.

일체형 테이블을 적용할 수 있는 경우

• 전자 카탈로그나 키워드 검색용 테이블
• 코드성 테이블
• 색인 테이블
• 공간 정보 관리용 테이블
• 대부분 기본키로 검색되는 테이블
• OLAP의 디멘젼 테이블
• Row의 길이가 비교적 짧고, 트랜젝션이 빈번하게 발생되지 않는 테이블

물리적 위치정보를 사용하지 않는 경우

• Secondery Index를 액세스하여 기본키 정보를 얻는다 
• 기본키로 데이터 블록 액세스

물리적 위치정보를 사용하는 경우 

• 추가적인 인덱스를 액세스하여 물리적 위치정보를 참조한다.
• 참조한 물리적 위치정보를 이용하여 데이터블록을 액세스한 후에 비교한다. 이때 기본키 값이 같으면 물리적 위치정보가 유효한 것으로 간주하여 액세스를 종료한다.
• 만약 유효하지 않다면 다시 기본키로 액세스하여 데이터 블록을 가져온다

4. 오버플로우 영역(Overflow Area) 

일체형 테이블의 부담요소

인덱스와 모든 컬럼이 같은 장소에 저장된다는 것은 저장공간의 분할이 발생한다든지 저장 밀도가 나빠지는 등의 부담이 증가한다.

일체형 테이블의 부담을 줄이는 방법

• 같이 적재할 컬럼을 줄인다. 
• 오버플로우 영역에 상대적으로 빈번하게 액세스되지 않는 컬럼을 옮겨 둔다. 
• 테이블 생성 시 컬럼의 순서를 잘 결정 해야 한다(INCLUDING 절 이후의 컬럼).

일체형 테이블의 Migration

• 테이블 생성시 include와 pctthreshold를 이용하여 결정된다.
• 일체형 구조의 본체는 인덱스 세그먼트에 저장되며 오버플로우 영역은 테이블 세그먼트에 저장된다.

5. 일체형 테이블의 생성

CREATE TABLE documents

(

    doc_id     VARCHAR2(5),

    title_name VARCHAR2(150),

    author     VARCHAR2(20),

    contents   VARCHAR2(2048),

    status     VARCHAR2(2),

    CONSTRAINT pk_dociot PRIMARY KEY (doc_id)

)

ORGANIZATION INDEX         ---- ①

TABLESPACE data01          ---- ②

PCTTHRESHOLD 20            ---- ③

INCLUDING contents         ---- ④

OVERFLOW TABLESPACE idx01; ---- ⑤

ORGANIZATION INDEX

일체형 테이블 생성을 정의하는 키워드

TABLESPACE

인덱스영역이 저장될 테이블 스페이스를 지정한다.

STORAGE 파라미터 사용도 가능하다.

PCTTHRESHOLD

인덱스 블록 내의 예약된 공간의 비율을 백분율로 지정한다.

단일 Row 크기가 (PCTTHRESHOLD / 100) / * DB_BLOCK_SIZE 보다 크면 이 범위 이내의 크기까지 인덱스 영역에 남겨두고 나머지 컬럼들은 모두 OVERFLOW 영역으로 이동 된다.

OVERFLOW를 지정하지 않았을 경우 임계값(Threshold)를 초과한 데이터는 입력이 실패한다.

INCLUDING

INCLUDING 이후에 선언된 컬럼 들은 오버플로우 영역에 저장된다.

만약, INCLUDING이 지정되지 않았을 때 Row의 크기가 PCTTHRESHOLD를 초과하면 오버플로우 영역에 저장된다.

특이점은 INCLUDING 절이 적용되는 시점은 처음 Row가 저장될 때만이다. Row UPDATE시 INCLUDING 절에 선언된 컬럼을 NULL로 넣고 이후 UPDATE시 선언된 컬럼에 값을 입력하면 PCTTHRESHOLD를 초과하지 않는 한도내에서 인덱스 영역에 저장된다.

OVERFLOW TABLESPACE

지정한 PCTTHRESHOLD 값을 초과한 Row의 일부가 저장될 테이블스페이스를 지정

※ 액세스의 효율성 향상

• 일반 테이블에서는 사용자가 체인을 결정할 수 없으며, 그 결과를 전략적으로 사용할 수 없다. 그러나 일체형에서는 사용자가 체인을 결정할 수 있으며, 그 결과에 따라 액세스 성능이 달라질 수 있다. 그러므로 일체형에서는 단순히 로우가 체인이 되었다라는 의미보다는 어떤 모양으로 체인이 되었는지가 중요하다. 이 체인은 Leaf-node 의 저장 밀도를 높이기 위한 전략이다.
• 전략적으로 인덱스 영역과 오버플로우 영역을 나누어야 하며, 가장 좋은 방안은 현재 및 향후의 액세스 패턴을 분석하는 것이다. 즉, 사용되는 SQL을 분석하는 것이다.

출처: 두리누리