今天CNOUG上，一個網友問了一個隨機取記錄的問題，連接如下：
http://www.oracle.com.cn/viewthread.php?tid=130433
在測試過程，卻引起我對Oracle的索引的機制有了一次生動的回顧。
測試的本意是參數一個隨機數，然后根據隨機數取一條記錄。
SELECT * FROM phs.t1 WHERE rn = trunc(dbms_random.value(1,100000))
Rn是索引字段 normal betree，根據rownum來建立。
但是初次測試的結果，卻出乎我意料，居然查詢結果是，無記錄或者隨機長度記錄集。多次測試：
SELECT COUNT(*) FROM phs.t1 WHERE rn = trunc(dbms_random.value(1,100000));
結果竟然是：0,28909，0,0,9870，23012，0,56789，45189，1240…
我花了5分鐘就解決了這個問題，但花了三個小時的思考，我終于想了一套說法來描述這里面的關系。
解決1：把sql改為
WITH tab AS(SELECT trunc(dbms_random.value(1,100000)) a FROM dual)
SELECT * FROM t1 WHERE rn =( SELECT a FROM tab)
解決2：把rn的索引改為，unique
實際看到第二種解決辦法，就大概有種明白的感覺了，但是想解釋一下又無法說清。說白了，這里面牽扯了Oracle Intenal的東西，這些東西又不是公開的。我只能憑著僅有的資料去猜測。
在normal索引下，SQL的執行計劃是RANGE SCAN，而unique索引，則是unique SCAN。
發生這種現象實際和SQL的執行機制以及索引的機制有著密切的聯系。
通過查詢，可以看出該索引有2747個葉子塊，67個branch 塊，height 3
SQL> SELECT s.leaf_blocks,s.blevel FROM dba_indexes s WHERE s.index_name = 'INDEX_T1_RN';
LEAF_BLOCKS BLEVEL
----------- ----------
2747 2
SQL> SELECT SUM(blocks) FROM dba_extents WHERE segment_name='INDEX_T1_RN';
SUM(BLOCKS)
-----------
2816
那么它的結構如圖：
'800')this.width='800';if(this.height>'600')this.height='600';">
SQL的執行步驟如下：
1．SQL匹配，語法語義檢查,通過對LIBRARY CACHE中對象的比對，進行匹配。
2．對子查詢，視圖等進行重新組合和SQL改寫，判斷對象訪問的開銷以及結果集的大小,每個對象都獨立計算成本以及返回的結果集的大小，判斷不同的連接順序的不同開銷
，連接方式和連接順序被通盤考慮，并且找到開銷最小的連接方式這個步驟里面包含了SQL執行計劃的優化。產生執行樹，執行樹被生成后放在LIBRARY CACHE里，當SQL執行的時候，被用來驅動查詢.
4．分配綁定變量需要的內存空間，綁定變量的值實現綁定。使用上一步產生的執行計劃執行SQL.
5．對于SELECT操作，比普通的SQL多了一個FETCH步驟，在這個步驟中，實際上的DB BLOCK的訪問才會產生。在這個階段，將剔除不需要的數據，把結果放入結果集，傳輸給客戶端。
有了上面的知識，我現在來解釋這個現象。
Q:為什么在normal的索引下，sql返回結構集的大小是隨機的？在unique下面卻能確定唯一記錄？
A:首先在normal索引下，優化器會把執行計劃解釋為RANGE SCAN，因為它認為可能返回多條記錄。優化器在進行執行計劃編寫的時候，首先就會計算 trunc(dbms_random.value(1,100000))，由此來確定選擇什么執行計劃，假設本次產生的隨機數是87905。
進入執行階段，系統找到87905“開始“所在的索引數據塊，注意這個“開始”（也就是說它只關注數據是從那里開始的），隨后進行遍歷該塊的水平鏈表尋找，執行過濾,找到適合rowid，再去獲取數據塊。
在normal模式下，由于是范圍SCAN，優化器實際把
Rn= trunc(dbms_random.value(1,100000))
拆成了
Rn >= trunc(dbms_random.value(1,100000)) and
Rn <= trunc(dbms_random.value(1,100000))
在unique模式下，仍然是
Rn= trunc(dbms_random.value(1,100000))
所以，在normal模式下，遍歷過程發現了87905的記錄，但它這個時候它還會判斷是否已經達到區間掃描的終點，又會執行Rn <= trunc(dbms_random.value(1,100000))
這個時候，trunc(dbms_random.value(1,100000))被重新計算！！！
那么就會出現下面的分支情況：
'800')this.width='800';if(this.height>'600')this.height='600';">
1.結果>87905,優化器認為已經達到區間終點（索引是有序的），謂詞判斷結果 true + false = false,所以返回空結果集。
2.結果小于等于87905,返回該記錄，計算出新的謂詞例如12346，再次尋找塊，遍歷水平列表（重復上面的動作），然后又進入分支判斷…最終出現分支1的時候終止。
這樣就說明為什么會出現這種情況。
在unique模式下，在遍歷水平鏈表的時候，找到當前值就返回，而不用進行區間判斷，或者是SCAN多條。所以結果集是正確的。
Q:為什么采用了with語法可以避免這種情況呢？
A:因為with字句在oracle內部被解釋為一個內聯視圖或者臨時表，所以trunc(dbms_random.value(1,100000))只會計算一次，之后的計算是針對固定的內聯視圖的。
oracle 不允許這種寫法，rn= sequences.nextval，估計也是出于這個考慮吧。
想到這里，就結束了，其實可以自己寫一個random函數，在里面加一個記錄點，來證明謂詞多次改變，也可以做更詳細的sql trace，dump dump…
或者換一個Oracle的人來解釋一下，就OK了，但我估計在Oracle china也沒幾個了解數據庫機密的人。唉…
引自：http://valen.blog.ccidnet.com/blog-htm-do-showone-uid-51502-type-blog-itemid-262825.html
posted on 2010-12-17 16:07
孤飛燕 閱讀(636)
評論(0) 編輯 收藏 所屬分類:
數據庫