教你如何成為Oracle 10g OCP - 第九章 物件管理(8) - 如何重建B樹索引

9.2.3   如何重建B樹索引

關於索引的一些說明：

1.索引的儲存是： 塊內無序，塊間有序；
2.rebuild index時對於索引塊的讀取也是Index Fast full scan
3.不是說讀索引的代價高，而是順序讀取塊內無序的索引的代價高
所以會採用Fast Full Scan方式讀取

------------------------------------------------------ 
rebuild index的詳細分析見：
http://space.itpub.net/35489/viewspace-594278 

alter index xx rebuild online ;   會走 Full Table Scan .
alter index xx rebuild ;   可能走Index Fast Full Scan，取原index資料
作為資料來源，也有可能取全表掃描即表的資料作為資料來源，取決於統計資料，
如果索引大小比表大，那麼會選擇使用全表掃描表中的資料。走Index Fast
Full Scan , 多塊讀，按照物理block儲存的順序讀取而不是index tree建立
的邏輯順序讀取， 之所以這樣做，是因為Sequential multiblock reads是最
快的IO訪問方式. I/O的快速帶來的負面影響就是要重新排序 . rebuild index
源資料來源於原來的index還是table, 取決於統計資訊，假如統計資訊顯示索
引比表還大，那麼無疑走全表比走索引快，因此走全表掃描。所以統計資訊的
完整與否影響到資料來源。

從索引開始rebuild online 的那一刻起,oracle會先建立一個SYS_JOURNAL_xxx
的系統臨時日誌表,結構類似於物化檢視日誌表mlog$_表,  透過內部觸發器, 
記錄了開始rebuild索引時表上所發生的改變的記錄,當索引已經建立好之後,
新資料將直接寫入索引,只需要把SYS_JOURNAL_xxx日誌表中的改變維護到索
引中即可 。 在rebulid index online 的時候走的是full table scan，這
時候需要排序；在rebulid index 走的index ffs (fast full scan)，而ffs
搜尋的順序是根據 leaf block 的物理儲存順序相關

rebuild index加 online時 ：存在DDL鎖 ，但是沒有DML鎖
rebuild index不加 online , DDL鎖和DML鎖都有

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

當進行index full scan的時候 oracle定位到索引的root block，然後
到branch block（如果有的話），再定位到第一個leaf block, 然後根據
leaf block的雙向連結串列順序讀取。它所讀取的塊都是有順序的，也是經過
排序的。
 
而index fast full scan則不同，它是從段頭開始，讀取包含點陣圖塊，root
block,所有的branch block, leaf block，讀取的順序完全有物理儲存位置
決定，並採取多塊讀，沒次讀取db_file_multiblock_read_count個塊。
index fast full scan  會比index full scan多一步sort order by

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

 

重建索引有兩種方法：

一種是最簡單的，刪除原索引，然後重建；第二種是使用ALTER INDEX .. REBUILD
命令對索引進行重建。第二種方式是從oracle 7.3.3版本開始引入的，從而使得使用者
在重建索引時不必刪除原索引再重新CREATE INDEX了。

ALTER INDEX … REBUILD相對CREATE INDEX有以下好處：

1). 它使用原索引的葉子節點作為新索引的資料來源。我們知道，原索引的葉子節
點的資料塊通常都要比表裡的資料塊要少很多，因此進行的I/O就會減少；同時，由
於原索引的葉子節點裡的索引條目已經排序了，因此在重建索引的過程中，所做的
排序工作也要少的多。

2). 自從oracle 8.1.6以來，ALTER INDEX … REBUILD命令可以新增ONLINE短語。
這使得在重建索引的過程中，使用者可以繼續對原來的索引進行修改，也就是說可以
繼續對錶進行DML操作。rebuild index online會是full table scan .

而同時，ALTER INDEX … REBUILD與CREATE INDEX也有很多相同之處：

1). 它們都可以透過新增PARALLEL提示進行並行處理。
2). 它們都可以透過新增NOLOGGING短語，使得重建索引的過程中產生最少
的重做條目（redo entry）。
3). 自從oracle 8.1.5以來，它們都可以新增COMPUTE STATISTICS短語，從而
在重建索引的過程中，就生成CBO所需要的統計資訊，這樣就避免了索引建立完
畢以後再次執行analyze或dbms_stats來收集統計資訊。

