by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

인덱스 조직 테이블(IOT)




ORACLE 8.0의 IOT(INDEX ORGANIZED TABLE. IOT)

개요

  • 특징
    1. Oracle 8 N.F
    2. 모든 테이블 데이터를 b-tree 구조로 저장
    3. 이 구조의 leaf 블록 : 인덱스 leaf 엔트리의 두번째 구성 요소에 rowid 대신 non-key 열을 가짐
    4. 테이블과 인덱스라는 별도의 두 세그먼트를 갖지 않음
    5. index rows = index key value + non-key value
    6. ROWID 정보 없음

  • IOT 비적용 대상
    1. 정렬에 대한 부담 : 데이터 입력, 수정시 DBMS에 부하가 많이 걸려 저장되는 데이터가 많거나 변경이 많은 경우는 비권장

  • IOT 적용 대상
    1. 자주 사용되는 마스터성 테이블 : 일부 마스터성 엔티티의 경우 트랜잭션이 집중되면서 시스템 성능의 병목지점으로 작용할 가능성이 큰데, IOT 사용 시 효과적
    2. 인덱스가 대부분인 테이블
      1. 인덱스는 칼럼수가 적은 것이 좋으나 부득이하게 전체 칼럼의 대부분을 인덱스가 될 경우 : 테이블과 인덱스에 같은 데이터를 중복해서 가질 필요 없이 IOT를 적용하면 효과를 볼 수 있음
    3. 접근 방법은 다르나 유사한 효과 발생 : MMDB(main memory db)의 hot table/data, ssd(solid state disk)의 hot file

  • 장점
    1. range search, exact match : 일반 테이블보다 빠른 key-based access 가능
    2. full table scan : primary key에 대한 full index scan이 이루어지므로 자동으로 정렬됨
    3. storage 절약 : index key column과 rowid에 대한 storage 중복을 피함

  • 제약사항
    1. 추가적인 index 생성 불가능 : IOT는 오직 primary key에 대한 인덱스 생성 가능
    2. cluster table로 이용되지 못함
    3. 병렬 작업 불가능
    4. 분산, 복제, 분할 불가능
    5. long, long raw, lob 지원 안 됨

  • 생성 구문

    SQL> CREATE TABLE IOTAB
    (COL_PK NUMBER PRIMARY KEY,
    COL2 VARCHAR2(500),
    COL3 NUMBER,
    COL4 VARCHAR2(1000))
    ORGANIZATION INDEX TABLESPACE USERS
    PCTTHRESHOLD 10 INCLUDING COL2
    OVERFLOW TABLESPACE USERS;

    • ORGANIZATION INDEX : IOT 생성을 정의하는 keyword
    • PCTTHRESHOLD :【 IOT의 단일 row > (PCTTHRESHOLD / 100) * DB_BLOCK_SIZE 】경우 INCLUDING column 이후의 row data는 overflow table에 저장
    • INCLUDING : including column 미지정 시 pramary key column 이외의 컬럼 데이터가 overflow table에 저장
    • overflow : 테이블 스페이스를 지정하지 않으면 user default tablespace가 사용됨
    • ORA-1429 : OVERFLOW TABLESPACE option을 주지 않고 생성된 IOT에서【 (PCTTHRESHOLD / 100) * DB_BLOCK_SIZE < row 】발생 시



  • IOT 테이블 생성
    conn scott/loveora
    
    create table iot
    (username varchar2(30), document_name varchar2(30),
    other_data char(100), constraint iot_pk
    primary key (username, document_name))
    organization index
    TABLESPACE TOOLS 
    INCLUDING "DOCUMENT_NAME" OVERFLOW TABLESPACE USERS;
    
    1. char(100) : 고정자리로 항상 100자리의 공간을 차지함. 테이블 row의 평균 크기를 약 130 byte정도로 만들기 위함
    2. heaps와 iot 차이점 : organization index 절의 유무
    3. organization index 절 : 테이블 데이터를 테이블 세그먼트가 아닌 인덱스 세그먼트에 저장하게 함
  • 일반 테이블 생성
    create table heaps
    (username varchar2(30), document_name varchar2(30),
    other_data char(100), constraint heap_pk
    primary key (username, document_name));
    



  • 데이터 넣기 : 문서 100개 추출
    begin
    for i in 1 .. 100
    loop
     for x in (select username from all_users)
     loop
      insert into heaps (username, document_name, other_data)
      values(x.username,x.username || '_' || i,'x');
      
      insert into iot (username, document_name, other_data)
      values(x.username,x.username || '_' || i,'x');
      end loop; 
     end loop;
     commit;
    end;
    /
    



  • 성능 비교
    1. user1에 해당하는 모든 row를 읽은 다음에 user2에 해당하는 모든 row를 읽음
    2. TKPROF 보고서로 비교
      alter session set tracefile_identifier='IOT_HEAP';
      set timing on
      set time on
      alter session set sql_trace=true;
      set autotrace on
      
      declare
      type array is table of varchar2(100);
       l_array1 array;
       l_array2 array;
       l_array3 array;
      begin
      for i in 1 .. 10
      loop
       for x in (select username from all_users)
       loop
        for y in (select * from heaps single_row where username = x.username)
        loop
         null;
        end loop;
        for y in (select * from iot single_row where username = x.username)
        loop
         null;
        end loop;
        select * bulk collect
         into l_array1,l_array2,l_array3
         from heaps bulk_collect
         where username = x.username;
         select * bulk collect
         into l_array1,l_array2,l_array3
         from iot bulk_collect
         where username = x.username;
        end loop;
       end loop;
       end;
       /
       22:29:54 SQL> /
      
      PL/SQL procedure successfully completed.
      
      query1 】
      select * from heaps single_rows;
      USERNAME        DOCUMENT_NAME    OTHER_DATA    
      -------------- -------------- --------------
      LOGCOPSDB      LOGCOPSDB_90         x
      ANONYMOUS      ANONYMOUS_90         x
      
      2900 rows selected.
      
      Elapsed: 00:00:05.26
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          1          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch      195      0.02       0.03          0        245          0        2900
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total      197      0.02       0.04          0        246          0        2900
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 54  (SCOTT)
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
         2900  TABLE ACCESS FULL HEAPS (cr=245 pr=0 pw=0 time=182821 us)
      
      
      Rows     Execution Plan
      -------  ---------------------------------------------------
            0  SELECT STATEMENT   MODE: ALL_ROWS
         2900   TABLE ACCESS (FULL) OF 'HEAPS' (TABLE)
      
      query2 】
      select * from iot single_rows;
      USERNAME        DOCUMENT_NAME   OTHER_DATA    
      -------------- -------------- --------------
      SCOTT            SCOTT_67            x
      SCOTT            SCOTT_68            x
      
      2900 rows selected.
      
      Elapsed: 00:00:05.87
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.01          0          2          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch      195      0.05       0.07          0        290          0        2900
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total      197      0.06       0.08          0        292          0        2900
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 54  (SCOTT)
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
         2900  INDEX FAST FULL SCAN IOT_PK (cr=290 pr=0 pw=0 time=424398 us)(object id 53144)
      
      
      Rows     Execution Plan
      -------  ---------------------------------------------------
            0  SELECT STATEMENT   MODE: ALL_ROWS
         2900   INDEX (FAST FULL SCAN) OF 'IOT_PK' (INDEX (UNIQUE))
      
      
      query3 】
      select * from heaps bulk_collect;
      USERNAME        DOCUMENT_NAME   OTHER_DATA    
      -------------- -------------- --------------
      LOGCOPSDB       LOGCOPSDB_90        x
      ANONYMOUS       ANONYMOUS_90        x
      
      2900 rows selected.
      
      Elapsed: 00:00:05.92
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.01          0          2          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch      195      0.04       0.05          0        245          0        2900
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total      197      0.05       0.06          0        247          0        2900
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 54  (SCOTT)
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
         2900  TABLE ACCESS FULL HEAPS (cr=245 pr=0 pw=0 time=188558 us)
      
      
      Rows     Execution Plan
      -------  ---------------------------------------------------
            0  SELECT STATEMENT   MODE: ALL_ROWS
         2900   TABLE ACCESS (FULL) OF 'HEAPS' (TABLE)
      
      
      query4 】
      select * from iot bulk_collect;
      USERNAME        DOCUMENT_NAME   OTHER_DATA    
      -------------- -------------- --------------
      SCOTT           SCOTT_39           x
      SCOTT           SCOTT_4            x
      
      2900 rows selected.
      
      Elapsed: 00:00:05.96
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          2          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch      195      0.04       0.05          0        290          0        2900
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total      197      0.04       0.06          0        292          0        2900
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 54  (SCOTT)
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
         2900  INDEX FAST FULL SCAN IOT_PK (cr=290 pr=0 pw=0 time=174079 us)(object id 53144)
      
      
      Rows     Execution Plan
      -------  ---------------------------------------------------
            0  SELECT STATEMENT   MODE: ALL_ROWS
         2900   INDEX (FAST FULL SCAN) OF 'IOT_PK' (INDEX (UNIQUE))
      



  • IOT 분석
    1. 모든 IOT에 각각 별도의 CHAINED ROWS테이블 생성 : DBMSIOTC.SQL과 PRVTIOTC.PLB를 실행하여 IOT에 대한 IOT_CHAINED_ROWS 테이블 생성 가능
    2. IOT chained_rows table 생성 ⇒ anlyze command 수행
    3. IOT 생성 시 "INCLUDING "DOCUMENT_NAME" OVERFLOW TABLESPACE USERS" 절 생략 : IOT chained rows확인 불가 & 에러 발생
    • overflow 추가 : alter table iot add OVERFLOW TABLESPACE users INCLUDING "DOCUMENT_NAME";
      conn sys/loveora as sysdba
      @$ORACLE_HOME/rdbms/admin/dbmsiotc.sql
      @$ORACLE_HOME/rdbms/admin/PRVTIOTV.PLB
      EXECUTE DBMS_IOT.BUILD_CHAIN_ROWS_TABLE('SCOTT','IOT');
      conn scott/loveora
      ANALYZE TABLE IOT LIST CHAINED ROWS INTO IOT_CHAINED_ROWS;
      



  • IO 방법
    1. 일반 테이블 : 하나 이상의 인덱스 블록을 읽어 rowid 를 읽고, 이 rowid 에 기초하여 테이블에 대한 IO가 발생
    2. Index Organized Table : 모든 행이 인덱스의 leaf 노드에 있으므로 인덱스 블록만 read 하면 됨(효율적)

  • IOT 인덱스의 사용
    1. IOT 인덱스를 검색한 후 rowid 를 통해 테이블에서 바로 데이터를 읽음 : 일반 테이블보다 액세스 속도 빠름(테이블 데이터 랜덤 액세스하지 않음)
    2. 인덱스가 전체 칼럼에서 많은 비중을 차지하거나, 트랜잭션이 많이 일어나는 마스터성 테이블을 IOT로 구성하면 유용
    3. 인덱스와 테이블을 각각의 객체로 유지하지 않기 때문에 인덱스의 rowid 영역이 없어져 저장 공간 절약


