ORACLESQL編寫規(guī)范(v1.1)課件

1、ORACLESQL編寫規(guī)范(v1.1)ORACLE SQL編寫規(guī)范編寫規(guī)范v1.12012年11月武明明技術(shù)規(guī)劃部 數(shù)據(jù)庫組 ORACLESQL編寫規(guī)范(v1.1)2 1. 1.SQL（PL/SQL）編碼格式規(guī)范u 統(tǒng)一的SQL編碼格式規(guī)范不但可以使閱讀者感到清晰明了，而且可以最大程度上避免同一SQL語句在不同地方處理時由于書寫格式的不統(tǒng)一，而造成無法共享從而增加SQL解析負(fù)擔(dān)的問題。 比如，如下的三條SQL，其達(dá)到的目的是一樣的，但是在ORACLE看來這是三條完全不同的語句，所以要進(jìn)行三次硬解析。 select * from employees where department_id = 6

2、0; SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60; select /*+ PARALLEL */ * from employees where department_id = 60; 對于聯(lián)機(jī)交易型系統(tǒng)來說，SQL的軟解析率是非常關(guān)注的一個指標(biāo)。而引起SQL不能共享的主要因素包括：大小寫、空格、注釋、提示等。ORACLESQL編寫規(guī)范(v1.1)31.SQL（PL/SQL）編碼格式規(guī)范 一些第三方開發(fā)工具，例如TOAD等，都有比較好的格式化功能，大家可以用其做風(fēng)格統(tǒng)一和美化使用，以下是自定義的一套規(guī)范，可以供大家參考。u 大小寫風(fēng)格大小寫風(fēng)

3、格 所有數(shù)據(jù)庫的關(guān)鍵字和保留字均使用大寫，對象名稱、列名稱則使用小寫。u 縮進(jìn)風(fēng)格縮進(jìn)風(fēng)格 程序塊以及SQL均采取統(tǒng)一的縮進(jìn)風(fēng)格書寫，保持代碼的清晰易懂，風(fēng)格一致，縮進(jìn)格式保持2到4個?？s進(jìn)使用空格，而不要使用【Tab】鍵。 當(dāng)一條SQL中的謂詞和子句比較多時，盡量斷開成多行，可以采用使子句開頭保持一行的方式，謂詞關(guān)鍵字保持右側(cè)對齊，左側(cè)縮進(jìn)的方式。ORACLESQL編寫規(guī)范(v1.1)IF flag = 1 THEN SELECT username INTO v_userinfo FROM userinfo WHERE userid = :iuserid;END IF;u 空格及換行空格及換

4、行 不允許把多個語句寫在一行，即一行只寫一條語句； 避免將復(fù)雜的語句寫在同一行，建議在謂詞和關(guān)鍵字處換行； 相對獨立的程序塊之間必須加空行； BEGIN、END獨立成行； 太長的表達(dá)式應(yīng)在低優(yōu)先級操作符處換行，操作符或關(guān)鍵字放在新行之首，劃分出新行應(yīng)適當(dāng)?shù)乜s進(jìn)，使排列整齊，語句可讀；1.SQL（PL/SQL）編碼格式規(guī)范ORACLESQL編寫規(guī)范(v1.1)1.SQL（PL/SQL）編碼格式規(guī)范 不同類型的操作符混合使用時，建議使用括號進(jìn)行隔離，以使代碼清晰； 減少控制語句的檢查次數(shù)，例如，在IFELSE控制語句中，應(yīng)將最常用的符合條件前置以被檢查到。 DECLARE -定義局部變量 vFla

5、g VARCHAR2(10); -判斷標(biāo)志 .BEGIN IF (a=b AND a=c AND a=d) OR -在OR處斷行，可使得邏輯更為清晰 (a=e AND e=f) THEN-處理部分 IF vFlag = 1 THEN -vFlag=1為經(jīng)常出現(xiàn)的條件，可有效減少判斷檢查次數(shù)-處理部分 ELSIF vFlag =2 THEN -vFlag=2為次之出現(xiàn)的條件-處理部分 ELSE -處理部分 END IF;END;ORACLESQL編寫規(guī)范(v1.1)2.ANSI SQL標(biāo)準(zhǔn)規(guī)范 1989年，美國國家標(biāo)準(zhǔn)協(xié)會（ANSI）第一次發(fā)布了SQL標(biāo)準(zhǔn)規(guī)范，92年被修訂，簡稱SQL-92，目

