數(shù)據(jù)庫程序員面試分類真題14簡答題1.

SQL語句的執(zhí)行過程有哪些?正確答案：Oracle數(shù)據(jù)庫里SQL語句的執(zhí)行過程可以如下圖所示。

在上圖中，可以看到SQL語句的執(zhí)行過程大致分為以下幾個步驟：

1)當(dāng)用戶提交待執(zhí)行的目標(biāo)SQL后，Oracle首先會對目標(biāo)SQL進(jìn)行解析。在這個過程中，Oracle會先執(zhí)行對目標(biāo)SQL的語法、語義和權(quán)限的檢查：

①語法檢查(SyntaxCheck)是檢查目標(biāo)SQL的拼寫是否正確，例如，錯將關(guān)鍵字“FROM”寫成“FORM”，將“SELECT”寫成“SELET”等。

②語義檢查(SemanticCheck)是檢查SQL語句中的訪問對象是否存在及該用戶是否具備相應(yīng)的權(quán)限。

2)如果目標(biāo)SQL不能通過上述語法、語義和權(quán)限的檢查，那么該目標(biāo)SQL將解析失敗。如果目標(biāo)SQL能通過上述檢查，那么接下來Oracle就會去SGA的共享池中的庫緩存中查找是否存在匹配的共享游標(biāo)。如果找到了匹配的共享游標(biāo)，那么Oracle就會把存儲于該共享游標(biāo)中的解析樹和執(zhí)行計劃直接拿過來重用，這相當(dāng)于跳過了后續(xù)的“查詢轉(zhuǎn)換”和“查詢優(yōu)化”這兩個步驟，直接進(jìn)入“實際執(zhí)行”階段。

3)如果找不到匹配的共享游標(biāo)，就意味著此時沒有可以被重用的解析樹和執(zhí)行計劃，那么接下來整個執(zhí)行過程就進(jìn)入查詢轉(zhuǎn)換這一步。在查詢轉(zhuǎn)換過程中，Oracle會根據(jù)一些規(guī)則來決定是否對目標(biāo)SQL執(zhí)行查詢轉(zhuǎn)換，這些規(guī)則在Oracle不同的版本里不盡相同。在Oracle9i中，查詢轉(zhuǎn)換是獨(dú)立于優(yōu)化器的，它與優(yōu)化器的類型無關(guān)，但是從Oracle10g開始，0racle會對某些類型的查詢轉(zhuǎn)換(比如子查詢展開、復(fù)雜視圖合并等)分別計算經(jīng)過查詢轉(zhuǎn)換后的等價改寫SQL的成本和原始SQL的成本。只有當(dāng)?shù)葍r改寫后SQL的成本值小于未經(jīng)過查詢轉(zhuǎn)換的原始SQL的成本值時，Oracle才會對目標(biāo)SQL執(zhí)行查詢轉(zhuǎn)換。在執(zhí)行完查詢轉(zhuǎn)換后，原目標(biāo)SQL可能就被Oracle改寫了，當(dāng)然也有可能沒有改寫。

4)接下來，Oracle就正式進(jìn)入了查詢優(yōu)化這個步驟。在這個步驟里，Oracle會根據(jù)不同的優(yōu)化器類型(CBO或RBO)采用不同的判斷原則，從執(zhí)行完查詢轉(zhuǎn)換后得到的目標(biāo)SQL的諸多可能的執(zhí)行路徑中選擇一條效率最高的路徑來作為其執(zhí)行計劃，即查詢優(yōu)化的輸入就是執(zhí)行完查詢轉(zhuǎn)換后得到的等價改寫SQL，其輸出就是該目標(biāo)SQL的最終執(zhí)行計劃。

5)在得到了目標(biāo)SQL的執(zhí)行計劃后，接下來Oracle就會根據(jù)執(zhí)行計劃去實際執(zhí)行該SQL，并將執(zhí)行結(jié)果返回給用戶。[考點(diǎn)]游標(biāo)

2.

綁定變量的適用場合有哪些?正確答案：對于綁定變量應(yīng)該根據(jù)系統(tǒng)的類型來決定是否使用綁定變量，具體如下：

1)在高并發(fā)的OLTP系統(tǒng)中，SQL語句重復(fù)執(zhí)行頻度高，但處理的數(shù)據(jù)量較少，結(jié)果集也相對較小，尤其是使用表上的索引來縮小中間結(jié)果集，其解析時間通常會接近或高于執(zhí)行時間，因此，在該場合一定要使用綁定變量，并且最好是使用批量綁定，因為這樣可以有效降低系統(tǒng)硬解析的數(shù)量，這也是OLTP類型的系統(tǒng)在數(shù)據(jù)庫端具備良好的性能和可擴(kuò)展性的前提條件。

2)在OLAP/DSS系統(tǒng)中，SQL語句執(zhí)行次數(shù)相對較少，但返回的數(shù)據(jù)量較大，其SQL語句執(zhí)行時間遠(yuǎn)高于其解析時間，硬解析對系統(tǒng)性能的影響是微乎其微的，因此，使用綁定變量對于總的執(zhí)行時間影響不大，對系統(tǒng)性能的提升也非常有限。

3)對于OLAP和OLTP混合型的應(yīng)用系統(tǒng)，如果有循環(huán)，不管這個循環(huán)是在前臺代碼還是在后臺PL/SQL代碼中，循環(huán)內(nèi)部的SQL語句一定要使用綁定變量，并且最好是使用批量綁定；至于循環(huán)外部的SQL語句，可以不使用綁定變量。

需要注意的是，對于實際的數(shù)據(jù)庫對象，例如表、視圖等，不能使用綁定變量替換，只能替換字面量。如果對象名是在運(yùn)行時生成的，那么需要對其用字符串拼接，同時，SQL只會匹配已經(jīng)在共享池中存在且相同的對象名。[考點(diǎn)]綁定變量

3.

什么是綁定變量窺探?正確答案：目標(biāo)SQL若不使用綁定變量，則當(dāng)具體輸入值一旦發(fā)生了變化，目標(biāo)SQL的SQL文本就會隨之發(fā)生變化，這樣Oracle就能很容易地計算出對應(yīng)Selectivity和Cardinality的值，進(jìn)而據(jù)此來選擇執(zhí)行計劃。但對于使用了綁定變量的目標(biāo)SQL而言，情況就完全不一樣了，因為現(xiàn)在無論對應(yīng)綁定變量的具體輸入值是什么，目標(biāo)SQL的SQL文本都是一模一樣的。對于使用了綁定變量的目標(biāo)SQL而言，Oracle可以選擇如下兩種方法來決定其執(zhí)行計劃：

1)使用綁定變量窺探(BindPeeking)。

2)如果不使用綁定變量窺探，那么對于那些可選擇率可能會隨著具體輸入值的不同而不同的謂詞條件使用默認(rèn)的可選擇率(例如5%)。

