為什么Oracle不使用我的索引
2024-08-29 13:39:14
供稿：網友
Author: Jonathan Lewis
Reference: http://www.jlcomp.demon.co.uk/
一. 樣例
create table t1 as
select trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad(/'x/', 215) v1
from all_objects
where rownum <= 3000;
create table t2 as
select
mod(rownum,200) n1,
mod(rownum,200) n2,
rpad(/'x/',215) v1
from all_objects
where rownum <= 3000;
create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);
analyze table t1 compute statistics;
analyze table t2 compute statistics;
二. 結果:(使用choose進行plan解釋得到的結果)
1. select * from t1 where n1 = 45;
-- use index range scan
EXECUTION PLAN
--------------
TABLE access BY INDEX ROWID OF T1 (C=2)
INDEX(RANGE SCAN) OF T1_I1 (C=1)
2. select * from t2 where n1 = 45;
-- use full table scan
EXECUTION PLAN
--------------
TABLE ACCESS FULL OF T2 (C=15)
3. select /*+ index(t2 t2_i1) */ from t2 where n1=45;
-- After using hint, Oracle choose index range scan
EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
INDEX(RANGE SCAN) OF T2_I1 (C=1)
三. 原因:
Oracle一般選擇最廉價的執行計劃.
select table_name, level,
avg_data_blocks_per_key,
avg_leaf_blocks_per_key,
clustering_factor
from user_indexes;
T1 T2
Blevel 1 1
Data block/key 1 15
Leaf block/key 1 1
Clustering factor 96 3000
Total Cost = avg_data_blocks_per_key + avg_leaf_blocks_per_key
（邱承國注：上文公式是說：總成本是每個鍵值所包含的平均數據塊數目與平均“葉”塊數目之和）
四. 分析
對于T1表, 使用了trunc()截斷函數將/"rownum - 1/"的值除以15并進行截斷以產生N1字段的值,
...
Trunc(675/15) = 45
Trunc(676/15) = 45
Trunc(689/15) = 45
...
實際上，上述值等于45的記錄將一個接一個的緊密排列在表中（甚至有可能完全填充在一個數據塊中）。
對于T2表，使用mod()取余函數將rownum的值除以200取余數，以產生N1字段的值:
mod(45,200) = 45
mod(245,200) = 45
...
mod(2845,200) = 45
上述值等于45的記錄會每隔200條記錄分布在表中（有可能隔1、2個數據塊才能找到一條記錄以上）
通過分析，Oracle能夠獲得表中數據的散列情況的一個完美的描述。因此優化器就可以精確地計算出Oracle在處理我們的查詢時需要訪問多少數據塊，并且，就象上面簡單的例子，計算出這個查詢成本中的需要訪問數據塊的數目
五. 為什么會是全表掃描?
現在我們看到了，同樣的查詢，T2表“基于索引掃描”的成本要比在T1表上的高得多，但是為什么Oracle會切換成“全表掃描方式”呢?
讓我們看看Oracle得兩個愚蠢、非常不合適、估計（邱承國注：意譯）的做法。
首先，每一次塊采集都帶來一次物理磁盤讀操作，其次是一次讀入多個塊要比讀入一個塊速度要快得多。
那么，這種傲慢的做法導致了什么結果?
假如你使用下列SQL語句查詢user_tables視圖:
select table_name,blocks
from user_tables;
你會發現我們前面提到的兩張表每張表使用了96個塊.
在這片文章開始時，我說明了這個測試用例使用Oracle 8的版本，并且db_file_multiblock_read_count參數取值為8（邱承國注：該參數決定讀取數據文件時每次讀入多少個塊）.
大致地說, Oracle會判定出要讀取一個96塊的表需要96/8 = 12次磁盤讀取請求。
而由于通過索引訪問這張表需要讀取16個塊或者說是16次磁盤讀取請求，從Oracle悲哀的、迷惑的觀點出發，很明顯全表掃描快得多— 要知道12次要比16少多了。
瞧! 假如你需要的數據比較對稱地散列在整張表中，你將不得不以“全表掃描”的方式撿索僅占很小百分比的數據，——正如例子中數據塊較大而記錄數較少因此問題就很明顯了。
六. 修正
事實上, 你可能已經注重到了我計算出的讀取次數時12次，同時execution plan報告的成本是15。 說起來比較簡單：全表掃描的成本（或者說是基于索引的掃描成本）就是
/'number of blocks/' /db_file_multiblock_read_count
（邱承國注：可以理解為，數據塊的數目/讀取數據文件時每次讀取的最多塊數）
其實，Oracle使用一個“可調整的”多塊讀取值來參與計算(盡管當掃描開始時它使用現實請求的尺寸).
例如，下列表格時一些現實尺寸和調整值的對比。
Actual Adjusted
4 4.175
8 6.589
16 10.398
32 16.409
64 25.895
128 40.865
就像你看到的，Oracle還是嘗試防止這種因為提供了一個較大的BLOCK數據塊讀取參數而產生的錯誤。
順便說一下，在Orace 9 的版本中有一個較小的改變，就是 全表掃描的成本有了一些增加調整，在成本計算結果上增加了一個單位——這也就是說V9版本的Oracle其全表掃描回避V8昂貴一些，這樣“基于索引的掃描”使用的概率會高一些了。
(我打算在 9i和 10G上在進行測試比較)
七. 調整
我們已經看到了有兩個估計被構造進了優化器里，而這是非常不明智的。
單單讀取一個塊和一次奪取多個塊成本竟然是一樣的——（最不希望看到的，當數據庫是運行在文件系統而不是裸設備時更為明顯）。
讀取一個塊竟然導致一次物理磁盤的讀取？——（那么，buffer cache 用來干什么?)
在使用Oracle 8.1的早些時候, 有一對參數答應我們通過一個適當的、符合實際的方式來修正這些估計值。
看看 Tim Gorman的文章，準確而又簡要的描述了著兩個參數：
參數Optimizer_index_cost_adj: 缺省為100，可以定義為1到10000。這個參數卓有成效地描述了單單讀取一個數據塊將比一次讀取多個塊便宜多少。舉例來說，我們通常將OLTP系統的值設為30，它告訴ORACLE單單讀取一個塊的成本時讀取多個塊的成本的30％，Oracle將因為這個參數值較低而傾向于使用基于索引的掃描方式。
參數Optimizer_index_caching:缺省為0，可以定義為0到100。這個參數告訴Oracle在buffer cache中估計能找到百分之多少地索引塊。在這個例子中，假如把這個值設定為接近100，Oracle更愿意使用索引掃描而不是全表掃描。
假如把這兩個參數都設定成符合實際的值，那真是一件大好事！
在參數列表的/"buffer cache hit ratio./"中設置optimizer_index_caching的值(你不得不自己決定是否這樣做，因為它會影響POOL的外形，default pool, keep pool, 或者二者都有)。.
optimizer_index_cost_adj參數有一些復雜。在視圖v$system_event中查找事件/"db file scattered read/" (multi block reads多塊讀取)和事件/"db file sequential reads/" (single block reads單塊讀取)的典型等待時間。 將后者的值除以前者再乘以100。
八. 優化
不要忘了這兩個參數在天天、每周的不同時刻都需要隨著最終用戶的作業情況進行調整。你絕對不能只定義一次而永遠使用。
幸運的是，在Oracle 9中，這些事情被改進了、現在你可以手機系統的統計信息，通常包括一下四個方面：
單個塊讀取的平均時間
多塊讀取的平均時間
當前多塊讀取的平均時間
概念上的可用CPU速度
夠了，關于這些特性的說明足夠寫一篇文章了——但還是要記住前面三個使Oracle發現多塊讀取的真實成本。并且，事實上，CPU速度將使Oracle計算出在不對稱地訪問那些部件時的CPU成本，例如在一個塊中讀取每一條記錄以便找到一個精確的數據把它展現出來。
當你升級到Oracle 9時，首先你應該調查系統統計信息是否被正確地使用了。這項獨一無二的特性可以減少你花在調整（/'tune/'）那些笨拙的SQL上面的時間。
接下來，不管系統統計信息有多么出色的表現，優化器的參數都必須進行調整——盡管從V8到V9這些參數的精確公式看起來已經發生了變化。
九. 不同場景的變化
當然了，我列舉了一個非常非凡的例子——非唯一性索引的單個字段而且表中沒有NUKK值——處理很簡單。
(我甚至還沒有提起索引的blevel和clustering_factor之間的關聯性。在Oracle的日常工作中，還有無數的不同的策略應用在一些案例中。
考慮以下的一些案例。我故意把它們忽略了：
多字段的索引
只使用部分字段的多字段的索引
Range scans某一范圍內的掃描
唯一索引
使用非唯一索引描述唯一約束
跳過索引的掃描
僅用于查詢的索引
位圖索引
Null值的影響
還有很多沒有列出來、這里并沒有一個簡單的公式能夠告訴你Oracle如何計算執行成本——而只有一個大概的方針可以讓你嘗嘗滋味，知道在不同的案例中應該采用的步驟和不同的公式。
不過話說回來, 這篇文章的目的在于讓你明白一般的步驟，明白在優化器的處理中有兩個假定。同時我希望這篇文章能夠引導你走完一段漫長的學習道路，理解Oracle優化器的那些看起來很希奇的現象。