6、前這個標(biāo)準(zhǔn)為SQL-99。各主要數(shù)據(jù)庫廠商均宣布支持該標(biāo)準(zhǔn)，但因歷史遺留問題，大多也有一些各自的特性在產(chǎn)品中。如果我們在SQL編寫規(guī)范上面做到符合ANSI的標(biāo)準(zhǔn)，在產(chǎn)品向不同的數(shù)據(jù)庫平臺移植的過程中就不會遇到由于某一產(chǎn)品的特殊語法與其他產(chǎn)品不兼容而需要調(diào)整的問題。 ORACLE由于早于ANSI有一套SQL語法定義方式，其與ANSI的不同主要體現(xiàn)在“關(guān)聯(lián)”（JOIN）語句的書寫方式上。 一些使用過ORACLE老版本的SQL編碼人員還是非常習(xí)慣于ORACLE那套老的書寫方式。是否使用ANSI SQL規(guī)范并非強制，可以由項目管理人員決定。如果考慮向不同數(shù)據(jù)庫平臺做移植時減少兼容性帶來的問題，可以參考

7、一下此規(guī)范。ORACLESQL編寫規(guī)范(v1.1)2.ANSI SQL標(biāo)準(zhǔn)規(guī)范 為了對比上的清晰，分別列出ORACLE老版本上的書寫方式（也稱ORACLE方言）與ANSI規(guī)范的主要不同之處。u 交叉連接（CROSS JOIN），也稱笛卡爾連接，是指不限定關(guān)聯(lián)條件的連接ORACLE方言寫法： SELECT * FROM instructor, course; ANSI寫法： SELECT * FROM instructor CROSS JOIN course;ORACLESQL編寫規(guī)范(v1.1)2.ANSI SQL標(biāo)準(zhǔn)規(guī)范u 等價連接（EQUI JOIN），也稱內(nèi)連接（INNER JOIN）或

8、規(guī)則連接（REGULAR JOIN），是指表間有等價連接條件的連接方式。ORACLE方言寫法： SELECT s.first_name, s.last_name, z.zip, z.city, z.state FROM student s, zipcode z WHERE s.zip = z.zip; ANSI寫法1： SELECT s.first_name, s.last_name, z.zip, z.city, z.state FROM student s JOIN zipcode z ON (s.zip=z.zip)；2.ANSI SQL標(biāo)準(zhǔn)規(guī)范ANSI寫法2： SELECT s.fir

9、st_name, s.last_name, zip, z.city, z.state FROM student s JOIN zipcode z USING (zip); 注意注意： ANSI寫法中沒有使用WHERE語句列出連接條件，而是使用了ON或者USING子句。 在使用USING子句的方式中，SELECT語句針對表間連接字段是不能用別名加限定的，如例中的zip字段。ORACLESQL編寫規(guī)范(v1.1)102.ANSI SQL標(biāo)準(zhǔn)規(guī)范多表連接的ORACLE方言寫法： SELECT s.section_no, c.course_no, c.description,i.first_name,

10、i.last_name FROM course c,section s,instructor i WHERE s.course_no = c.course_no AND i.instructor_id = s.instructor_id 多表連接的ANSI寫法： SELECT s.section_no, c.course_no, c.description,i.first_name, i.last_name FROM course c JOIN section s ON (s.course_no = c.course_no) JOIN instructor i ON (i.instructor

11、_id = s.instructor_id); ORACLESQL編寫規(guī)范(v1.1)2.ANSI SQL標(biāo)準(zhǔn)規(guī)范u 外連接（OUTER JOIN） ORACLE的方言寫法：使用（+）來實現(xiàn)外連接 SELECT i.first_name, i.last_name, z.state FROM instructor i, zipcode z WHERE i.zip (+) = z.zip GROUP BY i.first_name, i.last_name, z.state; ANSI標(biāo)準(zhǔn)寫法：使用RIGHT(LEFT) OUTER JOIN ON來實現(xiàn)外連接 SELECT i.first_nam

12、e, i.last_name, z.state FROM instructor i RIGHT OUTER JOIN zipcode z ON i.zip = z.zip GROUP BY i.first_name, z.state; ORACLESQL編寫規(guī)范(v1.1)3.提示（HINT）的書寫注意 提示的作用主要是SQL編寫人員將自己對執(zhí)行計劃的意愿做表達(dá)的一種方式，同時也是穩(wěn)定執(zhí)行計劃的一種最簡單的手段。但在提示的寫法上要有一定的注意，不正確的提示放置位置可能會使優(yōu)化器將其忽略從而失去了其存在的意義。 SELECT /*+ MERGE(v) */ e1.last_name, e1.sa

13、lary, v.avg_salary FROM employees e1, (SELECT /*+ INDEX(e2 idx_employees) */ department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) vWHERE e1.department_id = v.department_id AND e1.salary v.avg_salary; INSERT /*+ APPEND */ INTO big_emp(department_id) SELECT department_id FRO

