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) | |
