rebuild index 排序

好貼，http://www.itpub.net/viewthread.php?tid=205422&extra=&page=1 這裡摘錄整理一下。[@more@]

Eagle:

rebuild index為何要排序

我們都知道索引是有序的儲存

然而在block內部，實際上索引鍵值的儲存是無序的

比如說,你先存入了1,3

即使以後增加了一個2

那麼在同一個資料塊內部，資料庫也不會去動1,3的儲存

在讀取的時候，oracle可以作簡單的塊內排序，進行有序的讀取輸出

在重建索引的時候

Oracle顯然不會按照1,2,3..........的索引順序來讀出索引內容

因其代價高昂

Oracle實際執行的是Fast Full Scan

按順序讀取block

這樣讀取出來的資料需要重新sort

排序，然後重構索引

這個重構的索引在物理儲存上比原來更為有序

總結：

1、索引的儲存是，塊內無序，塊間有序

2、索引葉子節點內部沒有存在 連結串列狀結構來維護順序，則row# 總是可能發生變化的。記錄在塊內物理上是無序的，但是在邏輯上透過row#來表達了順序。

3、rebulid index 要考慮 rebuild後新的index大小，如果rebuild online 會有一些臨時資訊佔用空間，這些都在index所在表空間上。另外，rebuild需要排序，一般sort area size是不夠用的，會用到temp表空間。

4.如果是非ONLINE方式，通常會對該表設定一個表級共享(DML)鎖，那麼就對DML語句衝突，
如果設定ONLINE ,(會使用臨時日誌IOT表來記錄中間改變的資料)，但要使用兩倍於傳統方法的空間.表會變成行級共享鎖,在建立索引或者ALTER完成後,對臨時日誌表與基表進行MERGE
注意並行處理，DDL，點陣圖索引不能使用ONLINE。
5.從索引開始rebuild online開始的那一刻起,oracle會先建立一個SYS_JOURNAL_xxx的系統臨時日誌表,結構類似於mlog$_表,
透過內部觸發器,記錄了開始rebuild索引時表上所發生的改變的記錄,當索引已經建立好之後,新資料將直接寫入索引,
只需要把SYS_JOURNAL_xxx日誌表中的改變維護到索引中即可。
6.rebuild index 是讀取老的index的，但是 rebuild index online 是讀取基表的。

dlinger:

我作了兩個試驗。

我的index大小為7M左右。

首先，我將index所在的tablespace只開到10M，臨時表空間較大，

rebuild 時報無法在index所在的tablespace上擴充套件temp 段。

將表空間增大，rebuild成功。

在將臨時表空間只開到5M，表空間開到20M，rebuild就報無法在temp表空間上擴充套件，將臨時表空間增大到10M，rebuild成功

今天找了個時間做了把實驗

SQL> select * from test_index;

ID NAME

--------------------------------------- --------------------

1 aa

2 aa

3 aa

35 aa

32 bb

SQL> select dump(id) from test_index;

DUMP(ID)

--------------------------------------------------------------------------------

Typ=2 Len=2: 193,2

Typ=2 Len=2: 193,3

Typ=2 Len=2: 193,4

Typ=2 Len=2: 193,36

Typ=2 Len=2: 193,33

SQL> alter system dump datafile 15 block 42 ;

*** 2004-04-05 11:49:19.947

Start dump data blocks tsn: 14 file#: 15 minblk 42 maxblk 42

buffer tsn: 14 rdba: 0x03c0002a (15/42)

scn: 0x0000.00196f9b seq: 0x01 flg: 0x00 tail: 0x6f9b0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x03c0002a

Object id on Block? Y

seg/obj: 0x682f csc: 0x00.196f99 itc: 2 flg: - typ: 2 - INDEX

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x02 xid: 0x0004.050.000002ed uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Leaf block dump

===============

header address 60386396=0x3996c5c

kdxcolev 0

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 5

kdxcofbo 46=0x2e

kdxcofeo 7976=0x1f28

kdxcoavs 7930

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[8024] flag: -----, lock: 0

col 0; len 2; (2): c1 02