綁定變量窺探是在Oracle9i中引入的，是否啟用綁定變量窺探受隱含參數(shù)“_OPTIM_PEEK_USER_BINDS”的控制，該參數(shù)的默認(rèn)值是TRUE，表示在Oracle9i及其后續(xù)的版本中，默認(rèn)啟用綁定變量窺探。[考點(diǎn)]綁定變量

4.

如何得到已執(zhí)行的目標(biāo)SQL中的綁定變量的值?正確答案：當(dāng)Oracle解析和執(zhí)行含有綁定變量的目標(biāo)SQL時，如果滿足如下兩個條件之一，那么該SQL中的綁定變量的具體輸入值就會被Oracle捕獲：

1)當(dāng)含有綁定變量的目標(biāo)SQL以硬解析的方式被執(zhí)行時。

2)當(dāng)含有綁定變量的目標(biāo)SQL以軟解析或軟軟解析的方式重復(fù)執(zhí)行時，Oracle在默認(rèn)情況下至少得間隔15min才會捕獲一次。這個15min受隱含參數(shù)“_CURSOR_BIND_CAPTURE_INTERVAL”控制，默認(rèn)值為900s，即15min。

需要注意的是，Oracle只會捕獲那些位于目標(biāo)SQL的WHERE條件中的綁定變量的具體輸入值，而對于那些使用了綁定變量的INSERT語句，不管該INSERT語句是否是以硬解析的方式執(zhí)行，Oracle始終不會捕獲INSEKT語句的VALUES子句中對應(yīng)綁定變量的具體輸入值。

查詢視圖V$SQL_BIND_CAPTURE可以得到已執(zhí)行目標(biāo)SQL中綁定變量的具體輸入值。如果V$SQL_BIND_CAPTURE中查不到，那么有可能對應(yīng)的SharedCurrsor已經(jīng)從SharedPool中被清除了，這時候可以嘗試從AWR相關(guān)的數(shù)據(jù)字典表DBA_HIST_SQLSTAT或DBA_HIST_SQLBIND中查詢。[考點(diǎn)]綁定變量

5.

什么是游標(biāo)共享?正確答案：游標(biāo)共享(CursorSharing)是指共享游標(biāo)(SharedCursor)之間的共享，游標(biāo)共享可以實現(xiàn)重用存儲在子游標(biāo)(ChildCursor)中的解析樹和執(zhí)行計劃而不用從頭開始做硬解析，從而提高系統(tǒng)性能。特別是對于同一類型的目標(biāo)SQL更應(yīng)該實現(xiàn)游標(biāo)共享，而使用綁定變量就可以實現(xiàn)游標(biāo)共享。游標(biāo)共享分為常規(guī)游標(biāo)共享和自適應(yīng)游標(biāo)共享。[考點(diǎn)]綁定變量

6.

獲取執(zhí)行計劃有哪幾種方法?正確答案：對于這幾種獲取執(zhí)行計劃的方法有如下結(jié)論：

1)若目標(biāo)SQL需要執(zhí)行很長時間才能返回結(jié)果，則推薦使用EXPLAINPLANFOR來獲取執(zhí)行計劃。

2)若要查詢目標(biāo)SQL的所有子游標(biāo)的執(zhí)行計劃，則推薦使用DBMS_XPLAN.DISPLAY_CURSOR('&SQLID',NULL,'ADVANCEDALLSTATS')或awrsqrpt.sql來獲取執(zhí)行計劃。

3)若要分析SQL語句的內(nèi)部調(diào)用詳情，則推薦使用10046事件。

4)若想確?？吹秸鎸嵉膱?zhí)行計劃，則不能使用EXPLAINPLANFOR和SETAUTOTRACETRACEONLYEXPLAIN。

5)若想獲取到表的訪問次數(shù)，則推薦/*+GATHER_PLAN_STATISTICS*/。

6)若數(shù)據(jù)庫版本大于10g，則對執(zhí)行時間較長的SQL語句推薦使用SQL實時監(jiān)控特性查看html報告。[考點(diǎn)]執(zhí)行計劃

7.

如何得到真實的執(zhí)行計劃?正確答案：在Oracle數(shù)據(jù)庫中判斷得到的執(zhí)行計劃是否準(zhǔn)確，就是看目標(biāo)SQL是否被真正執(zhí)行過，真正執(zhí)行過的SQL所對應(yīng)的執(zhí)行計劃就是準(zhǔn)確的，反之則有可能不準(zhǔn)，因此，通過10046事件及如下的幾種方式得到的執(zhí)行計劃是最準(zhǔn)確的，而從其他方式獲取到的執(zhí)行計劃都有可能不準(zhǔn)確。

SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCEDALLSTATS'));

SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER,'ADVANCEDALLSTATS'));

SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));

這里需要注意的是，雖然SQL*Plus的AUTOTRACE功能有部分是真實執(zhí)行了SQL語句的(例如所有DML語句)，但是由于該命令所顯示的執(zhí)行計劃來源于調(diào)用EXPLAINPLAN命令，所以，其得到的執(zhí)行計劃依然可能不準(zhǔn)確(特別是在使用了綁定變量的情況下)。[考點(diǎn)]執(zhí)行計劃

8.

如何固定SQL執(zhí)行計劃?正確答案：在實際項目中，通常在開發(fā)環(huán)境下，一些SQL執(zhí)行沒有任何功能問題，而當(dāng)生產(chǎn)環(huán)境或生產(chǎn)環(huán)境的數(shù)據(jù)量發(fā)生較大的變化時，其SQL的執(zhí)行效率非常低。此時如果更改SQL，那么可能需要重新修改源程序以及重新編譯程序。如果修改源程序的成本比較大，那么可以使用一些方法在不改變源應(yīng)用程序的情況下更改特定SQL的執(zhí)行計劃并固定下來。因為在SQL語句的執(zhí)行計劃發(fā)生更改時，可能存在性能風(fēng)險。SQL計劃發(fā)生更改的原因有很多，如優(yōu)化程序版本、優(yōu)化程序統(tǒng)計信息、優(yōu)化程序參數(shù)、方案定義、系統(tǒng)設(shè)計和SQL概要文件創(chuàng)建等。有4種方式可以固定和控制執(zhí)行計劃，分別是使用Hint(提示)、OUTLINE(存儲概要)、SQLPROFILE(SQL概要文件)和SPM(SQLPlanManagemem，SQL計劃管理)。[考點(diǎn)]執(zhí)行計劃

9.

