SQL編寫規(guī)范和優(yōu)化講稿

1、SQL編寫規(guī)范和優(yōu)化編寫規(guī)范和優(yōu)化 SQL編寫規(guī)范編寫規(guī)范-1v原則定義原則定義 1、要求代碼行清晰、整齊，具有一定的可觀賞性； 2、代碼編寫要充分考慮執(zhí)行速度最優(yōu)的原則； 3、代碼行整體層次分明、結(jié)構(gòu)化強(qiáng)； 4、代碼中應(yīng)有必要的注釋以增強(qiáng)代碼的可讀性； 5、規(guī)范要求非強(qiáng)制性約束代碼開發(fā)人員的代碼編寫行為，在實(shí)際應(yīng)用中在不違反常規(guī)要求的前提下允許存在可理解的偏差。SQL編寫規(guī)范編寫規(guī)范-2v大小寫規(guī)則大小寫規(guī)則 1、所有的SQL語句中的保留字均采用全部大寫，不要使用縮寫；表別名也要大寫； 如ALL AS CASE CREATE DATABASE DELETE FROM IN INSERT JO

2、IN LEFT NO NOT NULL OUT SELECT TABLE TITLE UPDATE VIEW WHERE 等。 2、表名、視圖名、宏和存儲(chǔ)過程名：全部小寫；SQL編寫規(guī)范編寫規(guī)范-2v大小寫規(guī)則大小寫規(guī)則 3、字段名：每個(gè)單詞的首字母大寫，其余部分小寫，如party_id, loc_id, prod_inst_id, Acct_Id, Type_Id 等； 使用如下語句獲得字段列表: SQL編寫規(guī)范編寫規(guī)范-3v縮進(jìn)和換行縮進(jìn)和換行 整個(gè)的SQL語句最好按照子句進(jìn)行分行編寫，SELECT/ FROM/ WHERE/ UPDATE/ INSERT 等每個(gè)關(guān)鍵字都要另起一行。如：S

3、QL編寫規(guī)范編寫規(guī)范-3v縮進(jìn)和換行縮進(jìn)和換行SQL編寫規(guī)范編寫規(guī)范-3v縮進(jìn)和換行縮進(jìn)和換行 1、同一級(jí)別的子句間要對(duì)齊 2、逗號(hào)放在每行的開頭 3、分號(hào)放在SQL語句的最后，單獨(dú)占一行 4、每行寬度不超過120字符(每個(gè)字符為8個(gè)點(diǎn)陣寬)，超過行寬的代碼可折行與上行對(duì)齊編排； 5、每個(gè)字段后面使用字段標(biāo)題作為注釋 6、使用給出的語句獲得字段列表和字段標(biāo)題(借助UltraEdit 列模式快速編輯)SQL編寫規(guī)范編寫規(guī)范-3v縮進(jìn)和換行縮進(jìn)和換行 下面的編寫方式不是好的形式： 在所有需要縮進(jìn)的地方，每次縮進(jìn)4格；在以下情況下需要縮進(jìn)： 1、不同層次的SQL語句之間 2、SELECT INSER

4、T等關(guān)鍵字之后的字段列表和關(guān)鍵字之間SQL編寫規(guī)范編寫規(guī)范-4v別名別名 SQL語句別名的命名，分層命名，從第一層次至第四層次，分別用P 、S、 U 、D（都是大寫字母）表示，取意為Part, Segment, Unit, Detail。 對(duì)于同一層次的多個(gè)子句，在字母后加1、2、3、4區(qū)分。SQL編寫規(guī)范編寫規(guī)范-4v別名別名 如下圖所示：SQL編寫規(guī)范編寫規(guī)范-5v運(yùn)算符前后間隔要求運(yùn)算符前后間隔要求 算術(shù)運(yùn)算符、邏輯運(yùn)算符的前后至少要保留一個(gè)空格，如下圖所示：SQL編寫規(guī)范編寫規(guī)范-6v變量引用變量引用 1、在SQL語句中引用變量時(shí)，要在變量名兩端加花括號(hào) 2、對(duì)日期變量的引用要在單引號(hào)

