教你如何成為Oracle 10g OCP - 第九章 物件管理(9) - 重建索引對效能的影響

9.2.5.  重建B樹索引對於查詢效能的影響

      最後我們來看一下重建索引對於效能的提高到底會有什麼作用。假設
我們有一個表，該表具有1百萬條記錄，佔用了100000個資料塊。而在該表上
存在一個索引，在重建之前的pct_used為50%，高度為3，分支節點塊數為40個，
再加一個根節點塊，葉子節點數為10000個；重建該索引以後，pct_used為90%，
高度為3，分支節點塊數下降到20個，再加一個根節點塊，而葉子節點數下降
到5000個。
那麼從理論上說：

1）如果透過索引獲取單獨1條記錄來說：
重建之前的成本：1個根＋1個分支＋1個葉子＋1個表塊＝4個邏輯讀
重建之後的成本：1個根＋1個分支＋1個葉子＋1個表塊＝4個邏輯讀
效能提高百分比：0

2）如果透過索引獲取100條記錄（佔總記錄數的0.01%）來說，分兩種情況：
最差的clustering_factor（即該值等於表的資料行數）：
重建之前的成本：1個根＋1個分支＋0.0001*10000（1個葉子）＋100個表塊＝103個邏輯讀
重建之後的成本：1個根＋1個分支＋0.0001*5000（1個葉子）＋100個表塊＝102.5個邏輯讀
效能提高百分比：0.5%（也就是減少了0.5個邏輯讀）

最好clustering_factor（即該值等於表的資料塊）：
重建之前的成本：1個根＋1個分支＋0.0001*10000（1個葉子）＋0.0001*100000（10個表塊）＝13個邏輯讀
重建之後的成本：1個根＋1個分支＋0.0001*5000（1個葉子）＋0.0001*100000（10個表塊）＝12.5個邏輯讀
效能提高百分比：3.8%（也就是減少了0.5個邏輯讀）

3）如果透過索引獲取10000條記錄（佔總記錄數的1%）來說，分兩種情況：

最差的clustering_factor（即該值等於表的資料行數）：
重建之前的成本：1個根＋1個分支＋0.01*10000（100個葉子）＋10000個表塊＝10102個邏輯讀
重建之後的成本：1個根＋1個分支＋0.01*5000（50個葉子）＋10000個表塊＝10052個邏輯讀
效能提高百分比：0.5%（也就是減少了50個邏輯讀）

最好clustering_factor（即該值等於表的資料塊）：
重建之前的成本：1個根＋1個分支＋0.01*10000（100個葉子）＋0.01*100000（1000個表塊）＝1102個邏輯讀
重建之後的成本：1個根＋1個分支＋0.01*5000（50個葉子）＋0.01*100000（1000個表塊）＝1052個邏輯讀
效能提高百分比：4.5%（也就是減少了50個邏輯讀）

4）如果透過索引獲取100000條記錄（佔總記錄數的10%）來說，分兩種情況：

最差的clustering_factor（即該值等於表的資料行數）：
重建之前的成本：1個根＋1個分支＋0.1*10000（1000個葉子）＋100000個表塊＝101002個邏輯讀
重建之後的成本：1個根＋1個分支＋0.1*5000（500個葉子）＋100000個表塊＝100502個邏輯讀
效能提高百分比：0.5%（也就是減少了500個邏輯讀）

最好clustering_factor（即該值等於表的資料塊）：
重建之前的成本：1個根＋1個分支＋0.1*10000（1000個葉子）＋0.1*100000（10000個表塊）＝11002個邏輯讀
重建之後的成本：1個根＋1個分支＋0.1*5000（500個葉子）＋0.1*100000（10000個表塊）＝10502個邏輯讀
效能提高百分比：4.5%（也就是減少了500個邏輯讀）

5）對於快速全索引掃描來說，假設每次獲取8個資料塊：

重建之前的成本：（1個根＋40個分支＋10000個葉子）/ 8＝1256個邏輯讀
重建之後的成本：（1個根＋40個分支＋5000個葉子）/ 8＝631個邏輯讀
效能提高百分比：49.8%（也就是減少了625個邏輯讀）

      從上面有關效能提高的理論描述可以看出，對於透過索引獲取的記錄
行數不大的情況下，索引碎片對於效能的影響非常小；當透過索引獲取較大
的記錄行數時，索引碎片的增加可能導致對於索引邏輯讀的增加，但是索引
讀與表讀的比例保持不變；同時，我們從中可以看到，clustering_factor對
於索引讀取的效能有很大的影響，並且對於索引碎片所帶來的影響具有很大的
作用；最後，看起來，索引碎片似乎對於快速全索引掃描具有最大的影響。

      我們來看兩個實際的例子，分別是clustering_factor為最好和最差的
兩個例子。測試環境為8KB的資料塊，表空間採用ASSM的管理方式。先做一個
最好的clustering_factor的例子，建立測試表並填充1百萬條資料。

SQL> create table rebuild_test(id number,name varchar2(10));

SQL> begin
 2    for i in 1..1000000 loop
 3        insert into rebuild_test values(i,to_char(i));
 4            if mod(i,10000)=0 then
 5                commit;
 6            end if;
 7    end loop;
 8 end;
 9 /

   該表具有1百萬條記錄，分佈在2328個資料塊中。同時由於我們的數