什么是半連接(SemiJoin)?正確答案：半連接(SemiJoin)是一種特殊的連接類型，當(dāng)執(zhí)行子查詢展開時，Oracle經(jīng)常會把那些外部WHERE條件為EXISTS、IN或=ANY的子查詢轉(zhuǎn)換為對應(yīng)的半連接。半連接分為嵌套循環(huán)半連接(Hint為NL_SJ)、排序合并半連接(Hint為MERGE_SJ)和哈希半連接(Hint為HASH_SJ)，不過在新版本數(shù)據(jù)庫里，都傾向于使用哈希半連接。哈希半連接也有一些限制條件，例如，只能使用等值連接，不能使用GROUPBY、CONNECTBY、ROWNUM等限制條件。在執(zhí)行計劃中若有關(guān)鍵字“HASHJOINSEMI”，則說明Oracle使用了哈希半連接。[考點(diǎn)]SQL優(yōu)化

10.

什么是反連接(AntiJoin)?正確答案：反連接(AntiJoin)也是一種特殊的連接類型，通常用于從一個表中返回不在另一個數(shù)據(jù)源中的數(shù)據(jù)行。當(dāng)做子查詢展開時，Oracle經(jīng)常會把那些外部WHERE條件為NOTEXISTS、NOTIN或＜＞ALL的子查詢轉(zhuǎn)換成對應(yīng)的反連接。反連接分為嵌套循環(huán)反連接(NESTEDLOOPSANTI，Hint為NL_AJ)、排序合并反連接(MERGEJOINANTI，Hint為MERGE_AJ)和哈希反連接(HASHJOINANTI，Hint為HASH_AJ)。

需要注意的是，NOTIN和＜＞ALL對NULL值敏感，這意味著NOTIN后面的子查詢或者常量集合一旦有NULL值出現(xiàn)，則整個SQL的執(zhí)行結(jié)果就會為NULL，即此時的執(zhí)行結(jié)果將不包含任何記錄。但是，NOTEXISTS對NULL值不敏感，這意味著NULL值對NOTEXISTS的執(zhí)行結(jié)果不會有什么影響。正是因為NOTIN和＜＞ALL對NULL值敏感，所以一旦相關(guān)的連接列上出現(xiàn)了NULL值，此時Oracle如果還按照通常的反連接的處理邏輯來處理，得到的結(jié)果就不對了。為了解決NOTIN和＜＞ALL對NULL值敏感的問題，Oracle推出了改良的反連接，這種反連接能夠處理NULL值，Oracle稱其為Null-AwareAntiJoin，如下例：

執(zhí)行步驟的列Operation的值為“HASHJOINANTINA”，關(guān)鍵字“NA”就是Null-Aware的縮寫，表示這里采用的不是普通的哈希反連接，而是改良后的、能夠處理NULL值的哈希反連接。

在Oracle11gR2中，Oracle是否啟用Null-AwareAntiJoin受隱含參數(shù)“_OPTIMIZER_NULL_AWARE_ANTIJOIN”控制，其默認(rèn)值為TRUE，表示啟用Null-AwareAntiJoin。如果把該參數(shù)的值修改為FALSE，那么表示Oracle就不能再用Null-AwareAntiJoin了，而又因為NOTIN對NULL值敏感，所以O(shè)racle此時也不能用普通的反連接。[考點(diǎn)]SQL優(yōu)化

11.

什么是星型連接(StarJoin)?正確答案：星型連接(StarJoin)通常用于數(shù)據(jù)倉庫類型的應(yīng)用，它是一種單個事實表(FactTable)和多個維度表(DimensionTable)之間的連接。星型連接的各維度表之間沒有直接的關(guān)聯(lián)條件，其事實表和各維度表之間是基于事實表的外鍵列和對應(yīng)維度表的主鍵列之間的連接，并且通常在事實表的外鍵列上還會存在對應(yīng)的位圖索引。[考點(diǎn)]SQL優(yōu)化

12.

表和表之間的關(guān)聯(lián)方式有哪幾種?正確答案：目前為止，無論連接操作符如何，典型的連接類型共有3種：

1)排序合并連接(SortMergeJoin，SMJ)，Oracle6提供。

2)嵌套循環(huán)(NestedLoops，NL)，Oracle6提供。

3)哈希連接(HashJoin，HJ)，也叫散列連接，Oracle7.3新增。

另外，還有一種笛卡兒積(MergeJoinCartesian，MJC)連接，在Oracle6版本的時候就已經(jīng)提供，一般情況下，盡量避免使用。

