인덱스 조직 테이블(IOT) (by joang77) [2009.11.27]
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
☞ T = 8 + 64 = 72 Oracle Blocks.