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子句中包含的欄位之後的欄位全部放在溢位區裡了。