對于Oracle6提供的群集連接(ClusterJoin)和Oracle8提供的索引連接(IndexJoin)，本書不做介紹。對于DBA來說，掌握這3種表的連接方式可以對SQL優(yōu)化起到至關(guān)重要的作用。對于這3種關(guān)聯(lián)方式的詳細(xì)對比，參考下表。表連接方式嵌套循環(huán)排序合并連接哈希連接Hint/*+USE_NL(T1T2)*/或若/*+LEADING(T1)USE_NL(T2)*/則T1作為驅(qū)動表，T2作為被驅(qū)動表/*+USE_MERGE(T1T2)*//*+USE_HASH(T1T2)*/有無驅(qū)動順序有，驅(qū)動表的順序直接影響SQL語句的性能，應(yīng)該是小的結(jié)果集先訪問，大的結(jié)果集后訪問才能保證被驅(qū)動表的訪問次數(shù)降到最低，從而提升性能(邏輯讀BUFFER差異大)。需要注意的是，應(yīng)該選擇由過濾條件限制返回記錄最少的那張表作為驅(qū)動表，而不是根據(jù)表的大小來選擇驅(qū)動表無有，驅(qū)動表的順序非常重要，直接影響內(nèi)存消耗(0Mere、Used-Mem差異大)。所以，應(yīng)該小表作驅(qū)動，內(nèi)存消耗要小得多，性能更好表訪問次數(shù)驅(qū)動表最多被訪問1次，被驅(qū)動表被訪問N次，N由驅(qū)動表返回的結(jié)果集的行數(shù)來決定，即驅(qū)動表返回多少條記錄，被驅(qū)動表就訪問多少次相關(guān)聯(lián)的2張表都最多被訪問1次，和HJ一樣驅(qū)動表和被驅(qū)動表都是最多被訪問1次表訪問次數(shù)的特殊情況若驅(qū)動表根據(jù)過濾條件返回0條記錄，則此時，驅(qū)動表被訪問1次，被驅(qū)動表被訪問0次；若目標(biāo)SQL語句的WHERE條件不成立(例如，WHERE1=2)，則驅(qū)動表和被驅(qū)動表都不被訪問，即訪問次數(shù)為0次。雖然SMJ沒有驅(qū)動和被驅(qū)動的說法，但是，相關(guān)聯(lián)的表訪問次數(shù)和HJ是一樣的是否排序無須排序，不消耗內(nèi)存需要排序(SORT_AREA_SIZE)HJ在大多數(shù)隋況下都不需要排序，但會消耗內(nèi)存(HASH_AREA_SIZE)用于建立HASH表優(yōu)化方向1)選擇結(jié)果集最小的表作為驅(qū)動表2)對驅(qū)動表的限制條件建立索引，對被驅(qū)動表的連接矧牛建立索引3)盡量減少外層循環(huán)的次數(shù)，提高內(nèi)層循環(huán)的查詢效率1)只取業(yè)務(wù)需要的字段，避免用*，從而減少排序2)在兩表的限制條件上創(chuàng)建索引3)在兩表的連接條件上創(chuàng)建索引，利用索引來消除排序的次數(shù)4)盡量確保PAG足夠大，避免磁盤排序1)只取業(yè)務(wù)需要的字段，避免用*，從而減少排序2)索引列在表連接中無特殊要求3)兩表的限制條件有索引(根據(jù)索引可以過濾掉大部分?jǐn)?shù)據(jù))4)小結(jié)果集的表作驅(qū)動表5)盡量確保PGA可容納HASH運(yùn)算表連接的限制(使用條件)支持所有表連接的寫法支持大于、小于、大于等于和小于等于，但不支持＜＞、LIKE哈希連接只適用于CBO，且僅用于等值連接，不支持不等值連接(包括＜＞、＞、＜、＞=、＜=、OR、LIKE操作)，即使是哈希反連接，Oracle實際上也是將其轉(zhuǎn)換成了等價的等值連接消耗資源CPU、磁盤I/O磁盤I/O、內(nèi)存、臨時空間CPU是否有隱含參數(shù)控制無隱含參數(shù)“_OPTIMIZER_SORTMERGE_JOIN_ENABLED”控制著SMJ的啟用和關(guān)閉，該參數(shù)默認(rèn)值是TRUE，表示啟用SMJ從Oracle10g開始隱含參數(shù)“_HASH_JOIN_ENABLED”控制著HJ的啟用和關(guān)閉，該參數(shù)默認(rèn)值是TRUE，表示啟用HJ。需要注意的是，在Oracle10g之前是參數(shù)HASH_JOIN_ENABLED控制著HJ的啟用和關(guān)閉適用場合1)如果驅(qū)動表(外部表)返回的結(jié)果集比較小，并且在被驅(qū)動表(內(nèi)部表)上有唯一索引(或有高選擇性非唯一索引)時，那么使用NL可以得到較好的效率2)NL連接有其他連接方法沒有的一個優(yōu)點(diǎn)：可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完才返回數(shù)據(jù)，這可以實現(xiàn)快速的響應(yīng)3)兩表關(guān)聯(lián)返回的記錄不多，最佳情況是驅(qū)動表結(jié)果集僅返回一條或少量幾條記錄，而被驅(qū)動表僅匹配到1條或少量幾條記錄，這種情況下即便關(guān)聯(lián)的兩張表奇大無比，NL連接也是非常迅速的4)遇到一些不等值查詢導(dǎo)致哈希和排序合并連接被限制使用，不得不使用NL連接5)NL通常用于OLTP中，應(yīng)用有大量訪問，但是每個訪問最終返回的記錄很少的場景1)對于非等值連接，SMJ的效率是比較高的2)在通常情況下，SMJ并不適合OLTR類型的系統(tǒng)，而更適合OLAP場景，傾向于吞吐量比較大的操作，即最終SQL返回的記錄數(shù)比較多的場景。因為對于OLTP類型的系統(tǒng)而言，排序是非常昂貴的操作，當(dāng)然，如果能避免排序操作，那么即使是OLTP類型的系統(tǒng)，也還是可以使用SMJ的。比如兩個表雖然是做SMJ，但實際上它們并不需要排序，因為這兩個表在各自的連接列上都存在索引3)如果在關(guān)聯(lián)的列上都有索引，那么效果更好1)一般來說，HJ效率好于NL和SMJ連接，但是HJ只能用在CBO優(yōu)化器中，且只能用于等值連接中，另外需要設(shè)置合適的HASH_AREA_SIZE參數(shù)值才能取得較好的性能2)在兩個較大的表源之間連接時會取得相對較好的效率，在一個表源較小時則能取得更好的效率。HJ很適合于小表和大表之f叫做表連接且連接結(jié)果集的記錄數(shù)較多的情形，特別是在小表的連接列的可選擇性非常好的情況下，這時候HJ的執(zhí)行時間就可以近似看作是和全表掃描那個大表所耗費(fèi)的時間相當(dāng)3)當(dāng)兩個表做HJ時，如果在施加了目標(biāo)SQL中指定的謂詞條件(如果有的話)后得到的數(shù)據(jù)量較小的那個結(jié)果集所對應(yīng)的HashTable能夠完全被容納在內(nèi)存(PGA的工作區(qū))中，那么此時的HJ的執(zhí)行效率會非常高4)如果相關(guān)聯(lián)的表在同一數(shù)量級，且數(shù)據(jù)量比較大，那么此時應(yīng)選擇HJ5)HJ一般用于OLAP場景中，SQL最終返回的記錄數(shù)較多6)若表中無索引，則傾向于HJ工作方式在做表連接時依靠兩層嵌套循環(huán)(分別為外層循環(huán)和內(nèi)層循環(huán))來得到連接結(jié)果集。NL適用的場合是當(dāng)一個關(guān)聯(lián)表比較小的時候，效率會更高先將關(guān)聯(lián)表的關(guān)聯(lián)列各自做排序，然后從各自的排序表中抽取數(shù)據(jù)，到另一個排序表中做匹配，因為SMJ需要做更多的排序，所以，消耗的資源更多。通常來講，能夠使用SMJ的地方，HJ都可以發(fā)揮更好的性能將一個表(通常是小表)做HASH運(yùn)算，將列數(shù)據(jù)存儲到HASH列表中，從另一個表中抽取記錄，做HASH運(yùn)算，然后到HASH列表中找到相應(yīng)的值做匹配[考點(diǎn)]SQL優(yōu)化

13.

表的訪問方式有哪幾種?正確答案：訪問表的方式也叫優(yōu)化器訪問路徑，主要有3種訪問路徑：全表掃描(FULLTABLESCAN，F(xiàn)TS)、索引掃描(INDEXSCAN)和ROWID訪問。[考點(diǎn)]SQL優(yōu)化

14.

COUNT(*)計算行數(shù)有哪些優(yōu)化手段?正確答案：可以有如下表所示幾種優(yōu)化手段。手段命令執(zhí)行計劃主要原理詳細(xì)說明性能情況全表掃描