col 1; len 6; (6): 03 c0 00 0a 00 00

row#1[8012] flag: -----, lock: 0

col 0; len 2; (2): c1 03

col 1; len 6; (6): 03 c0 00 0a 00 01

row#2[8000] flag: -----, lock: 0

col 0; len 2; (2): c1 04

col 1; len 6; (6): 03 c0 00 0a 00 02

row#3[7988] flag: -----, lock: 0

col 0; len 2; (2): c1 21

col 1; len 6; (6): 03 c0 00 0a 00 04

row#4[7976] flag: -----, lock: 0

col 0; len 2; (2): c1 24

col 1; len 6; (6): 03 c0 00 0a 00 03

----- end of leaf block dump -----

SQL> insert into test_index values(21,'cs');

commit;

SQL> alter system dump datafile 15 block 42 ;

*** 2004-04-05 12:27:13.381

Start dump data blocks tsn: 14 file#: 15 minblk 42 maxblk 42

buffer tsn: 14 rdba: 0x03c0002a (15/42)

scn: 0x0000.00196f9f seq: 0x01 flg: 0x02 tail: 0x6f9f0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x03c0002a

Object id on Block? Y

seg/obj: 0x682f csc: 0x00.196f99 itc: 2 flg: - typ: 2 - INDEX

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x02 xid: 0x0005.04b.000002e3 uba: 0x00800815.0090.35 --U- 1 fsc 0x0000.00196f9f

Leaf block dump

===============

header address 60386396=0x3996c5c

kdxcolev 0

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 6

kdxcofbo 48=0x30

kdxcofeo 7964=0x1f1c

kdxcoavs 7916

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[8024] flag: -----, lock: 0

col 0; len 2; (2): c1 02

col 1; len 6; (6): 03 c0 00 0a 00 00

row#1[8012] flag: -----, lock: 0

col 0; len 2; (2): c1 03

col 1; len 6; (6): 03 c0 00 0a 00 01

row#2[8000] flag: -----, lock: 0

col 0; len 2; (2): c1 04

col 1; len 6; (6): 03 c0 00 0a 00 02

row#3[7964] flag: -----, lock: 2

col 0; len 2; (2): c1 16

col 1; len 6; (6): 03 c0 00 0a 00 05

row#4[7988] flag: -----, lock: 0

col 0; len 2; (2): c1 21

col 1; len 6; (6): 03 c0 00 0a 00 04

row#5[7976] flag: -----, lock: 0

col 0; len 2; (2): c1 24

col 1; len 6; (6): 03 c0 00 0a 00 03

----- end of leaf block dump -----

End dump data blocks tsn: 14 file#: 15 minblk 42 maxblk 42

--------------------------------------------------------------------------------

Biti_rainy

如上紅色部分，新記錄的插入並沒有導致物理位置發生變化。如果要維護塊內順序，則參考 在表資料塊中是 row dictionary 有另外一個地方維護這個結構。但這在index dump中是看不出來這樣的結構的，只能看到這裡是根據順序輸出的trace。我曾經想過這樣一個問題，那就是，如果要維護這個順序，假如索引塊中存在100個row記錄，新插入一個記錄進來(很顯然這裡每個row 消耗了12個位元組，應該就是 rowid 6 , col length 1,col 2,lock 1,flag 1, row# 1)， 這意味著新插入一條記錄將可能修改很多記錄的row#嗎？

其實，如果能在變化中把索引block的二進位制直接讀出來觀察變化應該可以立即斷定。

很顯然，索引葉子節點內部沒有存在 連結串列狀結構來維護順序，則row# 總是可能發生變化的。記錄在塊內物理上是無序的，但是在邏輯上透過row#來表達了順序。我們可以假想一種順序讀出資料的方式，block內部存在記錄 kdxconro 條 ，則讀出的時候建立結構陣列 index_struc[kdxconro] ,當讀到行號為 row# 的記錄的時候就放入 index_struc[row#] 。 這樣實現資料的有序讀取， 當寫入的時候，無論如何，可能去更改很多記錄的 row# 了