5、內(nèi)，如$MYDATESQL編寫規(guī)范編寫規(guī)范-7v注釋注釋 針對(duì)復(fù)雜的SQL語句，請(qǐng)盡量增加相應(yīng)的注釋說明，以便自己和其它同事事后可以比較容易的讀懂和修改。 注釋中應(yīng)包含以下內(nèi)容： 1、編寫人/編寫日期 2、修改人/修改日期 3、該腳本的編寫目的與主要內(nèi)容 4、如果有特殊處理、特別的技巧等內(nèi)容，一定要在注釋中詳細(xì)說明SQL編寫規(guī)范編寫規(guī)范-7v注釋注釋 5、每一段SQL的前面必須要有注釋，重點(diǎn)說明該SQL的技術(shù)含義和應(yīng)用含義、選擇理由。技術(shù)含義指這段SQL在技術(shù)上這么寫的原因、好處，應(yīng)用含義指這段SQL從應(yīng)用的角度將，是為了達(dá)到一個(gè)什么樣的目的。如果有可能，還需要說明為什么這么選擇，而不選擇別的

6、方式的理由。如果發(fā)現(xiàn)了不足，還可記錄不足的原因和建議的解決辦法等。SQL編寫規(guī)范編寫規(guī)范-8v連接的使用連接的使用 對(duì)于內(nèi)連接和外連接的使用，要求該使用外連接的地方都已經(jīng)使用了外連接，不需要外連接的地方一定不使用外連接。 表中的字段若是從其它表引用的，要確保該字段在被引用的表中存在。SQL編寫規(guī)范編寫規(guī)范-8v連接的使用連接的使用 要求所有的連接都寫成JOIN的形式，如：SQL編寫規(guī)范編寫規(guī)范-8v連接的使用連接的使用 而不要寫成：SQL編寫規(guī)范編寫規(guī)范-8v連接的使用連接的使用 另外，為了保證多表連接的連接條件穿透性，要求在多表連接的SQL書寫時(shí)將連接條件有機(jī)的閉環(huán)起來，尤其是多表PI相同，

7、并用PI連接時(shí)。SQL編寫規(guī)范編寫規(guī)范-8v連接的使用連接的使用 例如：SQL編寫規(guī)范編寫規(guī)范-8v連接的使用連接的使用 即連接條件如下所示：SQL優(yōu)化優(yōu)化(teradata)vSQL腳本優(yōu)化原則腳本優(yōu)化原則 要優(yōu)化腳本，在深刻理解業(yè)務(wù)邏輯的基礎(chǔ)上，一個(gè)重要的方式是一段一段的查看SQL的執(zhí)行計(jì)劃，然后針對(duì)執(zhí)行計(jì)劃中不合理、不優(yōu)化的地方對(duì)SQL進(jìn)行優(yōu)化編寫，這項(xiàng)工作需要實(shí)踐經(jīng)驗(yàn)，經(jīng)常看執(zhí)行計(jì)劃會(huì)有所幫助。SQL優(yōu)化優(yōu)化(teradata)vSQLSQL腳本優(yōu)化原則腳本優(yōu)化原則 表級(jí)優(yōu)化可以參照以下以下優(yōu)化原則： (1) 如果過濾性不很強(qiáng)，又不需要重分布，對(duì)大表盡可能不要只做一下過濾就進(jìn)一次SPO

8、OL，最好是直接與別的表JOIN，邊JOIN邊過濾了； (2) 如果過濾性非常強(qiáng)，可以只做一下過濾就進(jìn)一次SPOOL；SQL優(yōu)化優(yōu)化(teradata)vSQLSQL腳本優(yōu)化原則腳本優(yōu)化原則 (3) SPOOL空間盡量小原則，即盡可能使中間過程中的SPOOL空間小一些，這樣可以減小I/O以及繼續(xù)關(guān)聯(lián)的代價(jià)；在此原則下可以引申出下面幾個(gè)具體原則： (3.1) 在幾個(gè)表大小差不多時(shí)，過濾性條件較強(qiáng)的先JOIN； (3.2) 在大/大/小三個(gè)表內(nèi)聯(lián)時(shí)，避免先把兩個(gè)大表JOIN，除非過濾條件非常強(qiáng)； (3.3) 在大/小/小三個(gè)表內(nèi)聯(lián)時(shí)，盡量先把兩個(gè)小表JOIN； (3.4) 有時(shí)將看似沒必要的過濾條