TABLEACCESSFULL全表掃描OLTP中，通常是最慢的方式邏輯讀為1139增加普通索引CREATEINDEXIDX_OBJECT_NAMEONT(OBJECF_NAME);INDEXFASTFULLSCAN從全表掃描轉(zhuǎn)成全索引掃描因為索引一般比表小得多，所以全表掃描轉(zhuǎn)成全索引掃描，性能能大幅度提升邏輯讀為400常數(shù)索引CREATEINDEXIDX_OBJECT_NAMEONT(0);INDEXFASTFULLSCAN從全表掃描轉(zhuǎn)成全索引掃描常數(shù)索引比普通索引更小邏輯讀為151常數(shù)壓縮索引CREATEINDEXDX_OBJECT_NAMEONT(0)COMPRESS;INDEXFASTFULLSCAN從全表掃描轉(zhuǎn)成全索引掃描常數(shù)壓縮索引比常數(shù)索引更小邏輯讀為129位圖索引CREATEBITMAPINDEXIDX_OBJECT_NAMEONT(OBJECT_NAME);BITMAPINDEXFASTFULLSCAN從BTREE索引掃描轉(zhuǎn)成位圖索引掃描位圖索引的大小比BTREE索引要小得多，所以位圖索引掃描快邏輯讀為5物化視圖CREATEMATERIALIZEDVIEWMV_COUNT_TBUILDIMMEDIATEREFRESHONCOMMITENABLEQUERYREWRITEASSELECTCOUNT(*)FROMT;MAT_VIEWREWRITEACCESSFULL空間換時間要注意，如果數(shù)據(jù)要求比較實時，就不適用邏輯讀為3緩存結(jié)果SELECT/*+RESULT_CACHE*/COUNT(*)FROMT;RESULTCACHE直接把查詢結(jié)果拿來用要注意，如果數(shù)據(jù)頻繁更新，就不適用邏輯讀為0業(yè)務(wù)理解SELECTCOUNT(*)FROMTWHEREROWNUM=1;

如果COUNT(*)只是為了判斷條數(shù)，就自吐ROWNUM=1來判斷是否為1業(yè)務(wù)需求轉(zhuǎn)換，獲取條數(shù)有的時候，只是為了看看表是否為空，這時候是否是1條和是否大于0其實足一樣的不言而喻分析需求據(jù)說，這個COUNT(*)統(tǒng)計條數(shù)語句，是多余的!直接去掉這條語句，這里沒有SQL!最優(yōu)

位圖索引可以按很高密度存儲數(shù)據(jù)，因此往往比B樹索引小很多，前提是在基數(shù)比較小(列重復(fù)度比較高)的情況下。位圖索引是保存空值的，因此可以在COUNT中利用。位圖索引不太適合OLTP類型數(shù)據(jù)庫。物化視圖是應(yīng)用在數(shù)據(jù)要求不怎么及時的場景下。若表頻繁更新，則不適合緩存結(jié)果集。

優(yōu)化沒有止境，對數(shù)據(jù)庫了解越多，能想到的方法就越多。[考點(diǎn)]SQL優(yōu)化

15.

給出下面語句的可能的優(yōu)化思路。

CREATETABLET_YH_20170705_LHR(XINT);

BEGIN

FORIIN1..100000LOOP

EXECUTEIMMEDIATE'INSERTINTOT_YH_20170705_LHRVALUES('||I||')';

COMMIT;

ENDLOOP;

END;正確答案：優(yōu)化思路有：①采用綁定變量；②使用靜態(tài)SQL；③采用批量提交或循環(huán)外提交；④根據(jù)功能，可以去掉PL/SQL塊，采用直接一次性插入的方式來完成，SQL為“INSERTINTOT_YH_20170705_LHRSELECTROWNUMFROMDUALCONNECTBYLEVEL＜=100000;”；⑤采用直接路徑方式，例如，“CREATETABLET_YH_20170705_LHRASSELECTROWNUMXFROMDUALCONNECTBYLEVEL＜=100000;”；⑥采用NOLOGGING和PARALLEL的方式，例如，“CREATETABLET_YH_20170705_LHRNOLOGGINGPARALLEL8ASSELECTROWNUMXFROMDUALCONNECTBYLEVEL＜=100000;”。[考點(diǎn)]SQL優(yōu)化

16.

SQL如何優(yōu)化?SQL優(yōu)化關(guān)注點(diǎn)有哪些?正確答案：在多數(shù)情況下，Oracle使用索引來更快地遍歷表，優(yōu)化器主要根據(jù)定義的索引來提高性能。如果在SQL語句的WHERE子句中寫的SQL條件不合理，那么就會造成優(yōu)化器舍去索引而使用全表掃描，一般這種SQL語句的性能都是非常差的。在編寫SQL語句時，應(yīng)清楚優(yōu)化器根據(jù)何種原則來使用索引，這有助于寫出高性能的SQL語句。

SQL的優(yōu)化主要涉及如下幾個方面的內(nèi)容：

1)索引問題。是否可以使用組合索引；限制條件、連接條件的列是否有索引；能否使用到索引，避免全表掃描。一般情況下，盡量使用索引，因為索引在很多情況下可以提高查詢效率。排序字段有正確的索引，驅(qū)動表的限制條件有索引，被驅(qū)動表的連接條件有索引。

2)相關(guān)的統(tǒng)計信息缺失或者不準(zhǔn)確。查看SQL的執(zhí)行計劃是不是最優(yōu)，然后結(jié)合統(tǒng)計信息查看執(zhí)行計劃是否正確。

3)直方圖使用錯誤。

4)SQL本身的效率問題，例如使用綁定變量，批量DML采用BULK等，這個就考驗寫SQL的基本功了。

5)數(shù)據(jù)量大小。如果就是幾百條數(shù)據(jù)，那么就沒有所謂效率之分，一般情況下怎么寫效率都不低。如果數(shù)據(jù)量很大，那么就得考慮是否要分頁或排序。

6)綁定變量。大多數(shù)情況綁定變量能提高查詢效率，但也有降低效率的情況。

7)批量和并行的考慮。

8)業(yè)務(wù)需求需要正確理解，實現(xiàn)業(yè)務(wù)的邏輯需要正確，減少一些重復(fù)計算。有可能是設(shè)計的不合理、業(yè)務(wù)需求的不合理，而問題SQL并非根本原因。

9)查詢特別頻繁的結(jié)果是否可以緩存，比如Oracle的/*+result_cache*/。

10)分析表的連接方式。若是NL連接，則根據(jù)業(yè)務(wù)或表的數(shù)據(jù)質(zhì)量情況，分析能否減少驅(qū)動表的結(jié)果集。

11)是否可以吲定執(zhí)行計劃。

12)大表是否存在高水位。

13)在創(chuàng)建表的時候，應(yīng)盡量建立主鍵，盡量根據(jù)實際需要調(diào)整數(shù)據(jù)表的PCTFREE和PCTUSED參數(shù)。[考點(diǎn)]SQL優(yōu)化

17.

SQL優(yōu)化在寫法上有哪些常用的方法?正確答案：一般在書寫SQL時需要注意哪些問題，如何書寫可以提高查詢的效率?可以從以下幾個方面去考慮：

1)減少對數(shù)據(jù)庫的訪問次數(shù)。

2)減少對大表的掃描次數(shù)。可以利用WITH對SQL中多次掃描的表來進(jìn)行修改。采用各種手段來避免全表掃描。

