인덱스 구조 테이블

인덱스 구조 테이블 (IOT, Index Organized Table)

• 기본키 순서에 따라 데이터를 저장
• 인덱스나 데이터를 위한 추가 공간 불필요, (테이블 = 인덱스)

IOT 가 효과적인 경우

• 텍스트 정보 검색용 KeyWord 테이블
• 코드 검색 테이블
• 부모/자식 관계의 자식테이블
• 기본키를 이용한 Between 조건이 자주 사용되는 테이블

-- 검색용 테이블의 모든 항목이 인덱스 구성항목
-- 이 테이블은 실제 원본 테이블과 조인할 일이 거의 없다, 단독 조회
CREATE TABLE keywords
( word VARCHAR2(50)
, position INT
, doc_id INT
, PRIMARY KEY(word, position, doc_id)
)
ORGANIZATION INDEX
;

-- 코드 를 이용하여 코드명을 조회 할때
-- 렌덤엑세스 부하를 줄인다.
CREATE TABLE codes
( code INT
, code_nm VARCHAR2(20)
, PRIMARY KEY(code)
)
ORGANIZATION INDEX
;

-- 사원의 상세 주소 정보를 조회할 때
-- IOT 를 이용하면 여러 주소들이 인접해 저장되어 효과적이다.
CREATE TABLE iot_addresses
( empno REFERENCES emp(empno) ON DELETE CASCADE
, addr_type VARCHAR2(10)
, street    VARCHAR2(20)
, city      VARCHAR2(20)
, state     VARCHAR2(2)
, zip       NUMBER
, PRIMARY KEY(empno, addr_type)
)
ORGANIZATION INDEX
;

-- 특정 주식의 일자 구간에 대한 검색
-- 자료가 인접하게 저장되어 매우 효과적
SELECT *
  FROM stocks
 WHERE ticker = 'GURUBEE'
   AND day BETWEEN sysdate - 100 AND sysdate
;

CREATE TABLE t1(x INT PRIMARY KEY, y VARCHAR2(1), z DATE) ORGANIZATION INDEX;
CREATE TABLE t1(x INT PRIMARY KEY, y VARCHAR2(1), z DATE) ORGANIZATION INDEX OVERFLOW;
CREATE TABLE t1(x INT PRIMARY KEY, y VARCHAR2(1), z DATE) ORGANIZATION INDEX OVERFLOW INCLUDING y;

• OVERFLOW 절은 IOT 의 데이터가 너무 클 때 오버플로우 영역을 다른 세그먼트에 저장하도록 하는 것

OVERFLOW 세그먼트 사용 조건

• PCTTHRESHOLD
  • 로우의 데이터 크기가 블록의 PCTIHRESHOLD 비율을 초과할 경우, 로우의 남겨진 컬럼은 OVERFLOW에 저장한다.
  • 만약 PCTTHRESHOLD가 10%고 블록크기가 8KB라면, 로우의 길이가 800바이트보다 큰 로우는 일부가 인텍스 블록과 다른 곳에 저장될 것이다.
• INCLUDING
  • INCLUDING 절에 기술된 컬럼까지의 모든 컬럼은 인텍스 블록에 저장하고, 나머지는 오버플로 영역에 저장한다.

CREATE TABLE iot
( owner, object_type, object_name
, PRIMARY KEY(owner, object_type, object_name)
)
ORGANIZATION INDEX
NOCOMPRESS
AS
SELECT DISTINCT owner, object_type, object_name FROM all_objects
;

인덱스 리프 블록 비교 (NOCOMPRESS vs COMPRESS 2)

Secondary Index

• IOT 는 인덱스인데 여기에 또다른 인덱스가 추가될 때
• 논리 (logical) rowid를 사용한다.
• 이 논리 rowid는 10T의 기본 키에 기초하고 있다.
• 또한 로우의 현재 위치에 대한 추측을 포함하고 있을 수 있다.
• 이러한 추측은 IOT에 존재하는 로우에 대하여 secondary 인덱스로 처음 설정된 물리적인 주소다.
• 이 추측은 어느 정도의 시간이 지나면 IOT 데이터가 움직이기 때문에 거의 잘못되게 된다.

인텍스구조테이블요약

• 인텍스 블록의 데이터와 오버플로 세그먼트 데이터의 올바른 설정은 IOT 설정에서 가장 중요한 부분이다
• 다양한 오버플로 조건을 가진 다양한 시나리오로 비교 태스트를 해보고,
• INSERT, UPDATE, DELETE 에 어떻게 영향을 미치는지 살펴보아야 한다.
• 전체가 한 번에 생성되고 읽기 중심이라면, 인텍스 블록에 가능한 많은 데이터를 가지도록 한다
• IOT 구조가 빈번하게 수정된다면, 인텍스 블록에 전체 데이터를 가지는 것(조회의 장점)과 잦은 인텍스 데이터 재구성(변경의 단점)과의 균형을 고려해야 한다.
• 힙 구조 테이블에 적용된 FREELIST 고려사항은 IOT에도 마찬가지로 적용된다
• PCTFREE와 PCTUSED는 IOT에서 두 가지 구실을 한다
• IOT에서 PCTFREE의 중요도는 힙 구조 테이블에 비해 매우 미비하고, PCTUSED는 일반적으로 작동하지 않는다.
• 그러나 OVERFLOW 세그먼트를 고려할때 PCTFREE와 PCTUSED는 힙 테이블에서의 역할과 같은 의미가 있으며
• 오버플로 세그먼트에 설정된 값은 힙 구조 테이블의 설정값과 같은 로직으로 사용된다.