經(jīng)典Oralce-SQL語(yǔ)句優(yōu)化

SQL語(yǔ)句處理過(guò)程1、OpenCursor2、ParseStatement3、ExecuteStatement4、FetchValueFromCursorParseStatement搜索LibraryCache檢查語(yǔ)法檢查數(shù)據(jù)源獲取分析鎖檢查權(quán)限確定執(zhí)行規(guī)劃ExecuteStatement給綁定變量賦值（Attention）應(yīng)用執(zhí)行計(jì)劃進(jìn)行必要的I/O和排序動(dòng)作AfterExecute從查詢獲得結(jié)果，并將結(jié)果傳給變量。將SQL執(zhí)行規(guī)劃放在LibraryCache將從數(shù)據(jù)庫(kù)獲取的數(shù)據(jù)(表及索引中間結(jié)果)放在BufferCacheSQL共享為了不重復(fù)解析相同的SQL語(yǔ)句，在第一次解析之后，Oracle將SQL語(yǔ)句存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA的共享池中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶共享。因此，當(dāng)你執(zhí)行一個(gè)SQL語(yǔ)句(有時(shí)被稱(chēng)為一個(gè)游標(biāo))時(shí)，如果它和之前的執(zhí)行過(guò)的語(yǔ)句完全相同，Oracle就能很快獲得已經(jīng)被解析的語(yǔ)句以及執(zhí)行路徑。Oracle的這個(gè)功能大大地提高了SQL的分析性能并節(jié)省了內(nèi)存的使用。當(dāng)你向Oracle提交一個(gè)SQL語(yǔ)句，Oracle會(huì)首先在這塊內(nèi)存中查找相同的語(yǔ)句。這里需要注明的是，Oracle對(duì)兩者采取的是一種嚴(yán)格匹配，要達(dá)成共享，SQL語(yǔ)句必須完全相同(包括空格，大小寫(xiě)，換行等)。共享的語(yǔ)句必須滿足三個(gè)條件：1、字符級(jí)的比較當(dāng)前被執(zhí)行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同，如以下三條SQL不能共享：Select...FromEmpSelect...FromEmpSelect...FromEmp2、所指的對(duì)象必須完全相同用戶A：Select...FromEmp用戶BSelect...FromEmp這兩條SQL由于所指對(duì)象是各用戶的，對(duì)象不同，雖然SQL完全相同，但卻不能共享。3、使用相同的名字的綁定變量以下兩個(gè)語(yǔ)句是不同的(即使在運(yùn)行時(shí)，賦于不同的綁定變量相同的值)Selectpin,nameFrompeopleWherepin=:blk1.ot_ind;Selectpin,nameFrompeopleWherepin=:blk1.ov_ind;共享SQL優(yōu)勢(shì)：減少Parse動(dòng)態(tài)調(diào)整內(nèi)存增進(jìn)內(nèi)存使用效率在Oracle9i系統(tǒng)中，將參數(shù)Cursor_Sharing設(shè)為Similar或Force，則優(yōu)化器會(huì)自動(dòng)地將數(shù)值轉(zhuǎn)化為綁定變量，如：SelectNameFromEmpWhereID=1234;SelectNameFromEmpWhereID=5678;轉(zhuǎn)化為：SelectNameFromEmpWhereID=:SYS_B_O從而共享語(yǔ)句。

注意：由于對(duì)綁定變量的賦值是在SQL語(yǔ)句的執(zhí)行階段，而執(zhí)行規(guī)劃的制定是在分析階段，因此，使用綁定變量有可能使得制定的執(zhí)行規(guī)劃并不是最優(yōu)的執(zhí)行規(guī)劃，或者對(duì)于某些值是優(yōu)秀的執(zhí)行規(guī)劃，而對(duì)于其它值則不是。（基于CBO優(yōu)化器）

