[20151008]索引组织表上创建BITMAP索引.txt

[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应该不变。
--以此作为前面的补充。