首頁新隨筆新文章聯(lián)系聚合

posts - 2,comments - 4,trackbacks - 0

2025年6月

>

日

一

二

三

四

五

六

25

26

27

28

29

30

31

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

1

2

3

4

5

常用鏈接

留言簿

隨筆分類

隨筆檔案

2009年5月 (3)

文章分類

文章檔案

友情鏈接

搜索

閱讀排行榜

評論排行榜

唯一索引&普通索引的一個(gè)有趣的區(qū)別

今天CNOUG上，一個(gè)網(wǎng)友問了一個(gè)隨機(jī)取記錄的問題，連接如下：

http://www.oracle.com.cn/viewthread.php?tid=130433

在測試過程，卻引起我對Oracle的索引的機(jī)制有了一次生動(dòng)的回顧。

測試的本意是參數(shù)一個(gè)隨機(jī)數(shù)，然后根據(jù)隨機(jī)數(shù)取一條記錄。

SELECT * FROM phs.t1 WHERE rn = trunc(dbms_random.value(1,100000))

Rn是索引字段 normal betree，根據(jù)rownum來建立。

但是初次測試的結(jié)果，卻出乎我意料，居然查詢結(jié)果是，無記錄或者隨機(jī)長度記錄集。多次測試：

SELECT COUNT(*) FROM phs.t1 WHERE rn = trunc(dbms_random.value(1,100000));

結(jié)果竟然是：0,28909，0,0,9870，23012，0,56789，45189，1240…

我花了5分鐘就解決了這個(gè)問題，但花了三個(gè)小時(shí)的思考，我終于想了一套說法來描述這里面的關(guān)系。

解決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

實(shí)際看到第二種解決辦法，就大概有種明白的感覺了，但是想解釋一下又無法說清。說白了，這里面牽扯了Oracle Intenal的東西，這些東西又不是公開的。我只能憑著僅有的資料去猜測。

在normal索引下，SQL的執(zhí)行計(jì)劃是RANGE SCAN，而unique索引，則是unique SCAN。
發(fā)生這種現(xiàn)象實(shí)際和SQL的執(zhí)行機(jī)制以及索引的機(jī)制有著密切的聯(lián)系。
通過查詢，可以看出該索引有2747個(gè)葉子塊，67個(gè)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
那么它的結(jié)構(gòu)如圖：

'800')this.width='800';if(this.height>'600')this.height='600';">

SQL的執(zhí)行步驟如下：
1．SQL匹配，語法語義檢查,通過對LIBRARY CACHE中對象的比對，進(jìn)行匹配。
2．對子查詢，視圖等進(jìn)行重新組合和SQL改寫，判斷對象訪問的開銷以及結(jié)果集的大小,每個(gè)對象都獨(dú)立計(jì)算成本以及返回的結(jié)果集的大小，判斷不同的連接順序的不同開銷
，連接方式和連接順序被通盤考慮，并且找到開銷最小的連接方式這個(gè)步驟里面包含了SQL執(zhí)行計(jì)劃的優(yōu)化。產(chǎn)生執(zhí)行樹，執(zhí)行樹被生成后放在LIBRARY CACHE里，當(dāng)SQL執(zhí)行的時(shí)候，被用來驅(qū)動(dòng)查詢.
4．分配綁定變量需要的內(nèi)存空間，綁定變量的值實(shí)現(xiàn)綁定。使用上一步產(chǎn)生的執(zhí)行計(jì)劃執(zhí)行SQL.
5．對于SELECT操作，比普通的SQL多了一個(gè)FETCH步驟，在這個(gè)步驟中，實(shí)際上的DB BLOCK的訪問才會(huì)產(chǎn)生。在這個(gè)階段，將剔除不需要的數(shù)據(jù)，把結(jié)果放入結(jié)果集，傳輸給客戶端。
有了上面的知識(shí)，我現(xiàn)在來解釋這個(gè)現(xiàn)象。
Q:為什么在normal的索引下，sql返回結(jié)構(gòu)集的大小是隨機(jī)的？在unique下面卻能確定唯一記錄？
A:首先在normal索引下，優(yōu)化器會(huì)把執(zhí)行計(jì)劃解釋為RANGE SCAN，因?yàn)樗J(rèn)為可能返回多條記錄。優(yōu)化器在進(jìn)行執(zhí)行計(jì)劃編寫的時(shí)候，首先就會(huì)計(jì)算 trunc(dbms_random.value(1,100000))，由此來確定選擇什么執(zhí)行計(jì)劃，假設(shè)本次產(chǎn)生的隨機(jī)數(shù)是87905。

