IOT中溢出区属性pctthreshold和including验证

OCM考试中创建iot时要求使用溢出区属性pctthreshold和including，其实doc描述的还是比较清楚的，我群里有人让我给解释一下，顺道我也验证一下，下面是doc描述和我的验证过程，供参考。[@more@]

Index-Organized Tables with Row Overflow Area
B-tree index entries are usually quite small, because they only consist of the key value and a ROWID. In index-organized tables, however, the B-tree index entries can be large, because they consist of the entire row. This may destroy the dense clustering property of the B-tree index.
Oracle provides the OVERFLOW clause to handle this problem. You can specify an overflow tablespace so that, if necessary, a row can be divided into the following two parts that are then stored in the index and in the overflow storage area segment, respectively:
?The index entry, containing column values for all the primary key columns, a physical rowid that points to the overflow part of the row, and optionally a few of the nonkey columns
?The overflow part, containing column values for the remaining nonkey columns
With OVERFLOW, you can use two clauses, PCTTHRESHOLD and INCLUDING, to control how Oracle determines whether a row should be stored in two parts and if so, at which nonkey column to break the row. Using PCTTHRESHOLD, you can specify a threshold value as a percentage of the block size. If all the nonkey column values can be accommodated within the specified size limit, the row will not be broken into two parts. Otherwise, starting with the first nonkey column that cannot be accommodated, the rest of the nonkey columns are all stored in the row overflow segment for the table.
The INCLUDING clause lets you specify a column name so that any nonkey column, appearing in the CREATE TABLE statement after that specified column, is stored in the row overflow segment. Note that additional nonkey columns may sometimes need to be stored in the overflow due to PCTTHRESHOLD-based limits.
--=========================================================
SQL> create table t_iot(id int primary key,name varchar2(10) ,age int,name1 char(2000),name2 varchar2(10)) organization index pctthreshold 10 overflow
tablespace users;
Table created.
SQL> insert into t_iot values(1,'a',20,'a','a');
1 row created.
SQL> insert into t_iot values(2,'b',30,'b','b');
1 row created.
SQL> insert into t_iot values(3,'c',40,'c','c');
1 row created.
SQL> commit;
Commit complete.
SQL> set linesize 200
SQL> select id,name,age,substr(name1,1,2) name1,name2,rowid from t_iot;
ID NAME AGE NAME NAME2 ROWID
---------- ---------- ---------- ---- ---------- -----------------------------------------
1 a 20 a a *BABAhqICwQL+
2 b 30 b b *BABAhqICwQP+
3 c 40 c c *BABAhqICwQT+
SQL>
SQL> col rowid_dump format a50
SQL> col rowid format a20
SQL> select id,name,age,substr(name1,1,2) name1,name2,rowid,dump(rowid) rowid_dump from t_iot;
ID NAME AGE NAME NAME2 ROWID ROWID_DUMP
---------- ---------- ---------- ---- ---------- -------------------- --------------------------------------------------
1 a 20 a a *BABAhqICwQL+ Typ=208 Len=10: 2,4,0,64,134,162,2,193,2,254
2 b 30 b b *BABAhqICwQP+ Typ=208 Len=10: 2,4,0,64,134,162,2,193,3,254
3 c 40 c c *BABAhqICwQT+ Typ=208 Len=10: 2,4,0,64,134,162,2,193,4,254
SQL>
--==========================================================
这里要想弄清楚数据是如何存储的，首先要大致熟悉一下iot表中逻辑rowid的格式，可以参考老杨的文章：
http://yangtingkun.itpub.net/post/468/11363
--==========================================================
SQL> select (0*256+64)/64 from dual;
(0*256+64)/64
-------------
1
SQL> select 134*256+162 from dual;
134*256+162
-----------
34466
SQL> alter system dump datafile 1 block 34466;
System altered.
SQL>
--=======================================================
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x001b.000.000000bf 0x02400032.00ff.1e --U- 3 fsc 0x0000.0021ff12

