分享程序员开发的那些事...
更新时间：2022-09-11 15:44:58
[20151008]索引组织表上创建BITMAP索引.txt
--IOT 是一种特殊的索引结构，使用它能够解决特定场合的应用问题，但是在许多应用中很少使用，更多的是使用堆表。
--我偏向使用静态以及"只读"的小表。
--以前写过一个系列,一直没有很好理解在IOT表上建立位图索引,最近再研究看看;
--我当时不理解为什么mapping table与iot表对应的块为什么不一致?
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
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;
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 ;
exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');
2.建立位图索引:
SCOTT@test> 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 .
SCOTT@test> alter table t_iot move mapping table;
SCOTT@test> SELECT object_name, object_id, data_object_id, object_type, status FROM dba_objects WHERE owner = USER AND object_name IN ('T_IOT', 'T_IOT_PK');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- ---------- -------------- ------------------- -------
T_IOT 316905 TABLE VALID
T_IOT_PK 316906 316909 INDEX VALID
SCOTT@test> SELECT object_name, object_id, data_object_id, object_type, status FROM dba_objects WHERE owner = USER AND object_name like '%IOT_MAP%';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- ---------- -------------- ------------------- -------
SYS_IOT_MAP_316905 316908 316908 TABLE VALID
--可以知道mapping table的命名是SYS_IOT_MAP_<object_id>,OBJECT_TYPE=TABLE;
SCOTT@test> @desc SYS_IOT_MAP_316905
Name Null? Type
---------- -------- ----------------------------
SYS_NC_01 ROWID
--可以发现map table 仅仅有1个字段，类型是rowid。
SCOTT@test> column x format a66
SCOTT@test> column y format a66
SCOTT@test> select dump(rowid,16) y ,dump(sys_nc_01,16) x from SYS_IOT_MAP_316905;
Y X
------------------------------------------------------------------ ------------------------------------------------------------------
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,0 Typ=208 Len=9: 2,4,0,0,0,0,1,31,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,1 Typ=208 Len=10: 2,4,1,0,2,44,2,32,32,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,2 Typ=208 Len=11: 2,4,1,0,2,44,3,33,33,33,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,3 Typ=208 Len=12: 2,4,1,0,2,44,4,34,34,34,34,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,4 Typ=208 Len=13: 2,4,1,0,2,44,5,35,35,35,35,35,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,5 Typ=208 Len=14: 2,4,1,0,2,44,6,36,36,36,36,36,36,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,6 Typ=208 Len=15: 2,4,1,0,2,44,7,37,37,37,37,37,37,37,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,7 Typ=208 Len=16: 2,4,1,0,2,44,8,38,38,38,38,38,38,38,38,fe
Typ=69 Len=10: 0,4,d5,ec,1,0,2,33,0,8 Typ=208 Len=17: 2,4,1,0,2,44,9,39,39,39,39,39,39,39,39,39,fe
9 rows selected.
SCOTT@test> select dump(rowid,16) x,a from t_iot;
X A
------------------------------------------------------------------ ----------
Typ=208 Len=9: 2,4,1,0,2,43,1,31,fe 1
Typ=208 Len=10: 2,4,1,0,2,43,2,32,32,fe 22
Typ=208 Len=11: 2,4,1,0,2,43,3,33,33,33,fe 333
Typ=208 Len=12: 2,4,1,0,2,43,4,34,34,34,34,fe 4444
Typ=208 Len=13: 2,4,1,0,2,43,5,35,35,35,35,35,fe 55555
Typ=208 Len=14: 2,4,1,0,2,43,6,36,36,36,36,36,36,fe 666666
Typ=208 Len=15: 2,4,1,0,2,43,7,37,37,37,37,37,37,37,fe 7777777
Typ=208 Len=16: 2,4,1,0,2,43,8,38,38,38,38,38,38,38,38,fe 88888888
Typ=208 Len=17: 2,4,1,0,2,43,9,39,39,39,39,39,39,39,39,39,fe 999999999
9 rows selected.
--IOT的rowid格式在以前的文章有介绍，参考http://blog.itpub.net/267265/viewspace-719517/
--IOT的rowid是逻辑rowid,其存贮格式如下:
--结合dump（rowid）,dump(rowid,16)的转出信息.很容易推导出rowid的信息。以下的例子来说明:
Typ=208 Len=17: 2,4,1,0,2,43,9,39,39,39,39,39,39,39,39,39,fe 999999999
1.开头的2,4，以及结尾fe实在不好猜测，不过好像是固定不变的。
2.中间的1,0,2,43正好对应的就是IOT的根节点，也就是对应数据IOT的数据块。(因为我的数据量很小)
3.9,39,39,39,39,39,39,39,39,39中9表示主键长度，39,39,39,39,39,39,39,39,39就是主键的信息。
--我当时感到奇怪的是map table记录的sys_nc_01信息，第一条是0,0,0,0，剩下的都是1,0,2,44。与当前IOT表看到的逻辑rowid完全不一致。
--当然随着数据的插入，表IOT的记录的逻辑rowid会变化，map table的对应信息应该不会改变(除非主键发生了变化).
SCOTT@test> select sys_nc_01 c30 from SYS_IOT_MAP_316905;
C30
------------------------------
*BAAAAAABMf4
*BAEAAkQCMjL+
*BAEAAkQDMzMz/g
*BAEAAkQENDQ0NP4
*BAEAAkQFNTU1NTX+
*BAEAAkQGNjY2NjY2/g
*BAEAAkQHNzc3Nzc3N/4
*BAEAAkQIODg4ODg4ODj+
*BAEAAkQJOTk5OTk5OTk5/g
9 rows selected.
--实际上映射的信息主要主键对上就ok了，做1个简单的查询例子：
SCOTT@test> select sys_nc_01 c30,'*BAAAABABMf4' c20 from SYS_IOT_MAP_316905 where sys_nc_01='*BAAAABABMf4';
C30 C20
------------------------------ --------------------
*BAAAAAABMf4 *BAAAABABMf4
SCOTT@test> select sys_nc_01 c30,'*BAAFFFABMf4' c20 from SYS_IOT_MAP_316905 where sys_nc_01='*BAAFFFABMf4';
C30 C20
------------------------------ --------------------
*BAAAAAABMf4 *BAAFFFABMf4
--注意从字串看两者是不等的。实际上这种逻辑rowid比较仅仅关心主键部分是否一致。当然开头的2,4，以及结尾fe应该不变。
--以此作为前面的补充。