ORACLE索引組織表討論

本文只代表作者觀點，如有錯誤請指正
關於索引組織表本文主要討論以下幾個方面
1、什麼是索引組織表
2、索引組織表的關鍵特性
3、如果建立一個合適的索引組織表
4、什麼事邏輯ROWID以及物理猜（Physical Guesses）
5、從內部結構進行分析和證明這些觀點


一般的情況下索引是和表分離的SEGMENT，索引的行記錄的是索引鍵值和ROWID，而在索引組織表中就整個表就是一個索引，索引的頁節點記錄的並非
鍵值和ROWID而記錄是整個資料行，這裡和MYSQL INNODB的表非常相像，MYSQL的INNODB 中每一個表都是索引組織表。關於這一點在隨後的DUMP中會得
到體現。索引組織表中可以支援各種資料庫的特性，比如SECONED INDEX（除主鍵以外的索引），分割槽（不能建立子分割槽），但是不能是聚族表、不能包含
虛擬列，以及LONG COLUMN。
值得注意的是在SECONED INDEX中使用的是邏輯ROWID，而非一般的ROWID，在ORACLE文件中說明了這個邏輯ROWID
 Oracle Database uses row identifiers called logical rowids for index-organized tables. 
 A logical rowid is a base64-encoded representation of the table primary key. 
 The logical rowid length depends on the primary key length
也就是說這個邏輯ROWID是一個關於主鍵的base64-encoded的一個值，主鍵的長度決定了ROWID的長度。為什麼引入邏輯ROWID是由於，在堆表中（普通表）中
行的位置是固定，始終會有一個固定的ROWID，即使這行超過整個塊允許的長度也會透過行遷移的方式來保留原有的ROWID。而這一點到了索引中就不適用了
索引中的行是可變的，他可能由於某些原因進行了塊分裂，那麼使用ROWID來標示一行就不那麼有用了，這樣ORACLE引入了邏輯ROWID，既然行的位置不固定那麼
使用主鍵總是可以的。
   下面介紹一下物理猜想（Logical Rowids and Physical Guesses）
   一般來說使用邏輯ROWID讀取資料的準確性是很高的，不然ORACLE絕不可能使用邏輯ROWID作為SECONED INDEX訪問資料的方式，但是在有些情況下，也就是頻繁進行
INSERT的表，或者進行大量UPDATE欄位為一個很大的值的時候，索引塊進行分裂，這樣原來的邏輯ROWID就不準了，這個時候如果邏輯ROWID不準確的情況下，就會使用
主鍵進行查詢，利用主鍵查詢的方式一定慢於邏輯ROWID，可能這是由ORACLE的演算法決定的不然邏輯ROWID的存在就沒有理由了，另外一種更多的情況。


   介紹一下索引組織表的溢位段，如果索引組織表中存放的是表所有的資料，那麼這個索引結構分裂的可能性大大增加，這樣就造成了GUESSES MISS的機率大大增加
我感覺使用溢位段最主要的作用在於降低索引結構分裂造成邏輯ROWID不準的可能性，但是溢位的欄位一定是不常用的，或者說不經常查詢的，不然整個查詢會變得更慢。
所以溢位欄位的選擇是非常重要的。如果長期都是SELECT * FROM 那麼溢位段也就沒有用處了。
  討論了這些特性，我們來討論一下索引組織表的有點，首先顯而易見的是索引組織表是可以節約空間的，因為索引和表合二為一，還有就是根據主鍵進行唯一掃描或者