9、件加上，在關(guān)聯(lián)表較多時(shí)可能有效的減小SPOOL；如表A、B、C、D的關(guān)聯(lián)字段均為k，即A.k=B.k AND A.k=C.k AND A.k=D.k，而同時(shí)還有條件substr(A.k,1,2)=01，有時(shí)加上substr(B.k,1,2)=01、substr(C.k,1,2)=01、substr(D.k,1,2)=01 會(huì)有好處；SQL優(yōu)化優(yōu)化(teradata)vSQLSQL腳本優(yōu)化原則腳本優(yōu)化原則 (4) 盡量避免大表重分布； (5) 當(dāng)大表與很小的表(記錄數(shù)量級(jí)在5位數(shù)以內(nèi))JOIN時(shí)，盡量讓小表Duplicate； (6) 如果必須有重分布時(shí)，盡量使之靠后； (7) 盡量減少較大的中

10、間過程中的SPOOL空間重分布的次數(shù)；SQL優(yōu)化優(yōu)化(teradata)vSQLSQL腳本優(yōu)化原則腳本優(yōu)化原則 (8) 遇到product join時(shí)要小心一些； (9) 盡量減少對(duì)大表的掃描次數(shù)； (10)在拆SQL時(shí)也應(yīng)注意，合起來雖然可能大些，但只掃描一次大表，而拆成多句后就要多次掃描大表，可能效率反降；SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句優(yōu)化語句優(yōu)化 數(shù)據(jù)庫的優(yōu)化方法有很多種，在應(yīng)用層來說，主要是基于索引的優(yōu)化。難就難在如何判斷哪些索引是必要的，哪些又是不必要的。判斷的最終標(biāo)準(zhǔn)是看這些索引是否對(duì)我們的數(shù)據(jù)庫性能有所幫助。 具體到方法上，就必須熟悉數(shù)據(jù)庫應(yīng)

11、用程序中的所有SQL語句，從中統(tǒng)計(jì)出常用的可能對(duì)性能有影響的部分SQL，分析、歸納出作為Where條件子句的字段及其組合方式；在這一基礎(chǔ)上可以初步判斷出哪些表的哪些字段應(yīng)該建立索引。SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句優(yōu)化語句優(yōu)化 其次，必須熟悉應(yīng)用程序。必須了解哪些表是數(shù)據(jù)操作頻繁的表；哪些表經(jīng)常與其他表進(jìn)行連接；哪些表中的數(shù)據(jù)量可能很大；對(duì)于數(shù)據(jù)量大的表，其中各個(gè)字段的數(shù)據(jù)分布情況如何；等等。對(duì)于滿足以上條件的這些表，必須重點(diǎn)關(guān)注，因?yàn)樵谶@些表上的索引，將對(duì)SQL語句的性能產(chǎn)生舉足輕重的影響SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句

12、優(yōu)化語句優(yōu)化 建立索引常用的規(guī)則如下： 1、表的主鍵、外鍵必須有索引； 2、數(shù)據(jù)量超過300的表應(yīng)該有索引； 3、經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引； 4、經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引； 5、索引應(yīng)該建在選擇性高的字段上； SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句優(yōu)化語句優(yōu)化 6、索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引； 7、復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替： A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段； B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出

13、現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句優(yōu)化語句優(yōu)化 C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引； D、如果復(fù)合索引所包含的字段超過3個(gè)，那么仔細(xì)考慮其必要性，考慮減少?gòu)?fù)合的字段； E、如果既有單字段索引，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句優(yōu)化語句優(yōu)化 8、頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引； 9、刪除無用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響；

14、 以上是一些普遍的建立索引時(shí)的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。 SQL優(yōu)化優(yōu)化(oracle)v基于索引的基于索引的SQLSQL語句優(yōu)化語句優(yōu)化 太多的索引與不充分、不正確的索引對(duì)性能都毫無益處：在表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開銷，索引對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開銷。 另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價(jià)值的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，特別是對(duì)頻繁更新的表來說，負(fù)面影響更大。 SQL優(yōu)化優(yōu)化(oracle)v避免對(duì)列的操作避免對(duì)列的操作 任何對(duì)列的操作都可能導(dǎo)致全表掃描，這里所

15、謂的操作包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等式的右邊，甚至去掉函數(shù)。 例1：下列SQL條件語句中的列都建有恰當(dāng)?shù)乃饕?，?0萬行數(shù)據(jù)情況下執(zhí)行速度卻非常慢： select * from record where substrb(CardNo,1,4)=5378(13秒) select * from record where amount/30 1000（11秒） select * from record where to_char(ActionTime,yyyymmdd)=19991201（10秒） SQL優(yōu)化優(yōu)化(oracle)v避免對(duì)列的操作避免對(duì)列的操作 由于whe