據都是按照順序遞增插入的，所以可以知道，在id列上建立的索引都是
具有最好的clustering_factor值的。我們執行以下查詢測試語句，分別
返回1、100、1000、10000、50000、100000以及1000000條記錄。

select * from rebuild_test where id = 10;
select * from rebuild_test where id between 100 and 199;
select * from rebuild_test where id between 1000 and 1999;
select * from rebuild_test where id between 10000 and 19999;
select /*+ index(rebuild_test) */ * from rebuild_test where id between 50000 and 99999;
select /*+ index(rebuild_test) */ * from rebuild_test where id between 100000 and 199999;
select /*+ index(rebuild_test) */ * from rebuild_test where id between 1 and 1000000;
select /*+ index_ffs(rebuild_test) */ id from rebuild_test where id between 1 and 1000000;

在執行這些測試語句前，先建立一個pctfree為50%的索引，來模擬索引碎
片，分析並記錄索引資訊。

SQL> create index idx_rebuild_test on rebuild_test(id) pctfree 50;
SQL> exec dbms_stats.gather_table_stats(user,'rebuild_test',cascade=>true);

然後執行測試語句，記錄每條查詢語句所需的時間；接下來以pctfree為10%
重建索引，來模擬修復索引碎片，分析並記錄索引資訊。

SQL> alter index idx_rebuild_test rebuild pctfree 10;
SQL> exec dbms_stats.gather_table_stats(user,'rebuild_test',cascade=>true);

接著再次執行這些測試語句，記錄每條查詢語句所需的時間。下表顯示了兩個索引資訊的對比情況。

pctfree
 Height
 blocks
 br_blks
 lf_blks
 pct_used
 clustering_factor
 
50%
 3
 4224
 8
 4096
 49%
 2326
 
10%
 3
 2304
 5
 2226
 90%
 2326
 

下表顯示了不同的索引下，執行測試語句所需的時間對比情況。

記錄數
 佔記錄總數的百分比
 pctused(50%)
 pctused(90％)
 效能提高百分比
 
1條記錄
 0.0001%
 0.01
 0.01
 0.00%
 
100條記錄
 0.0100%
 0.01
 0.01
 0.00%
 
1000條記錄
 0.1000%
 0.01
 0.01
 0.00%
 
10000條記錄
 1.0000%
 0.02
 0.02
 0.00%
 
50000條記錄
 5.0000%
 0.06
 0.06
 0.00%
 
100000條記錄
 10.0000%
 1.01
 1.00
 0.99%
 
1000000條記錄
 100.0000%
 13.05
 11.01
 15.63%
 
1000000條記錄(FFS)
 100.0000%
 7.05
 7.02
 0.43%
 

   上面是對最好的clustering_factor所做的測試，那麼對於最差的
clustering_factor會怎麼樣呢？我們將rebuild_test中的id值反過來排
列，也就是說，比如對於id為3478的記錄，將id改為8743。這樣的話，
就將把原來按順序排列的id值徹底打亂，從而使得id上的索引的clustering_factor
變成最差的。為此，我寫了一個函式用來反轉id的值。

create or replace function get_reverse_value(id in number) return varchar2 is
 ls_id varchar2(10);
 ls_last_item varchar2(10);
 ls_curr_item varchar2(10);
 ls_zero varchar2(10);
 li_len integer;
 lb_stop boolean;
begin
 ls_id := to_char(id);
 li_len := length(ls_id);
 ls_last_item := '';
 ls_zero := '';
 lb_stop := false;
 while li_len>0 loop
       ls_curr_item := substr(ls_id,li_len,1);
       if ls_curr_item = '0' and lb_stop = false then
           ls_zero := ls_zero || ls_curr_item;
       else
           lb_stop := true;
           ls_last_item:=ls_last_item||ls_curr_item;
       end if;
       ls_id := substr(ls_id,1,li_len-1);
       li_len := length(ls_id);
 end loop;
 return(ls_last_item||ls_zero);
end get_reverse_value;

      接下來，我們建立我們第二個測試的測試表。並按照與第一個測
試案例相同的方式進行測試。注意，對於測試查詢來說，要把表名（包
括提示裡的）改為rebuild_test_cf。

SQL> create table rebuild_test_cf as select * from rebuild_test;
SQL> update rebuild_test_cf set name=get_reverse_value(id);

 

 

 

B-Tree索引結構參考：

http://www.cublog.cn/u3/112761/showart_2218897.html
http://space.itpub.net/?uid-9842-action-viewspace-itemid-324139

http://www.itpub.net/thread-300772-1-1.html

 

深入研究Oracle B樹索引系列 -by hanson
 
深入研究B樹索引（一）：http://space.itpub.net/?uid-9842-action-viewspace-itemid-312607
深入研究B樹索引（二）：http://space.itpub.net/?uid-9842-action-viewspace-itemid-321866
深入研究B樹索引（三、四）http://space.itpub.net/?uid-9842-action-viewspace-itemid-324139
深入研究B樹索引（四）續：http://space.itpub.net/?uid-9842-action-viewspace-itemid-324586
深入研究B樹索引（五）：http://space.itpub.net/?uid-9842-action-viewspace-itemid-324587
深入研究B樹索引（五）續:http://space.itpub.net/?uid-9842-action-viewspace-itemid-324588