14、M employees;ORACLESQL編寫規(guī)范(v1.1)3.提示（HINT）的書寫注意 u 提示中的+不能少，如果少了，會被優(yōu)化器當(dāng)做注釋來處理。 u 提示必須跟在語句的第一個關(guān)鍵字后面，如第二個語句中如果在INTO關(guān)鍵字后面放置提示的話，將會被優(yōu)化器忽略。 u 如需要寫多個提示，則可以合在一起，中間使用空格隔開。 u 提示中所引用的表如果定義了別名，則提示中必須引用別名，否則該提示也起不到作用。ORACLESQL編寫規(guī)范(v1.1)4.盡量避免笛卡爾連接 產(chǎn)生笛卡爾連接的原因就是在多張表進(jìn)行關(guān)聯(lián)的操作中缺少了表間的連接條件。由于笛卡爾積產(chǎn)生的結(jié)果集將是多表記錄的乘積關(guān)系，因此當(dāng)哪怕只有

15、一張表的記錄數(shù)比較大時，其結(jié)果集都將被數(shù)倍以上地放大，這勢必給數(shù)據(jù)庫性能帶來嚴(yán)重影響。 因此，除一些特殊原因外，要盡量避免笛卡爾連接的產(chǎn)生，也就是說在寫關(guān)聯(lián)語句時要嚴(yán)格檢查連接條件是否有遺漏。 請編碼及檢查人員注意，不帶連接條件的多表連接語句是嚴(yán)格禁止的，如果有則必須要說明原因。ORACLESQL編寫規(guī)范(v1.1)5.語句中盡量避免使用* 當(dāng)對表中的所有字段不做篩選地全選擇時，可以使用*來替代所有字段。但這樣做有兩個缺點，一是ORACLE優(yōu)化器在執(zhí)行SQL前有一步是查詢轉(zhuǎn)換，這種情況將會被改寫以具體的字段來替代*，查詢轉(zhuǎn)換的操作勢必要加重語句處理的負(fù)擔(dān)。二是這種省事的方式可能會使編碼人員忽略

16、檢查是否有必要查詢表中的所有字段，而在優(yōu)化SQL中有一條原則就是盡量避免多余部分被處理。所以說，不使用*，并且認(rèn)真檢查所提取的每一個字段都是否有必要將是嚴(yán)謹(jǐn)而避免低效的方法。 INSERT語句中列出具體的字段還有一個好處就是可以避免當(dāng)表結(jié)構(gòu)發(fā)生變化時產(chǎn)生編譯性的錯誤。ORACLESQL編寫規(guī)范(v1.1)6.使用TRUNCATE替代不含過濾條件的DELETE 不帶WHERE語句的DELETE，其作用相當(dāng)于全刪除操作。如果表的記錄數(shù)比較多，速度將比較慢，可以使用TRUNCATE語句來替代，TRUNCATE是DDL語句，直接截斷表的空間存儲與表定義之間的關(guān)系。 因為是字典一級的操作，所以速度會非常

17、快，而且無論表記錄數(shù)的多少，正常情況下TRUNCATE語句都會在數(shù)秒內(nèi)完成。但要注意其與DELETE操作的區(qū)別是不需要提交操作，且無法回滾。 TRUNCATE操作可以針對表以及表分區(qū)級，在對分區(qū)數(shù)據(jù)做清理時還是非常有用的。ORACLESQL編寫規(guī)范(v1.1)7. FOR UPDATE語句使用注意 FOR UPDATE語句的作用在于并發(fā)環(huán)境下，某用戶將查詢到的數(shù)據(jù)加鎖，以便后續(xù)的操作過程中，該數(shù)據(jù)不會被其他用戶所修改，這通常用于一些公共模塊的公共處理場景。 由于鎖定操作勢必會影響到并發(fā)性，所以原則就是盡量把條件限定嚴(yán)格，使鎖定的記錄數(shù)最少，并且在后續(xù)的操作完成后盡快提交或回滾事務(wù)，以便其他用戶

18、能盡快得到鎖資源。 除特殊原因外，嚴(yán)格禁止不加過濾條件的SELECT語句中使用FOR UPDATE子句。ORACLESQL編寫規(guī)范(v1.1)8.提交語句(commit)使用的原則 事務(wù)設(shè)計中，為了保證事務(wù)的完整性和有效性必然使用到提交語句。但提交語句在使用上也有一些需要注意的地方。 u 必須及時提交。 這主要是針對高并發(fā)的聯(lián)機(jī)事務(wù)型（OLTP）數(shù)據(jù)庫所考慮的，因為提交會使本事務(wù)所鎖定的資源釋放給其它事務(wù)，以提高并發(fā)性。鎖的設(shè)計是并發(fā)聯(lián)機(jī)事務(wù)所必須要考慮的東西，提交操作又是其中最重要的部分之一。 u 提交的動作不宜過于頻繁。 在不違背第一條原則的前提下，提交又不宜過于頻繁。比如一些循環(huán)語句的內(nèi)

