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

ORACLE SQL編寫規(guī)范 v1.1,2012年11月,武明明 技術(shù)規(guī)劃部 數(shù)據(jù)庫(kù)組 ,2,1.SQL（PL/SQL）編碼格式規(guī)范,統(tǒng)一的SQL編碼格式規(guī)范不但可以使閱讀者感到清晰明了，而且可以最大程度上避免同一SQL語(yǔ)句在不同地方處理時(shí)由于書寫格式的不統(tǒng)一，而造成無(wú)法共享從而增加SQL解析負(fù)擔(dān)的問(wèn)題。 比如，如下的三條SQL，其達(dá)到的目的是一樣的，但是在ORACLE看來(lái)這是三條完全不同的語(yǔ)句，所以要進(jìn)行三次硬解析。 select * from employees where department_id = 60; SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60; select /*+ PARALLEL */ * from employees where department_id = 60; 對(duì)于聯(lián)機(jī)交易型系統(tǒng)來(lái)說(shuō)，SQL的軟解析率是非常關(guān)注的一個(gè)指標(biāo)。而引起SQL不能共享的主要因素包括：大小寫、空格、注釋、提示等。,3,1.SQL（PL/SQL）編碼格式規(guī)范,一些第三方開(kāi)發(fā)工具，例如TOAD等，都有比較好的格式化功能，大家可以用其做風(fēng)格統(tǒng)一和美化使用，以下是自定義的一套規(guī)范，可以供大家參考。 大小寫風(fēng)格 所有數(shù)據(jù)庫(kù)的關(guān)鍵字和保留字均使用大寫，對(duì)象名稱、列名稱則使用小寫。 縮進(jìn)風(fēng)格 程序塊以及SQL均采取統(tǒng)一的縮進(jìn)風(fēng)格書寫，保持代碼的清晰易懂，風(fēng)格一致，縮進(jìn)格式保持2到4個(gè)?？s進(jìn)使用空格，而不要使用【Tab】鍵。 當(dāng)一條SQL中的謂詞和子句比較多時(shí)，盡量斷開(kāi)成多行，可以采用使子句開(kāi)頭保持一行的方式，謂詞關(guān)鍵字保持右側(cè)對(duì)齊，左側(cè)縮進(jìn)的方式。,IF flag = 1 THEN SELECT username INTO v_userinfo FROM userinfo WHERE userid = :iuserid; END IF; 空格及換行 不允許把多個(gè)語(yǔ)句寫在一行，即一行只寫一條語(yǔ)句； 避免將復(fù)雜的語(yǔ)句寫在同一行，建議在謂詞和關(guān)鍵字處換行； 相對(duì)獨(dú)立的程序塊之間必須加空行； BEGIN、END獨(dú)立成行； 太長(zhǎng)的表達(dá)式應(yīng)在低優(yōu)先級(jí)操作符處換行，操作符或關(guān)鍵字放在新行之首，劃分出新行應(yīng)適當(dāng)?shù)乜s進(jìn)，使排列整齊，語(yǔ)句可讀；,1.SQL（PL/SQL）編碼格式規(guī)范,1.SQL（PL/SQL）編碼格式規(guī)范,不同類型的操作符混合使用時(shí)，建議使用括號(hào)進(jìn)行隔離，以使代碼清晰； 減少控制語(yǔ)句的檢查次數(shù)，例如，在IFELSE控制語(yǔ)句中，應(yīng)將最常用的符合條件前置以被檢查到。 DECLARE -定義局部變量 vFlag 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;,2.ANSI SQL標(biāo)準(zhǔn)規(guī)范,1989年，美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì)（ANSI）第一次發(fā)布了SQL標(biāo)準(zhǔn)規(guī)范，92年被修訂，簡(jiǎn)稱SQL-92，目前這個(gè)標(biāo)準(zhǔn)為SQL-99。各主要數(shù)據(jù)庫(kù)廠商均宣布支持該標(biāo)準(zhǔn)，但因歷史遺留問(wèn)題，大多也有一些各自的特性在產(chǎn)品中。如果我們?cè)赟QL編寫規(guī)范上面做到符合ANSI的標(biāo)準(zhǔn)，在產(chǎn)品向不同的數(shù)據(jù)庫(kù)平臺(tái)移植的過(guò)程中就不會(huì)遇到由于某一產(chǎn)品的特殊語(yǔ)法與其他產(chǎn)品不兼容而需要調(diào)整的問(wèn)題。 ORACLE由于早于ANSI有一套SQL語(yǔ)法定義方式，其與ANSI的不同主要體現(xiàn)在“關(guān)聯(lián)”（JOIN）語(yǔ)句的書寫方式上。 一些使用過(guò)ORACLE老版本的SQL編碼人員還是非常習(xí)慣于ORACLE那套老的書寫方式。是否使用ANSI SQL規(guī)范并非強(qiáng)制，可以由項(xiàng)目管理人員決定。如果考慮向不同數(shù)據(jù)庫(kù)平臺(tái)做移植時(shí)減少兼容性帶來(lái)的問(wèn)題，可以參考一下此規(guī)范。,2.ANSI SQL標(biāo)準(zhǔn)規(guī)范,為了對(duì)比上的清晰，分別列出ORACLE老版本上的書寫方式（也稱ORACLE方言）與ANSI規(guī)范的主要不同之處。 交叉連接（CROSS JOIN），也稱笛卡爾連接，是指不限定關(guān)聯(lián)條件的連接 ORACLE方言寫法： SELECT * FROM instructor, course; ANSI寫法： SELECT * FROM instructor CROSS JOIN course;,2.ANSI SQL標(biāo)準(zhǔn)規(guī)范,等價(jià)連接（EQUI JOIN），也稱內(nèi)連接（INNER JOIN）或規(guī)則連接（REGULAR JOIN），是指表間有等價(jià)連接條件的連接方式。 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.first_name, s.last_name, zip, z.city, z.state FROM student s JOIN zipcode z USING (zip); 注意： ANSI寫法中沒(méi)有使用WHERE語(yǔ)句列出連接條件，而是使用了ON或者USING子句。 在使用USING子句的方式中，SELECT語(yǔ)句針對(duì)表間連接字段是不能用別名加限定的，如例中的zip字段。,10,2.ANSI SQL標(biāo)準(zhǔn)規(guī)范,多表連接的ORACLE方言寫法： SELECT s.section_no, c.course_no, c.description,i.first_name,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_id = s.instructor_id);,2.ANSI SQL標(biāo)準(zhǔn)規(guī)范,外連接（OUTER JOIN） ORACLE的方言寫法：使用（+）來(lái)實(shí)現(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來(lái)實(shí)現(xiàn)外連接 SELECT i.first_name, 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;,3.提示（HINT）的書寫注意,提示的作用主要是SQL編寫人員將自己對(duì)執(zhí)行計(jì)劃的意愿做表達(dá)的一種方式，同時(shí)也是穩(wěn)定執(zhí)行計(jì)劃的一種最簡(jiǎn)單的手段。但在提示的寫法上要有一定的注意，不正確的提示放置位置可能會(huì)使優(yōu)化器將其忽略從而失去了其存在的意義。 SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary FROM employees e1, (SELECT /*+ INDEX(e2 idx_employees) */ department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) v WHERE e1.department_id = v.department_id AND e1.salary v.avg_salary; INSERT /*+ APPEND */ INTO big_emp(department_id) SELECT department_id FROM employees;,3.提示（HINT）的書寫注意,提示中的+不能少，如果少了，會(huì)被優(yōu)化器當(dāng)做注釋來(lái)處理。 提示必須跟在語(yǔ)句的第一個(gè)關(guān)鍵字后面，如第二個(gè)語(yǔ)句中如果在INTO關(guān)鍵字后面放置提示的話，將會(huì)被優(yōu)化器忽略。 如需要寫多個(gè)提示，則可以合在一起，中間使用空格隔開(kāi)。 提示中所引用的表如果定義了別名，則提示中必須引用別名，否則該提示也起不到作用。,4.盡量避免笛卡爾連接,產(chǎn)生笛卡爾連接的原因就是在多張表進(jìn)行關(guān)聯(lián)的操作中缺少了表間的連接條件。由于笛卡爾積產(chǎn)生的結(jié)果集將是多表記錄的乘積關(guān)系，因此當(dāng)哪怕只有一張表的記錄數(shù)比較大時(shí)，其結(jié)果集都將被數(shù)倍以上地放大，這勢(shì)必給數(shù)據(jù)庫(kù)性能帶來(lái)嚴(yán)重影響。 因此，除一些特殊原因外，要盡量避免笛卡爾連接的產(chǎn)生，也就是說(shuō)在寫關(guān)聯(lián)語(yǔ)句時(shí)要嚴(yán)格檢查連接條件是否有遺漏。 請(qǐng)編碼及檢查人員注意，不帶連接條件的多表連接語(yǔ)句是嚴(yán)格禁止的，如果有則必須要說(shuō)明原因。,5.語(yǔ)句中盡量避免使用*,當(dāng)對(duì)表中的所有字段不做篩選地全選擇時(shí)，可以使用*來(lái)替代所有字段。但這樣做有兩個(gè)缺點(diǎn)，一是ORACLE優(yōu)化器在執(zhí)行SQL前有一步是查詢轉(zhuǎn)換，這種情況將會(huì)被改寫以具體的字段來(lái)替代*，查詢轉(zhuǎn)換的操作勢(shì)必要加重語(yǔ)句處理的負(fù)擔(dān)。二是這種省事的方式可能會(huì)使編碼人員忽略檢查是否有必要查詢表中的所有字段，而在優(yōu)化SQL中有一條原則就是盡量避免多余部分被處理。所以說(shuō)，不使用*，并且認(rèn)真檢查所提取的每一個(gè)字段都是否有必要將是嚴(yán)謹(jǐn)而避免低效的方法。 INSERT語(yǔ)句中列出具體的字段還有一個(gè)好處就是可以避免當(dāng)表結(jié)構(gòu)發(fā)生變化時(shí)產(chǎn)生編譯性的錯(cuò)誤。,6.使用TRUNCATE替代不含過(guò)濾條件的DELETE,不帶WHERE語(yǔ)句的DELETE，其作用相當(dāng)于全刪除操作。如果表的記錄數(shù)比較多，速度將比較慢，可以使用TRUNCATE語(yǔ)句來(lái)替代，TRUNCATE是DDL語(yǔ)句，直接截?cái)啾淼目臻g存儲(chǔ)與表定義之間的關(guān)系。 因?yàn)槭亲值湟患?jí)的操作，所以速度會(huì)非常快，而且無(wú)論表記錄數(shù)的多少，正常情況下TRUNCATE語(yǔ)句都會(huì)在數(shù)秒內(nèi)完成。但要注意其與DELETE操作的區(qū)別是不需要提交操作，且無(wú)法回滾。 TRUNCATE操作可以針對(duì)表以及表分區(qū)級(jí)，在對(duì)分區(qū)數(shù)據(jù)做清理時(shí)還是非常有用的。,7. FOR UPDATE語(yǔ)句使用注意,FOR UPDATE語(yǔ)句的作用在于并發(fā)環(huán)境下，某用戶將查詢到的數(shù)據(jù)加鎖，以便后續(xù)的操作過(guò)程中，該數(shù)據(jù)不會(huì)被其他用戶所修改，這通常用于一些公共模塊的公共處理場(chǎng)景。 由于鎖定操作勢(shì)必會(huì)影響到并發(fā)性，所以原則就是盡量把條件限定嚴(yán)格，使鎖定的記錄數(shù)最少，并且在后續(xù)的操作完成后盡快提交或回滾事務(wù)，以便其他用戶能盡快得到鎖資源。 除特殊原因外，嚴(yán)格禁止不加過(guò)濾條件的SELECT語(yǔ)句中使用FOR UPDATE子句。,8.提交語(yǔ)句(commit)使用的原則,事務(wù)設(shè)計(jì)中，為了保證事務(wù)的完整性和有效性必然使用到提交語(yǔ)句。但提交語(yǔ)句在使用上也有一些需要注意的地方。 必須及時(shí)提交。 這主要是針對(duì)高并發(fā)的聯(lián)機(jī)事務(wù)型（OLTP）數(shù)據(jù)庫(kù)所考慮的，因?yàn)樘峤粫?huì)使本事務(wù)所鎖定的資源釋放給其它事務(wù)，以提高并發(fā)性。鎖的設(shè)計(jì)是并發(fā)聯(lián)機(jī)事務(wù)所必須要考慮的東西，提交操作又是其中最重要的部分之一。 提交的動(dòng)作不宜過(guò)于頻繁。 在不違背第一條原則的前提下，提交又不宜過(guò)于頻繁。比如一些循環(huán)語(yǔ)句的內(nèi)部。這一點(diǎn)主要是從性能角度所考慮的。因?yàn)樘峤粍?dòng)作本身會(huì)使ORACLE后臺(tái)產(chǎn)生一系列操作，將會(huì)消耗掉很大的系統(tǒng)資源。究竟循環(huán)處理多少條做一次提交，需要做一些對(duì)比測(cè)試來(lái)最終決定，但過(guò)于頻繁的提交必定會(huì)影響整體性能。,8.提交語(yǔ)句(commit)使用的原則,提交操作也不宜過(guò)少。 這一條是相對(duì)于第二條原則而言的。處理的數(shù)據(jù)量過(guò)大而不做提交的話，會(huì)消耗比較大的數(shù)據(jù)庫(kù)回滾段，甚至有可能導(dǎo)致“回滾段不足”的系統(tǒng)級(jí)錯(cuò)誤發(fā)生。因此在處理數(shù)據(jù)量過(guò)大的情況時(shí)，中間完全不提交而只是等到最后才做提交的方式也是需要慎重評(píng)估和考慮的。最重要的還是做性能與可靠性的綜合測(cè)試來(lái)確定大數(shù)據(jù)量處理中提交的位置與頻率。,9.子查詢語(yǔ)句與關(guān)聯(lián)語(yǔ)句的轉(zhuǎn)換,ORACLE優(yōu)化器做查詢轉(zhuǎn)換這一步時(shí)通常會(huì)將用戶所編寫的子查詢語(yǔ)句改寫為關(guān)聯(lián)語(yǔ)句，因?yàn)樵诤芏嗲闆r下關(guān)聯(lián)方式效率要高于子查詢方式。 因此，我們?cè)诰帉慡QL語(yǔ)句時(shí)盡量將子查詢語(yǔ)句改寫成關(guān)聯(lián)語(yǔ)句。,10.關(guān)聯(lián)表個(gè)數(shù)限制的基本原則,在報(bào)表數(shù)據(jù)庫(kù)或批處理數(shù)據(jù)庫(kù)中經(jīng)常會(huì)有需要關(guān)聯(lián)多張表做查詢的操作，而這些表有的可能會(huì)是大表。過(guò)多的表做關(guān)聯(lián)可能給性能帶來(lái)嚴(yán)重的影響，因此，原則上關(guān)聯(lián)表的個(gè)數(shù)規(guī)定不超過(guò)4個(gè)。 如果不能滿足這個(gè)限定條件，可以考慮如下的兩種處理方式： 對(duì)于經(jīng)常被關(guān)聯(lián)使用的個(gè)別字段，可以考慮在一邊增加冗余字段的方式來(lái)減少關(guān)聯(lián)，這是一種反范式化的處理方式，但經(jīng)常被用于報(bào)表查詢類型的系統(tǒng)中。增加冗余字段的方式會(huì)給數(shù)據(jù)導(dǎo)入或表插入操作帶來(lái)負(fù)載上的增加，因此這種方式也要綜合評(píng)估和取舍。 使用中間結(jié)果落地的方式。這種方式就是將原來(lái)一個(gè)SQL完成的操作拆開(kāi)成多個(gè)SQL進(jìn)行，將某兩張或三張表的關(guān)聯(lián)結(jié)果先取出，然后再拿結(jié)果集與剩下的表繼續(xù)做關(guān)聯(lián)，得到最終完整的結(jié)果。在做分拆過(guò)程中表的選取時(shí)要遵循的一個(gè)原則是，分拆出的兩個(gè)或多個(gè)SQL其處理的結(jié)果集要盡量均衡，否則就使分拆的作用打了折扣。,11.列放置順序的原則,在設(shè)計(jì)時(shí)，表中各個(gè)列的放置順序要有一定的考慮。通常情況是按照操作的頻繁程度為判定，操作頻繁的列盡量往前放置，因?yàn)榉胖迷娇壳暗牧衅涮幚淼恼w效率是越高的。,12.表及分區(qū)表的類型,堆表（HEAP） 默認(rèn)類型，適合于大多數(shù)情況 索引組織表（IOT） 以B*Tree索引的形式組織表，適合于只按 照主鍵進(jìn)行查詢的數(shù)據(jù) 簇表（CLUSTER） 以簇結(jié)構(gòu)建表并組織數(shù)據(jù)，適合于經(jīng)常被 關(guān)聯(lián)使用的表 外部表（EXTERNAL） 方便使用數(shù)據(jù)庫(kù)直接處理庫(kù)外的文本文 件數(shù)據(jù) 全局臨時(shí)表（GLOBAL TEMPORARY） 放置事務(wù)處理過(guò)程中的數(shù)據(jù) 且可以做到隔離 高級(jí)隊(duì)列表（AQ） 建AQ時(shí)系統(tǒng)自動(dòng)創(chuàng)建并維護(hù),12.表及分區(qū)表的類型,ORALCE10G中的分區(qū)表類型： 列表 LIST 適合于有限分布的固定值，比如機(jī)構(gòu)名稱 范圍 RANGE 適合于范圍取值，比如時(shí)間 哈希 HASH 適合于不好分類的情形，如序列產(chǎn)生的號(hào)碼 組合 RANGE -LIST 組合 RANGE -HASH ORALCE11G中增加的分區(qū)表類型： 組合 RANGE-RANGE 組合 LIST-LIST 組合 LIST-RANGE 組合 LIST-HASH,13.索引的類型與選擇,B樹(shù)索引（B*Tree） 默認(rèn)類型，適用范圍最廣 降序索引 適用于字段上還需要做降序排序 反向關(guān)鍵字索引 減少類似SEQUENCE生成數(shù)據(jù)時(shí)其索引上的熱塊沖突 函數(shù)索引 過(guò)濾字段上有函數(shù)或隱含轉(zhuǎn)換函數(shù) 位圖索引（BITMAP） 低基數(shù)字段，且表的修改非常少的情況 全文索引 用于搜索詞匯信息等特殊情景，較少使用 索引要在做過(guò)濾的字段上考慮，但總體原則是注意選擇性，選擇性如果超過(guò)10%則要慎重考慮，超過(guò)20%則不要建立索引。 如果有多個(gè)過(guò)濾字段，可以考慮組合索引，但要將選擇性強(qiáng)的字段放在前面。 如果查詢字段較少，且與過(guò)濾字段接近，則可以考慮將查詢字段也加入組合索引中，這時(shí)可以只使用索引掃描而不需要再掃描表，執(zhí)行效率會(huì)更高。,13.索引的類型與選擇,索引在提高查詢語(yǔ)句效率的同時(shí)會(huì)降低DML語(yǔ)句的效率，如何權(quán)衡兩者之間關(guān)系需要多做考慮和測(cè)試。在生產(chǎn)系統(tǒng)中如果發(fā)現(xiàn)沒(méi)有使用過(guò)或者效率比較差的索引，最好考慮刪除掉。 對(duì)于有大量DML操作的表，其索引增長(zhǎng)可能會(huì)較快，而索引的頁(yè)塊中又可能存在大量的空隙使得索引的使用效率逐漸降低。所以針對(duì)這種情況，應(yīng)該在非業(yè)務(wù)時(shí)間定期做索引重建等維護(hù)工作。 大表做分區(qū)后，原則上分區(qū)的索引都要考慮使用本地（LOCAL）索引。即使可能會(huì)犧牲某些全局查詢時(shí)檢索的效率，但考慮到這種情況相對(duì)較少，而本地索引在應(yīng)用能夠進(jìn)入分區(qū)后的查詢效率要高很多，并且不會(huì)產(chǎn)生某些分區(qū)維護(hù)操作使索引失效的風(fēng)險(xiǎn)。,14.模糊查詢語(yǔ)句的使用注意,ORACLE的模糊查詢主要有以下三種形式： 后模糊：col like ABC% 前模糊：col like %ABC 全模糊：col like %ABC% 后模糊是最好的，在col字段上建立索引是可以被優(yōu)化器選擇的，并且是效率比較高的索引范圍掃描方式，所以要盡量采取或轉(zhuǎn)換成這種形式。 前模糊形式即使在col字段上建立索引通常也還是不會(huì)被使用，即使使用效率也不會(huì)太高。所以這種方式是要盡量避免的，或者采取一些變通的手段比如采取反轉(zhuǎn)函數(shù)等，但基本都需要改寫原來(lái)的代碼。,14.模糊查詢語(yǔ)句的使用注意,全模糊形式普通索引的效率也會(huì)很差，寫法上也是需要盡量避免的。雖然可以采用全文索引的方式來(lái)達(dá)到提高索引效率的目的，但全文索引相對(duì)復(fù)雜且占用空間要比普通索引大很多，且索引維護(hù)時(shí)要消耗更多資源，設(shè)計(jì)上需要綜合考慮。,15.注意索引無(wú)法被使用的情況,索引通常是提高語(yǔ)句查詢效率的最常用手段之一，但如下一些容易被大家忽視的情況會(huì)導(dǎo)致索引不被優(yōu)化器選擇（能用但不用），或者根本就不能被使用。 被索引字段在過(guò)濾謂詞中使用了IS NULL或IS NOT NULL 原則上應(yīng)盡量避免這種寫法。 首先，普通的B-TREE索引是根本無(wú)法用于IS NULL條件的，雖然可以強(qiáng)制用于IS NOT NULL條件，但效率往往都比較差。 如果數(shù)據(jù)分布適合建立位圖索引的話，這兩個(gè)條件是可以使用位圖索引的，但要注意的是位圖索引所適用的數(shù)據(jù)分布，數(shù)據(jù)的靜態(tài)性條件，以及聯(lián)機(jī)交易型系統(tǒng)是嚴(yán)格禁止使用位圖索引的。,15.注意索引無(wú)法被使用的情況,被索引字段在過(guò)濾謂詞上使用了全模糊查詢的方式如col like %ABC% 這種情況的處理原則還是盡量避免或者進(jìn)行改寫，具體方法可參照前文有關(guān)模糊查詢的部分。 被索引字段在過(guò)濾謂詞中使用了!=，NOT IN等 這種情況的處理原則還是盡量避免或者進(jìn)行改寫，比如說(shuō)取不等于或NOT IN部分的反，然后就可以將條件改為等于或者是IN了。但要評(píng)估各自占總記錄數(shù)的比例，如果占比過(guò)高的話，索性就用全表掃描了。,15.注意索引無(wú)法被使用的情況,被索引字段在過(guò)濾謂詞中使用了函數(shù)。 此時(shí)除非建立函數(shù)索引，否則普通索引是無(wú)法被使用的，如例： WHERE TO_CHAR(zip) = 94002; 類似這種情況我們可以將改寫一下，將計(jì)算移動(dòng)到等號(hào)右側(cè)： WHERE zip = TO_NUMBER(94002); 改寫的原則就是使過(guò)濾謂詞一邊，也就是左側(cè)保持“干凈”，這樣在zip字段上的普通索引就可以被使用了。因?yàn)楫吘购瘮?shù)索引在做索引維護(hù)的時(shí)候成本是比較高的，而且在zip字段上所建立的函數(shù)索引只能用于這種使用了該函數(shù)的場(chǎng)景，針對(duì)其它沒(méi)使用函數(shù)的zip字段直接做過(guò)濾條件的語(yǔ)句是無(wú)法共用的，整體使用效率就打了折扣。,15.注意索引無(wú)法被使用的情況,因此，但凡能夠通過(guò)改寫解決問(wèn)題的情況就不要建立函數(shù)索引。如果實(shí)在改寫不了，評(píng)估建立函數(shù)索引后效率會(huì)有所提高后，也要考慮建立，畢竟全表掃描的情況是要盡量避免的。,16.減少REDO日志生成的一些手段,通過(guò)一些手段可以達(dá)到減少某些SQL的REDO日志生成量，從而達(dá)到提高效率的目的。對(duì)于報(bào)表系統(tǒng)、批處理系統(tǒng)、大批量數(shù)據(jù)加載等操作還是有一定的現(xiàn)實(shí)意義。 但切記一點(diǎn)的是，對(duì)關(guān)鍵的業(yè)務(wù)數(shù)據(jù)庫(kù)是禁止使用該方法的，因?yàn)闇p少了REDO日志會(huì)使數(shù)據(jù)庫(kù)做恢復(fù)時(shí)可能丟失一些沒(méi)有被記錄日志的數(shù)據(jù)，在對(duì)數(shù)據(jù)的完整性要求非常嚴(yán)格的系統(tǒng)中，這種操作是應(yīng)該被禁止的。,16.減少REDO日志生成的一些手段,CTAS語(yǔ)句 CREATE TABLE a2 AS SELECT * FROM a1; -未使用 CREATE TABLE a2 NOLOGGING AS SELECT * FROM a1; -使用了 NOLOGGING 創(chuàng)建（重建）索引 CREATE INDEX idx_t1 ON t1(col1); -未使用 CREATE INDEX idx_t1 ON t1(col1) NOLOGGING; -使用了NOLOGGING ALTER INDEX idx_t1 REBUILD; -未使用 ALTER INDEX idx_t1 REBUILD NOLOGGING; -使用了NOLOGGING 移動(dòng)表 ALTER TABLE t1 MOVE; -未使用 ALTER TABLE t1 MOVE NOLOGGING; -使用了NOLOGGING,16.減少REDO日志生成的一些手段,直接路徑插入 INSERT INTO t2 SELECT * FROM t1; -未使用 ALTER TABLE t2 NOLOGGING; -使用了NOLOGGING INSERT /*+APPEND*/ INTO t2 SELECT * FROM t1; -使用了APPEND 注意：APPEND提示要與NOLOGGING結(jié)合使用； 對(duì)于INSERT INTO VALUES語(yǔ)句，不要使用APPEND提示， 因?yàn)槠鸩坏阶饔茫?使用了APPEDN提示后的插入語(yǔ)句必須及時(shí)提交，這也限制了并 發(fā)性，因此在聯(lián)機(jī)交易型系統(tǒng)中禁止使用該提示。 SQL*Loader 直接路徑裝載 使用APPEND的方式加載數(shù)據(jù)。 SQL*Loader另外的三種裝載方式為INSERT（默認(rèn)）、REPLACE、TRUNCATE。,17.SQL中實(shí)現(xiàn)并行操作,使用并行操作的主要目的是提高語(yǔ)句的執(zhí)行效率，但是否考慮使用并行操作要遵守以下的幾點(diǎn)原則： 一定要在多CPU的服務(wù)器上測(cè)試并行操作的效果，在單CPU的PC機(jī)上是很難看到效果的，容易使測(cè)試失真。 針對(duì)于報(bào)表系統(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í)，可以考慮使用并行技術(shù)。如果硬件資源已經(jīng)發(fā)現(xiàn)不足甚至已經(jīng)成為瓶頸時(shí)，先不要考慮使用并行技術(shù)。,17.SQL中實(shí)現(xiàn)并行操作,從語(yǔ)句類型上來(lái)分，并行操作可以分為如下的幾種類型： 并行查詢 可以有兩種方式實(shí)現(xiàn)并行查詢： 將表的并行屬性打開(kāi)，并行度可以設(shè)置，也可以不設(shè)置。如果不設(shè)置并行度，則會(huì)使用默認(rèn)值，也就是ORACLE自己來(lái)計(jì)算并行度。打開(kāi)并行的方式如下： ALTER TABLE t1 PARALLEL 4; -打開(kāi)t1表的并行屬性，并且設(shè)置并行度為4 ALTER TABLE t1 PARALLEL;-打開(kāi)t1表的并行屬性，并且設(shè)置并行度為默認(rèn)值 CREATE TABLE t1 PARALLEL 4; -創(chuàng)建t1表時(shí)就打開(kāi)了并行屬性，并且設(shè)置了并行度，不寫并行度時(shí)也同樣是設(shè)置了默認(rèn)值 打開(kāi)了表上的并行屬性后，查詢語(yǔ)句不需要做任何修改，即實(shí)現(xiàn)了并行查詢， SELECT COUNT(col1) FROM t1;,17.SQL中實(shí)現(xiàn)并行操作,2. 不需要在表上打開(kāi)并行，而是通過(guò)提示的方式，實(shí)現(xiàn)并行查詢： SELECT /*+ PARALLEL(t1 4) */ COUNT(col1) FROM t1; -提示的方式實(shí)現(xiàn)并行，并設(shè)置并行度為4 SELECT /*+ PARALLEL */ COUNT(col1) FROM t1; -提示的方式實(shí)現(xiàn)并行，并設(shè)置并行度為默認(rèn)值 并行DDL 可以并行化的DDL包括如下一些示例，可以針對(duì)表、表分區(qū)、索引、索引分區(qū)等： CREATE INDEX PARALLEL 4; ALTER 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;,17.SQL中實(shí)現(xiàn)并行操作,注意：并行DDL語(yǔ)句是無(wú)法通過(guò)提示的方式實(shí)現(xiàn)的，在ALTER語(yǔ)句中也不需要被修改的表或者索引在定義時(shí)就打開(kāi)并行屬性。只需要在正常的DDL語(yǔ)句的末尾增加PARALLEL關(guān)鍵字即可。并行度可以跟在PARALLEL關(guān)鍵字的后面，如果不寫并行度，則是取默認(rèn)值，將由ORACLE自行決定并行度。 關(guān)于并行度的選擇，可以遵循以下原則： 1. 如果我們自己設(shè)置并行度，那么要綜合考慮服務(wù)器的CPU的顆數(shù)，每顆的核（線程）數(shù)，實(shí)例個(gè)數(shù)（RAC系統(tǒng)會(huì)有意義），系統(tǒng)CPU資源總體的利用效率，以及系統(tǒng)中同一時(shí)間可能會(huì)有多少個(gè)啟動(dòng)并行執(zhí)行的任務(wù)。 2. 總的原則是同一時(shí)間啟動(dòng)并行執(zhí)行的所有任務(wù)的總的并行度不超過(guò)CPU顆數(shù)*CPU核數(shù)（線程數(shù)）*實(shí)例個(gè)數(shù)。 3. 如果判斷當(dāng)前時(shí)間只會(huì)有自己這一個(gè)大的SQL在執(zhí)行，那么其上開(kāi)的并行度最大值就是以上公式計(jì)算出的取值。但考慮系統(tǒng)CPU整體的負(fù)載情況后，并行度一定要小于該取值。而且并行度并非越大越好，達(dá)到一定的值后有可能反倒設(shè)得越高性能越差。所以具體的并行度設(shè)置還是要謹(jǐn)慎，最好測(cè)試對(duì)比一下。,17.SQL中實(shí)現(xiàn)并行操作,4. 如果不好估計(jì)系統(tǒng)的其它任務(wù)情況以及CPU負(fù)載情況也不穩(wěn)定，則可以采取默認(rèn)值的方式。這時(shí)ORACLE會(huì)根據(jù)系統(tǒng)的資源使用情況動(dòng)態(tài)地調(diào)整每個(gè)并行SQL并行度的取值。 最重要的一個(gè)原則就是對(duì)于一個(gè)CPU數(shù)量很低或者CPU負(fù)載已經(jīng)很高（比如 已經(jīng)長(zhǎng)時(shí)間超過(guò)了70%）的情況，則嚴(yán)格禁止再啟動(dòng)語(yǔ)句的并行。已經(jīng)開(kāi)啟的并 行，可以考慮降低并行度或者關(guān)閉并行。 并行DML 并行DML主要是針對(duì)INSERT、DELETE、UPDATE、MERGE等DML語(yǔ)句上實(shí)現(xiàn)并行執(zhí)行。 但與并行查詢以及并行DDL都有區(qū)別的一點(diǎn)是，并行DML默認(rèn)是不開(kāi)啟的，在執(zhí)行語(yǔ)句之前需要執(zhí)行如下一句作為并行功能的開(kāi)啟。 ALTER SESSION ENABLE PARALLEL DML;,17.SQL中實(shí)現(xiàn)并行操作,開(kāi)啟了并行功能之后，就可以在接下來(lái)的DML中實(shí)現(xiàn)并行執(zhí)行了。 UPDATE /*+ PARALLEL(test 4) */ t1 SET OBJECT_NAME=abc; -提示的方式打開(kāi)表上的并行,并設(shè)置并行度為4 ALTER TABLE t1 PARALLEL 4; -打開(kāi)表上的并行屬性 UPDATE t1 SET OBJECT_NAME=abc; -無(wú)須提示，即可實(shí)現(xiàn)并行執(zhí)行 并行DML的實(shí)現(xiàn)方式與并行查詢類似，可以使用提示的方式，也可以通過(guò)在表或索引上打開(kāi)并行，然后直接執(zhí)行的方式。但為了避免整體并行度的不可控，建議采取提示的方式。,18.分頁(yè)查詢語(yǔ)句的寫法,SELECT * FROM (SELECT a.*, rownum rn FROM (SELECT * FROM table_name) a -內(nèi)層為原始查詢語(yǔ)句 WHERE rownum = 1 這是一個(gè)最常見(jiàn)的分頁(yè)查詢的寫法，正確的寫法可以使查詢的性能得到優(yōu)化，在表的數(shù)據(jù)量很大的情況下也能盡快的返回結(jié)果集，往后查詢時(shí)性能即使下降也不會(huì)下降很多。 內(nèi)層的SELECT * FROM table_name 是最原始的查詢語(yǔ)句。 改分頁(yè)查詢時(shí)首先在原有查詢語(yǔ)句外面包一層，這一層中再取一個(gè)偽列,18.分頁(yè)查詢語(yǔ)句的寫法,rownum，并為其取一個(gè)別名，直接使用rownum取分頁(yè)的上邊界； 然后再在外面包一層，在這一層中用上一層取的別名寫分頁(yè)的下邊界。 以下是一個(gè)比較典型的分頁(yè)寫法不對(duì)的例子。 第一個(gè)文件中SQL雖然也是每次取15行數(shù)據(jù)，但是沒(méi)有達(dá)到性能優(yōu)化的目的，第一頁(yè)就已經(jīng)很慢了，往后會(huì)更慢。 第二個(gè)文件是修改后的情況，可以看到效果非常明顯。修改前查第一頁(yè)的耗時(shí)是39.91秒，修改后為0.07秒。通過(guò)執(zhí)行計(jì)劃和SQL的狀態(tài)信息都可以明顯地看到兩者的差別。,19.盡量減少數(shù)據(jù)庫(kù)處理的負(fù)擔(dān),這條原則看似一句很虛的話，但其實(shí)有著很現(xiàn)實(shí)的意義。幾乎所有的數(shù)據(jù)庫(kù)優(yōu)化手段其達(dá)到的最終目的都是減少了數(shù)據(jù)庫(kù)的處理負(fù)擔(dān)。比如說(shuō) ： 使用索引達(dá)到優(yōu)化效果其實(shí)是縮減少了所掃描字段的數(shù)量以及優(yōu)化了掃描算法，最終降低了數(shù)據(jù)庫(kù)處理時(shí)邏輯讀的數(shù)量。 使用分區(qū)表其實(shí)是縮小了原來(lái)的掃描范圍，從而減輕了數(shù)據(jù)庫(kù)的處理負(fù)擔(dān)。 使用并行操作其實(shí)是利用空閑的CPU資源分擔(dān)某一時(shí)刻某一操作所帶來(lái)的過(guò)