[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應該不變。
--以此作為前面的補充。

[20151008]索引組織表上建立BITMAP索引.txt

相關文章