19、部。這一點主要是從性能角度所考慮的。因為提交動作本身會使ORACLE后臺產(chǎn)生一系列操作，將會消耗掉很大的系統(tǒng)資源。究竟循環(huán)處理多少條做一次提交，需要做一些對比測試來最終決定，但過于頻繁的提交必定會影響整體性能。ORACLESQL編寫規(guī)范(v1.1)8.提交語句(commit)使用的原則u 提交操作也不宜過少。 這一條是相對于第二條原則而言的。處理的數(shù)據(jù)量過大而不做提交的話，會消耗比較大的數(shù)據(jù)庫回滾段，甚至有可能導(dǎo)致“回滾段不足”的系統(tǒng)級錯誤發(fā)生。因此在處理數(shù)據(jù)量過大的情況時，中間完全不提交而只是等到最后才做提交的方式也是需要慎重評估和考慮的。最重要的還是做性能與可靠性的綜合測試來確定大數(shù)據(jù)量處

20、理中提交的位置與頻率。ORACLESQL編寫規(guī)范(v1.1)9.9.子查詢語句與關(guān)聯(lián)語句的轉(zhuǎn)換 ORACLE優(yōu)化器做查詢轉(zhuǎn)換這一步時通常會將用戶所編寫的子查詢語句改寫為關(guān)聯(lián)語句，因為在很多情況下關(guān)聯(lián)方式效率要高于子查詢方式。 因此，我們在編寫SQL語句時盡量將子查詢語句改寫成關(guān)聯(lián)語句。ORACLESQL編寫規(guī)范(v1.1)10.關(guān)聯(lián)表個數(shù)限制的基本原則 在報表數(shù)據(jù)庫或批處理數(shù)據(jù)庫中經(jīng)常會有需要關(guān)聯(lián)多張表做查詢的操作，而這些表有的可能會是大表。過多的表做關(guān)聯(lián)可能給性能帶來嚴(yán)重的影響，因此，原則上關(guān)聯(lián)表的個數(shù)規(guī)定不超過4個。 如果不能滿足這個限定條件，可以考慮如下的兩種處理方式：u 對于經(jīng)常被關(guān)聯(lián)

21、使用的個別字段，可以考慮在一邊增加冗余字段的方式來減少關(guān)聯(lián)，這是一種反范式化的處理方式，但經(jīng)常被用于報表查詢類型的系統(tǒng)中。增加冗余字段的方式會給數(shù)據(jù)導(dǎo)入或表插入操作帶來負(fù)載上的增加，因此這種方式也要綜合評估和取舍。 u 使用中間結(jié)果落地的方式。這種方式就是將原來一個SQL完成的操作拆開成多個SQL進(jìn)行，將某兩張或三張表的關(guān)聯(lián)結(jié)果先取出，然后再拿結(jié)果集與剩下的表繼續(xù)做關(guān)聯(lián)，得到最終完整的結(jié)果。在做分拆過程中表的選取時要遵循的一個原則是，分拆出的兩個或多個SQL其處理的結(jié)果集要盡量均衡，否則就使分拆的作用打了折扣。ORACLESQL編寫規(guī)范(v1.1)11.列放置順序的原則 在設(shè)計時，表中各個列的

22、放置順序要有一定的考慮。通常情況是按照操作的頻繁程度為判定，操作頻繁的列盡量往前放置，因為放置越靠前的列其處理的整體效率是越高的。ORACLESQL編寫規(guī)范(v1.1)12.表及分區(qū)表的類型u 堆表（HEAP） 默認(rèn)類型，適合于大多數(shù)情況u 索引組織表（IOT） 以B*Tree索引的形式組織表，適合于只按 照主鍵進(jìn)行查詢的數(shù)據(jù)u 簇表（CLUSTER） 以簇結(jié)構(gòu)建表并組織數(shù)據(jù)，適合于經(jīng)常被 關(guān)聯(lián)使用的表 u 外部表（EXTERNAL） 方便使用數(shù)據(jù)庫直接處理庫外的文本文 件數(shù)據(jù)u 全局臨時表（GLOBAL TEMPORARY） 放置事務(wù)處理過程中的數(shù)據(jù) 且可以做到隔離u 高級隊列表（AQ） 建