3)SELECT子句中避免使用“*”，應(yīng)該寫出需要查詢的字段。

4)盡量使用表的別名(ALIAS)。

當(dāng)在SQL語句中連接多個表時，請使用表的別名，并把別名前綴于每個列上。此時就可以減少解析的時間并減少那些由列歧義引起的語法錯誤。

5)對于數(shù)據(jù)量較少，又有主鍵索引的情況，可以考慮將關(guān)聯(lián)子查詢或外連接的SQL修改為標(biāo)量子查詢。

6)避免隱式類型轉(zhuǎn)換(ImplicitTypeConversion)。如果進(jìn)行比較的兩個值的數(shù)據(jù)類型不同，那么Oracle必須將其中一個值進(jìn)行類型轉(zhuǎn)換使其能夠比較。這就是所謂的隱式類型轉(zhuǎn)換。通常當(dāng)開發(fā)人員將數(shù)字存儲在字符列時會導(dǎo)致這種問題的產(chǎn)生。Oracle在運(yùn)行時會在索引字符列使用TO_NUMBER函數(shù)強(qiáng)制轉(zhuǎn)化字符類型為數(shù)值類型。由于添加函數(shù)到索引列，導(dǎo)致索引不被使用。實際上，Oracle也只能這么做，類型轉(zhuǎn)換是一個應(yīng)用程序設(shè)計因素。由于轉(zhuǎn)換是在每行都進(jìn)行的，這會導(dǎo)致性能問題。一般情況下，當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時，Oracle自動地從復(fù)雜向簡單的數(shù)據(jù)類型轉(zhuǎn)換。所以，字符類型的字段值應(yīng)該加上引號。例如，假設(shè)USER_NO是一個字符類型的索引列，則

SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NO=109204421;

這個語句在執(zhí)行的時候被Oracle在內(nèi)部自動地轉(zhuǎn)換為

SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHERETO_NUMBER(USER_NO)=109204421;

因為是內(nèi)部發(fā)生的類型轉(zhuǎn)換，這個索引將不會被使用，所以正確的寫法應(yīng)該是

SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREUSER_NO='109204421';

7)避免使用耗費(fèi)資源的操作，包括DISTINCT、UNION、MINUS、INTERSECT、ORDERBY、GROUPBY等。能用DISTINCT的就不用GROUPBY，能用UNIONALL就不要用UNION。

8)用TRUNCATE替代DELETE。若要刪除表中所有的數(shù)據(jù)，則可以用TRUNCATE替代DELETE。

9)根據(jù)查詢條件建立合適的索引，利用索引可以避免大表全表掃描(FULLTABLESCAN)。

10)合理使用臨時表。

11)避免寫過于復(fù)雜的SQL，不一定非要一個SQL解決問題。將一個大的SQL改寫為多個小的SQL來實現(xiàn)功能。條件允許的情況下可以使用批處理來完成。

12)在不影響業(yè)務(wù)的前提下盡量減小事務(wù)的粒度。

13)當(dāng)使用基于規(guī)則的優(yōu)化器(RBO)時，在多表連接查詢的時候，記錄數(shù)少的表應(yīng)該放在右邊。

14)避免使用復(fù)雜的集合函數(shù)，像NOTIN等。通常，要避免在索引列上使用NOT，NOT會產(chǎn)生和在索引列上使用函數(shù)相同的影響。當(dāng)Oracle遇到NOT操作符時，它就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。很多時候用EXISTS和NOTEXISTS代替IN和NOTIN語句是一個好的選擇。需要注意的是，在Oracle11g之前，若NOTIN的列沒有指定非空(注意：是主表和子表的列未同時有NOTNULL約束，或都未加ISNOTNULL限制)，則NOTIN選擇的是filter操作(如果指定了非空，那么會選擇ANTI的反連接)，但是從Oracle11g開始有新的ANTINA(NULLAWARE)優(yōu)化，可以對予查詢進(jìn)行UNNEST，NOTIN和NOTEXISTS都選擇的是ANTI的反連接，所以效率是一樣的。在一般情況下，ANTI的反連接算法比filter更高效。對于未UNNEST的子查詢，若選擇了filter操作，則至少有兩個子節(jié)點(diǎn)，執(zhí)行計劃還有個特點(diǎn)就是Predicate謂詞部分有“:B1”這種類似綁定變量的內(nèi)容，內(nèi)部操作類似NestedLoops操作。如果在Oracle11g之前，遇到NOTIN無法UNNEST，那么可以將NOTIN部分的匹配條件均設(shè)為NOTNULL約束。若不添加NOTNULL約束，則需要兩個條件均增加ISNOTNULL條件。當(dāng)然也可以將NOTIN修改為NOTEXISTS。

15)盡量避免使用UNION關(guān)鍵詞，可以根據(jù)情況修改為UNIONALL。

16)在Oracle數(shù)據(jù)庫里，IN和OR是等價的，優(yōu)化器在處理帶IN的目標(biāo)SQL時會將其轉(zhuǎn)換為帶OR的等價SQL。例如，“DEPTNOIN(10,20)”和“DEPTNO=10ORDEPTNO=20”是等價的。

17)選擇合適的謂詞進(jìn)行過濾。

18)避免使用前置通配符(%)。在WHERE子句中，如果索引列所對應(yīng)的值的第一個字符由通配符(WILDCARD)開始，索引將不被采用。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。若前置通配符實在無法取消，則可以從3個方面去考慮：①去重和去空。應(yīng)該把表中的重復(fù)記錄或者為空的記錄全部去掉，這樣可以大大減少結(jié)果集，因而提升性能，這里也體現(xiàn)了大表變小表的思想。②考慮建立文本索引。③做相關(guān)的轉(zhuǎn)換。

19)應(yīng)盡量避免在WHERE子句中對字段進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式等操作，因為這樣可能會使索引失效，查詢時要盡可能將操作移至等號右邊。見如下例子：

SELECT*FROMT1WHERESUBSTR(NAME,2,1)='L';

