꿈꾸는 개발자, DBA 커뮤니티 구루비

IOT 실습 예제

똑같은 테이블과 똑같은 데이터를 가지고 일반테이블과 IOT로 실습을 해보겠습니다.

1) 먼저 일반테이블을 생성해서 데이터를 INSERT 한 예제입니다.

 
-- 테이블을 생성합니다.
SQL> 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 테이블을 생성합니다.
SQL> 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 향상기능

확장된 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