23、AQ時系統(tǒng)自動創(chuàng)建并維護(hù)ORACLESQL編寫規(guī)范(v1.1)12.表及分區(qū)表的類型u ORALCE10G中的分區(qū)表類型：列表 LIST 適合于有限分布的固定值，比如機(jī)構(gòu)名稱范圍 RANGE 適合于范圍取值，比如時間哈希 HASH 適合于不好分類的情形，如序列產(chǎn)生的號碼組合 RANGE -LIST 組合 RANGE -HASHu ORALCE11G中增加的分區(qū)表類型：組合 RANGE-RANGE 組合 LIST-LIST組合 LIST-RANGE組合 LIST-HASHORACLESQL編寫規(guī)范(v1.1)13.索引的類型與選擇u B樹索引（B*Tree） 默認(rèn)類型，適用范圍最廣 降序索引 適

24、用于字段上還需要做降序排序 反向關(guān)鍵字索引 減少類似SEQUENCE生成數(shù)據(jù)時其索引上的熱塊沖突 函數(shù)索引 過濾字段上有函數(shù)或隱含轉(zhuǎn)換函數(shù)u 位圖索引（BITMAP） 低基數(shù)字段，且表的修改非常少的情況u 全文索引 用于搜索詞匯信息等特殊情景，較少使用 索引要在做過濾的字段上考慮，但總體原則是注意選擇性，選擇性如果超過10%則要慎重考慮，超過20%則不要建立索引。 如果有多個過濾字段，可以考慮組合索引，但要將選擇性強的字段放在前面。 如果查詢字段較少，且與過濾字段接近，則可以考慮將查詢字段也加入組合索引中，這時可以只使用索引掃描而不需要再掃描表，執(zhí)行效率會更高。ORACLESQL編寫規(guī)范(v1

25、.1)13.索引的類型與選擇 索引在提高查詢語句效率的同時會降低DML語句的效率，如何權(quán)衡兩者之間關(guān)系需要多做考慮和測試。在生產(chǎn)系統(tǒng)中如果發(fā)現(xiàn)沒有使用過或者效率比較差的索引，最好考慮刪除掉。 對于有大量DML操作的表，其索引增長可能會較快，而索引的頁塊中又可能存在大量的空隙使得索引的使用效率逐漸降低。所以針對這種情況，應(yīng)該在非業(yè)務(wù)時間定期做索引重建等維護(hù)工作。 大表做分區(qū)后，原則上分區(qū)的索引都要考慮使用本地（LOCAL）索引。即使可能會犧牲某些全局查詢時檢索的效率，但考慮到這種情況相對較少，而本地索引在應(yīng)用能夠進(jìn)入分區(qū)后的查詢效率要高很多，并且不會產(chǎn)生某些分區(qū)維護(hù)操作使索引失效的風(fēng)險。ORAC

26、LESQL編寫規(guī)范(v1.1)14.模糊查詢語句的使用注意ORACLE的模糊查詢主要有以下三種形式： 后模糊：col like ABC% 前模糊：col like %ABC 全模糊：col like %ABC% 后模糊是最好的，在col字段上建立索引是可以被優(yōu)化器選擇的，并且是效率比較高的索引范圍掃描方式，所以要盡量采取或轉(zhuǎn)換成這種形式。 前模糊形式即使在col字段上建立索引通常也還是不會被使用，即使使用效率也不會太高。所以這種方式是要盡量避免的，或者采取一些變通的手段比如采取反轉(zhuǎn)函數(shù)等，但基本都需要改寫原來的代碼。 ORACLESQL編寫規(guī)范(v1.1)14.模糊查詢語句的使用注意 全模糊形

27、式普通索引的效率也會很差，寫法上也是需要盡量避免的。雖然可以采用全文索引的方式來達(dá)到提高索引效率的目的，但全文索引相對復(fù)雜且占用空間要比普通索引大很多，且索引維護(hù)時要消耗更多資源，設(shè)計上需要綜合考慮。ORACLESQL編寫規(guī)范(v1.1)15.注意索引無法被使用的情況 索引通常是提高語句查詢效率的最常用手段之一，但如下一些容易被大家忽視的情況會導(dǎo)致索引不被優(yōu)化器選擇（能用但不用），或者根本就不能被使用。 u 被索引字段在過濾謂詞中使用了IS NULL或IS NOT NULL原則上應(yīng)盡量避免這種寫法。 首先，普通的B-TREE索引是根本無法用于IS NULL條件的，雖然可以強制用于IS NOT