舉例如下：Trans表有幾百萬(wàn)條數(shù)據(jù)，列Status有兩種值，1與0，決大多數(shù)為前者，0只有100條，語(yǔ)句Select...FromTransWhereStatus=:v_sta如果采用全表掃描，當(dāng)Status值為0，就不是最優(yōu)執(zhí)行規(guī)劃；而如果用索引掃描，當(dāng)status值為1，就會(huì)出現(xiàn)問(wèn)題。如果不使用綁定變量，當(dāng)使用CBO優(yōu)化器時(shí)，并且表（包含列）分析過(guò)，優(yōu)化器就會(huì)參照Histogram信息，制定正確執(zhí)行規(guī)劃。如果使用綁定變量，則無(wú)論如何都不會(huì)參照Histogram信息。對(duì)于OLTP類(lèi)型數(shù)據(jù)庫(kù)，Cursor_Sharing設(shè)Similar、Force有助于提升性能，特別是基于RBO優(yōu)化方法。而對(duì)數(shù)據(jù)倉(cāng)庫(kù)，則Cursor_Sharing應(yīng)為Exact。這是由于內(nèi)存對(duì)數(shù)據(jù)倉(cāng)庫(kù)不是問(wèn)題，而性能至關(guān)重要。在Oracle9i之前，對(duì)于綁定變量，由于在評(píng)估之前，無(wú)法知道變量值，值只在執(zhí)行計(jì)劃已制定后才傳給游標(biāo)，在這種情況下，優(yōu)化器很可能做出錯(cuò)誤選擇。在Oracle9i，分析語(yǔ)句之前，優(yōu)化器會(huì)首先查看PGA中綁定變量值，再制定執(zhí)行規(guī)劃（通過(guò)參數(shù)_potim_peek_user_binds控制）。注意：以上只適用于Hard_Parse情況，即執(zhí)行規(guī)劃根據(jù)第一個(gè)綁定變量值確定。Explain方案的解釋規(guī)則由里到外，由高到低?！坝衫锏酵狻辈荒苓`反“由高到低”Selectstatement5nestedloops4tableaccessfullsales1tableaccessbyrowid3indexuniquescan2And-EuqalSelect/...+And_Euqal(T1I1I2).../C1FromT1WhereC1=0AndC2=0SelectStatementTableAccess(ByIndexRowid)Of'T1'

And-EQUAL

Index(RangeScan)Of'I1'Index(RangeScan)Of'I2'在合并索引的情況下，驅(qū)動(dòng)索引的選擇對(duì)于性能有很大的影響。一般優(yōu)化器選擇Where條件第一個(gè)索引作為驅(qū)動(dòng)索引。以上SQL，如果列C2選擇性比C1強(qiáng)，則條件改為：WhereC2=0AndC1=0性能會(huì)更好。BitmapAndSelect/...+Index_Combine(T1I1I2).../C1,C2FromT1WhereC1=0AndC2=0SelectStatementBitmapConversion(ToRowids)

BitmapAnd

BitmapIndexOf'I1'BitmapIndexOf'I2'CONCATENATIONSelect/...+Use_Concat.../C1FromT1WhereC2=0OrC3=0SelectStatement

CONCATENATION

TableAccess(Full)Of'T1'TableAccess(Full)Of'T1'CONNECTBYSelectC1,C2FromT1ConnectByC1=PriorC2StartWithC1=0;SelectStatement

CONNECTBY

TableAccess(Full)Of'T1'TableAccess(ByUserRowid)Of'T1'TableAccess(Full)Of'T1'COUNT(STOPKEY)SelectRownumFromT1WhereRownum<10;SelectStatement

Count(Stopkey)

TableAccess(Full)Of'T1'FILTERSelectC2FromT1WhereExist(SelectC1FromT2WhereT1.C1=T2.C1)SelectStatement

FILTER

TableAccess(Full)Of'T1'TableAccess(Full)Of'T2'HashJoinSelect/...+Use_Hash(T1T2).../T1.C2,T2.C2FromT1,T2WhereT1.C1=T2.C1SelectStatement

HashJoin

TableAccess(Full)Of'T1'TableAccess(Full)Of'T2'HashJoin(Anti)SelectC2FromT1WhereC1NotIn(Select/...+Hash_AJ.../C1FromT2)SelectStatement

HashJoin(Anti)

TableAccess(Full)Of'T1'ViewOf'VW_NSO_1'TableAccess(Full)Of'T2'HashJoin(Semi)SelectC2FromT1WhereExists(Select/...+Hash_sj.../C1FromT2WhereT1.C1=T2.C1)SelectStatement

HashJoin(Semi)

TableAccess(Full)Of'T1''T1'TableAccess(Full)Of'T1''T2'

IndexFastFullScanSelect/...+Index_Ffs(T1I1).../C1FromT1WhereC1IsNotNullSelectStatementIndex(FastFullScan)Of'I1'IndexSkipScanCreateIndexI1OnT1(C1,C2)Select/...+Index_SS(T1I1).../C1FromT1WhereC2=0;SelectStatement

Index(SkipScan)Of'I1‘只在9i才實(shí)現(xiàn)，只適用于首列值數(shù)量有限的情況。對(duì)首列的每個(gè)值，使用選擇的尾隨列查找索引。IndexUniqueScanSelect/...+Index(T1I1).../C1FromT1WhereC1=10;SelectStatement

Index(UniqueScan)Of'I1'InlistIteratorSelectC1FromT1WhereC1IN(10,20,30,40)SelectStatement

InlistIterator

Index(RangeScan)Of'I1'MERGEJoinSelect/...+Use_Merge(T1T2).../T1.C2,T2.C2FromT1,T2WhereT1.C1=T2.C1;SelectStatement

MergeJoinSort(Join)TableAccess(Full)Of'T1'Sort(Join)TableAccess(Full)Of'T2'MergeJoinAntiSelectC2FromT1WhereC1NotIn(Select/...+Merge_AJ.../C1FromT2)SelectStatement

MergeJoin(Anti)

Sort(Join)TableAccess(Full)Of'T1'Sort(Unique)ViewOf'VW_NSO_1'TableAccess(Full)Of'T2'MergeJoinSemiSelectC2FromT1WhereC1Exists(Select/...+MERGE_SJ.../C1FromT2WhereT1.C1=T2.C1);SelectStatement

MergeJoin(Semi)

Sort(Join)TableAccess(Full)Of'T1'Sort(Unique)TableAccess(Full)Of'T2'NestedLoopsSelect/...+Use_nl(T1T2).../T1.C2,T2.C2FromT1,T2WhereT1.C1=T2.C1SelectStatement

NestedLoops

TableAccess(Full)Of'T1'TableAccess(Full)Of'T2'NestedLoopsAntiSelectC2FromT1WhereC1NotIn(Select/...+NL_AJ.../C1FromT2)SelectStatementNestedLoops(Anti)TableAccess(Full)Of'T1'TableAccess(Full)Of'T2'NestedLoopsSemiSelectC2FromT1WhereExists(Select/...+NL_SJ.../C1FromT2WhereT1.C1=T2.C1);SelectStatement

NestedLoops(Semi)

TableAccess(Full)Of'T1'TableAccess(Full)Of'T2'PartitionRangeAllSelectC1FromT1;SelectStatement

PartitionRange(All)

TableAccess(Full)Of'T1'PartitionRangeInlistSelectC1FromT1WhereC1IN(10,20);SelectStatement

PartitionRange(Inlist)

TableAccess(Full)Of'T1'PartitionRangeIteratorSelectC1FromT1WhereC1>8AndC1<12;SelectStatement

PartitionRange(Iterator)

TableAccess(Full)Of'T1'Sort(Join)Select/...+Use_Merge(T1T2).../T1.C2,T2.C2FromT1,T2WhereT1.C1=T2.C1;SelectStatementMergeJoin

Sort(Join)TableAccess(Full)Of'T1'

Sort(Join)TableAccess(Full)Of'T2'TableAccessByIndexRowidSelectC2FromT1WhereC1=0;SelectStatementTableAccess(BYIndexRowid)Index(RangeScan)Of'I1'TableAccessFullSelectC1FromT1;SelectStatement

TableAccess(Full)Of'T1'

Union-AllSelectC1FromT1UnionAllSelectC1FromT2;SelectStatement

Union-All

TableAccess(Full)Of'T1'TableAccess(Full)Of'T2'ViewSelect/...+No_Merge(v1).../...From(SelectC1FromT1)v1;SelectStatement

View

TableAccess(Full)Of'T1'Hint使用Hint只能用于Delete、Update、Select之后，Update語(yǔ)句則忽略Hint。Hint只對(duì)其存放的部分有效，放在子查詢中只對(duì)子查詢有效；放在主查詢中只對(duì)主查詢有效；放在Union查詢中只對(duì)其存放部分有效。如果語(yǔ)句使用Alias，則在Hint引用表時(shí)必須引用Alias，而不能引用表名。對(duì)于多個(gè)Hint中間用空格分開(kāi)。All_Rows、First_Rows：使用CBO并分別以最佳吞吐量和最短反應(yīng)時(shí)間為目標(biāo)，不管是否含有分析數(shù)據(jù)。And_Equal(TableIndex1Index2...):使用索引合并。Append：使用DirectLoad方法，直接插入表末尾，高位標(biāo)志之后。Tablespace、Table必須用Nologging選項(xiàng)創(chuàng)建。Cache（Table）：將通過(guò)全表掃描的表放在LRU列表前端。Full（Table）：強(qiáng)迫對(duì)指定表進(jìn)行全表掃描。Hash_AJ、Merge_AJ：強(qiáng)迫反聯(lián)結(jié)語(yǔ)句(含有NotIn、NotExists的語(yǔ)句)使用HashJoin、MergeJoin、NestedLoops。提示必須放在子句中?；蛘邔?shù)Always_Anti_Join設(shè)為Merge或Hash。Hash_SJ、Merge_SJ：使用半聯(lián)結(jié)(對(duì)于含有In、Exists子查詢，首先消除內(nèi)部表的冗余值，再使用聯(lián)結(jié))?；蛘邔?shù)Always_Semi_Join設(shè)為Merge或Hash。提示必須放在子句中。Index（TableIndex）：強(qiáng)迫對(duì)指定表進(jìn)行索引掃描。Index_Desc(TableIndex)：強(qiáng)迫索引降序掃描。默認(rèn)使用升序掃描。Index_Ffs(TableIndex)：強(qiáng)迫索引全掃描，而不是表全掃描。與Index全掃描不同，快速全掃描可以使用多塊讀技術(shù)。Merge(Table)：合并視圖。與No_Merge相反。NoIndex（TableIndex）禁止使用索引。NoReWrite禁止查詢重寫(xiě)。如使用物化視圖進(jìn)行查詢重寫(xiě)。Ordered要求按From語(yǔ)句中表的順序?qū)Ρ磉M(jìn)行連接，忽略表的索引等級(jí)。ReWrite：使用物化視圖進(jìn)行查詢重寫(xiě)。Leading（Table）指定聯(lián)結(jié)次序中的首表。Use_Hash(Table1Table2)、Use_Merge(Table1Table2)、Use_NL(Table1Table2)：分別使用HashJoin、MergeJoin、NestedLoops。查詢轉(zhuǎn)換簡(jiǎn)單視圖合并，視圖合并步驟如下：1、將主查詢涉及的視圖用視圖中的基表替代。2、對(duì)主查詢的視圖選擇列、視圖條件列用視圖基表列替代。3、將視圖的Where條件加到主查詢的Where條件中。4、將視圖GroupBy與Having條件附到Where條件之后。合并后的表的順序與視圖中的表的順序相同。復(fù)雜視圖合并：視圖中必須包含Union，ConnectBy，Distinct，GroupBy，Rownum，以及匯集函數(shù)。主要是為了避免出現(xiàn)：Max(Max(Val))9i加入Merge提示才能部分實(shí)現(xiàn)。謂詞下推和上移：復(fù)雜查詢往往包含多個(gè)視圖與子查詢，在這些視圖和子查詢中包含多個(gè)謂詞。Oracle可將謂詞移入或移出視圖，以產(chǎn)生新的高效查詢。如：CreateViewEmp_AggAsSelectDeptno,Avg(Sal)Avg_SalFromEmpGroupByDeptno;SQL：SelectDeptno,Avg_SalFromEmp_AggWhereDeptno=10;Oracle會(huì)將謂詞Deptno=10推進(jìn)該視圖，將查詢轉(zhuǎn)換為：SelectDeptno,Avg(Sal)FromEmpWhereDeptno=10GroupByDeptno;子查詢“展平”：將不同類(lèi)型的子查詢轉(zhuǎn)變?yōu)槁?lián)接、半聯(lián)接或反聯(lián)接。主要用于含：In、NotIn、Exist、NotExist的語(yǔ)句?！鞍肼?lián)接”是一種特殊類(lèi)型的聯(lián)接，適用于含有In或Exists的語(yǔ)句，它消除了聯(lián)接中來(lái)自內(nèi)表的冗余值RBO：基于規(guī)則優(yōu)化器以一套預(yù)先定義的規(guī)則確定SQL語(yǔ)句對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)路徑在以下情況下RDBMS內(nèi)核會(huì)使用RBO：Optimizer_Mode=RuleOptimizer_Mode=Choose并且From語(yǔ)句涉及的表全部都沒(méi)分析過(guò)，且不包含除Rule外的其它Hint。使用提示Select/...+Rule.../

CBO：基于成本優(yōu)化器CBO優(yōu)化器使用數(shù)據(jù)字典表：DBA_TableS,DBA_Index,DBA_TAB_Columns查詢到的信息進(jìn)行成本估價(jià)，進(jìn)而構(gòu)造出最優(yōu)的執(zhí)行計(jì)劃。默認(rèn)情況下以最佳吞吐量為目標(biāo)。注意：如果優(yōu)化程序?qū)QL選擇基于開(kāi)銷(xiāo)的方法，并且該語(yǔ)句所涉及的表沒(méi)有任何統(tǒng)計(jì)數(shù)據(jù)，那么優(yōu)化程序?qū)⑹褂脙?nèi)部信息（諸如分配給表盤(pán)區(qū)數(shù)）來(lái)估計(jì)其他統(tǒng)計(jì)信息。在下列情況下使用CBO優(yōu)化器：Optimizer_Mode=Choose,并且至少一個(gè)表曾經(jīng)分析過(guò),優(yōu)化器選擇基于開(kāi)銷(xiāo)的方法,并以最佳吞吐量為目標(biāo)。Optimizer_Mode=First_Rows或All_Rows，不管數(shù)據(jù)字典中是否存在統(tǒng)計(jì)信息，優(yōu)化器都將選擇基于開(kāi)銷(xiāo)的方法，前者以最佳響應(yīng)時(shí)間為目標(biāo)，而后者則以最佳吞吐量為目標(biāo)。除Rule之外提示，如Select/...+First_Rows.../。CBO執(zhí)行規(guī)劃制定查詢轉(zhuǎn)換：包括視圖合并；子查詢轉(zhuǎn)換為聯(lián)結(jié)；用實(shí)體化視圖進(jìn)行查詢重寫(xiě)成本評(píng)估：主要是對(duì)選擇性、基數(shù)、開(kāi)銷(xiāo)進(jìn)行評(píng)估。產(chǎn)生規(guī)劃程序，比較成本，產(chǎn)生最優(yōu)執(zhí)行計(jì)劃。Analyze命令:AnalyzeTableEmpEstimateStatisticsSample5PercentForAllIndexedColumns；AnalyzeIndexEmp_indEstimateStatisticsSample5PercentForAllIndexedColumns；AnalyzeTableEmpComputeStatisticsForAllIndexedColumns;AnalyzeTableEmpDeleteStatistics；以下參數(shù)將影響RBO制定規(guī)劃：Compatible：設(shè)為當(dāng)前版本可使優(yōu)化器有效得利用當(dāng)前版本的新特性。Sort_Area_Size，越低越有可能采用NestedLoops。Hash_Area_Size，一般為排序區(qū)2倍，為使用散列聯(lián)結(jié)，至少要1M，同時(shí)Hash_Join_Enabled為T(mén)rue。Optimizer_Mode_Enable，啟用新優(yōu)化器特性，比如8.1.7Optimize_Index_Caching，值范圍0—100之間。比如50，它將告訴優(yōu)化器有50%索引掃描需要的數(shù)據(jù)會(huì)在內(nèi)存中Optimizer_Index_Cost_Adj，正常值為100，可設(shè)低于或高于100。比如10，表示優(yōu)化器在估計(jì)索引掃描的開(kāi)銷(xiāo)時(shí)，將其定為正常值的10%。Query_Rewrite_Enable，啟用基于函數(shù)索引，以及使用物化視圖進(jìn)行查詢重寫(xiě)功能Always_Semi_Join，該參數(shù)主要用于調(diào)解含Exists的語(yǔ)句。Always_Anti_Join，該參數(shù)主要用于調(diào)解含NotIn的語(yǔ)句。為使用如下數(shù)據(jù)庫(kù)功能，必須使用CBO：StoredOutline、HashJoin、IOT、Function-BasedIndex、MaterializedViews、BitmapIndex、Histogram、Hints。如果表未分析過(guò)，為使用以上功能項(xiàng)，必須使用Hint。Hint能夠激活CBO，并使用內(nèi)部信息（諸如分配給表盤(pán)區(qū)數(shù)）進(jìn)行數(shù)據(jù)收集。RBO選擇索引規(guī)則：唯一性索引對(duì)于多列索引，列數(shù)越多，級(jí)別越高單列索引等式匹配比不等式匹配或模糊匹配的等級(jí)要高如果用到的索引都是單列非唯一性索引，則優(yōu)化器選擇合并索引。

RBO聯(lián)結(jié)規(guī)則在索引等級(jí)相同的情況下，不同表聯(lián)結(jié)順序如下：From語(yǔ)句從右至左；同一張表Where條件應(yīng)用順序從左至右。Where條件中，有索引的非聯(lián)結(jié)條件優(yōu)于聯(lián)結(jié)條件，無(wú)有索引的非聯(lián)結(jié)條件的情況下，根據(jù)From順序結(jié)合聯(lián)結(jié)條件確定聯(lián)結(jié)順序；

無(wú)索引的非聯(lián)結(jié)條件可以被忽略。表的優(yōu)先級(jí)表的優(yōu)先級(jí)與Where條件中涉及索引的優(yōu)先級(jí)相關(guān)聯(lián)。Where條件中對(duì)主鍵與唯一性索引引用使得包含該索引的表優(yōu)先于其它包含非唯一性索引的表。Where條件包含對(duì)所有索引列的等式引用，將優(yōu)先于只對(duì)部分列的引用。Where條件中含有對(duì)雙列索引全部列的引用，將優(yōu)先于對(duì)兩個(gè)單列索引的引用。同等條件下列在From語(yǔ)句最后一位的表將作為驅(qū)動(dòng)表,而不是按索引時(shí)間。根據(jù)Rowid訪問(wèn)單行簇聯(lián)結(jié)主鍵單行訪問(wèn)基于主鍵或唯一鍵的單行訪問(wèn)簇表聯(lián)結(jié)優(yōu)于非簇表聯(lián)結(jié)多列索引優(yōu)于單列索引索引列的有界范圍掃描與LIKE‘A%’。索引列無(wú)界范圍掃描。索引列的ORDERBY。全表掃描。NestedLoop驅(qū)動(dòng)表的全表掃描或索引掃描被驅(qū)動(dòng)表的索引掃描發(fā)現(xiàn)匹配，通過(guò)被驅(qū)動(dòng)表的RowID訪問(wèn)TableAccessFullIndexSCANTableAccessBYRowidNESTEDLOOPOracle假定Nested_Loop優(yōu)于MergeJoin;如果采用Nested_Loop，由于被驅(qū)動(dòng)表必須有索引，因此，即使在Where子句中只有對(duì)該表索引列的約束條件，而非聯(lián)結(jié)條件的情況下，優(yōu)化器仍然選擇其它表作為驅(qū)動(dòng)表。驅(qū)動(dòng)表選擇(對(duì)于涉及到聯(lián)結(jié)的兩張同時(shí)包含索引的情況)：根據(jù)包含在Where條件中的表的索引的選擇性以及涉及的列的數(shù)量和匹配條件選擇驅(qū)動(dòng)表。當(dāng)驅(qū)動(dòng)表只返回一行時(shí)（唯一性索引），優(yōu)化程序會(huì)選擇對(duì)被驅(qū)動(dòng)表進(jìn)行全表掃描。Nested_Loop特征聯(lián)結(jié)性能的關(guān)鍵是表聯(lián)結(jié)的次序。與Nested_Loop有關(guān)的提示包括：Ordered,Full,Index,Use_nl。改變索引的可用性，也會(huì)影響Nested_Loop驅(qū)動(dòng)表的選擇。常用方法包括：對(duì)數(shù)字或日期型的列加零，對(duì)字符型加空格。MergeJoin對(duì)聯(lián)結(jié)的每個(gè)表進(jìn)行TableAccessFull進(jìn)行SortJoin，對(duì)以上結(jié)果進(jìn)行排序。進(jìn)行MergeJoin，將SortJoin結(jié)果合并。TableAccessFullTableAccessFullSortJoinSortJoinMergeJoinMergeJoin特征從查詢返回第一行較慢。（集操作）它的結(jié)果集不會(huì)緩存在SGA。處理查詢也許需要分配臨時(shí)段。適用于全表掃描比索引掃描更可取的情況。如：表很小，一次物理讀可完成；對(duì)于多表聯(lián)結(jié)，如果聯(lián)結(jié)表在聯(lián)結(jié)條件及非聯(lián)結(jié)條件中無(wú)任何有索引的約束條件，優(yōu)化器將選擇MergeJoin；HashJoin在HashJoin里，兩個(gè)表都被全表掃描。Oracle讀取涉及聯(lián)結(jié)的其中一個(gè)表，并在內(nèi)存創(chuàng)建來(lái)自表的聯(lián)結(jié)列的值的位圖。隨著行的讀取，它們按聯(lián)結(jié)關(guān)鍵字被分組送入散列功能分區(qū)。對(duì)第二張表執(zhí)行同樣的散列功能分區(qū)，同時(shí)，第一張表創(chuàng)建的位圖被用作過(guò)濾器。HashJoin聯(lián)結(jié)性能要比MergeJoin好。特別是非常巨大的表。HashJoin只對(duì)等式聯(lián)結(jié)有效，而對(duì)于不等或范圍聯(lián)結(jié)則無(wú)效；而對(duì)MergeJoin，等式聯(lián)結(jié)與不等式及范圍聯(lián)結(jié)都有效。

聯(lián)結(jié)順序假設(shè)表T1、T2、t3，每個(gè)表有記錄100000條，按NAME均勻分部，每NAME值有10條記錄，每100條記錄占1數(shù)據(jù)塊，每張表占1000個(gè)數(shù)據(jù)塊。每個(gè)表在NAME，SALARY列都建有索引。SQL：Select...fromT1，T2，t3whereT1.name=T2.nameandT2.name=andT1.salary=1000andt3.salary=2000聯(lián)結(jié)順序有6種。T1.salary=10006條t3.salary=2000100條順序T1、T2、T3訪問(wèn)T1(通過(guò)SALARY索引訪問(wèn))，需3＋6個(gè)邏輯IO。訪問(wèn)T2(通過(guò)NAME索引訪問(wèn))：6...3+6...10訪問(wèn)T3(通過(guò)SALARY索引訪問(wèn))：3＋100總共需190邏輯IO。順序T1、T3、T2訪問(wèn)T1(通過(guò)SALARY索引訪問(wèn))，需3＋6個(gè)邏輯IO。訪問(wèn)T3(通過(guò)SALARY索引訪問(wèn))：3＋100如果T3(通過(guò)NAME索引訪問(wèn))：6...13T1、T3聯(lián)結(jié)結(jié)果記錄數(shù)在0－－60之間，因此，訪問(wèn)T2(通過(guò)NAME索引訪問(wèn))地IO在0到60...3+60...10之間。而為0概率很大?？偣睮O可能為：87或112順序T2、T1、T3T2全表掃描需10000個(gè)物理IOT1索引掃描(通過(guò)SALARY索引訪問(wèn))，需3＋6個(gè)邏輯IO。T3索引掃描(通過(guò)SALARY索引訪問(wèn))，3＋100個(gè)邏輯IO?？侷O量非常大。T2、T1、T3之間通過(guò)Hash聯(lián)結(jié)，如果采用NESTEDLOOP，T1邏輯IO將超過(guò)30000。順序T2、T3、T1T2全表掃描需10000個(gè)物理IOT3索引掃描(通過(guò)SALARY索引訪問(wèn))，3＋100個(gè)邏輯IO。T1索引掃描(通過(guò)SALARY索引訪問(wèn))，需3＋6個(gè)邏輯IO?？侷O量非常大。T2、T1、T3之間通過(guò)Hash聯(lián)結(jié)，如果采用NESTEDLOOP，T1邏輯IO將超過(guò)30000。順序T3、T2、T1訪問(wèn)T3(通過(guò)SALARY索引訪問(wèn))：3＋100訪問(wèn)T2(通過(guò)NAME索引訪問(wèn))：13...100訪問(wèn)T1(通過(guò)SALARY索引訪問(wèn))，需3＋6總共需1412IO順序T3、T1、T2訪問(wèn)T3(通過(guò)SALARY索引訪問(wèn))，需103個(gè)邏輯IO。訪問(wèn)T1(通過(guò)SALARY索引訪問(wèn))：3＋6如果T3(通過(guò)NAME索引訪問(wèn))：100...13T1、T3聯(lián)結(jié)結(jié)果記錄數(shù)在0－－60之間，因此，訪問(wèn)T2(通過(guò)NAME索引訪問(wèn))地IO在0到60...3+60...10之間。而為0概率很大?？偣睮O可能為：112多表聯(lián)結(jié)順序規(guī)則選擇返回行比較少，并且，有等式聯(lián)結(jié)條件的表作為聯(lián)結(jié)第一張表。如果第一張表的等式聯(lián)結(jié)條件與多個(gè)表相聯(lián)系，應(yīng)選擇返回中間結(jié)果較少的表作為聯(lián)結(jié)第二張表。根據(jù)聯(lián)結(jié)條件依次選擇第三、四表。選擇聯(lián)結(jié)順序時(shí)，可適當(dāng)參照索引，盡量避免全表掃描。分步式查詢優(yōu)化如果Oracle需要多個(gè)遠(yuǎn)程表，優(yōu)化程序首先確定所有表是否在同一位置，如果在同一位置，將整個(gè)查詢送到遠(yuǎn)程站點(diǎn)，只返回最終結(jié)果集；如果遠(yuǎn)程表在多個(gè)位置，優(yōu)化程序?qū)⒉樵兎纸鉃槎鄠€(gè)SQL，分別訪問(wèn)遠(yuǎn)程表，將中間結(jié)果返回本地進(jìn)行聯(lián)結(jié)。聯(lián)結(jié)執(zhí)行的位置以稱(chēng)驅(qū)動(dòng)站點(diǎn)。對(duì)于遠(yuǎn)程表，用戶只能使用聯(lián)結(jié)次序和聯(lián)結(jié)操作提示，而不能使用訪問(wèn)方法提示。對(duì)基于RBO的優(yōu)化器，由于本地優(yōu)化程序無(wú)法知道遠(yuǎn)程表的索引情況，只會(huì)以“對(duì)遠(yuǎn)程表進(jìn)行全表掃描”制定執(zhí)行計(jì)劃。對(duì)遠(yuǎn)程表實(shí)際操作方法根據(jù)語(yǔ)句傳送到遠(yuǎn)程后再具體決定。對(duì)基于CBO的優(yōu)化器，本地優(yōu)化程序能夠知道是否存在可用的遠(yuǎn)程表索引，能夠制定更優(yōu)的執(zhí)行規(guī)則。對(duì)基于RBO的優(yōu)化程序，如果本地表在聯(lián)結(jié)列上建有索引，優(yōu)化程序?qū)⒁赃h(yuǎn)程表為驅(qū)動(dòng)表，與本地表進(jìn)行NestedLoops。如果本地表在聯(lián)結(jié)列上無(wú)索引，一般是采取MergeJoin。如果非聯(lián)結(jié)約束條件有對(duì)本地表Unique索引全部列等式引用（即：只返回一行），優(yōu)化器將以本地表為驅(qū)動(dòng)表，與遠(yuǎn)程表進(jìn)行NestedLoops。SQL：SelectDname,EnameFromDept,Emp@RemWhereDept.Deptno=Emp.Deptno將被分解為(MergeJoin)：SelectDeptno,DnameFromDept(本地執(zhí)行);SelectDeptno,EnameFromEmp(遠(yuǎn)程執(zhí)行)。或分解為(使用NestedLoop)：SelectDeptno,DnameFromDeptWhereDeptno=:1(在本地執(zhí)行)，SelectDeptno,EnameFromEmp(遠(yuǎn)程執(zhí)行)。在遠(yuǎn)程：CreateViewDept_EmpAsSelectDname,EnameFromDept@Local,EmpWhereDept.Deptno=Emp.Deptno在本地：Select...FromDept_Emp@Rem優(yōu)化程序?qū)⒈镜乇鞤ept發(fā)送到遠(yuǎn)程數(shù)據(jù)庫(kù)，只聯(lián)結(jié)結(jié)果返回本地。與SQL：Select/...+Driving_site(b).../Dname,EnameFromDepta,Emp@RembWherea.Deptno=b.Deptno執(zhí)行規(guī)劃相同。OuterJoin基于RBO優(yōu)化器下，優(yōu)化器選擇不含(+)的表作為驅(qū)動(dòng)表進(jìn)行NestedLoop，因此，我們又稱(chēng)返回所有行的表為驅(qū)動(dòng)表。由于外聯(lián)結(jié)的返回結(jié)果與普通聯(lián)結(jié)的返回結(jié)果不同，因此，不同的聯(lián)結(jié)順序產(chǎn)生的結(jié)果也不同，這防礙了視圖合并。外聯(lián)結(jié)表的所有條件(聯(lián)結(jié)或非聯(lián)結(jié))都必須是(+)格式，SQL就不是外，它可轉(zhuǎn)換為普通聯(lián)結(jié)：SelectA.Name,B.Address,C.SesFromTest1A,Test2B,Test3CWhereA.Name(+)=B.NameAndA.Num=C.NumExists與InExists相對(duì)于IN有兩點(diǎn)優(yōu)勢(shì)：值傳遞；會(huì)盡可能使用索引進(jìn)行存在判斷Exists只要發(fā)現(xiàn)關(guān)系成立，子查詢就中止。而IN無(wú)論如何都要完全子查詢操作。在執(zhí)行步驟上,Exists語(yǔ)句是外部表驅(qū)動(dòng)內(nèi)部表的存在判斷；而對(duì)于IN語(yǔ)句，則是內(nèi)部表先執(zhí)行，再以內(nèi)部表作為驅(qū)動(dòng)表，進(jìn)行NestedLoop。由于Exists是由外表驅(qū)動(dòng)內(nèi)表做存在判斷，因此，對(duì)于外表較小而內(nèi)表較大的情況使用Exists較好；而IN則是由內(nèi)表驅(qū)動(dòng)外表做NestedLoop，因此，對(duì)于內(nèi)表較小的情況使用IN較好。具體好壞要參照索引狀況。優(yōu)化在對(duì)待子查詢時(shí)一般將其轉(zhuǎn)化為連接查詢，因此在寫(xiě)語(yǔ)句時(shí)盡可能用連接替換子查詢。特別是MergeJoin或HashJoin比NestedLoop聯(lián)結(jié)效果更好的地方。索引失效問(wèn)題Select...FromEmpWhereTo_Char(ID)=‘123’使用全表掃描。Select...FromEmpWhereID=To_Number(‘123’)使用索引掃描。Select...FromEmpWhereIDNot=0使用全表掃描。Select...FromEmpWhereName||‘’=‘ABC’使用全表掃描。Select...FromEmpWhereName=‘ABC’||‘DEF’使用索引掃描。Select...FromEmpOrderByName使用索引掃描。Select...FromEmpOrderByTo_Char(Name)使用全表掃描。

隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換：默認(rèn)轉(zhuǎn)換類(lèi)型是：字符－>數(shù)值，字符―>Date；即等式兩邊如果發(fā)生類(lèi)型不一致，則優(yōu)先選擇將字符類(lèi)型轉(zhuǎn)換為數(shù)值類(lèi)型或Date。如以下語(yǔ)句：Select...FromEmpWhereID=‘123’（ID=To_Number(‘123’)）使用索引掃描。Select...FromEmpWhereName=123（隱含To_Number(Name)=123）使用全表掃描。對(duì)于Like類(lèi)型的查詢，Select...FromEmpWhereIDlike‘123%’不會(huì)用到索引，這是因?yàn)镮D為Number類(lèi)型。這是由于Like只對(duì)字符類(lèi)型的有用。常用優(yōu)化技巧Union、OR與IN一般情況下，用OR或IN替換Union會(huì)取得更好的性能，這是由于使用Union會(huì)產(chǎn)生對(duì)同一操作對(duì)象的多次掃描。OR與IN在執(zhí)行性能上是相同的，但在功能上并不完全相同，雖然在有些場(chǎng)合是可相互替換的。Select...FromT1WhereC1IN(1,2)進(jìn)行二次索引掃描(C1有索引)或一次全表掃描。Select...FromT1WhereC1=1OrC2(C1)=2；進(jìn)行二次索引掃描(都有索引)或一次全表掃描(至少一個(gè)無(wú)索引)。Select...FromT1WhereC1=1UnionSelect...FromT1WhereC1(C2)=12；進(jìn)行二次索引掃描(都有索引)或一次全表掃描與索引掃描(一個(gè)無(wú)索引)或二次全表掃描(都無(wú)索引，即使是同一列)。如果確實(shí)要用Union，將結(jié)果較小的表放在前面，這樣有利于節(jié)省內(nèi)存消耗。如果你堅(jiān)持要用Or,那就需要返回記錄最少的索引列寫(xiě)在最前