進(jìn)入執(zhí)行階段，系統(tǒng)找到87905“開始“所在的索引數(shù)據(jù)塊，注意這個(gè)“開始”（也就是說它只關(guān)注數(shù)據(jù)是從那里開始的），隨后進(jìn)行遍歷該塊的水平鏈表尋找，執(zhí)行過濾,找到適合rowid，再去獲取數(shù)據(jù)塊。

在normal模式下，由于是范圍SCAN，優(yōu)化器實(shí)際把

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模式下，遍歷過程發(fā)現(xiàn)了87905的記錄，但它這個(gè)時(shí)候它還會(huì)判斷是否已經(jīng)達(dá)到區(qū)間掃描的終點(diǎn)，又會(huì)執(zhí)行Rn <= trunc(dbms_random.value(1,100000))

這個(gè)時(shí)候，trunc(dbms_random.value(1,100000))被重新計(jì)算?。?！

那么就會(huì)出現(xiàn)下面的分支情況：

'800')this.width='800';if(this.height>'600')this.height='600';">

1.結(jié)果>87905,優(yōu)化器認(rèn)為已經(jīng)達(dá)到區(qū)間終點(diǎn)（索引是有序的），謂詞判斷結(jié)果 true + false = false,所以返回空結(jié)果集。

2.結(jié)果小于等于87905,返回該記錄，計(jì)算出新的謂詞例如12346，再次尋找塊，遍歷水平列表（重復(fù)上面的動(dòng)作），然后又進(jìn)入分支判斷…最終出現(xiàn)分支1的時(shí)候終止。

這樣就說明為什么會(huì)出現(xiàn)這種情況。

在unique模式下，在遍歷水平鏈表的時(shí)候，找到當(dāng)前值就返回，而不用進(jìn)行區(qū)間判斷，或者是SCAN多條。所以結(jié)果集是正確的。
Q:為什么采用了with語法可以避免這種情況呢？
A:因?yàn)?/font>with字句在oracle內(nèi)部被解釋為一個(gè)內(nèi)聯(lián)視圖或者臨時(shí)表，所以trunc(dbms_random.value(1,100000))只會(huì)計(jì)算一次，之后的計(jì)算是針對固定的內(nèi)聯(lián)視圖的。

oracle 不允許這種寫法，rn= sequences.nextval，估計(jì)也是出于這個(gè)考慮吧。
想到這里，就結(jié)束了，其實(shí)可以自己寫一個(gè)random函數(shù)，在里面加一個(gè)記錄點(diǎn)，來證明謂詞多次改變，也可以做更詳細(xì)的sql trace，dump dump…

或者換一個(gè)Oracle的人來解釋一下，就OK了，但我估計(jì)在Oracle china也沒幾個(gè)了解數(shù)據(jù)庫機(jī)密的人。唉…

引自：http://valen.blog.ccidnet.com/blog-htm-do-showone-uid-51502-type-blog-itemid-262825.html

posted on 2010-12-17 16:07 孤飛燕閱讀(638) 評論(0) 編輯收藏所屬分類: 數(shù)據(jù)庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: Oracle創(chuàng)建臨時(shí)表 ORACLE 中ROWNUM用法總結(jié)! left join如何去除重復(fù)數(shù)據(jù) ORACLE導(dǎo)出導(dǎo)入 Oracle drop database Oracle轉(zhuǎn)義字符關(guān)于navicat連接oracle 報(bào) ORA-12737 set CHS16GBK錯(cuò)誤的解決 Oracle創(chuàng)建外鍵(刪除外鍵) 存儲(chǔ)過程分批批量刪除大數(shù)據(jù)量表如何查詢一個(gè)表被其他表所引用？