28、NULL條件，但效率往往都比較差。 如果數(shù)據(jù)分布適合建立位圖索引的話，這兩個條件是可以使用位圖索引的，但要注意的是位圖索引所適用的數(shù)據(jù)分布，數(shù)據(jù)的靜態(tài)性條件，以及聯(lián)機(jī)交易型系統(tǒng)是嚴(yán)格禁止使用位圖索引的。ORACLESQL編寫規(guī)范(v1.1)15.注意索引無法被使用的情況u 被索引字段在過濾謂詞上使用了全模糊查詢的方式如col like %ABC% 這種情況的處理原則還是盡量避免或者進(jìn)行改寫，具體方法可參照前文有關(guān)模糊查詢的部分。u 被索引字段在過濾謂詞中使用了!=，NOT IN等 這種情況的處理原則還是盡量避免或者進(jìn)行改寫，比如說取不等于或NOT IN部分的反，然后就可以將條件改為等于或者是I

29、N了。但要評估各自占總記錄數(shù)的比例，如果占比過高的話，索性就用全表掃描了。ORACLESQL編寫規(guī)范(v1.1)15.注意索引無法被使用的情況u被索引字段在過濾謂詞中使用了函數(shù)。此時除非建立函數(shù)索引，否則普通索引是無法被使用的，如例： WHERE TO_CHAR(zip) = 94002; 類似這種情況我們可以將改寫一下，將計算移動到等號右側(cè)： WHERE zip = TO_NUMBER(94002); 改寫的原則就是使過濾謂詞一邊，也就是左側(cè)保持“干凈”，這樣在zip字段上的普通索引就可以被使用了。因為畢竟函數(shù)索引在做索引維護(hù)的時候成本是比較高的，而且在zip字段上所建立的函數(shù)索引只能用于這

30、種使用了該函數(shù)的場景，針對其它沒使用函數(shù)的zip字段直接做過濾條件的語句是無法共用的，整體使用效率就打了折扣。 ORACLESQL編寫規(guī)范(v1.1)15.注意索引無法被使用的情況 因此，但凡能夠通過改寫解決問題的情況就不要建立函數(shù)索引。如果實在改寫不了，評估建立函數(shù)索引后效率會有所提高后，也要考慮建立，畢竟全表掃描的情況是要盡量避免的。ORACLESQL編寫規(guī)范(v1.1)16.減少REDO日志生成的一些手段 通過一些手段可以達(dá)到減少某些SQL的REDO日志生成量，從而達(dá)到提高效率的目的。對于報表系統(tǒng)、批處理系統(tǒng)、大批量數(shù)據(jù)加載等操作還是有一定的現(xiàn)實意義。 但切記一點的是，對關(guān)鍵的業(yè)務(wù)數(shù)據(jù)庫

31、是禁止使用該方法的，因為減少了REDO日志會使數(shù)據(jù)庫做恢復(fù)時可能丟失一些沒有被記錄日志的數(shù)據(jù)，在對數(shù)據(jù)的完整性要求非常嚴(yán)格的系統(tǒng)中，這種操作是應(yīng)該被禁止的。ORACLESQL編寫規(guī)范(v1.1)16.減少REDO日志生成的一些手段u CTAS語句CREATE TABLE a2 AS SELECT * FROM a1; -未使用CREATE TABLE a2 NOLOGGING AS SELECT * FROM a1; -使用了 NOLOGGING u 創(chuàng)建（重建）索引CREATE INDEX idx_t1 ON t1(col1); -未使用CREATE INDEX idx_t1 ON t1(c

32、ol1) NOLOGGING; -使用了NOLOGGINGALTER INDEX idx_t1 REBUILD; -未使用ALTER INDEX idx_t1 REBUILD NOLOGGING; -使用了NOLOGGING u 移動表ALTER TABLE t1 MOVE; -未使用ALTER TABLE t1 MOVE NOLOGGING; -使用了NOLOGGINGORACLESQL編寫規(guī)范(v1.1)16.減少REDO日志生成的一些手段u 直接路徑插入INSERT INTO t2 SELECT * FROM t1; -未使用ALTER TABLE t2 NOLOGGING; -使用了N

33、OLOGGINGINSERT /*+APPEND*/ INTO t2 SELECT * FROM t1; -使用了APPEND 注意：APPEND提示要與NOLOGGING結(jié)合使用； 對于INSERT INTO VALUES語句，不要使用APPEND提示， 因為起不到作用； 使用了APPEDN提示后的插入語句必須及時提交，這也限制了并 發(fā)性，因此在聯(lián)機(jī)交易型系統(tǒng)中禁止使用該提示。 u SQL*Loader 直接路徑裝載使用APPEND的方式加載數(shù)據(jù)。SQL*Loader另外的三種裝載方式為INSERT（默認(rèn)）、REPLACE、TRUNCATE。ORACLESQL編寫規(guī)范(v1.1)17.SQL

