1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 6차 코어 오라클 데이터베이스 스터디
  4. 5.4 IOT 테이블 분석

5.4 IOT 테이블 분석 (by darkturtle) [2011.06.18]

===============================================================
===============================================================

IOT 테이블분석

TABLE+INDEX와 IOT의 차이점

  • TABLE + INDEX에서 데이터를 조회하기 위해서는 INDEX스캔 후 TABLE로 액세스하게됨
  • IOT는 INDEX스캔으로 데이터에 대한 조회가 가능하다.

===============================================================
테스트 진행 : IOT
===============================================================
-- FLM 방식의 테이블스페이스에다 생성
create table flm_iot (
id number(5) constraint flm_iot_pk primary key,
name char(1000))
organization index
tablespace flmtbs;

-- ASSM 방식의 테이블스페이스에다 생성
create table assm_iot (
id number(5) constraint assm_iot_pk primary key,
name char(1000))
organization index
tablespace assmtbs;

-- BLOCK HEADER를 비교
select segment_name, header_file, header_block, blocks
from dba_segments
where segment_name in ('FLM_IOT_PK','ASSM_IOT_PK');
SEGMENT_NAME         HEADER_FILE HEADER_BLOCK     BLOCKS
-------------------- ----------- ------------ ----------
FLM_IOT_PK                    10            9          8
ASSM_IOT_PK                    9          147          8

select segment_name, extent_id, file_id, block_id, blocks
from dba_extents
where segment_name in ('FLM_IOT_PK','ASSM_IOT_PK');

SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
FLM_IOT_PK                    0         10          9          8
ASSM_IOT_PK                   0          9        145          8

--인덱스 세그먼트만 존재하므로 블록의 구조를 확인하기 위해 오브젝트 아이디를 확인
select object_id, object_name, object_type
  from dba_objects
 where object_name in ('FLM_IOT_PK','ASSM_IOT_PK');

OBJECT_ID OBJECT_NAME           OBJECT_TYPE
---------- -------------------- -------------------
    154670 ASSM_IOT_PK          INDEX
    154667 FLM_IOT_PK           INDEX

-- 데이터입력 후 인덱스 트리 덤프를 수행
begin
  for i in 1..100 loop
      insert into assm_iot values (i, to_char(i)||'Row');
      insert into flm_iot values (i, to_char(i)||'Row');
end loop;
commit;
end;
/
alter session set events 'immediate trace name treedump level 154670';
alter session set events 'immediate trace name treedump level 154667';

< ASSM_IOT_PK 인덱스덤프내용>
----- begin tree dump
branch: 0x2400094 37748884 (0: nrow: 15, level: 1)
   leaf: 0x2400096 37748886 (-1: nrow: 7 rrow: 7)
   leaf: 0x2400097 37748887 (0: nrow: 7 rrow: 7)
   leaf: 0x2400098 37748888 (1: nrow: 7 rrow: 7)
   leaf: 0x2400095 37748885 (2: nrow: 7 rrow: 7)
   leaf: 0x240009a 37748890 (3: nrow: 7 rrow: 7)
   leaf: 0x240009b 37748891 (4: nrow: 7 rrow: 7)
   leaf: 0x240009c 37748892 (5: nrow: 7 rrow: 7)
   leaf: 0x240009d 37748893 (6: nrow: 7 rrow: 7)
   leaf: 0x240009e 37748894 (7: nrow: 7 rrow: 7)
   leaf: 0x240009f 37748895 (8: nrow: 7 rrow: 7)
   leaf: 0x24000a0 37748896 (9: nrow: 7 rrow: 7)
   leaf: 0x2400099 37748889 (10: nrow: 7 rrow: 7)
   leaf: 0x24000a5 37748901 (11: nrow: 7 rrow: 7)
   leaf: 0x24000a6 37748902 (12: nrow: 7 rrow: 7)
   leaf: 0x24000a7 37748903 (13: nrow: 2 rrow: 2)
----- end tree dump

