[20120324]IOT索引组织表相关信息的学习(二).txt
上次链接: 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; 来消除物理读失败.比重建索引效率也许要高一些.
)