34、中實現(xiàn)并行操作 使用并行操作的主要目的是提高語句的執(zhí)行效率，但是否考慮使用并行操作要遵守以下的幾點原則： 一定要在多CPU的服務(wù)器上測試并行操作的效果，在單CPU的PC機(jī)上是很難看到效果的，容易使測試失真。 針對于報表系統(tǒng)、批處理系統(tǒng)、OLAP系統(tǒng)可以考慮使用并行技術(shù)，聯(lián)機(jī)交易型（OLTP）系統(tǒng)不要輕易使用。 由于數(shù)據(jù)量比較巨大導(dǎo)致SQL執(zhí)行非常緩慢，但發(fā)現(xiàn)硬件資源CPU、內(nèi)存、I/O（尤其是CPU）負(fù)擔(dān)比較輕時，可以考慮使用并行技術(shù)。如果硬件資源已經(jīng)發(fā)現(xiàn)不足甚至已經(jīng)成為瓶頸時，先不要考慮使用并行技術(shù)。ORACLESQL編寫規(guī)范(v1.1)17.SQL中實現(xiàn)并行操作從語句類型上來分，并行操作可

35、以分為如下的幾種類型： u 并行查詢可以有兩種方式實現(xiàn)并行查詢： 將表的并行屬性打開，并行度可以設(shè)置，也可以不設(shè)置。如果不設(shè)置并行度，則會使用默認(rèn)值，也就是ORACLE自己來計算并行度。打開并行的方式如下：ALTER TABLE t1 PARALLEL 4; -打開t1表的并行屬性，并且設(shè)置并行度為4ALTER TABLE t1 PARALLEL;-打開t1表的并行屬性，并且設(shè)置并行度為默認(rèn)值CREATE TABLE t1 PARALLEL 4; -創(chuàng)建t1表時就打開了并行屬性，并且設(shè)置了并行度，不寫并行度時也同樣是設(shè)置了默認(rèn)值 打開了表上的并行屬性后，查詢語句不需要做任何修改，即實現(xiàn)了并行查

36、詢，SELECT COUNT(col1) FROM t1;ORACLESQL編寫規(guī)范(v1.1)17.SQL中實現(xiàn)并行操作2. 不需要在表上打開并行，而是通過提示的方式，實現(xiàn)并行查詢：SELECT /*+ PARALLEL(t1 4) */ COUNT(col1) FROM t1; -提示的方式實現(xiàn)并行，并設(shè)置并行度為4SELECT /*+ PARALLEL */ COUNT(col1) FROM t1; -提示的方式實現(xiàn)并行，并設(shè)置并行度為默認(rèn)值 u 并行DDL 可以并行化的DDL包括如下一些示例，可以針對表、表分區(qū)、索引、索引分區(qū)等： CREATE INDEX PARALLEL 4; AL

37、TER INDEX REBUILD PARALLEL 4;ALTER INDEX SPLIT PARTITION PARALLEL 4;CREATE TABLE AS SELECT PARALLEL 4;ALTER TABLE MOVE PARALLEL 4;ALTER TABLE SPLIT|COALESCE PARALLEL 4;ORACLESQL編寫規(guī)范(v1.1)17.SQL中實現(xiàn)并行操作 注意：并行DDL語句是無法通過提示的方式實現(xiàn)的，在ALTER語句中也不需要被修改的表或者索引在定義時就打開并行屬性。只需要在正常的DDL語句的末尾增加PARALLEL關(guān)鍵字即可。并行度可以跟在PAR

38、ALLEL關(guān)鍵字的后面，如果不寫并行度，則是取默認(rèn)值，將由ORACLE自行決定并行度。關(guān)于并行度的選擇，可以遵循以下原則：1. 如果我們自己設(shè)置并行度，那么要綜合考慮服務(wù)器的CPU的顆數(shù)，每顆的核（線程）數(shù)，實例個數(shù)（RAC系統(tǒng)會有意義），系統(tǒng)CPU資源總體的利用效率，以及系統(tǒng)中同一時間可能會有多少個啟動并行執(zhí)行的任務(wù)。2. 總的原則是同一時間啟動并行執(zhí)行的所有任務(wù)的總的并行度不超過CPU顆數(shù)*CPU核數(shù)（線程數(shù)）*實例個數(shù)。3. 如果判斷當(dāng)前時間只會有自己這一個大的SQL在執(zhí)行，那么其上開的并行度最大值就是以上公式計算出的取值。但考慮系統(tǒng)CPU整體的負(fù)載情況后，并行度一定要小于該取值。而且并