Leaf block dump
===============
header address 125706844=0x77e225c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7979=0x1f2b
kdxcoavs 7937
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8017] flag: K-----, lock: 2, len=19
col 0; len 2; (2): c1 02
tl: 14 fb: --H-F--- lb: 0x0 cc: 2
nrid: 0x01003d58.0
col 0: [ 1] 61
col 1: [ 2] c1 15
row#1[7998] flag: K-----, lock: 2, len=19
col 0; len 2; (2): c1 03
tl: 14 fb: --H-F--- lb: 0x0 cc: 2
nrid: 0x01003d58.1
col 0: [ 1] 62
col 1: [ 2] c1 1f
row#2[7979] flag: K-----, lock: 2, len=19
col 0; len 2; (2): c1 04
tl: 14 fb: --H-F--- lb: 0x0 cc: 2
nrid: 0x01003d58.2
col 0: [ 1] 63
col 1: [ 2] c1 29
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 34466 maxblk 34466
--=======================================================
从上面的dump信息已经清楚的看出name1和name2字段放在了overflow区，溢出block的地址是0x01003d58，下面的dump信息也验证了这一点。为什么从name1字段开始overflow呢，因为block大小是8k，pctthreshold指定的是10%，也就是从哪个字段开始超过8k*10就从这个字段开始(包含这个字段)放在overflow指定的溢出表空间了。
--=======================================================
SQL> select to_number('01003d58','xxxxxxxx') from dual;
TO_NUMBER('01003D58','XXXXXXXX')
--------------------------------
16792920
SQL> select dbms_utility.data_block_address_file(16792920) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16792920)
----------------------------------------------
4
SQL> select dbms_utility.data_block_address_block(16792920) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16792920)
-----------------------------------------------
15704
SQL>
SQL> alter system dump datafile 4 block 15704;
System altered.
SQL>
--=========================================================
Block header dump: 0x01003d58
Object id on Block? Y
seg/obj: 0x3053 csc: 0x00.21ff07 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1003d51 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001b.000.000000bf 0x02400032.00ff.1d --U- 3 fsc 0x0000.0021ff12
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x77e2264
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x077e2264
bdba: 0x01003d58
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x810
avsp=0x7f8
tosp=0x7f8
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x17c0
0x14:pri[1] offs=0xfe8
0x16:pri[2] offs=0x810
block_row_dump:
tab 0, row 0, @0x17c0
tl: 2008 fb: -----L-- lb: 0x1 cc: 2
col 0: [2000]
61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...此处省略n多20(20是空格的ascii码)
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1: [ 1] 61
tab 0, row 1, @0xfe8
tl: 2008 fb: -----L-- lb: 0x1 cc: 2
col 0: [2000]
62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...此处省略n多20(20是空格的ascii码)
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1: [ 1] 62
tab 0, row 2, @0x810
tl: 2008 fb: -----L-- lb: 0x1 cc: 2
col 0: [2000]
63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...此处省略n多20(20是空格的ascii码)
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1: [ 1] 63
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 15704 maxblk 15704
--===============================================================================
验证一下有including子句的情况:
SQL> drop table t_iot;
Table dropped.
SQL> create table t_iot(id int primary key,name varchar2(10) ,age int,name1 char(2000),name2 varchar2(10)) organization index pctthreshold 10 includin
g name overflow tablespace users;
Table created.
SQL> insert into t_iot values(1,'a',20,'a','a');
1 row created.
SQL> insert into t_iot values(2,'b',30,'b','b');
1 row created.
SQL> insert into t_iot values(3,'c',40,'c','c');
1 row created.
SQL> commit;
Commit complete.
SQL> col rowid_dump format a40
SQL> col rowid_dump format a50
SQL> col rowid format a20
SQL> select id,name,age,substr(name1,1,2) name1,name2,rowid,dump(rowid) rowid_dump from t_iot;
ID NAME AGE NAME NAME2 ROWID ROWID_DUMP
---------- ---------- ---------- ---- ---------- -------------------- --------------------------------------------------
1 a 20 a a *BABAhqICwQL+ Typ=208 Len=10: 2,4,0,64,134,162,2,193,2,254
2 b 30 b b *BABAhqICwQP+ Typ=208 Len=10: 2,4,0,64,134,162,2,193,3,254
3 c 40 c c *BABAhqICwQT+ Typ=208 Len=10: 2,4,0,64,134,162,2,193,4,254
SQL> select (0*256+64)/64 from dual;
(0*256+64)/64
-------------
1
SQL> select 134*256+162 from dual;
134*256+162
-----------
34466
SQL> alter system dump datafile 1 block 34466;
System altered.
SQL>
--=======================================================================
Block header dump: 0x004086a2
Object id on Block? Y
seg/obj: 0x3057 csc: 0x00.2205e9 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x001b.024.000000c0 0x02400037.00ff.0c --U- 3 fsc 0x0000.00220606

