ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > [20120324]IOT索引组织表相关信息的学习(二).txt

[20120324]IOT索引组织表相关信息的学习(二).txt

原创 Linux操作系统 作者：lfree 时间：2012-03-26 10:15:39 0 删除 编辑

上次链接:
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 <第2索引> UPDATE BLOCK REFERENCES; 来消除物理读失败.比重建索引效率也许要高一些.











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

0
0
上一篇： [20120323]linux下sort排序.txt
下一篇： [20120327]toad与sqlplus下执行sql语句的一个细节.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969544

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593