在以上SQL中，即使NAME字段建有唯一索引，該SQL語句也無法利用索引進(jìn)行檢索數(shù)據(jù)，而是走全表掃描的方式。一些常見的改寫見下表。原SQL語句優(yōu)化后SQL語句SELECT*FROMT1WHERECOL/2=100;SELECT*FROMT1WHERECOL=200;SELECT*FROMT1WHERESUBSTR(CARD_NO,1,4)='5378';SELECT*FROMT1WHERECARD_NOLIKE'5378%';SELECT*FROMT1WHERETO_CHAR(CREATED,'YYYY')='2011';SELECT*FROMT1WHERECREATED＞=TO_DATE('20110101','YYYYMMDD')ANDCREATED＜TO_DATE('20120101','YYYYMMDD');SELECT*FROMT1WHERETRUNC(CREATED)=TRUNC(SYSDATE)；SELECT*FROMT1WHERECREATED＞=TRUNC(SYSDATE)ANDCREATED＜TRUNC(SYSDATE+1);SELECT*FROMT1WHERE'X'||COL2＞'X5400021452';SELECT*FROMT1WHERECOL2＞'5400021452';SELECT*FROMT1WHERECOL||COL2='5400250000';(在該SQL中，COL和COL2列長度固定)SELECT*FROMT1WHERECOL='5400'ANDCOL2='250000';SELECT*FROMT1WHERETO_CHAR(CREATED,'YYYY')=TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY');SELECT*FROMT1WHERECREATED＞=TRUNC(ADD_MONTHS(SYSDATE,-12),'YYYY')ANDCREATED＜TRUNC(SYSDATE,'YYYY');--去年

需要注意的是，如果SELECT需要檢索的字段只包含索引列且WHERE查詢中的索引列含有非空約束的時候，以上規(guī)則并不適用。例如，SQL語句“SELECTCREATEDFROMT1WHERETRUNC(CREATED)=TRUNC(SYSDATE);”，若CREATED列上有非空約束或在WHERE子句中加上“CREATEDISNOTNULL”，則該SQL語句仍然會走索引，如下：

DROPTABLETPURGE;

CREATETABLETNOLOGGINGASSELECT*FROM

DBA_OBJECTSD;

CREATE

INDEXIND_OBJECTNAMEONT(OBJECT_NAME);

SELECTT.OBJECT_NAMEFROMTWHERET.OBJECT_NAME='T';--一走索引

SELECTT.OBJECT_NAMEFROMTWHEREUPPER(T.OBJECTNAME)='T';

--不走索引

SELECTT.OBJECT_NAMEFROMTWHEREUPPER(T.OBJECTNAME)='T'ANDT.OBJECT_NAMEISNOTNULL;

--走索引(INDEXFASTFULLSCAN)

SELECTT.OBJECT_NAMEFROMTWHEREUPPER(T.OBJECT_NAME)||'AAA'='T'||'AAA'ANDT.OBJECT_NAMEISNOTNULL;

--走索引(INDEXFASTFULLSCAN)

SELECTT.OBJECT_NAME,T.OWNERFROMTWHEREUPPER(T.OBJECT_NAME)||'AAA'='T'||'AAA'ANDT.OBJECT_NAMEISNOTNULL;--不走索引

20)合理使用分析函數(shù)。

21)應(yīng)盡量避免在WHERE子句中使用不等操作符(!=或＜＞)，否則引擎將放棄使用索引而進(jìn)行全表掃描。

22)避免不必要和無意義的排序。

23)盡可能減少關(guān)聯(lián)表的數(shù)量，關(guān)聯(lián)表盡量不要超過3張。

24)在建立復(fù)合索引時，盡量把最常用、重復(fù)率低的字段放在最前面。在查詢的時候，WHERE條件盡量要包含索引的第一列即前導(dǎo)列。

25)應(yīng)盡量避免在WHERE子句中對字段進(jìn)行ISNULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。可以通過加偽列創(chuàng)建偽聯(lián)合索引來使得ISNULL使用索引。例如語句：“SELECTIDFROMTWHERENUMISNULL;”可以在NUM上設(shè)置默認(rèn)值0，確保表中NUM列沒有NULL值，然后這樣查詢：“SELECTIDFROMTWHERENUM=0;”。

26)IN要慎用，因為IN會使系統(tǒng)無法使用索引，而只能直接搜索表中的數(shù)據(jù)。如：

SELECTIDFROMTWHERENUMIN(1,2,3);

對于連續(xù)的數(shù)值，能用BETWEEN就不要用IN了：

SELECTIDFROMTWHERENUMBETWEEN1AND3;

27)必要時使用Hint強(qiáng)制查詢優(yōu)化器使用某個索引，如在WHERE子句中使用參數(shù)，也會導(dǎo)致全表掃描。因為SQL只有在運(yùn)行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運(yùn)行時；它必須在編譯時進(jìn)行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。

28)在條件允許的情況下，只訪問索引，從而可以避免索引回表讀(TABLEACCESSBYINDEXROWID，通過索引再去讀表中的內(nèi)容)。當(dāng)索引中包括處理查詢所需要的所有數(shù)據(jù)時，可以執(zhí)行只掃描索引操作，而不用做索引回表讀操作。因為索引回表讀開銷很大，能避免則避免。避免的方法：①根據(jù)業(yè)務(wù)需求只留下索引字段；②建立聯(lián)合索引。這里的第二點(diǎn)需要注意平衡，如果聯(lián)合索引的聯(lián)合列太多，必然導(dǎo)致索引過大，雖然消減了回表動作，但是索引塊變多，在索引中的查詢可能就要遍歷更多的BLOCK了，所以需要全面考慮，聯(lián)合索引列不宜過多，一般來說超過3個字段組成的聯(lián)合索引都是不合適的，需要權(quán)衡利弊。

29)選擇合適的索引。Oracle在進(jìn)行一次查詢時，一般對一個表只會使用一個索引。例如，某表有索引1(POLICYNO)和索引2(CLASSCODE)，如果查詢條件為POLICYNO='XX'ANDCLASSCODE='XX'，則系統(tǒng)有可能會使用索引2，相較于使用索引1，查詢效率明顯降低。

30)優(yōu)先且盡可能使用分區(qū)索引。

31)在刪除(DELETE)、插入(INSERT)、更新(UPDATE)頻繁的表中，建議不要使用位圖索引。

32)對于分區(qū)表，應(yīng)該減少需要掃描的分區(qū)，避免全分區(qū)掃描。對于單分區(qū)掃描，在分區(qū)表后加上PARTITION(分區(qū)名)；對于多分區(qū)掃描，使用分區(qū)關(guān)鍵字來限制需要掃描的范圍，從而可以避免全分區(qū)掃描。

33)使用分批處理、DBMS_PARALLEL_EXECUTE進(jìn)行處理。

34)刪除重復(fù)記錄盡量采用ROWID的方法，如下：

DELETEFROMSCOTF.EMPEWHEREE.ROWID＞(SELECTMIN(X.ROWID)FROMSCOTT.EMPXWHEREX.EMPNO=E.EMPNO);

35)SQL中慎用自定義函數(shù)。如果白定義函數(shù)的內(nèi)容，只是針對函數(shù)輸入?yún)?shù)的運(yùn)算，而沒有訪問表這樣的代碼，那么這樣的自定義函數(shù)在SQL中直接使用是高效的；否則，如果函數(shù)中含有對表的訪問的語句，那么在SQL中調(diào)用該函數(shù)很可能會造成很大的性能問題，需要謹(jǐn)慎!在這種情況下，往往將函數(shù)中訪問表的代碼取出和調(diào)用它的SQL整合成新的SQL。