16、re子句中對(duì)列的任何操作結(jié)果都是在SQL運(yùn)行時(shí)逐行計(jì)算得到的，因此它不得不進(jìn)行表掃描，而沒有使用該列上面的索引；如果這些結(jié)果在查詢編譯時(shí)就能得到，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引，避免表掃描，因此將SQL重寫如下： select * from record where CardNo like 5378%（ 1秒） select * from record where amount 1000*30（ 1秒） select * from record where ActionTime= to_date (19991201 ,yyyymmdd)（10， 應(yīng)該寫為： select col1,co

17、l2 from tab1 where col110。SQL優(yōu)化優(yōu)化(oracle)v增加查詢的范圍限制增加查詢的范圍限制 增加查詢的范圍限制，避免全范圍的搜索。 例3：以下查詢表record 中時(shí)間ActionTime小于2001年3月1日的數(shù)據(jù)：select * from record where ActionTime to_date (20010301 ,yyyymm) 查詢計(jì)劃表明，上面的查詢對(duì)表進(jìn)行全表掃描，如果我們知道表中的最早的數(shù)據(jù)為2001年1月1日，那么，可以增加一個(gè)最小時(shí)間，使查詢?cè)谝粋€(gè)完整的范圍之內(nèi)。修改如下： SQL優(yōu)化優(yōu)化(oracle)v增加查詢的范圍限制增加查詢的范

18、圍限制 select * from record where ActionTime to_date (20010101 ,yyyymm) 后一種SQL語句將利用上ActionTime字段上的索引，從而提高查詢效率。把20010301換成一個(gè)變量，根據(jù)取值的機(jī)率，可以有一半以上的機(jī)會(huì)提高效率。同理，對(duì)于大于某個(gè)值的查詢，如果知道當(dāng)前可能的最大值，也可以在Where子句中加上 “AND 列名 MAX(最大值)”。SQL優(yōu)化優(yōu)化(oracle)v盡量去掉盡量去掉ININ、OR“OR“ 含有IN、OR的Where子句常會(huì)使用工作表，使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開；拆開的子句中應(yīng)該

19、包含索引。 例4：select count(*) from stuff where id_no in(0,1)（23秒） 可以考慮將or子句分開： select count(*) from stuff where id_no=0 select count(*) from stuff where id_no=1 然后再做一個(gè)簡(jiǎn)單的加法，與原來的SQL語句相比，查詢速度更快。SQL優(yōu)化優(yōu)化(oracle)v盡量去掉盡量去掉 “ 盡量去掉 ，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍固定，則修改為OR方式。 例5：UPDATE SERVICEINFO SET STATE=0 WHERE STATE0

20、; 以上語句由于其中包含了，執(zhí)行計(jì)劃中用了全表掃描（TABLE ACCESS FULL），沒有用到state字段上的索引。實(shí)際應(yīng)用中，由于業(yè)務(wù)邏輯的限制，字段state為枚舉值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉，利用索引來提高效率。 修改為：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。進(jìn)一步的修改可以參考第4種方法。SQL優(yōu)化優(yōu)化(oracle)v去掉去掉WhereWhere子句中的子句中的IS NULLIS NULL和和IS NOT NULLIS NOT NULL Where字句中的IS

21、 NULL和IS NOT NULL將不會(huì)使用索引而是進(jìn)行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，去掉Where子句中的IS NULL和IS NOT NULL。SQL優(yōu)化優(yōu)化(oracle)v索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率 索引的選擇性低，但數(shù)據(jù)的值分布差異很大時(shí)，仍然可以利用索引提高效率。A、數(shù)據(jù)分布不均勻的特殊情況下，選擇性不高的索引也要?jiǎng)?chuàng)建。 表ServiceInfo中數(shù)據(jù)量很大，假設(shè)有一百萬行，其中有一個(gè)字段DisposalCourseFlag，取值范圍為枚舉值：0，1，2，3，4，5，6，7。按照前面說的索引建立的規(guī)則，“選擇性不高的字段

22、不應(yīng)該建立索引，該字段只有8種取值，索引值的重復(fù)率很高，索引選擇性明顯很低，因此不建索引。然而，由于該字段上數(shù)據(jù)值的分布情況非常特殊，具體如下表：SQL優(yōu)化優(yōu)化(oracle)v索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率 而且，常用的查詢中，查詢DisposalCourseFlag6 的情況既多又頻繁，毫無疑問，如果能夠建立索引，并且被應(yīng)用，那么將大大提高這種情況的查詢效率。因此，我們需要在該字段上建立索引。SQL優(yōu)化優(yōu)化(oracle)v利用利用HINTHINT強(qiáng)制指定索引強(qiáng)制指定索引 在ORACLE優(yōu)化器無法用上合理索引的情況下，利用HINT強(qiáng)制指定索引。 繼續(xù)上面7