Leaf block dump
===============
header address 125706844=0x77e225c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7988=0x1f34
kdxcoavs 7946
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8020] flag: K-----, lock: 2, len=16
col 0; len 2; (2): c1 02
tl: 11 fb: --H-F--- lb: 0x0 cc: 1
nrid: 0x01003d58.0
col 0: [ 1] 61
row#1[8004] flag: K-----, lock: 2, len=16
col 0; len 2; (2): c1 03
tl: 11 fb: --H-F--- lb: 0x0 cc: 1
nrid: 0x01003d58.1
col 0: [ 1] 62
row#2[7988] flag: K-----, lock: 2, len=16
col 0; len 2; (2): c1 04
tl: 11 fb: --H-F--- lb: 0x0 cc: 1
nrid: 0x01003d58.2
col 0: [ 1] 63
----- end of leaf block dump -----
--=======================================================================
从上面的dump信息可以清楚的看出name字段连同主键字段id被存放在一起，name字段之后的age连同name1和name2一起放在了溢出区，下面验证一下：
--=======================================================================
Block header dump: 0x01003d58
Object id on Block? Y
seg/obj: 0x3056 csc: 0x00.220604 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1003d51 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001b.024.000000c0 0x02400037.00ff.0b --U- 3 fsc 0x0000.00220606
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x77e2264
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x077e2264
bdba: 0x01003d58
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x807
avsp=0x7ef
tosp=0x7ef
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x17bd
0x14:pri[1] offs=0xfe2
0x16:pri[2] offs=0x807
block_row_dump:
tab 0, row 0, @0x17bd
tl: 2011 fb: -----L-- lb: 0x1 cc: 3
col 0: [ 2] c1 15
col 1: [2000]
61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......................省略20若干个
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 2: [ 1] 61
tab 0, row 1, @0xfe2
tl: 2011 fb: -----L-- lb: 0x1 cc: 3
col 0: [ 2] c1 1f
col 1: [2000]
62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
........................省略20若干个
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 2: [ 1] 62
tab 0, row 2, @0x807
tl: 2011 fb: -----L-- lb: 0x1 cc: 3
col 0: [ 2] c1 29
col 1: [2000]
63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
........................省略20若干个
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 2: [ 1] 63
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 15704 maxblk 15704
--==========================================
col 0: [ 2] c1 15
col 1: [2000]
这里col 1: [2000]就是name1字段，我们当时定义的是char(2000),它的前面col 0: [ 2] c1 15就是age字段的存储，可以坚定验证一下：
SQL> col dump_age format a30
SQL> select age,dump(age,16) dump_age from t_iot;
AGE DUMP_AGE
---------- ------------------------------
20 Typ=2 Len=2: c1,15
30 Typ=2 Len=2: c1,1f
40 Typ=2 Len=2: c1,29
SQL>
很显然c1,15就是age字段对应的20,至此有关doc描述的iot的2个溢出属性pctthreshold和including已经很清楚了。之所以create语法中叫
including是指主键字段中要包含including后面的字段。including子句中包含的字段之后的字段全部放在溢出区里了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1058480/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1058480/