當我們重建索引以後，在物理上所能獲得的好處就是能夠減少索引所佔的
空間大小（特別是能夠減少葉子節點的數量）。而索引大小減小以後，又
能帶來以下若干好處：

1). CBO對於索引的使用可能會產生一個較小的成本值，從而在執行計劃中
選擇使用索引。
2). 使用索引掃描的查詢掃描的物理索引塊會減少，從而提高效率。
3). 由於需要快取的索引塊減少了，從而讓出了記憶體以供其他元件使用。

儘管重建索引具有一定的好處，但是盲目的認為重建索引能夠解決很多問
題也是不正確的。比如我見過一個生產系統，每隔一個月就要重建所有的
索引（而且我相信，很多生產系統可能都會這麼做），其中包括一些100GB
的大表。為了完成重建所有的索引，往往需要把這些工作分散到多個晚上進
行。事實上，這是一個7×24的系統，僅重建索引一項任務就消耗了非常多
的系統資源。但是每隔一段時間就重建索引有意義嗎? 

這裡就有一些關於重建索引的很流行的說法，主要包括：

1). 如果索引的層級超過X（X通常是3）級以後需要透過重建索引來降低其級別。
2). 如果經常刪除索引鍵值，則需要定時重建索引來收回這些被刪除的空間。
3). 如果索引的clustering_factor很高(解釋見後面)，則需要重建索引來降低該值。
4). 定期重建索引能夠提高效能。

對於第一點來說，我們在前面已經知道，B樹索引是一棵在高度上平衡的樹，
所以重建索引基本不可能降低其級別，除非是極特殊的情況導致該索引有非
常大量的碎片，導致B樹索引“虛高”，那麼這實際又來到第二點上（因為
碎片通常都是由於刪除引起的）。對於第一和第二點，我們應該透過執行
ALTER INDEX …validate structure命令以後檢查index_stats檢視pct_used
欄位來判斷是否有必要重建索引。

 

9.2.4   重建B樹索引對於clustering_factor的影響

而對於clustering_factor來說，它是用來比較索引的順序程度與表的雜亂
排序程度的一個度量。Oracle在計算某個clustering_factor時，會對每個
索引鍵值查詢對應到表的資料，在查詢的過程中，會跟蹤從一個表的資料塊
跳轉到另外一個資料塊的次數（當然，它不可能真的這麼做，原始碼裡只是
簡單的掃描索引，從而獲得ROWID，然後從這些ROWID獲得表的資料塊的地址）。
每一次跳轉時，有個計數器就會增加，最終該計數器的值就是clustering_factor。
下圖描述了這個原理:
http://space.itpub.net/batch.download.php?aid=5061 
         
     在圖中，我們有一個表，該表有4個資料塊，以及20條記錄。在列N1上
有一個索引，上圖中的每個小黑點就表示一個索引條目。列N1的值如圖所示。
而N1的索引的葉子節點包含的值為：A、B、C、D、E、F。如果oracle開始掃
描索引的底部，葉子節點包含的第一個N1值為A，那麼根據該值可以知道對應
的ROWID位於第一個資料塊的第三行裡，所以我們的計數器增加1。同時，A值
還對應第二個資料塊的第四行，由於跳轉到了不同的資料塊上，所以計數器
再加1。同樣的，在處理B時，可以知道對應第一個資料塊的第二行，由於我們
從第二個資料塊跳轉到了第一個資料塊，所以計數器再加1。同時，B值還對應
了第一個資料塊的第五行，由於我們這裡沒有發生跳轉，所以計數器不用加1。

在上面的圖裡，在表的每一行的下面都放了一個數字，它用來顯示計數器跳
轉到該行時對應的值。當我們處理完索引的最後一個值時，我們在資料塊上
一共跳轉了十次，所以該索引的clustering_factor為10。

注意第二個資料塊，clustering_factor為8出現了4次。因為在索引裡N1為E
所對應的4個索引條目都指向了同一個資料塊。從而使得clustering_factor不
再增長。同樣的現象出現在第三個資料塊中，它包含三條記錄，它們的值都是C，
對應的clustering_factor都是6。

從clustering_factor的計算方法上可以看出，我們可以知道它的最小值就
等於表所含有的資料塊的數量；而最大值就是表所含有的記錄的總行數。很
明顯，clustering_factor越小越好，越小說明透過索引查詢表裡的資料行時
需要訪問的表的資料塊越少。