< FLM_IOT_PK 인덱스덤프내용>
----- begin tree dump
branch: 0x280000a 41943050 (0: nrow: 15, level: 1)
   leaf: 0x280000b 41943051 (-1: nrow: 7 rrow: 7)
   leaf: 0x280000c 41943052 (0: nrow: 7 rrow: 7)
   leaf: 0x280000d 41943053 (1: nrow: 7 rrow: 7)
   leaf: 0x280000e 41943054 (2: nrow: 7 rrow: 7)
   leaf: 0x280000f 41943055 (3: nrow: 7 rrow: 7)
   leaf: 0x2800010 41943056 (4: nrow: 7 rrow: 7)
   leaf: 0x2800011 41943057 (5: nrow: 7 rrow: 7)
   leaf: 0x2800012 41943058 (6: nrow: 7 rrow: 7)
   leaf: 0x2800013 41943059 (7: nrow: 7 rrow: 7)
   leaf: 0x2800014 41943060 (8: nrow: 7 rrow: 7)
   leaf: 0x2800015 41943061 (9: nrow: 7 rrow: 7)
   leaf: 0x2800016 41943062 (10: nrow: 7 rrow: 7)
   leaf: 0x2800017 41943063 (11: nrow: 7 rrow: 7)
   leaf: 0x2800018 41943064 (12: nrow: 7 rrow: 7)
   leaf: 0x2800019 41943065 (13: nrow: 2 rrow: 2)
----- end tree dump

-- 
select segment_name, extent_id, file_id, block_id, blocks
from dba_extents
where segment_name in ('FLM_IOT_PK','ASSM_IOT_PK');

SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
FLM_IOT_PK                    0         10          9          8
FLM_IOT_PK                    1         10         17          8
FLM_IOT_PK                    2         10         25          8
ASSM_IOT_PK                   0          9        145          8
ASSM_IOT_PK                   1          9        153          8
ASSM_IOT_PK                   2          9        161          8
alter system dump datafile 10 block min 9 block max 20;
alter system dump datafile 9 block min 145 block max 152;

<FLM_index구조>
buffer tsn: 9 rdba: 0x02800009 (10/9)
scn: 0x0000.0681a238 seq: 0x01 flg: 0x04 tail: 0xa2381001
frmt: 0x02 chkval: 0xbe0e type: 0x10=DATA SEGMENT HEADER - UNLIMITED

buffer tsn: 9 rdba: 0x0280000a (10/10)
scn: 0x0000.0681a23a seq: 0x02 flg: 0x04 tail: 0xa23a0602
frmt: 0x02 chkval: 0x59ba type: 0x06=trans data
Branch block dump

buffer tsn: 9 rdba: 0x0280000b (10/11)
scn: 0x0000.0681a1d7 seq: 0x01 flg: 0x04 tail: 0xa1d70601
frmt: 0x02 chkval: 0x296c type: 0x06=trans data
Leaf block dump

<ASSM_index구조>
buffer tsn: 8 rdba: 0x02400091 (9/145)
scn: 0x0000.0681a224 seq: 0x02 flg: 0x04 tail: 0xa2242002
frmt: 0x02 chkval: 0xdc8c type: 0x20=FIRST LEVEL BITMAP BLOCK

buffer tsn: 8 rdba: 0x02400092 (9/146)
scn: 0x0000.0681a225 seq: 0x01 flg: 0x04 tail: 0xa2252101
frmt: 0x02 chkval: 0xddbc type: 0x21=SECOND LEVEL BITMAP BLOCK

buffer tsn: 8 rdba: 0x02400093 (9/147)
scn: 0x0000.0681a225 seq: 0x01 flg: 0x04 tail: 0xa2252301
frmt: 0x02 chkval: 0xf132 type: 0x23=PAGETABLE SEGMENT HEADER

buffer tsn: 8 rdba: 0x02400094 (9/148)
scn: 0x0000.0681a23a seq: 0x01 flg: 0x04 tail: 0xa23a0601
frmt: 0x02 chkval: 0x044a type: 0x06=trans data
Branch block dump

buffer tsn: 8 rdba: 0x02400095 (9/149)
scn: 0x0000.0681a23b seq: 0x01 flg: 0x06 tail: 0xa23b0601
frmt: 0x02 chkval: 0x69e2 type: 0x06=trans data
Leaf block dump

===============================================================

인덱스의 구조

  • FLM : Segment Header + Branch Block + leaf Block
  • ASSM : L1 BMB + L2 BMB + L3 BMB + Branch Block + leaf Block
  • FLM과 ASSM은 비슷한 구조로 인덱스 내용이 저장된다.

<그림 5-2>

  • Index Row Header : Flag + Lock정보
  • Index PK Column(s) : Index Col 0 정보는 Column Length + Column Value
  • Table Row Header : Flag + Lock정보 + Column 정보
  • Table Column Data : CColumn Length + Column Value

IOT에서의 OVERFLOW절의 용도
OVERFLOW영역에 저장되는 데이터는 어떤방식으로 연결되는지 확인



===============================================================
테스트 진행 : IOT 중 PlaceHold영역
===============================================================
create table flm_iot_of(
id number(5) constraint flm_iot_of_pk primary key,
pwd varchar(20),
name char(1000))
organization index
overflow
including pwd
tablespace FLMTBS;