IOT가 사용한 SPACE 계산법(How To Determine the Space Allocated to an IOT)

  • 사용된 공간 확인 : dba_segments.segment_name or DBA_EXTENTS view.segment_name
  • IOT는 2두개의 segment 생성
    conn scott/loveora
    select object_name, object_id
     from dba_objects
     where object_name='IOT' and owner='SCOTT';
    
     object_name  object_id
    ============ ==========
     IOT            53143
     
    select segment_name, segment_type, bytes, blocks 
     from dba_segments 
     where segment_name like '%53143%' OR segment_name = 'IOT_PK';
    
    segment_name      segment_type    bytes   blocks
    ================= ============= ======== =======
    SYS_IOT_OVER_53143  TABLE         65536     8
    IOT_PK              INDEX         524288    64
    
    select table_name, iot_type, iot_name, tablespace_name 
     from dba_tables
     where table_name = 'IOT'
        or (iot_name = 'TEST_CHECK' and iot_type = 'IOT_OVERFLOW');
     
    table_name            iot_type      iot_name    tablespace_name
    ================== ============== ============ =================
    SYS_IOT_OVER_53143  IOT_OVERFLOW       IOT       USERS
    IOT                     IOT		
    
    SELECT index_name, index_type, tablespace_name, table_name
     FROM dba_indexes 
     where table_name = 'IOT';
     index_name           index_type    tablespace_name   table_name
     ==================  =============  ================ =============
    IOT_PK                IOT - TOP         TOOLS         IOT_PK
    



  • 계산
    1. A = IOT Index (IOT_PK)
    2. B = OVERFLOW segment(SYS_IOT_OVER_53143)
    3. T = IOT에 의해 사용된 total Physical Storage Space
    4. IOT에 의해 사용된 총 space(T) = A + B
    &nbsp;&nbsp;&nbsp;☞ T = 8 + 64 = 72 Oracle Blocks.
    



Reference Document

1. OTN 포럼 : ORACLE 8.0의 IOT(INDEX ORGANIZED TABLE)
2. OTN 포럼 : IOT(INDEX ORGANIZED TABLE)이 사용한 SPACE를 계산하는 방법
3. 진화하는 다른 인덱스 알아보기

문서에 대하여

  • 최초작성자 : 박혜은
  • 최초작성일 : 2009년 11월 27일
  • 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.