[20121028]IOT字段定义顺序的问题.txt
如果在IOT中定义的主键不在第1字段，实际的存贮会出现什么情况呢？看了一些blog：
http://richardfoote.wordpress.com/2012/01/18/index-organized-tables-overflow-segment-part-ii-the-loneliest-guy/
自己做一些测试，加强记忆与理解：
1.测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table t_iot ( b varchar2(10),vc varchar2(1200),a varchar2(10), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX;
--我定义主键（字段a）不在第1的位置。
insert into t_iot values ('a','a','1' );
insert into t_iot values ('b','a','22' );
insert into t_iot values ('c','a','333' );
insert into t_iot values ('d','a','4444' );
insert into t_iot values ('e','a','55555' );
insert into t_iot values ('f','a','666666' );
insert into t_iot values ('g','a','7777777' );
insert into t_iot values ('h','a','88888888' );
commit ;
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');
2.这样会出现什么情况呢？
SQL> select object_id,data_object_id from dba_objects where object_name='T_IOT_PK';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
117540 117540
SQL> select * from sys.col$ where obj#=117540 ;
no rows selected
SQL> select object_id,data_object_id from dba_objects where object_name='T_IOT';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
117539
SQL> select obj#,col#,segcol#,name,intcol# from sys.col$ where obj#=117539;
OBJ# COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------- -------------------- ----------
117539 1 2 B 1
117539 2 3 VC 2
117539 3 1 A 3
--可以发现按照segcol#从小到大排列顺序是A->B->C.
3.转储看看:
SQL> select header_file,header_block from dba_segments where segment_name='T_IOT_PK';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 162
SQL> alter system dump datafile 4 block 163;
System altered.
Block header dump: 0x010000a3
Object id on Block? Y
seg/obj: 0x1cb24 csc: 0x00.b374a641 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0008.008.0000243e 0x00c0546f.11e6.1b --U- 8 fsc 0x0000.b374a652
Leaf block dump
===============
header address 182924685412=0x2a97293064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 8
kdxcofbo 52=0x34
kdxcofeo 7916=0x1eec
kdxcoavs 7864
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8021] flag: K-----, lock: 2, len=11
col 0; len 1; (1): 31
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 61
col 1: [ 1] 61
row#1[8009] flag: K-----, lock: 2, len=12
col 0; len 2; (2): 32 32
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 62
col 1: [ 1] 61
row#2[7996] flag: K-----, lock: 2, len=13
col 0; len 3; (3): 33 33 33
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 63
col 1: [ 1] 61
row#3[7982] flag: K-----, lock: 2, len=14
col 0; len 4; (4): 34 34 34 34
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 64
col 1: [ 1] 61
row#4[7967] flag: K-----, lock: 2, len=15
col 0; len 5; (5): 35 35 35 35 35
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 65
col 1: [ 1] 61
row#5[7951] flag: K-----, lock: 2, len=16
col 0; len 6; (6): 36 36 36 36 36 36
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 66
col 1: [ 1] 61
row#6[7934] flag: K-----, lock: 2, len=17
col 0; len 7; (7): 37 37 37 37 37 37 37
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 67
col 1: [ 1] 61
row#7[7916] flag: K-----, lock: 2, len=18
col 0; len 8; (8): 38 38 38 38 38 38 38 38
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 68
col 1: [ 1] 61
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 163 maxblk 163
--可以发现字段第1字段是A,后面才是B,VC.与定义排列顺序不一样.
--从各种情况看,IOT最好主键定义在最前面.