-- Test를 위해 짝수행에만 Name을 입력
begin
  for i in 1..10 loop
     if mod(i,2) = 0 then
        insert into flm_iot_of
            values (i, to_char(i) || to_char(i), to_char(i) || '는 짝수값이다.');
     else
        insert into flm_iot_of
            values (i, to_char(i) || to_char(i), null );
     end if;

     commit;
  end loop;
end;
/

-- Dump를 내리기 위해 정보수집
select object_id, object_name, object_type
  from dba_objects
 where object_name = UPPER('flm_iot_of_pk');

OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
    154673 FLM_IOT_OF_PK        INDEX

select segment_name, extent_id, file_id, block_id, blocks
from dba_extents
where segment_name = UPPER('flm_iot_of_pk');

SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
FLM_IOT_OF_PK                 0         10         33          8

-- Dump 수행
alter session set events 'immediate trace name treedump level 154673';

alter system dump datafile 10 block min 33 block max 38;

<인덱스덤프내용>
----- begin tree dump
leaf: 0x2800022 41943074 (0: nrow: 10 rrow: 10)
----- end tree dump

<블록덤프내용>
buffer tsn: 9 rdba: 0x02800022 (10/34)
scn: 0x0000.0681a841 seq: 0x02 flg: 0x06 tail: 0xa8410602
frmt: 0x02 chkval: 0x84f7 type: 0x06=trans data

Leaf block dump
===============
row#0[8025] flag: K-----, lock: 0, len=11
col 0; len 2; (2):  c1 02
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  31 31
row#1[8008] flag: K-----, lock: 0, len=17
col 0; len 2; (2):  c1 03
tl: 12 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x004179d2.0
col  0: [ 2]  32 32
row#2[7997] flag: K-----, lock: 0, len=11
col 0; len 2; (2):  c1 04
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  33 33
row#3[7980] flag: K-----, lock: 0, len=17
col 0; len 2; (2):  c1 05
tl: 12 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x004179d2.1
col  0: [ 2]  34 34
row#4[7969] flag: K-----, lock: 0, len=11
col 0; len 2; (2):  c1 06
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  35 35
row#5[7952] flag: K-----, lock: 0, len=17
col 0; len 2; (2):  c1 07
tl: 12 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x004179d2.2
col  0: [ 2]  36 36
row#6[7941] flag: K-----, lock: 0, len=11
col 0; len 2; (2):  c1 08
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  37 37
row#7[7924] flag: K-----, lock: 0, len=17
col 0; len 2; (2):  c1 09
tl: 12 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x004179d2.3
col  0: [ 2]  38 38
row#8[7913] flag: K-----, lock: 0, len=11
col 0; len 2; (2):  c1 0a
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  39 39
row#9[7894] flag: K-----, lock: 2, len=19
col 0; len 2; (2):  c1 0b
tl: 14 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x004179d2.4
col  0: [ 4]  31 30 31 30
----- end of leaf block dump -----
===============================================================

덤프 분석결과

  • Overflow영역에 있는 곳은 주소로만 표시된다. (nrid 항목)

Overflow영역 확인

  • IOT에서 Overflow영역을 지정하게 되면 SYS_IOT_OVER_<object_id>라는 이름으로 별도의 오브젝트가 생성된다. 


===============================================================
테스트 진행 : IOT 중 Overflow 영역
===============================================================

select to_char(to_number('004','XXXXXXXX')) / 4 as fno,
       to_char(to_number('179d2','XXXXXXXX')) as bno
from dual;

FNO BNO
---------- ------------------
         1 96722

alter system dump datafile 1 block 96722 ;

buffer tsn: 0 rdba: 0x004179d2 (1/96722)
scn: 0x0000.0681a841 seq: 0x02 flg: 0x06 tail: 0xa8410602
frmt: 0x02 chkval: 0xc1a7 type: 0x06=trans data

block_row_dump:
tab 0, row 0, @0x1bca
tl: 1006 fb: -----L-- lb: 0x0  cc: 1
col  0: [1000]

tab 0, row 1, @0x17dc
tl: 1006 fb: -----L-- lb: 0x0  cc: 1
col  0: [1000]
 
tab 0, row 2, @0x13ee
tl: 1006 fb: -----L-- lb: 0x0  cc: 1
col  0: [1000]

tab 0, row 3, @0x1000
tl: 1006 fb: -----L-- lb: 0x0  cc: 1
col  0: [1000]

tab 0, row 4, @0xc12
tl: 1006 fb: -----L-- lb: 0x1  cc: 1
col  0: [1000]
  1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 6차 코어 오라클 데이터베이스 스터디
  4. 5.4 IOT 테이블 분석