IOT(INDEX ORGANIZED TABLE)

IOT(INDEX ORGANIZED TABLE) 란?


일반 테이블에서 인덱스를 통해서 레코드를 액세스 할 때에는 키 값을 가지고 인덱스를 탐색해서 ROWID를 얻은 다음에  다시 ROWID를 이용해서 테이블을 읽는 두 번의 과정을 거쳐야 합니다.

또한 키 컬럼이 인덱스와 테이블 양 쪽에 중복해서 저장되므로 키 값이 큰 경우에는 디스크의 낭비 또한 무시할 수 없습니다.

이러한 문제점을 해결하고자 고안된 것이 IOT(Index Organized Table) 입니다.


 - IOT는 인덱스 안에 테이블을 넣어 버린 구조로 되어 있기 때문에 인덱스를 읽는 것으로 모든 작업이  완료 됩니다.

 - 키 값에 해당되는 레코드를 테이블에서 읽을 필요도 없고, 데이터의 중복 문제도 자연스럽게 해결할 수 있습니다.

 - IOT는 겉보기에는 테이블이지만 실제로는 기본 키(primary key)를 근간으로 한 인덱스이기 때문에 전제 조건으로 Primary Key를 필요로 합니다.

 - IOT를 생성하면 인덱스와 테이블이 같은 저장 구조에 생성되어 SQL 문이 실행되면, 무조건 IOT 인덱스를 통해 데이터를 검색하여 빠른 데이터 검색이 가능하고 저장 공간이 적게 사용 합니다.


◈ 특징

- IOT는 table data를 primary key에 대한 B*Tree index에 적재하는 구조를 갖습니다.

- IOT의 index row들은 인덱스 키 값과 non-key 값을 포한 합니다.

- IOT의 index에는 ROWID 정보가 없습니다.


◈ 장점

- range search, exact match를 수행하는 경우 일반적인 table보다 빠른   key-based access가 가능 합니다.

- full table scan 시 primary key에 대한 full index scan이 이루어지므로  자동적인 ordering이 이루어 집니다.

- index key column과 rowid에 대한 storage 중복을 피할 수 있어 storage가 절약 됩니다.

일반 테이블과 IOT테이블의 차이점


 - 일반테이블은 ROWID로 행을 구별하지만 IOT는 PK로 행을 구별합니다.

 - 일반 테이블의 FULL SCAN은 행이 Return되는 순서를 예측할 수 없지만, IOT는 PK값의 순서에 따랄 출력됩니다.

 - IOT는 Unique 제약조건을 설정 할 수 없습니다.

 - IOT는 클러스트를 사용할 수 없습니다.

 - IOT는 일반테이블보다 저장공간이 감소합니다.

 - IOT는 Primary Key를 꼭 생성해야 합니다.  

 - IOT의 Secondary 인덱스는 Primary Key값과 그것을 기반으로 하는 ’Universal Rowid’ 즉, Urowid를 가지고 인덱스가 만들어집니다.

 - IOT는 일반 인덱스와 달리 물리적인 ROWID 정보를 가지고 있지 않고 논리적인 유니버설 ROWID(UROWID)를 가지고 있어 빠르게 검색할 수 있습니다.


 ※ UROWID(Universal Rowid) : 인덱스 구성 테이블(IOT, Index-Organized Table)행의 논리적인 위치

IOT(INDEX ORGANIZED TABLE) 생성


[Syntax]



-- IOT 생성 예제
SQL> CREATE TABLE iot_test (
         id NUMBER CONSTRAINT   iot_test_pk_id   PRIMARY KEY,
         name VARCHAR2(30),
         contents VARCHAR2(2000))
         ORGANIZATION INDEX TABLESPACE indx
         PCTTHRESHOLD 40 INCLUDING name
         OVERFLOW TABLESPACE users;
 
테이블이 생성되었습니다.


아래는 위에서 생성한 문법의 설명 입니다.

◈ ORGANIZATION INDEX TABLESPACE : IOT Data가 저장되는 Tablespace
 
 
◈ PCTTHRESHOLD

  - IOT를 위해서 예약된 공간의 백분율로, 1블럭의 N% 비율보다 큰 데이터가 입력되면 키 열이 아닌 데이터는 OVERFLOW TABLESPACE절에 정의된 테이블스페이스에 저장 됩니다.

  - 0~50 사이값으로만 지정가능, 디폴트는 50

  - 만약 PCTTHRESHOLD 40 으로 지정했을 경우,  하나의 행자체가 위에 값보다 크게 되면 INCLUDING컬