範圍掃描的時候由於索引的排列順序這些列是按索引排列好的，而且比一般索引少一次ROWID回表的操作，那麼速度會更快，第三如果根據資料特點比如一個會員ID，一個
會員卡號，顯然一個會員ID可以有多個銀行卡號，如果我們建立索引組織表元件為(會員ID和會員卡號），顯然如果在查詢的時候使用ID=** 那麼這種情況下，索引組織
表的優勢就出來了，首先他少一次ROWID回表操作，其次索引組織表的排列是有序的，那麼同樣會員ID的的卡號資訊一定儲存在臨近的塊中，這實際也是第二點的一個
列子。






下面首先給出建立索引組織表的部分語法：


create table   ottest1
 ( empno  number(10) ,
   ename  varchar2(10),
   city varchar2(20),
   state varchar2(2),
   zip number(10),
  primary key (empno)
  ) 
  ORGANIZATION INDEX  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
 TABLESPACE "USERS"
 PCTTHRESHOLD 50 INCLUDING ename 
 OVERFLOW
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
 TABLESPACE "USERS";
 
ORGANIZATION INDEX 為索引組織表的標示，說明他使用索引組織結構。
PCTFREE、PCTUSED 和一般的沒有什麼不同，唯一注意的是MSSM和ASSM下堆表的
PCTUSED的不同，因為ASSM下不存在PCTUSED的問題，因為使用點陣圖塊進行管理空塊的
而索引基本不存在PCTFREE、PCTUSED，使用pctfree只在初始化建立的時候有用。
INITRANS 1 MAXTRANS 255沒什麼說是初始的事物槽的多少和最大值。
重要就是我們說的
 PCTTHRESHOLD 50 INCLUDING "ename" ，這個含義就是剛才溢位段，一個是行佔塊的百分比一個是哪些列，我們寫ename就代表empno和ename就在
 索引組織表結構中
 OVERFLOW則是我們的溢位段，溢位段可以設定到其他表空間，他就像一個堆表。
 
下面我們將透過DUMP來看一下索引組織表內部和堆表到底有什麼區別：




首先建立2個表一個索引組織表一個普通表，為了觀察方便我們插入同樣的2條資料
create table   ottest1
 ( empno  number(10) ,
   ename  varchar2(10),
   city varchar2(20),
   state varchar2(2),
   zip number(10),
  primary key (empno)
  ) 
  ORGANIZATION INDEX  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
 TABLESPACE "USERS"
 PCTTHRESHOLD 50 INCLUDING ename 
 OVERFLOW
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
 TABLESPACE "USERS";
  




create table notest1
 ( empno  number(10) ,
   ename  varchar2(10),
   city varchar2(20),
   state varchar2(2),
   zip number(10),
  primary key (empno)
  ) ;
  
  
insert into ottest1 values(2,'gaopeng','chongqing','CH','400000');
insert into ottest1 values(3,'gaopeng','chongqing','CH','400000');
insert into notest1 values(2,'gaopeng','chongqing','CH','400000');
insert into notest1 values(3,'gaopeng','chongqing','CH','400000');


SQL> select owner,table_name,tablespace_name,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where lower(table_name) in ('notest1','ottest1');
OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ------------
PTEST                          NOTEST1                        USERS                                   2          5            0
PTEST                          OTTEST1                                                                2            


這裡可以看到DBA_TABLES中TABLESPACE_NAME為空，因為他的儲存資訊完全儲存在DBA_INDEXES中，而不再TBALES中顯示


SQL> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME,PCT_THRESHOLD,INCLUDE_COLUMN from dba_indexes where lower(table_name) in ('notest1','ottest1');
OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     PCT_THRESHOLD INCLUDE_COLUMN
------------------------------ ------------------------------ --------------------------- ------------------------------ ------------- --------------
PTEST                          SYS_IOT_TOP_79293              IOT - TOP                   OTTEST1                                   50              2
PTEST                          SYS_C0012161                   NORMAL                      NOTEST1                                      


可以看到索引組織表的索引名字為SYS_IOT_TOP_開頭為ORACLE自動命名


SQL> select OWNER,SEGMENT_SUBTYPE,HEADER_FILE,HEADER_BLOCK,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where segment_name in ('SYS_IOT_TOP_79293','NOTEST1');
OWNER                          SEGMENT_SUBTYPE HEADER_FILE HEADER_BLOCK SEGMENT_NAME                                                                     SEGMENT_TYPE
------------------------------ --------------- ----------- ------------ -------------------------------------------------------------------------------- ------------------
PTEST                          ASSM                     11          162 NOTEST1                                                                          TABLE
PTEST                          ASSM                     11          154 SYS_IOT_TOP_79293                                                                INDEX


同樣我們可以使用，如下方法來匯出索引的結構


SQL> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where object_name in ('SYS_IOT_TOP_79293','NOTEST1');
OBJECT_NAME                                                                       OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------------------------------------- ---------- -------------- -------------------
SYS_IOT_TOP_79293                                                                     79295          79295 INDEX
NOTEST1                                                                               79296          79296 TABLE




alter session set events 'immediate trace name treedump level 79295';


----- begin tree dump
leaf: 0x2c0009b 46137499 (0: nrow: 2 rrow: 2)
----- end tree dump


實際這裡這裡已經找到了頁塊為0x2c0009b，但是為了簡單描述一下ASSM的點陣圖塊我們還是從HEAD_BLOCK看起，
並且我們只有1個塊的資料，我們可以簡單找到第一個資料塊就能找到資料，因為IOT表是排序好的。堆表則不同
堆表塊的選擇基本是隨機的（根據PID的hash值判斷),所以這種方法不能找到資料在堆表中的塊，但是能夠簡單的
瞭解一下三級為圖塊。


