兩種使用B樹在列上建立索引的情況：
l 索引用于訪問表中的行：通過讀索引來訪問表中的行。此時你希望訪問表中很少的一部分行（只占一個很小的百分比）。
l 索引用于回答一個查詢：索引包含了足夠的信息來回答整個查詢，我根本不用去訪問表。在這種情況下，索引則用作一個“較瘦“版本的表，即通過查詢索引就能找到查詢結果，在這種情況下，可以通過處理標準100%的數據，而不像第一種情況中只能訪問少量的數據。
為什么在通過索引訪問表時如果數據量比較大的話，使用索引反而會降低性能？
一般來講，B*樹索引會放在頻繁使用查詢謂詞的列上，而且我們希望從表中只返回少量的數據（只占很小的百分比），或者最終用戶請求立即得到反饋。在一個瘦（thin）表（也就是說，只有很少的幾個列，或者列很小）上，這個百分比可能相當小。使用這個索引的查詢應該只獲取表中2%～3%（或者更少）的行。在一個胖（fat）表中（也就是說，這個表有很多列，或者列很寬），百分比則可能會上升到表的20%～25%。以上建議不一定直接適用于每一個人；這個比例并不直觀，但很精確。
索引按索引鍵的順序存儲。索引會按鍵的有序順序進行訪問。索引指向的塊則隨機地存儲在堆中。因此，我們通過索引訪問表時，會執行大量分散、隨機的I/O。這里“分散“（scattered）是指，索引會告訴我們讀取塊1，然后是塊1000、塊205、塊321、塊1、塊1032、塊1，等等，它不會要求我們按一種連續的方式讀取塊1、然后是塊2，接著是塊3（原因在與表中的每一行并沒有按照索引鍵的順序存儲在堆中，否則不會出現這種情況）。我們將以一種非常隨意的方式讀取和重新讀取塊。這種塊I/O可能非常慢。
下面來看這樣一個簡化的例子，假設我們通過索引讀取一個瘦表，而且要讀取表中20%的行。若這個表中有100,000行，其中的20%就是2,0000行。如果行大小約為80字節，在一個塊大小為8KB的數據庫中，每個塊上則有大約100行。這說明，這個表有大約1000個塊。了解了這些情況，計算起來就非常容易了。我們要通過索引讀取20,000行；這說明，大約是20,000個TABLE ACCESS BY ROWID操作。為此要處理20,000個表塊來執行這個查詢。不過，整個表才有大約1000個塊！因而最后會平均把表中的每一個塊讀取和處理20次（效果就是緩存的命中率很低，緩存的相鄰塊的數據沒有用到）。 即使把行的大小提高一個數量級，達到每行800字節，這樣每塊有11.行，現在表中就有11.,000個塊。要通過索引訪問20,000行，仍要求我們把每一個塊平均讀取2次。在這種情況下，全表掃描就比使用索引高效得多，因為每個塊只會命中一次。如果查詢使用這個索引來訪問數據，效率都不會高，除非對于800字節的行，平均只訪問表中不到5%的數據，因為這樣一來，就只會訪問大約5,000個塊，如果是80字節的行，則訪問的數據應當只占更小的百分比，大約0.5%或更少（這就是為什么在一次查詢中一個“胖表”的數據獲取百分比比一個“瘦表”相對高的原因）。
因此，根本原因是因為緩存命中率降低，出現了大量隨機分散的I/O操作。
物理組織對索引效率的影響
此外，數據在磁盤上如何物理組織，對上述過程也會有顯著影響。
表會很自然地按主鍵順序聚簇（因為數據或多或少就是已這種屬性增加的）。當然，它不一定嚴格按照鍵聚簇（要想做到這一點，必須使用一個IOT），但是，一般來講，主鍵值彼此接近的行的物理位置也會“靠“在一起。如果發出以下查詢：
select * from T where primary_key between :x and :y；
你想要的行通常就位于同樣的塊上。在這種情況下，即使要訪問大量的行（占很大的百分比），索引區間掃描可能也很有用。原因在于：我們需要讀取和重新讀取的數據庫塊很可能會被緩存，因為數據共同放置在同一個位置（co-located）。另一方面，如果行并非共同存儲在一個位置上，使用這個索引對性能來講可能就是災難性的。
聚簇因子
接下來，我們來看Oracle所用的一些信息。我們要特別查看USER_INDEXES視圖中的CLUSTERING_FACTOR列。Oracle reference手冊指出了這個列有以下含義：
根據索引的值指示表中行的有序程度：
如果這個值與塊數接近，則說明表相當有序，得到了很好的組織，在這種情況下，同一個葉子塊中的索引條目可能指向同一個數據塊上的行。
如果這個值與行數接近，表的次序可能就是非常隨機的。在這種情況下，同一個葉子塊上的索引條目不太可能指向同一個數據塊上的行。
可以把聚簇因子（clusteringfactor）看作是通過索引讀取整個表時對表執行的邏輯I/O次數。也就是說，CLUSTERING_FACTOR指示了表相對于索引本身的有序程度，查看這些索引時，會看到以下結果：
ops$tkyte@ORA10G> select a.index_name,
2 b.num_rows,
3 b.blocks,
4 a.clustering_factor
5 from user_indexes a, user_tables b
6 where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
7 and a.table_name = b.table_name
8 /
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
--------------- ---------- ---------- -----------------
COLOCATED_PK 100000 1252 1190
DISORGANIZED_PK 100000 1219 99932
所以數據庫說：“如果通過索引COLOCATED_PK從頭到尾地讀取COLOCATED表中的每一行，就要執行1190次I/O。不過，如果我們對DISORGANIZED表做同樣的事情，則會對這個表執行99,932次I/O“。之所以存在這么大的區別，原因在于，當Oracle對索引結構執行區間掃描時，如果它發現索引中的下一行幾乎總與前一行在同一個數據庫塊上，就不會再執行另一個I/O從緩沖區緩存中獲得表塊。它已經有表塊的一個句柄，只需直接使用就可以了。不過，如果下一行不在同一個塊上，就會釋放當前的這個塊，而執行另一個I/O從緩沖區緩存獲取要處理的下一個塊。因此，在我們對索引執行區間掃描時，COLOCATED_PK索引會發現下一行幾乎總于前一行在同一個塊上。DISORGANIZED_PK索引發現的情況則恰好相反。
以上討論的關鍵點是，索引并不一定總是合適的訪問方法。優化器也許選擇不使用索引，而且如前面的例子所示，這種選擇可能很正確。影響優化器是否使用索引的因素有很多，包括物理數據布局。因此，你可能會矯枉過正，力圖重建所有的表來使所有索引有一個好的聚簇因子，但是在大多數情況下這可能只會浪費時間。只有當你在對表中的大量數據（所占百分比很大）執行索引區間掃描時，這才會產生影響。另外必須記住，對于一個表來說，一般只有一個索引能有合適的聚簇因子！表中的行可能只以一種方式排序。在前面所示的例子中，如果Y列上還有一個索引，這個索引在COLOCATED表中可能就不能很好地聚簇，而在DISORGANIZED表中則恰好相反。如果你認為數據物理聚簇很重要，可以考慮使用一個IOT、B*樹聚簇，或者在連續地重建表時考慮散列聚簇。
B*樹索引小結
什么時候建立索引，在哪些列上建立索引，你的設計中必須注意這些問題。索引并不一定就意味著更快的訪問；實際上你會發現，在許多情況下，如果Oracle使用索引，反而會使性能下降。這實際上兩個因素的一個函數，其中一個因素是通過索引需要訪問表中多少數據（占多大的百分比），另一個因素是數據如何布局。如果能完全使用索引“回答問題“（而不用表），那么訪問大量的行（占很大的百分比）就是有意義的，因為這樣可以避免讀表所帶來的額外的分散I/O。如果使用索引來訪問表，可能就要確保只處理整個表中的很少一部分（只占很小的百分比）。
應該在應用的設計期間考慮索引的設計和實現，而不要事后才想起來（我就經常見到這種情況）。如果對如何訪問數據做了精心的計劃和考慮，大多數情況下就能清楚地知道需要什么索引。