上次链接:
http://space.itpub.net/?uid-267265-action-viewspace-itemid-717272
IOT 是一种特殊的索引结构，使用它能够解决特定场合的应用问题，但是在许多应用中很少使用，更多的是使用堆表。
我仅仅在生产系统中对3个表使用，我偏向使用静态以及"只读"的小表。
如果IOT表存在除主键外的第2索引，如果使用它存在物理猜"physical guess",我以前的理解一直是第2索引记录了主键信息，通过这个信息就可以定位IOT表中对应的数据，一直没有很好的测试与理解。我最近也看了两个链接,介绍了IOT的内容：
http://richardfoote.wordpress.com
http://mwidlake.wordpress.com
这次对上次的学习做一些补充:
1.测试环境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200),d varchar2(1), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX;
SQL> create index i_t_iot_b on t_iot(b);
insert into t_iot values ('1' ,'a','a','1');
insert into t_iot values ('22' ,'b','a','1');
insert into t_iot values ('333' ,'c','a','1');
insert into t_iot values ('4444' ,'d','a','1');
insert into t_iot values ('55555' ,'e','a','1');
insert into t_iot values ('666666' ,'f','a','0');
insert into t_iot values ('7777777' ,'g','a','0');
insert into t_iot values ('88888888' ,'h','a','0');
insert into t_iot values ('999999999','i','a','0');
commit ;
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');
2.建立位图索引:
SQL> create bitmap index i_t_iot_d on t_iot(d);
create bitmap index i_t_iot_d on t_iot(d)
*
ERROR at line 1:
ORA-28669: bitmap index can not be created on an IOT with no mapping table
--可以发现要在IOT表上建立位图索引必须要建立mapping table .
SQL> alter table t_iot move mapping table;
SQL> SELECT index_name, table_name, blevel, leaf_blocks, status FROM dba_indexes WHERE table_name = 'T_IOT';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ----------- --------
T_IOT_PK T_IOT 0 1 VALID
I_T_IOT_B T_IOT 0 1 VALID
--可以发现索引依旧有效,不需要重建.
SQl> SELECT object_name, object_id, data_object_id, object_type, status FROM dba_objects
WHERE wner = USER AND object_name IN ('T_IOT', 'T_IOT_PK');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- ---------- -------------- ------------------- -------
T_IOT 87282 TABLE VALID
T_IOT_PK 87283 87286 INDEX VALID
T_IOT的object_id=87282,因为我执行了一次alter table t_iot move mapping table; T_IOT_PK的object_id与data_object_id不等.
SQL> SELECT object_name, object_id, data_object_id, object_type, status
FROM dba_objects WHERE wner = USER AND object_name like '%IOT_MAP%';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- ---------- -------------- ------------------- -------
SYS_IOT_MAP_87282 87285 87285 TABLE VALID
--可以知道mapping table的命名是SYS_IOT_MAP_,OBJECT_TYPE=TABLE;
SQL> desc SYS_IOT_MAP_87282
Name Null? Type
---------- -------- -------
SYS_NC_01 ROWID
SQL> column x format a66
SQL> column y format a66
SQL> select dump(rowid,16) y ,dump(sys_nc_01,16) x from SYS_IOT_MAP_87282;
Y X
------------------------------------------------------------------ ------------------------------------------------------------------
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,0 Typ=208 Len=9: 2,4,0,0,0,0,1,31,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,1 Typ=208 Len=10: 2,4,1,0,2,24,2,32,32,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,2 Typ=208 Len=11: 2,4,1,0,2,24,3,33,33,33,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,3 Typ=208 Len=12: 2,4,1,0,2,24,4,34,34,34,34,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,4 Typ=208 Len=13: 2,4,1,0,2,24,5,35,35,35,35,35,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,5 Typ=208 Len=14: 2,4,1,0,2,24,6,36,36,36,36,36,36,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,6 Typ=208 Len=15: 2,4,1,0,2,24,7,37,37,37,37,37,37,37,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,7 Typ=208 Len=16: 2,4,1,0,2,24,8,38,38,38,38,38,38,38,38,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,8 Typ=208 Len=17: 2,4,1,0,2,24,9,39,39,39,39,39,39,39,39,39,fe
9 rows selected.
9 rows selected.
SQL> select dump(rowid,16) x,a from t_iot;
X A
------------------------------------------------------------------ ----------
Typ=208 Len=9: 2,4,1,0,2,23,1,31,fe 1
Typ=208 Len=10: 2,4,1,0,2,23,2,32,32,fe 22
Typ=208 Len=11: 2,4,1,0,2,23,3,33,33,33,fe 333
Typ=208 Len=12: 2,4,1,0,2,23,4,34,34,34,34,fe 4444
Typ=208 Len=13: 2,4,1,0,2,23,5,35,35,35,35,35,fe 55555
Typ=208 Len=14: 2,4,1,0,2,23,6,36,36,36,36,36,36,fe 666666
Typ=208 Len=15: 2,4,1,0,2,23,7,37,37,37,37,37,37,37,fe 7777777
Typ=208 Len=16: 2,4,1,0,2,23,8,38,38,38,38,38,38,38,38,fe 88888888
Typ=208 Len=17: 2,4,1,0,2,23,9,39,39,39,39,39,39,39,39,39,fe 999999999
9 rows selected.
--前面我已经讲过,IOT的rowid是逻辑rowid,其存贮格式如下:
--结合dump（rowid）,dump(rowid,16)的转出信息.很容易推导出rowid的信息。以下的例子来说明:
Typ=208 Len=17: 2,4,1,0,2,23,9,39,39,39,39,39,39,39,39,39,fe 999999999
1.开头的2,4，以及结尾fe实在不好猜测，不过好像是固定不变的。
2.中间的1,0,2,23正好对应的就是IOT的根节点，也就是对应数据IOT的数据块。
3.9,39,39,39,39,39,39,39,39,39中9表示主键长度，39,39,39,39,39,39,39,39,39就是主键的信息。
--不过感到奇怪的是,在没有移动iot表里面数据的时候,SYS_IOT_MAP_87282上记录的urowid(sys_nc_01)与t_iot当时的rowid并不是对应的.
--仅仅对应的键值一样.不知道为什么这样?
--而且a='1'对应的SYS_IOT_MAP_87282的sys_nc_01的对应的数据块竟然是0,0,0,0.
SQL> select header_file,header_block from dba_segments where wner=USER and segment_name='SYS_IOT_MAP_87282';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 538
3.插入一行看看:
SQL> insert into t_iot values ('0000000000','i','j','0');
1 row created.
SQL> commit ;
Commit complete.
SQL> select dump(rowid,16) y ,dump(sys_nc_01,16) x from SYS_IOT_MAP_87282;
Y X
------------------------------------------------------------------ ------------------------------------------------------------------
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,0 Typ=208 Len=9: 2,4,0,0,0,0,1,31,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,1 Typ=208 Len=10: 2,4,1,0,2,24,2,32,32,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,2 Typ=208 Len=11: 2,4,1,0,2,24,3,33,33,33,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,3 Typ=208 Len=12: 2,4,1,0,2,24,4,34,34,34,34,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,4 Typ=208 Len=13: 2,4,1,0,2,24,5,35,35,35,35,35,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,5 Typ=208 Len=14: 2,4,1,0,2,24,6,36,36,36,36,36,36,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,6 Typ=208 Len=15: 2,4,1,0,2,24,7,37,37,37,37,37,37,37,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,7 Typ=208 Len=16: 2,4,1,0,2,24,8,38,38,38,38,38,38,38,38,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,8 Typ=208 Len=17: 2,4,1,0,2,24,9,39,39,39,39,39,39,39,39,39,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,9 Typ=208 Len=18: 2,4,0,0,0,0,a,30,30,30,30,30,30,30,30,30,30,fe
10 rows selected.
SQL> select dump(rowid,16) x,a from t_iot;
X A
------------------------------------------------------------------ ----------
Typ=208 Len=18: 2,4,1,0,2,23,a,30,30,30,30,30,30,30,30,30,30,fe 0000000000
Typ=208 Len=9: 2,4,1,0,2,23,1,31,fe 1
Typ=208 Len=10: 2,4,1,0,2,23,2,32,32,fe 22
Typ=208 Len=11: 2,4,1,0,2,23,3,33,33,33,fe 333
Typ=208 Len=12: 2,4,1,0,2,23,4,34,34,34,34,fe 4444
Typ=208 Len=13: 2,4,1,0,2,23,5,35,35,35,35,35,fe 55555
Typ=208 Len=14: 2,4,1,0,2,23,6,36,36,36,36,36,36,fe 666666
Typ=208 Len=15: 2,4,1,0,2,23,7,37,37,37,37,37,37,37,fe 7777777
Typ=208 Len=16: 2,4,1,0,2,23,8,38,38,38,38,38,38,38,38,fe 88888888
Typ=208 Len=17: 2,4,1,0,2,23,9,39,39,39,39,39,39,39,39,39,fe 999999999
10 rows selected.
--可以发现插入后SYS_IOT_MAP_87282也添加一行,其对应的sys_nc_01内容如下:2,4,0,0,0,0,a,30,30,30,30,30,30,30,30,30,30,fe.数据块对应的也是0.
--这不知道mapping table与iot表对应的块为什么不一致?
4.转储T_IOT_PK
SQL> select header_file,header_block from dba_segments where segment_name='T_IOT_PK';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 546
SQL> select object_id,data_object_id from dba_objects where object_name='T_IOT_PK';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
87283 87286
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 87283';
----- begin tree dump
leaf: 0x1000223 16777763 (0: nrow: 10 rrow: 10)
----- end tree dump
--仅仅占用1个块。HEADER_BLOCK=546,根节点=547.而且IOT root节点块地址=0x1000223.不知道mapping table记录的逻辑rowid不一致,不过键值是一样的.
5.再次建立索引看看.
SQL> create bitmap index i_t_iot_d on t_iot(d);
Index created.
--OK 建立成功.
SQL> select header_file,header_block from dba_segments where segment_name='I_T_IOT_D';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 522
SQL> select object_id,data_object_id from dba_objects where object_name='I_T_IOT_D';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
87490 87490
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 87490';
*** 2012-03-26 09:28:26.747
----- begin tree dump
leaf: 0x100020b 16777739 (0: nrow: 2 rrow: 2)
----- end tree dump
SQL> alter system dump datafile 4 block 523 ;
Block header dump: 0x0100020b
Object id on Block? Y
seg/obj: 0x155c2 csc: 0x00.7acbe4 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000208 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 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.007acbe4
Leaf block dump
===============
header address 182924354148=0x2a97242264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 7989=0x1f35
kdxcoavs 7949
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8010] flag: ------, lock: 0, len=22
col 0; len 1; (1): 30
col 1; len 6; (6): 01 00 02 1b 00 00
col 2; len 6; (6): 01 00 02 1b 00 0f
col 3; len 3; (3): c9 e0 03
row#1[7989] flag: ------, lock: 0, len=21
col 0; len 1; (1): 31
col 1; len 6; (6): 01 00 02 1b 00 00
col 2; len 6; (6): 01 00 02 1b 00 07
col 3; len 2; (2): c8 1f
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 523 maxblk 523
--可以发现第2位图索引中记录的start_rowid,end_rowid与SYS_IOT_MAP_87282表中的相对应.
--可以看出:Mapping table的每一行存储了对应的IOT表中记录的logical rowid(仅仅是主键一致). 因此这个mapping table就维护了IOT 表logical rowid
--和 mapping table的每一行的physical rowid 的mapping 的关系。为什么要这样做呢？因为bitmap索引条目中保存的rowid要用physical rowid, 而IOT是
--无法提供稳定的physical rowid的，因此就借助于mapping table的physical rowid。 通过bitmap索引来访问表中的数据的执行计划大概就是首先根据
--bitmap index中的physical rowid访问mapping table, 然后通过mapping table中的logical rowid再来访问IOT表。
6.再次修改记录:
SQL> update t_iot set vc=lpad('a',1000,'a') ;
10 rows updated.
SQL> commit ;
Commit complete.
--vc长度加大到1000,原来的IOT数据块已经无法存在这些信息，发现IOT表发生了1次索引分裂,生成两个叶子节点.
SQL> select dump(rowid,16) y ,dump(sys_nc_01,16) x from SYS_IOT_MAP_87282;
Y X
------------------------------------------------------------------ ------------------------------------------------------------------
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,0 Typ=208 Len=9: 2,4,0,0,0,0,1,31,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,1 Typ=208 Len=10: 2,4,1,0,2,24,2,32,32,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,2 Typ=208 Len=11: 2,4,1,0,2,24,3,33,33,33,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,3 Typ=208 Len=12: 2,4,1,0,2,24,4,34,34,34,34,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,4 Typ=208 Len=13: 2,4,1,0,2,24,5,35,35,35,35,35,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,5 Typ=208 Len=14: 2,4,1,0,2,24,6,36,36,36,36,36,36,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,6 Typ=208 Len=15: 2,4,1,0,2,24,7,37,37,37,37,37,37,37,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,7 Typ=208 Len=16: 2,4,1,0,2,24,8,38,38,38,38,38,38,38,38,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,8 Typ=208 Len=17: 2,4,1,0,2,24,9,39,39,39,39,39,39,39,39,39,fe
Typ=69 Len=10: 0,1,54,f5,1,0,2,1b,0,9 Typ=208 Len=18: 2,4,0,0,0,0,a,30,30,30,30,30,30,30,30,30,30,fe
10 rows selected.
SQL> select dump(rowid,16) x,a from t_iot;
X A
------------------------------------------------------------------ ----------
Typ=208 Len=18: 2,4,1,0,2,25,a,30,30,30,30,30,30,30,30,30,30,fe 0000000000
Typ=208 Len=9: 2,4,1,0,2,25,1,31,fe 1
Typ=208 Len=10: 2,4,1,0,2,25,2,32,32,fe 22
Typ=208 Len=11: 2,4,1,0,2,25,3,33,33,33,fe 333
Typ=208 Len=12: 2,4,1,0,2,25,4,34,34,34,34,fe 4444
Typ=208 Len=13: 2,4,1,0,2,25,5,35,35,35,35,35,fe 55555
Typ=208 Len=14: 2,4,1,0,2,26,6,36,36,36,36,36,36,fe 666666
Typ=208 Len=15: 2,4,1,0,2,26,7,37,37,37,37,37,37,37,fe 7777777
Typ=208 Len=16: 2,4,1,0,2,26,8,38,38,38,38,38,38,38,38,fe 88888888
Typ=208 Len=17: 2,4,1,0,2,26,9,39,39,39,39,39,39,39,39,39,fe 999999999
10 rows selected.
--可以发现t_iot表的逻辑rowid发生了变化.这样通过主键查询表中内容的物理猜会失败,要通过主键重新定位信息.即逻辑读会增加.
--上次已经讲过可以通过rebuild来重建索引来消除这种情况.如何确定哪些IOT的第2索引需要重建呢?可以通过视图user_indexes字段
--pct_direct_access来确定.
SQL> select index_name, index_type, pct_direct_access from user_indexes where index_name='I_T_IOT_B';
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
------------------------------ --------------------------- -----------------
I_T_IOT_B NORMAL 0
--可以发现PCT_DIRECT_ACCESS已经变成了0.实际上有两者方法消除这个问题.
1.重建第2索引.
2.修改索引块的相关信息.使索引中对应的块信息正确.而且这样工作量小一些.
SQL> ALTER INDEX I_T_IOT_B UPDATE BLOCK REFERENCES;
SQL> select index_name, index_type, pct_direct_access from user_indexes where index_name = 'I_T_IOT_B';
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
------------------------------ --------------------------- -----------------
I_T_IOT_B NORMAL 0
--没有变化!分析IOT表看看.
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');
PL/SQL procedure successfully completed.
SQL> select index_name, index_type, pct_direct_access from user_indexes where index_name = 'I_T_IOT_B';
INDEX_NAME INDEX_TYPE PCT_DIRECT_ACCESS
------------------------------ --------------------------- -----------------
I_T_IOT_B NORMAL 100
--可以发现现在PCT_DIRECT_ACCESS=100.
7.取消mapping table:
SQL> alter table t_iot move nomapping;
alter table t_iot move nomapping
*
ERROR at line 1:
ORA-28670: mapping table cannot be dropped due to an existing bitmap index
--要先删除位图索引i_t_iot_d.
SQL> drop index i_t_iot_d;
Index dropped.
SQL> alter table t_iot move nomapping;
Table altered.
SQL> SELECT object_name, object_id, data_object_id, object_type, status
FROM dba_objects WHERE wner = USER AND object_name like '%IOT_MAP%';
no rows selected
总结:
1.mapping table对应逻辑rowid为什么与T_iot的不同,但是毕竟是通过里面的键值来对应.很多东西还是未知???
2.可以通过视图user_indexes的PCT_DIRECT_ACCESS字段来确定是否要重建第2索引以及修改索引信息的方式来消除物理读失败.
3.可以通过执行ALTER INDEX UPDATE BLOCK REFERENCES; 来消除物理读失败.比重建索引效率也许要高一些.