SQL> oradebug tracefile_name
/ora11g/diag/rdbms/testdg1/test/trace/test_ora_5288.trc
SQL> alter system dump datafile 11 block 162;


System altered. 


SQL> oradebug tracefile_name
/ora11g/diag/rdbms/testdg1/test/trace/test_ora_5292.trc
SQL> alter system dump datafile 11 block 154;


System altered.


：
我們先找到
   Second Level Bitmap block DBAs 
   --------------------------------------------------------
   DBA 1:   0x02c00099
這個LEVEL 2的塊然
SQL> select to_number('02c00099','xxxxxxxxxxx') from dual;
TO_NUMBER('02C00099','XXXXXXXX
------------------------------
                      46137497


SQL> 
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                            11                            153
然後DUMP這個LEVEL 2的塊：
  L1 Ranges :
  --------------------------------------------------------
   0x02c00098  Free: 5 Inst: 1 
找到L1塊為02c00098


SQL> select dbms_utility.data_block_address_file(46137496),
  2     dbms_utility.data_block_address_block(46137496) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                            11                            152
再次DUMP L1塊                            


SQL> oradebug tracefile_name


SQL> alter system dump datafile 11 block 152;


System altered.


找到L1的如下資訊
      Highwater::  0x02c0009c  ext#: 0      blk#: 4      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 1     
  mapblk  0x00000000  offset: 0     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x02c00098  Length: 8      Offset: 0      
  
   0:Metadata   1:Metadata   2:Metadata   3:25-50% free
   4:unformatted   5:unformatted   6:unformatted   7:unformatted
  --------------------------------------------------------
End dump data blocks tsn: 4 file#: 11 minblk 152 maxblk 152


根據0:Metadata   1:Metadata   2:Metadata   3:25-50% free我們可以推斷
02c00098 02c00099 02c0009a 為metadata塊 02c0009b就為資料塊為25-50% free
這和我們
----- begin tree dump
leaf: 0x2c0009b 46137499 (0: nrow: 2 rrow: 2)
----- end tree dump
的資訊一致。


但是普通表不能透過這個方式獲得位置，因為普通表的插入順序是完全無序的，
這種情況我們透過ROWID進行獲取就行了。
最後獲得的是
普通表的塊的位置是 11,166
索引組織表的塊的位置是 11,155


索引組織表的行資訊，去掉塊頭：
row#0[8010] flag: K-----, lock: 2, len=22   \\Row flag set to K
col 0; len 2; (2):  c1 03                   \\主鍵值 
tl: 17 fb: --H-F--- lb: 0x0  cc: 1          \\ H代表是這行的開頭 F表示是開始但是沒有L 就代表這行的結尾不在這個塊中
nrid:  0x02c00096.0                         \\類似行遷移賦予一個指向其他塊的指標，.0是行的偏移量,隨後我們DUMP這個塊
col  0: [ 7]  67 61 6f 70 65 6e 67          \\第一個欄位的值
row#1[7988] flag: K-----, lock: 2, len=22
col 0; len 2; (2):  c1 04
tl: 17 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x02c00096.1
col  0: [ 7]  67 61 6f 70 65 6e 67


索引組織表的溢位段的資訊根據02c00096找到為


block_row_dump:
tab 0, row 0, @0x1f85               
tl: 19 fb: -----L-- lb: 0x1  cc: 3     \\這裡為L就代表這是行的結尾
col  0: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  1: [ 2]  43 48
col  2: [ 2]  c3 29
tab 0, row 1, @0x1f72
tl: 19 fb: -----L-- lb: 0x1  cc: 3
col  0: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  1: [ 2]  43 48
col  2: [ 2]  c3 29


可以看到這裡的組織和堆表並無不同。可以考慮為堆表結構，既然是行遷移那麼如果要訪問這個欄位那麼必然需要更多的I/O，
這也證明了溢位段為什麼如此重要，原因在於為了避免更多的二級索引物理猜失敗，而儘可能了保證索引結構的變化更小，
關於二級索引在IOT表塊分裂後邏輯ROWID失效的方面將在接下來討論。


我們還是比較一下普通表的行資訊作為對比，去掉塊頭：
tab 0, row 0, @0x1f7a
tl: 30 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 03
col  1: [ 7]  67 61 6f 70 65 6e 67
col  2: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  3: [ 2]  43 48
col  4: [ 2]  c3 29
tab 0, row 1, @0x1f5c
tl: 30 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 04
col  1: [ 7]  67 61 6f 70 65 6e 67
col  2: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  3: [ 2]  43 48
col  4: [ 2]  c3 29




然後我們來看一看所謂的邏輯ROWID的組成，
物理ROWID很簡單就是：
如果是AAAC5m AAJ AAABUC AAA
Object: 0x00002e66 = 11878 (AAAC5m) 
DBA: 0x02401502 -> 
Relative File 9 (AAJ), Block 5378 (AAABUC) 
Slot: 0x0000 = 0 (AAA)


32bit的object number，每個資料庫最多有4G個物件
10bit的file number，每個物件最多有1022個檔案（2個檔案預留）
22bit的block number，每個檔案最多有4M個BLOCK
16bit的row number，每個BLOCK最多有64K個ROWS


關於邏輯ROWID為，我這裡
select dump(rowid,16),a.* from ottest1 a;
為：
2,4,2,c0,0,9b,2,c1,3,fe
2,4,2,c0,0,9b,2,c1,4,fe




按照文件說明如下：
2,4,2,64,18,245,2,193,3,7,120,199,10,1,1,1,1,254


Type Indicator: 2 (Logical ROWID) 
DBA Guess Length: 4 
DBA Guess: 02,64,18,24 = 0x024012f5 
(File 9, Block 4853) 
Slot Guess Length: 2 
Slot Guess: 193,3 (Slot 2) 
Primary Key Length: 7 
Primary Key Value: 120,199,10,1,1,1,1 
(01-OCT-2099) 


可以對照但是我這裡並沒有Slot Guess: 193,3 (Slot 2) 
可以看出10G-11G邏輯ROWID組成為
Type Indicator+DBA Guess Length+DBA Guess+Slot Guess Length+Primary Key Length+Primary Key Value
既然最後是主鍵值那麼也就是反證了最開始給出了為什麼受到主鍵長度的影響邏輯ROWID會變長。


那麼二級索引中又記錄了什麼樣的值呢？
為此我建立了如下的索引
 create index ottest1_n_i on ottest1(ename);
 
如下：
row#0[8011] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
row#1[7990] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
----- end of leaf block dump -----


可以看到col  0: [ 4]  02 c0 00 9b就是DBA的值，而col 1; len 2; (2):  c1 03是我們的主鍵
這樣可以看到當塊分裂後DBA的值改變了其中記錄的DBA Guess也就不準確了。


我們進行大量的資料插入後在來看看我們的這個邏輯ROWID分裂塊後是否還是準確的。






IOT表中索引的結構已經發生了變化


branch: 0x2c0009b 46137499 (0: nrow: 32, level: 1)
   leaf: 0x2c0009c 46137500 (-1: nrow: 323 rrow: 323)
   leaf: 0x2c0009d 46137501 (0: nrow: 319 rrow: 319)
   leaf: 0x2c0009e 46137502 (1: nrow: 319 rrow: 319)
   leaf: 0x2c0009f 46137503 (2: nrow: 319 rrow: 319)
   leaf: 0x2c000dc 46137564 (3: nrow: 320 rrow: 320)
   leaf: 0x2c000dd 46137565 (4: nrow: 319 rrow: 319)
   leaf: 0x2c000de 46137566 (5: nrow: 319 rrow: 319)
   leaf: 0x2c000df 46137567 (6: nrow: 319 rrow: 319)
   ......
   
可以看到原有2c0009b塊已經成了跟節點。我們知道跟節點是不存放的資料的。而原有的資料已經變成了
SQL> select dump(rowid,16) from ottest1 where empno=2;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=208 Len=10: 2,4,2,c0,0,9c,2,c1,3,fe
這裡是我們的以前的資料 主鍵ID為2的行DBA已經變成
2,c0,0,9c和DUMP一致而以前是
2,c0,0,9b,但是2,c0,0,9b這個塊現在已經是分支節點了，說明他的位置已經發生了改變而且是塊級別的改變。
這說明隨著IOT的表的變大索引的塊會不斷分離，物理位置也在不斷的改變。




我們再來DUMP一下原有的索引塊而且還是 2 和3 這兩條記錄
row#0[719] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
row#1[740] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
可以看到col  0: [ 4]  02 c0 00 9b和以前的一樣並沒有跟新為02 c0 00 9c，這也充分的說明
索引塊的分裂會導致原有的二級索引的邏輯ROWID錯誤，導致一次使用主鍵去訪問資料。
當然如果我們進行索引REBULID後我們再來看看




SQL> alter index ottest1_n_i rebuild online;
Index altered


row#0[719] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9c
row#1[740] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9c


最後我們發現如果要糾正邏輯ROWID錯誤的方法就是REBUILD二級索引，但是有必要說一下，這種情況僅僅是
二級索引會出現問題，但是主鍵不會有任何問題。


所以最後給出索引組織表不適合的情況
有大量的二級索引（非主鍵），如果頻繁INSERT操作，會導致二級索引邏輯ROWID錯誤，造成GUESS失敗，這種情況
下ORACLE不得不用主鍵來進行定位操作，這個代價是顯而易見的因為僅僅有主鍵ORACLE不得不重新掃描IOT表的主鍵來得到資料，
那麼很顯然他要經歷兩個索引結構的遍歷1次是二級索引，一次是索引組織表本生的索引，如果邏輯ROWID不出問題，那麼就只是經歷
一次索引結構的遍歷，根據GUESS DBA就能定位到塊。雖然可以進行REBULID來糾正這個問題但是在大型的系統大型的表中REBULID
是那麼困難。
另外就是索引組織表不支援的方面比如子分割槽。
當然如果一個表只有一個主鍵，而基本沒有什麼二級索引並且為了節約空間，
，且長期按照這個主鍵來進行訪問，使用索引組織表是可以的。