39、行度并非越大越好，達(dá)到一定的值后有可能反倒設(shè)得越高性能越差。所以具體的并行度設(shè)置還是要謹(jǐn)慎，最好測試對比一下。ORACLESQL編寫規(guī)范(v1.1)17.SQL中實現(xiàn)并行操作4. 如果不好估計系統(tǒng)的其它任務(wù)情況以及CPU負(fù)載情況也不穩(wěn)定，則可以采取默認(rèn)值的方式。這時ORACLE會根據(jù)系統(tǒng)的資源使用情況動態(tài)地調(diào)整每個并行SQL并行度的取值。最重要的一個原則就是對于一個CPU數(shù)量很低或者CPU負(fù)載已經(jīng)很高（比如已經(jīng)長時間超過了70%）的情況，則嚴(yán)格禁止再啟動語句的并行。已經(jīng)開啟的并行，可以考慮降低并行度或者關(guān)閉并行。u 并行DML 并行DML主要是針對INSERT、DELETE、UPDATE、ME

40、RGE等DML語句上實現(xiàn)并行執(zhí)行。 但與并行查詢以及并行DDL都有區(qū)別的一點是，并行DML默認(rèn)是不開啟的，在執(zhí)行語句之前需要執(zhí)行如下一句作為并行功能的開啟。 ALTER SESSION ENABLE PARALLEL DML;ORACLESQL編寫規(guī)范(v1.1)17.SQL中實現(xiàn)并行操作 開啟了并行功能之后，就可以在接下來的DML中實現(xiàn)并行執(zhí)行了。 UPDATE /*+ PARALLEL(test 4) */ t1 SET OBJECT_NAME=abc; -提示的方式打開表上的并行,并設(shè)置并行度為4 ALTER TABLE t1 PARALLEL 4; -打開表上的并行屬性UPDATE t

41、1 SET OBJECT_NAME=abc; -無須提示，即可實現(xiàn)并行執(zhí)行 并行DML的實現(xiàn)方式與并行查詢類似，可以使用提示的方式，也可以通過在表或索引上打開并行，然后直接執(zhí)行的方式。但為了避免整體并行度的不可控，建議采取提示的方式。ORACLESQL編寫規(guī)范(v1.1)18.分頁查詢語句的寫法SELECT * FROM (SELECT a.*, rownum rn FROM (SELECT * FROM table_name) a -內(nèi)層為原始查詢語句 WHERE rownum = 1 這是一個最常見的分頁查詢的寫法，正確的寫法可以使查詢的性能得到優(yōu)化，在表的數(shù)據(jù)量很大的情況下也能盡快的返回

42、結(jié)果集，往后查詢時性能即使下降也不會下降很多。 內(nèi)層的SELECT * FROM table_name 是最原始的查詢語句。 改分頁查詢時首先在原有查詢語句外面包一層，這一層中再取一個偽列ORACLESQL編寫規(guī)范(v1.1)18.分頁查詢語句的寫法rownum，并為其取一個別名，直接使用rownum取分頁的上邊界；然后再在外面包一層，在這一層中用上一層取的別名寫分頁的下邊界。 以下是一個比較典型的分頁寫法不對的例子。 第一個文件中SQL雖然也是每次取15行數(shù)據(jù)，但是沒有達(dá)到性能優(yōu)化的目的，第一頁就已經(jīng)很慢了，往后會更慢。 第二個文件是修改后的情況，可以看到效果非常明顯。修改前查第一頁的耗時是39.91秒，修改后為0.07秒。通過執(zhí)行計劃和SQL的狀態(tài)信息都可以明顯地看到兩者的差別。ORACLESQL編寫規(guī)范(v1.1)19.盡量減少數(shù)據(jù)庫處理的負(fù)擔(dān) 這條原則看似一句很虛的話，但其實有著很現(xiàn)實的意義。幾乎所有的數(shù)據(jù)庫優(yōu)化手段其達(dá)到的最終目的都是減少了數(shù)據(jù)庫的處理負(fù)擔(dān)。比如說 ： u 使用索引達(dá)到優(yōu)化效果其實是縮減少了所掃描字段的數(shù)量以及優(yōu)化了掃描算法，最終降低了數(shù)據(jù)庫處理時邏輯讀的數(shù)量。 u 使用分區(qū)表其實是縮小了原來的掃描范圍，從而減輕了數(shù)據(jù)庫的處理負(fù)擔(dān)。 u 使用并行操作其實是利用空閑的CPU資源分擔(dān)