전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
인덱스 구조 테이블 0 0 50,371

by 구루비스터디 테이블 table IOT [2018.09.27]


인덱스 구조 테이블

인덱스 구조 테이블 (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)
NOCOMPRESSSYS,TABLE,T1SYS,TABLE,T2SYS,TABLE,T3...SYS,TABLE,T100
COMPRESS 1SYS,TABLE,T1TABLE,T2TABLE,T3...TABLE,T100
COMPRESS 2SYS,TABLE,T1T2T3...T100


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는 힙 테이블에서의 역할과 같은 의미가 있으며
  • 오버플로 세그먼트에 설정된 값은 힙 구조 테이블의 설정값과 같은 로직으로 사용된다.
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4034

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입