我們來看一個例子，來說明重建索引對於減小clustering_factor沒有用處。
首先我們建立一個測試表：

SQL> create table clustfact_test(id number,name varchar2(10));
SQL> create index idx_clustfact_test on clustfact_test(id);

然後，我們插入十萬條記錄。

SQL> begin
 2           for i in 1..100000 loop
 3               insert into clustfact_test values(mod(i,200),to_char(i));
 4           end loop;
 5           commit;
 6 end;
 7 /

因為使用了mod的關係，最終資料在表裡排列的形式為：

0,1,2,3,4,5,…,197,198,199,0,1,2,3,…, 197,198,199,0,1,2,3,…, 197,198,199,0,1,2,3,…

 接下來，我們分析表。

SQL> exec dbms_stats.gather_table_stats(user,'clustfact_test',cascade=>true);
這個時候，我們來看看該索引的clustering_factor。

SQL> select num_rows, blocks from user_tables where table_name = 'CLUSTFACT_TEST';

 NUM_ROWS    BLOCKS
---------- ----------
   100000       202

SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,
   2 clustering_factor from user_indexes where index_name = 'IDX_CLUSTFACT_TEST';

 NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ------------- ----------------------- ----------------------- -----------------
   100000          200                      1                    198            39613

      從上面的avg_data_blocks_per_key的值為198可以知道，每個鍵值平均
分佈在198個資料塊裡，而整個表也就202個資料塊。這也就是說，要獲取某個
鍵值的所有記錄，幾乎每次都需要訪問所有的資料塊。從這裡已經可以猜測到
clustering_factor會非常大。事實上，該值近4萬，也說明該索引並不會很有效。

我們來看看下面這句SQL語句的執行計劃。

SQL> select count(name) from clufac_test where id = 100;

Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=32 Card=1 Bytes=9)
  1   0  SORT (AGGREGATE)
  2   1    TABLE ACCESS (FULL) OF 'CLUFAC_TEST' (Cost=32 Card=500 Bytes=4500)

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
       205 consistent gets
……

      很明顯，CBO棄用了索引，而使用了全表掃描。這實際上已經說明由於
索引的clustering_factor過高，導致透過索引獲取資料時跳轉的資料塊過多，
成本過高，因此直接使用全表掃描的成本會更低。

      這時我們來重建索引看看會對clustering_factor產生什麼影響。從下面
的測試中可以看到，沒有任何影響。

SQL> alter index idx_clustfact_test rebuild;
SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,
 2 clustering_factor from user_indexes where index_name = 'IDX_CLUSTFACT_TEST';

 NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ------------- ----------------------- ----------------------- -----------------
   100000          200                      1                    198            39613

 

    那麼當我們將表裡的資料按照id的順序（也就是索引的排列順序）重
建時，該SQL語句會如何執行？

SQL> create table clustfact_test_temp as select * from clustfact_test order by id;
SQL> truncate table clustfact_test;
SQL> insert into clustfact_test select * from clustfact_test_temp;
SQL> exec dbms_stats.gather_table_stats(user,'clustfact_test',cascade=>true);
SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,
   2 clustering_factor from user_indexes where index_name = 'IDX_CLUSTFACT_TEST';

 NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ------------- ----------------------- ----------------------- -----------------
   100000          200                      1                      1              198

    很明顯的，這時的索引裡每個鍵值只分布在1個資料塊裡，同時clustering_factor
也已經降低到了198。這時再次執行相同的查詢語句時，CBO將會選擇索引，同時可以看
到consistent gets也從205降到了5。

SQL> select count(name) from clustfact_test where id = 100;

Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
  1   0  SORT (AGGREGATE)
  2   1    TABLE ACCESS (BY INDEX ROWID) OF 'CLUSTFACT_TEST' (Cost=2 Card=500 Bytes=4500)
  3   2      INDEX (RANGE SCAN) OF 'IDX_CLUSTFACT_TEST' (NON-UNIQUE) (Cost=1 Card=500)

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         5 consistent gets
……

      所以我們可以得出結論，如果僅僅是為了降低索引的clustering_factor
而重建索引沒有任何意義。降低clustering_factor的關鍵在於重建表裡的資料。
只有將表裡的資料按照索引列排序以後，才能切實有效的降低clustering_factor。
但是如果某個表存在多個索引的時候，需要仔細決定應該選擇哪一個索引列來重
建表。