뒤에 있는 컬럼만 제외하고는 모두 OVERFLOW 세그먼트로 이동됩니다.
 
 
◈ INCLUDING

  - IOT 행을 인덱스와 오버플로우 구역으로 나눌 열을 구분합니다.  

  - INCLUDING이 지정되지 않았는데 행 크기가 PCTTHRESHOLD를 초과하면 기본 키 열을 제외한 모든 이 오버플로우 세그먼트에 저장이 됩니다.

  - INCLUDING 뒤에 있는 컬럼만 제외하고 모두 오버플로우 세그먼트에 저장이 됩니다.

  - INCLUDING절에 오는 열은 기본 키의 마지막 열의 이름 이거나 기본 키가 아닌 열 입니다.
 
 
◈ OVERFLOW TABLESPACE
 
  - PCTTHRESHOLD를 초과하는 Data 행이 저장됩니다.

  - 만일 Overflow Tablespace Option을 주지 않고 생성된 IOT에 대하여 (PCTTHRESHOLD / 100) *DB_BLOCK_SIZE보다 큰 row가 발생한다면 ORA-1429와 같은 오류를 만나게 됩니다

 

IOT 생성시 만들어지는 세그먼트


위에서 설명한거와 같이 IOT를 생성할 때 기본적으로 인덱스 세그먼트와 OVERFLOW 세그먼트를 지정합니다.


◈ 인덱스 : PK 지정시 생성되는 인덱스로 CREATE TABLE 문에 지정된 테이블스페이스에 생성되며 이때 지정된 스토리지와 공간활용 파라미터를 가진다.

◈ OVERFLOW 행조각을 수용할 테이블 : PCTTHRESHOLD를 초과한 경우 나머지 컬럼들이 저장되는 테이블로 이름이 SYS_IOT_OVER_n으로 지정되며 n은 DBS_OBJECTS에서 볼 수 있는 IOT의 OBJECT_ID입니다.





- 위에 그림과 같이 인덱스 세그먼트이름은 PK 이름이 되고, 세그먼트유형은 INDEX가 됩니다.

- OverFlow 세그먼트는 세그먼트이름은  SYS_IOT_OVER_n로 지정되고, 세그먼트유형은 TABLE이 됩니다.



-- 위에서 생성한 IOT의 인덱스 세그먼트를 조회 예제 입니다.
SQL> SELECT segment_name FROM user_segments WHERE segment_type=’INDEX’;
SEGMENT_NAME
----------------------
PK_DEPT
PK_EMP
IOT_TEST_PK_ID    --> IOT의 Data가 저장되는 세그먼트



-- 위에서 생성한 IOT의 OverFlow 세그먼트 조회 예제 입니다.
SQL> SELECT segment_name FROM user_segments WHERE segment_type=’TABLE’;
 
SEGMENT_NAME
------------------------------------------------------------------------
DEPT
EMP
SYS_IOT_OVER_30957 --> OVERFLOW에 해당하는 Data가 저장되는 세그먼트

 

IOT 실습

똑같은 테이블과 똑같은 데이터를 가지고 일반테이블과 IOT로 실습을 해보겠습니다.
 
 1) 먼저 일반테이블을 생성해서 데이터를 INSERT합니다.


-- 테이블을 생성합니다.
 CREATE TABLE TEST(
 NO NUMBER  CONSTRAINT TEST_NO_PK Primary Key,
 TITLE VARCHAR2(50),
 CONTNETS VARCHAR2(500))
 TABLESPACE USERS;


 -- Data를 INSERT합니다.
 INSERT INTO TEST
VALUES(3,’CCCCCCCCCC’,’CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC’);
 INSERT INTO TEST
VALUES(1,’AAAAAAAAAA’,’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
 INSERT INTO TEST
VALUES(5,’EEEEEEEEEE’,’EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE’);
 INSERT INTO TEST
VALUES(2,’BBBBBBBBBB’,’BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB’);
 INSERT INTO TEST
VALUES(4,’DDDDDDDDDD’,’DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD’);
 COMMIT;


-- 데이타를 조회 합니다.
SQL> SELECT no, title FROM test;
 
  NO TITLE
---- ------------ 
   3 CCCCCCCCCC
   1 AAAAAAAAAA
   5 EEEEEEEEEE
   2 BBBBBBBBBB
   4 DDDDDDDDDD



 2) 위와 똑같이 IOT를 생성해서 똑같은 데이터를 INSERT합니다.
 
 
-- IOTTEST 테이블을 생성합니다.
CREATE TABLE IOTTEST (
NO NUMBER CONSTRAINT   IOTTEST_PK_NO   PRIMARY KEY,
TITLE VARCHAR2(50),
CONTENTS VARCHAR2(500))
ORGANIZATION INDEX TABLESPACE INDX
PCTTHRESHOLD 40 INCLUDING TITLE
OVERFLOW TABLESPACE USERS;
 
 
-- DATA를 INSERT합니다.
 INSERT INTO IOTTEST
