[20120324]IOT索引組織表相關資訊的學習(二).txt

lfree發表於2012-03-26
索引

上次連結:
http://space.itpub.net/?uid-267265-action-viewspace-itemid-717272

IOT 是一種特殊的索引結構，使用它能夠解決特定場合的應用問題，但是在許多應用中很少使用，更多的是使用堆表。
我僅僅在生產系統中對3個表使用，我偏向使用靜態以及"只讀"的小表。

如果IOT表存在除主鍵外的第2索引，如果使用它存在物理猜"physical guess",我以前的理解一直是第2索引記錄了主鍵資訊，透過這個資訊就可以定位IOT表中對應的資料，一直沒有很好的測試與理解。我最近也看了兩個連結,介紹了IOT的內容：




這次對上次的學習做一些補充:

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; 來消除物理讀失敗.比重建索引效率也許要高一些.











來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-719517/，如需轉載，請註明出處，否則將追究法律責任。

相關文章