23、的例子，ORACLE缺省認(rèn)定，表中列的值是在所有數(shù)據(jù)行中均勻分布的，也就是說，在一百萬數(shù)據(jù)量下，每種DisposalCourseFlag值各有12.5萬數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高，ORACLE因此對(duì)索引“視而不見”，從而在查詢路徑的選擇中，用其他字段上的索引甚至全表掃描。根據(jù)我們上面的分析，數(shù)據(jù)值的分布很特殊，嚴(yán)重的不均勻。SQL優(yōu)化優(yōu)化(oracle)v利用利用HINTHINT強(qiáng)制指定索引強(qiáng)制指定索引 為了利用索引提高效率，此時(shí)，一方面可以單獨(dú)對(duì)該字段或該

24、表用analyze語句進(jìn)行分析，對(duì)該列搜集足夠的統(tǒng)計(jì)數(shù)據(jù)，使ORACLE在查詢選擇性較高的值時(shí)能用上索引； 另一方面，可以利用HINT提示，在SELECT關(guān)鍵字后面，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式，強(qiáng)制ORACLE優(yōu)化器用上該索引。 比如： select * from serviceinfo where DisposalCourseFlag=1 ;SQL優(yōu)化優(yōu)化(oracle)v利用利用HINTHINT強(qiáng)制指定索引強(qiáng)制指定索引 上面的語句，實(shí)際執(zhí)行中ORACLE用了全表掃描，加上藍(lán)色提示部分后，用到索引查詢。如下： select /*+ INDEX(SERVICEIN

25、FO,IX_S_DISPOSALCOURSEFLAG) */ * from serviceinfo where DisposalCourseFlag=1; 請(qǐng)注意，這種方法會(huì)加大代碼維護(hù)的難度，而且該字段上索引的名稱被改變之后，必須要同步所有指定索引的HINT代碼，否則HINT提示將被ORACLE忽略掉。SQL優(yōu)化優(yōu)化(oracle)v屏蔽無用索引屏蔽無用索引 繼續(xù)上面8的例子，由于實(shí)際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時(shí)如果用上該字段上的索引，將是非常不明智的，效率也極低。因此這種情況下，我們需要用特殊的方法屏蔽該索引，以便ORACLE選擇其他字段上的索引

26、。比如，如果字段為數(shù)值型的就在表達(dá)式的字段名后，添加“+ 0”，為字符型的就并上空串：“|” 如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = 36 。 不過，不要把該用的索引屏蔽掉了，否則同樣會(huì)產(chǎn)生低效率的全表掃描。SQL優(yōu)化優(yōu)化(oracle)v分解復(fù)雜查詢，用常量代替變量分解復(fù)雜查詢，用常量代替變量 對(duì)于復(fù)雜的Where條件組合，Where中含有多個(gè)帶索引的字段，考慮用IF語句分情況進(jìn)行討論；同時(shí)，去掉不必要的外來參數(shù)條件，減低復(fù)雜度，以便在不同情況下用不同字段上的索引。SQL優(yōu)化優(yōu)化(or

27、acle)v分解復(fù)雜查詢，用常量代替變量分解復(fù)雜查詢，用常量代替變量 繼續(xù)上面9的例子，對(duì)于包含 Where (DisposalCourseFlag v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and .的查詢，(這里v_DisPosalCourseFlag為一個(gè)輸入變量，取值范圍可能為NULL，0，1，2，3，4，5，6，7)，可以考慮分情況用IF語句進(jìn)行討論，類似： IF v_DisPosalCourseFlag =1 THEN Where DisposalCourseFlag = 1 and . ELSIF v_DisP

28、osalCourseFlag =2 THEN Where DisposalCourseFlag = 2 and . 。SQL優(yōu)化優(yōu)化(oracle)vlikelike子句盡量前端匹配子句盡量前端匹配 因?yàn)閘ike參數(shù)使用的非常頻繁，因此如果能夠?qū)ike子句使用索引，將很高的提高查詢的效率。 例6：select * from city where name like %S% 以上查詢的執(zhí)行計(jì)劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為： select * from city where name like S% 那么查詢的執(zhí)行計(jì)劃將會(huì)變成（INDEX RANGE SCA