VALUES(3,’CCCCCCCCCC’,’CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC’);
 INSERT INTO IOTTEST
VALUES(1,’AAAAAAAAAA’,’AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
 INSERT INTO IOTTEST
VALUES(5,’EEEEEEEEEE’,’EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE’);
 INSERT INTO IOTTEST
VALUES(2,’BBBBBBBBBB’,’BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB’);
 INSERT INTO IOTTEST
VALUES(4,’DDDDDDDDDD’,’DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD’);
 COMMIT;
 
 
-- 데이타를 조회 합니다.
SQL>COL title FORMAT A30;
SQL> SELECT no, title FROM iottest;
 
  NO TITLE
---- ----------
   1 AAAAAAAAAA
   2 BBBBBBBBBB
   3 CCCCCCCCCC
   4 DDDDDDDDDD
   5 EEEEEEEEEE
 
→ 입력한 순서에 상관없이 오름차순으로 정렬되어 있는 것을 확인 할 수 있습니다

 

IOT 9i 향상기능


오라클 9i에서 확장된 IOT에 대한 온라인 기능을 보겠습니다.
 
 
 -- IOT의 세컨더리 인덱스에 대해 온라인 생성과 재구성을 할 수 있습니다.  
SQL>CREATE INDEX iottest_title_idx ON iottest (title) ONLINE;  
인덱스가 생성되었습니다.
 
 -- IOT의 프라이머리 인덱스에 대해 온라인 COALESCE를 할 수 있습니다.  
SQL>ALTER TABLE iottest COALESCE;  
 
 
 
※ IOT 로지컬 ROWID의 온라인 갱신
 
IOT 세컨더리 인덱스는 성능향상을 위해 각 엔트리에 대한 로지컬 ROWID를 저장합니다.
이 로지컬 ROWID는 변경될 수 있기 때문에 인덱스에 저장된 값은 최대 추측가능 값이 됩니다.

따라서, 로지컬 ROWID는 내내 변질 상태가 될 수 있고, 이 변질된 로지컬 ROWID은 온라인으로 갱신이 가능합니다.

아래 명령어로 로지컬 ROWID의 온라인 갱신을 할 수 있습니다.
 
 
 -- IOT의 세컨더리 인덱스에 대한 로지컬 ROWID의 온라인 갱신을 할 수 있습니다.  
SQL>ALTER INDEX iottest_title_idx UPDATE BLOCK REFERENCES;  
 
 
 ※ IOT 온라인 MOVE

IOT는 단편화(fragmentation)을 방지하기 위해 재생성 될 수 있습니다.
ALTER명령의 MOVE옵션을 사용하여 재생성 작업을 온라인으로 할 수 있습니다.
오버플로우 데이터 세그먼트도 온라인으로 옮겨질 수 있습니다.


 -- IOT를 오버플로우 세그먼트와 함께 온라인으로 옮길 수 있습니다.
 -- iottest 테이블의 테이블스페이스와 OVERFLOW 테이블스페이스 변경  
SQL>ALTER TABLE iottest MOVE ONLINE TABLESPACE tbs1
      OVERFLOW TABLESPACE tbs2;

 

IOT 정보조회


-- PAGE,문자열 포맷설정
SET LINESIZE 120;
COL IOT FORMAT A10;
COL OVERFLOW FORMAT A25;
COL INDEX FORMAT A20;
COL OVERFLOWTS FORMAT A10;
COL INDEXTS FORMAT A10;


-- IOT 정보 조회
SELECT t.table_name AS "IOT", o.table_name AS "Overflow",
       i.index_name AS "Index", o.tablespace_name AS "OverflowTS",
       i.tablespace_name AS "IndexTS", I.pct_threshold
FROM dba_tables t, dba_tables o, dba_indexes I
WHERE t.owner = o.owner
  AND t.table_name = o.iot_name
  AND t.owner = I.owner
  AND t.table_name = I.table_name
  AND t.owner = ’SCOTT’;


-- 조회 결과
IOT        Overflow                  Index                OverflowTS IndexTS    PCT_THRESHOLD
---------- ------------------------- -------------------- ---------- ---------- -------------
IOTTEST    SYS_IOT_OVER_30979        IOTTEST_PK_NO        USERS      INDX                  40
IOTTEST    SYS_IOT_OVER_30979        IOTTEST_TITLE_IDX    USERS      SYSTEM
IOT_TEST   SYS_IOT_OVER_30966        IOT_TEST_PK_ID       USERS      INDX                  40

<< 참고문헌 >> - Oracle Technical Bulletins  No.11615 ORACLE 8.0의 IOT(INDEX ORGANIZED TABLE) - Oracle 9i Performance Tuning Student Guide Volumn 1

================================================     * 데이터베이스 정보공유 커뮤니티 oracleclub.com     * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)