36)使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表，這對于大表非常有效，如下：

SELECTCOUNT(*),SUM(SAL)FROMSCOTT.EMPWHEREDEPTNO=20ANDENAMELIKE'SMITH%';

SELECTCOUNT(*),SUM(SAL)FROMSCOTT.EMPWHEREDEPTNO=30ANDENAMELIKE'SMITH%';

若使用DECODE函數(shù)，則對SCOTT.EMP表只訪問一次，如下：

SELECTCOUNT(DECODE(DEPTNO,20,'1',NULL))D20_COUNT,COUNT(DECODE(DEPTNO,30,'1',NULL))D30_COUNT,

SUM(DECODE(DEPTNO,20,SAL,NULL))D20_SAL,SUM(DECODE(DEPTNO,30,SAL,NULL))D30_SAL

FROMSCOTT.EMPWHEREENAMELIKE'SMITH%';

類似地，DECODE函數(shù)也可以運(yùn)用于GROUPBY和ORDERBY子句中。

37)在計算表的行數(shù)時，若表上有主鍵，則盡量使用COUNT(*)或COUNT(1)。

38)用WHERE子句替換HAVING子句。避免使用HAVING子句，因為HAVING只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾。這個處理需要排序、總計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那么就能提高SQL的性能，如下：

低效：SELECTT.EMPNO,COUNT(*)FROMSCOTT.EMPTGROUPBYT.EMPNOHAVINGEMPNO=7369;

高效：SELECTT.EMPNO,COUNT(*)FROMSCOTT.EMPTWHEREEMPNO=7369GROUPBYT.EMPNO;

39)減少對表的查詢，尤其是要避免在同一個SQL中多次訪問同一張大表?？梢钥紤]如下的改寫方法：

①先根據(jù)條件提取數(shù)據(jù)到臨時表中，然后做連接，即利用WITH進(jìn)行改寫。

②有的相似的語句可以用MAX+DECODE函數(shù)來處理。

③在含有子查詢的SQL語句中，要特別注意減少對表的查詢，例如，形如“UPDATEAAATSETT.A=(....)T.B=(....)WHERE....;”該更新的SQL語句小括號中的大表都是一樣的，且查詢非常相似，這個時候可以修改為“UPDATEAAATSET(T.A,T.B)=(....)

WHERE....;”。

40)SQL語句統(tǒng)一使用大寫。因為Oracle總是先解析SQL語句，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行。

41)對于一些固定性的查詢結(jié)果可以使用結(jié)果集緩存(ResultCache)，對于一些常用的小表可以使用保留池(KeepPool)。

42)如果在一條SQL語句中同時取最大值和最小值，那么需要注意寫法上的差異：

SELECTMAX(OBJECT_ID),MIN(OBJECT_ID)FROMT;--效率差,選擇INDEXFASTFULLSCAN

SELECTMAX_VALUE,MIN_VALUEFROM(ELECTMAX(OBJECT_ID)MAX_VALUEFROMT)A,(SELECTMIN(OBJECT_ID)MIN_VALUEFROMDB;--效率高,選擇INDEXFULLSCAN(MIN/MAX)

43)在PL/SQL中，定義變量類型時盡量使用%TYPE和%ROWTYPE，這樣可以減少代碼的修改，增加程序的可維護(hù)性。

以上講解的每點(diǎn)優(yōu)化內(nèi)容希望讀者可以通過實驗來加深理解。[考點(diǎn)]SQL優(yōu)化

18.

有哪些方法可以提高DML語句效率?正確答案：若是批量處理海量數(shù)據(jù)，則通常都是很復(fù)雜及緩慢的，方法也很多，但是通常的概念是，分批刪除，逐次提交。下面介紹一下提高DML語句效率的常用方法。

以上這些方法都是拋磚引玉，數(shù)據(jù)庫優(yōu)化沒有最好的方法，只有最合適的方法。[考點(diǎn)]SQL優(yōu)化

19.

COUNT(1)比COUNT(*)在執(zhí)行效率上要快嗎?正確答案：錯。COUNT(1)和COUNT(*)在執(zhí)行效率上是一樣的。COUNT()函數(shù)是Oracle中的聚合函數(shù)，用于統(tǒng)計結(jié)果集的行數(shù)。其語法形式如下：

COUNT({*|[DISTINCT|ALL]expr})[OVER(analytic_clause)]

可以把COUNT的使用情況分為以下3類：

1)COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)。

2)COUNT(允許為空列)。

3)COUNT(DISTINCT列名)。

下面分別從查詢結(jié)果和效率方面做個比較。

1.結(jié)果區(qū)別

1)COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)這幾種方式統(tǒng)計的行數(shù)是表中所有存在的行的總數(shù)，包括值為NULL的行和非空行。所以，這幾種方式的執(zhí)行結(jié)果相同。這里的常量可以為數(shù)字或字符串，例如，COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是，這里的COUNT(1)中的“1”并不表示表中的第一列，它其實是一個表達(dá)式，可以換成任意數(shù)字、字符或表達(dá)式。

2)COUNT(允許為空列1這種方式統(tǒng)計的行數(shù)不會包括字段值為NULL的行。

3)COUNT(DISTINCT列名)得到的結(jié)果是除去值為NULL和重復(fù)數(shù)據(jù)后的結(jié)果。

4)“SELECTCOUNT(")，COUNT(NULL)FROMT_COUNT_LHR;”返回0行。

2.效率、索引

1)如果存在主鍵或非空列上的索引，那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)會首先選擇主鍵上的索引快速全掃描(INDEXFASTFULLSCAN)。若主鍵不存在則會選擇非空列上的索引。若非空列上沒有索引則肯定走全表掃描(TABLEACCESSFULL)。其中，COUNT(ROWID)在走索引的時候比其他幾種方式要慢。通過10053事件可以看到這幾種方式除了COUNT(ROWID)之外，其他最終都會轉(zhuǎn)換成COUNT(*)的方式來執(zhí)行。需要注意的是，在以下幾種情況下，Oracle會選擇全表掃描：

①主鍵索引或非空列上的索引所占用的塊數(shù)比表的塊數(shù)大，此時選擇全表掃描的COST會比選擇索引的COST小，所以，Oracle會選擇全表掃描。當(dāng)索引碎片過多、收集表的統(tǒng)計信息與收集索引的統(tǒng)計信息之間隔了很久，或手動使用DBMS_STATS包不正確地設(shè)置了表或索引的統(tǒng)計信息的時候，可能會出現(xiàn)這種情況。但是，在一般情況下不會出現(xiàn)這種情況，所以需要及時地、正確地收集統(tǒng)計信息。

②主鍵索引或非空列上的索引處于無效狀態(tài)。在此種情況下，Oracle必然選擇全表掃描。

2)對于COUNT(COL1)來說，只要列字段上有索引則會選擇