29、N），成功的利用了name字段的索引。這意味著Oracle SQL優(yōu)化器會(huì)識(shí)別出用于索引的like子句，只要該查詢的匹配端是具體值。因此我們?cè)谧鰈ike查詢時(shí)，應(yīng)該盡量使查詢的匹配端是具體值，即使用like S%。SQL優(yōu)化優(yōu)化(oracle)v用用CaseCase語句合并多重掃描語句合并多重掃描 我們常常必須基于多組數(shù)據(jù)表計(jì)算不同的聚集。例如下例通過三個(gè)獨(dú)立查詢： 例8：1）select count(*) from emp where sal5000; 這樣我們需要進(jìn)行三次全表查詢，但是如果我們使用case語句：SQL優(yōu)化優(yōu)化(oracle)v用用CaseCase語句合并多重掃描語句合并多重

30、掃描 select count (sale when sal 5000 then 1 else null end) count_poor from emp; 這樣查詢的結(jié)果一樣，但是執(zhí)行計(jì)劃只進(jìn)行了一次全表查詢SQL優(yōu)化優(yōu)化(oracle)v使用使用nls_date_formatnls_date_format 例9： select * from record where to_char(ActionTime,mm)=12 這個(gè)查詢的執(zhí)行計(jì)劃將是全表查詢，如果我們改變nls_date_format， SQLalert session set nls_date_formate=MM; 現(xiàn)在重新修改

31、上面的查詢： select * from record where ActionTime=12 這樣就能使用actiontime上的索引了，它的執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）SQL優(yōu)化優(yōu)化(oracle)v使用基于函數(shù)的索引使用基于函數(shù)的索引 前面談到任何對(duì)列的操作都可能導(dǎo)致全表掃描，例如： select * from emp where substr(ename,1,2)=SM; 但是這種查詢?cè)诳头到y(tǒng)又經(jīng)常使用，我們可以創(chuàng)建一個(gè)帶有substr函數(shù)的基于函數(shù)的索引， create index emp_ename_substr on eemp ( substr(ename,

32、1,2) ); 這樣在執(zhí)行上面的查詢語句時(shí)，這個(gè)基于函數(shù)的索引將排上用場(chǎng)，執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。SQL優(yōu)化優(yōu)化(oracle)v基于函數(shù)的索引要求等式匹配基于函數(shù)的索引要求等式匹配 上面的例子中，我們創(chuàng)建了基于函數(shù)的索引，但是如果執(zhí)行下面的查詢： select * from emp where substr(ename,1,1)=S 得到的執(zhí)行計(jì)劃將還是（TABLE ACCESS FULL），因?yàn)橹挥挟?dāng)數(shù)據(jù)列能夠等式匹配時(shí)，基于函數(shù)的索引才能生效，這樣對(duì)于這種索引的計(jì)劃和維護(hù)的要求都很高。請(qǐng)注意，向表中添加索引是非常危險(xiǎn)的操作，因?yàn)檫@將導(dǎo)致許多查詢執(zhí)行計(jì)劃的變更。然

33、而，如果我們使用基于函數(shù)的索引就不會(huì)產(chǎn)生這樣的問題，因?yàn)镺racle只有在查詢使用了匹配的內(nèi)置函數(shù)時(shí)才會(huì)使用這種類型的索引。SQL優(yōu)化優(yōu)化(oracle)v使用分區(qū)索引使用分區(qū)索引 在用分析命令對(duì)分區(qū)索引進(jìn)行分析時(shí)，每一個(gè)分區(qū)的數(shù)據(jù)值的范圍信息會(huì)放入Oracle的數(shù)據(jù)字典中。Oracle可以利用這個(gè)信息來提取出那些只與SQL查詢相關(guān)的數(shù)據(jù)分區(qū)。 例如，假設(shè)你已經(jīng)定義了一個(gè)分區(qū)索引，并且某個(gè)SQL語句需要在一個(gè)索引分區(qū)中進(jìn)行一次索引掃描。Oracle會(huì)僅僅訪問這個(gè)索引分區(qū)，而且會(huì)在這個(gè)分區(qū)上調(diào)用一個(gè)此索引范圍的快速全掃描。因?yàn)椴恍枰L問整個(gè)索引，所以提高了查詢的速度。SQL優(yōu)化優(yōu)化(oracle)v使用位圖索引使用位圖索引 位圖索引可以從本質(zhì)上提高使用了小于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列的查詢速度，因?yàn)樵谖粓D索