建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
以下的文章主要講述的是Oracle優(yōu)化查詢(xún)的實(shí)際操作我們首先是假設(shè)LARGE_TABLE是一個(gè)表（較大型），而且username列上是沒(méi)有相關(guān)索引的，那么它在實(shí)際的操作中要用到的語(yǔ)句有那些？下面就是對(duì)其語(yǔ)句的描述：
在龍馬潭等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶(hù)提供成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè) 網(wǎng)站設(shè)計(jì)制作按需求定制設(shè)計(jì),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),營(yíng)銷(xiāo)型網(wǎng)站,成都外貿(mào)網(wǎng)站建設(shè),龍馬潭網(wǎng)站建設(shè)費(fèi)用合理。
- SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST';
- Query Plan
- SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
- TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]
在這個(gè)例子中，TABLE ACCESS FULL LARGE_TABLE是第一個(gè)操作，意思是在LARGE_TABLE表上做全表掃描。當(dāng)這個(gè)操作完成之后，產(chǎn)生的row source中的數(shù)據(jù)被送往下一步驟進(jìn)行處理，在此例中，SELECT STATEMENT操作是這個(gè)Oracle優(yōu)化查詢(xún)語(yǔ)句的最后一步。
Optimizer=CHOOSE 指明這個(gè)查詢(xún)的optimizer_mode，即optimizer_mode初始化參數(shù)指定的值，它并不是指語(yǔ)句執(zhí)行時(shí)真的使用了該優(yōu)化器。決定該語(yǔ)句使用何種優(yōu)化器的唯一方法是看后面的cost部分。例如，如果給出的是下面的形式，則表明使用的是CBO優(yōu)化器，此處的cost表示優(yōu)化器認(rèn)為該執(zhí)行計(jì)劃的代價(jià):
- SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
然而假如執(zhí)行計(jì)劃中給出的是類(lèi)似下面的信息，則表明是使用RBO優(yōu)化器，因?yàn)閏ost部分的值為空，或者壓根就沒(méi)有cost部分。
- SELECT STATEMENT Optimizer=CHOOSE Cost=
- SELECT STATEMENT Optimizer=CHOOSE
這樣我們從Optimizer后面的信息中可以得出執(zhí)行該語(yǔ)句時(shí)到底用了什么樣的優(yōu)化器。特別的，如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n，則使用的是CBO優(yōu)化器;如果Optimizer=RULE，則使用的是RBO優(yōu)化器。
cost屬性的值是一個(gè)在Oracle內(nèi)部用來(lái)比較各個(gè)執(zhí)行計(jì)劃所耗費(fèi)的代價(jià)的值，從而使優(yōu)化器可以選擇最好的執(zhí)行計(jì)劃。不同語(yǔ)句的cost值不具有可比性，只能對(duì)同一個(gè)語(yǔ)句的不同執(zhí)行計(jì)劃的cost值進(jìn)行比較。
[:Q65001] 表明該部分查詢(xún)是以并行方式運(yùn)行的。里面的數(shù)據(jù)表示這個(gè)操作是由并行Oracle優(yōu)化查詢(xún)的一個(gè)slave進(jìn)程處理的，以便該操作可以區(qū)別于串行執(zhí)行的操作。
[ANALYZED] 表明操作中引用的對(duì)象被分析過(guò)了，在數(shù)據(jù)字典中有該對(duì)象的統(tǒng)計(jì)信息可以供CBO使用。
例2：
假定A、B、C都是不是小表，且在A表上一個(gè)組合索引：A(a.col1,a.col2) ，注意a.col1列為索引的引導(dǎo)列?？紤]下面的查詢(xún)：
- select A.col4
- from A , B , C
- where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
- Execution Plan
- SELECT STATEMENT Optimizer=CHOOSE
- 0 MERGE JOIN
- 1 SORT (JOIN)
- 2 NESTED LOOPS
- 3 TABLE ACCESS (FULL) OF 'B'
- 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
- 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
- 1 SORT (JOIN)
- 7 TABLE ACCESS (FULL) OF 'C'
- Statistics
- 0 recursive calls
- 8 db block gets
- 6 consistent gets
- 0 physical reads
- 0 redo size
- 551 bytes sent via SQL*Net to client
- 430 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 2 sorts (memory)
- 0 sorts (disk)
- 6 rows processed
在表做連接時(shí)，只能2個(gè)表先做連接，然后將連接后的結(jié)果作為一個(gè)row source，與剩下的表做連接，在上面的例子中，連接順序?yàn)锽與A先連接，然后再與C連接：
B <---> A <---> C
col3=10 col3=5
如果沒(méi)有執(zhí)行計(jì)劃，分析一下，上面的3個(gè)表應(yīng)該拿哪一個(gè)作為第一個(gè)驅(qū)動(dòng)表？從SQL語(yǔ)句看來(lái)，只有B表與C表上有限制條件，所以第一個(gè)驅(qū)動(dòng)表應(yīng)該為這2個(gè)表中的一個(gè)，到底是哪一個(gè)呢？
B表有謂詞B.col3 = 10，這樣在對(duì)B表做全表掃描的時(shí)候就將where子句中的限制條件(B.col3 = 10)用上，從而得到一個(gè)較小的row source, 所以B表應(yīng)該作為第一個(gè)驅(qū)動(dòng)表。而且這樣的話(huà)，如果再與A表做關(guān)聯(lián)，可以有效利用A表的索引(因?yàn)锳表的col1列為leading column)。
當(dāng)然上面的Oracle優(yōu)化查詢(xún)中C表上也有謂詞(C.col3 = 5)，有人可能認(rèn)為C表作為第一個(gè)驅(qū)動(dòng)表也能獲得較好的性能。讓我們?cè)賮?lái)分析一下：如果C表作為第一個(gè)驅(qū)動(dòng)表，則能保證驅(qū)動(dòng)表生成很小的row source，但是看看連接條件A.col2 = C.col2，此時(shí)就沒(méi)有機(jī)會(huì)利用A表的索引，因?yàn)锳表的col2列不為leading column，這樣nested loop的效率很差，從而導(dǎo)致查詢(xún)的效率很差。
所以對(duì)于NL連接選擇正確的驅(qū)動(dòng)表很重要。
因此上面查詢(xún)比較好的連接順序?yàn)?B - - > A) - - > C。如果數(shù)據(jù)庫(kù)是基于代價(jià)的優(yōu)化器，它會(huì)利用計(jì)算出的代價(jià)來(lái)決定合適的驅(qū)動(dòng)表與合適的連接順序。一般來(lái)說(shuō)，CBO都會(huì)選擇正確的連接順序，如果CBO選擇了比較差的連接順序，我們還可以使用Oracle提供的hints來(lái)讓CBO采用正確的連接順序。如下所示：
- select /*+ ordered */ A.col4
- from B,A,C
- where B.col3 = 10
- and A.col1 = B.col1
- and A.col2 = C.col2
- and C.col3 = 5
既然選擇正確的驅(qū)動(dòng)表這么重要，那么讓我們來(lái)看一下執(zhí)行計(jì)劃，到底各個(gè)表之間是如何關(guān)聯(lián)的，從而得到執(zhí)行計(jì)劃中哪個(gè)表應(yīng)該為驅(qū)動(dòng)表：在執(zhí)行計(jì)劃中，需要知道哪個(gè)操作是先執(zhí)行的，哪個(gè)操作是后執(zhí)行的，這對(duì)于判斷哪個(gè)表為驅(qū)動(dòng)表有用處。
判斷之前，如果對(duì)表的訪(fǎng)問(wèn)是通過(guò)rowid，且該rowid的值是從索引掃描中得來(lái)得，則將該索引掃描先從執(zhí)行計(jì)劃中暫時(shí)去掉。然后在執(zhí)行計(jì)劃剩下的部分中，判斷執(zhí)行順序的指導(dǎo)原則就是：最右、最上的操作先執(zhí)行。具體解釋如下：
得到去除妨礙判斷的索引掃描后的執(zhí)行計(jì)劃：
- Execution Plan
- SELECT STATEMENT Optimizer=CHOOSE
- 0 MERGE JOIN
- 1 SORT (JOIN)
- 2 NESTED LOOPS
- 3 TABLE ACCESS (FULL) OF 'B'
- 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
- 1 SORT (JOIN)
- 7 TABLE ACCESS (FULL) OF 'C'
看執(zhí)行計(jì)劃的第3列，即字母部分，每列值的左面有空格作為縮進(jìn)字符。在該列值左邊的空格越多，說(shuō)明該列值的縮進(jìn)越多，該列值也越靠右。如上面的執(zhí)行計(jì)劃所示：第一列值為6的行的縮進(jìn)最多，即該行最靠右;第一列值為4、5的行的縮進(jìn)一樣，其靠右的程度也一樣，但是第一列值為4的行比第一列值為5的行靠上;談?wù)撋舷玛P(guān)系時(shí)，只對(duì)連續(xù)的、縮進(jìn)一致的行有效。
從這個(gè)圖中我們可以看到，對(duì)于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS (FULL) OF 'B'，所以這一操作先執(zhí)行，所以該操作對(duì)應(yīng)的B表為第一個(gè)驅(qū)動(dòng)表(外部表)，自然，A表就為內(nèi)部表了。
從圖中還可以看出，B與A表做嵌套循環(huán)后生成了新的row source ，對(duì)該row source進(jìn)行來(lái)排序后，與C表對(duì)應(yīng)的排序了的row source(應(yīng)用了C.col3 = 5限制條件)進(jìn)行MSJ連接操作。所以從上面可以得出如下事實(shí)：B表先與A表做嵌套循環(huán)，然后將生成的row source與C表做排序—合并連接。
通過(guò)分析上面的執(zhí)行計(jì)劃，我們不能說(shuō)C表一定在B、A表之后才被讀取，事實(shí)上，B表有可能與C表同時(shí)被讀入內(nèi)存，因?yàn)閷⒈碇械臄?shù)據(jù)讀入內(nèi)存的操作可能為并行的。事實(shí)上許多操作可能為交叉進(jìn)行的，因?yàn)镺racle讀取數(shù)據(jù)時(shí)，如果就是需要一行數(shù)據(jù)也是將該行所在的整個(gè)數(shù)據(jù)塊讀入內(nèi)存，而且還有可能為多塊讀。
看執(zhí)行計(jì)劃時(shí)，我們的關(guān)鍵不是看哪個(gè)操作先執(zhí)行，哪個(gè)操作后執(zhí)行，而是關(guān)鍵看表之間連接的順序(如得知哪個(gè)為驅(qū)動(dòng)表，這需要從操作的順序進(jìn)行判斷)、使用了何種類(lèi)型的關(guān)聯(lián)及具體的存取路徑(如判斷是否利用了索引)
在從執(zhí)行計(jì)劃中判斷出哪個(gè)表為驅(qū)動(dòng)表后，根據(jù)我們的知識(shí)判斷該表作為驅(qū)動(dòng)表(就像上面判斷ABC表那樣)是否合適，如果不合適，對(duì)SQL語(yǔ)句進(jìn)行更改，使優(yōu)化器可以選擇正確的驅(qū)動(dòng)表。
對(duì)于RBO優(yōu)化器：
在Oracle文檔上說(shuō)：對(duì)于RBO來(lái)說(shuō)，以from 子句中從右到左的順序選擇驅(qū)動(dòng)表，即最右邊的表為第一個(gè)驅(qū)動(dòng)表，這是其英文原文：All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。
不過(guò)，在我做的測(cè)試中，從來(lái)也沒(méi)有驗(yàn)證過(guò)這種說(shuō)法是正確的。我認(rèn)為，即使在RBO中，也是有一套規(guī)則來(lái)決定使用哪種連接類(lèi)型和哪個(gè)表作為驅(qū)動(dòng)表，在選擇時(shí)肯定會(huì)考慮當(dāng)前索引的情況，還可能會(huì)考慮where 中的限制條件，但是肯定是與where中限制條件的位置無(wú)關(guān)。
測(cè)試 ：如果我創(chuàng)建3個(gè)表：
- create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
- create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
- create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
- create index inx_col12A on a(col1,col2);
執(zhí)行Oracle優(yōu)化查詢(xún)：
- select A.col4
- from B, A, C
- where B.col3 = 10
- and A.col1 = B.col1
- and A.col2 = C.col2
- and C.col3 = 5;
- Execution Plan
- SELECT STATEMENT Optimizer=RULE
- 0 MERGE JOIN
- 1 SORT (JOIN)
- 2 NESTED LOOPS
- 3 TABLE ACCESS (FULL) OF 'B'
- 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
- INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
- 1 SORT (JOIN)
- 7 TABLE ACCESS (FULL) OF 'C'
- select A.col4
- from B, A, C
- where A.col1 = B.col1
- and A.col2 = C.col2;
- Execution Plan
- SELECT STATEMENT Optimizer=RULE
- 0 MERGE JOIN
- 1 SORT (JOIN)
- 2 NESTED LOOPS
- 3 TABLE ACCESS (FULL) OF 'B'
- 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
- 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
- 1 SORT (JOIN)
- 7 TABLE ACCESS (FULL) OF 'C'
將A表上的索引inx_col12A刪除后：
- select A.col4
- from B, A, C
- where A.col1 = B.col1
- and A.col2 = C.col2;
- Execution Plan
- SELECT STATEMENT Optimizer=RULE
- MERGE JOIN
- 1 SORT (JOIN)
- 2 MERGE JOIN
- 3 SORT (JOIN)
- 4 TABLE ACCESS (FULL) OF 'C'
- 3 SORT (JOIN)
- 6 TABLE ACCESS (FULL) OF 'A'
- 1 SORT (JOIN)
- 8 TABLE ACCESS (FULL) OF 'B'
通過(guò)上面的這些例子，使我對(duì)Oracle文檔上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”這句話(huà)持懷疑態(tài)度。此時(shí)，我也不能使用hints來(lái)強(qiáng)制優(yōu)化器使用nested loop，如果使用了hints，這樣就自動(dòng)使用CBO優(yōu)化器，而不是RBO優(yōu)化器了。
當(dāng)前文章：Oracle優(yōu)化查詢(xún)中所用到的語(yǔ)句有哪些？
URL分享：http://www.dlmjj.cn/article/cdjjced.html
