이펙티브 오라클 (2009년)
인덱스 조직 테이블(IOT) 0 0 58,951

by 구루비스터디 IOT [2018.05.26]


  1. ORACLE 8.0의 IOT(INDEX ORGANIZED TABLE. IOT)
    1. IOT가 사용한 SPACE 계산법(How To Determine the Space Allocated to an IOT)
    2. Reference Document


ORACLE 8.0의 IOT(INDEX ORGANIZED TABLE. IOT)


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


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


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

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


제약사항
  • 추가적인 index 생성 불가능 : IOT는 오직 primary key에 대한 인덱스 생성 가능
  • cluster table로 이용되지 못함
  • 병렬 작업 불가능
  • 분산, 복제, 분할 불가능
  • 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 】발생 시
    !NON_IOT vs IOT.jpg!


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;

  • char(100) : 고정자리로 항상 100자리의 공간을 차지함. 테이블 row의 평균 크기를 약 130 byte정도로 만들기 위함
  • heaps와 iot 차이점 : organization index 절의 유무
  • 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;
/


성능 비교
  • user1에 해당하는 모든 row를 읽은 다음에 user2에 해당하는 모든 row를 읽음
  • 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 분석
  • 모든 IOT에 각각 별도의 CHAINED ROWS테이블 생성 : DBMSIOTC.SQL과 PRVTIOTC.PLB를 실행하여 IOT에 대한 IOT_CHAINED_ROWS 테이블 생성 가능
  • IOT chained_rows table 생성 ⇒ anlyze command 수행
  • 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 방법
  • 일반 테이블 : 하나 이상의 인덱스 블록을 읽어 rowid 를 읽고, 이 rowid 에 기초하여 테이블에 대한 IO가 발생
  • Index Organized Table : 모든 행이 인덱스의 leaf 노드에 있으므로 인덱스 블록만 read 하면 됨(효율적)


IOT 인덱스의 사용
  • IOT 인덱스를 검색한 후 rowid 를 통해 테이블에서 바로 데이터를 읽음 : 일반 테이블보다 액세스 속도 빠름(테이블 데이터 랜덤 액세스하지 않음)
  • 인덱스가 전체 칼럼에서 많은 비중을 차지하거나, 트랜잭션이 많이 일어나는 마스터성 테이블을 IOT로 구성하면 유용
  • 인덱스와 테이블을 각각의 객체로 유지하지 않기 때문에 인덱스의 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

"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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