依據(jù)SQL編寫的規(guī)范標(biāo)準(zhǔn)說(shuō)明

1、!-SQL 編寫規(guī)范1書寫格式示例代碼：存儲(chǔ)過(guò)程 SQL 文書寫格式例selectc.dealerCode,roun d(sum(c.submitSubletAmou ntDLR + c.submitPartsAm oun tDLRc.submitLaborA moun tDLR) / coun t(*), 2) as avg,decode( null, x, xx, CNY)from (selecta.dealerCode,a.submitSubletAmou ntDLR,a.submitPartsAm oun tDLR,a. submitLaborA moun tDLRfrom SRV_T

2、WC_F awhere (to_char(a.ORIGSUBMITTIME, yyyy/mm/dd) = Date Ra nge(start) andto_char(a.ORIGSUBMITTIME, yyyy/mm/dd) = Date Ran ge(e nd) and nvl(a.deleteflag, 0) 1) union allselectb. dealerCode,b.submitSubletAmou ntDLR,b.submitPartsAmou ntDLR,b.submitLaborA moun tDLRfrom SRV_TWCHistory_F bwhere (to_char

3、(b.ORIGSUBMITTIME, yyyy/mm/dd) = Date Ra nge(start) andto_char(b.ORIGSUBMITTIME,yyyy/mm/dd) = Date Ra nge(e nd) and nvl(b.deleteflag,O) 1)cgroup by c.dealerCodeorder by avg desc;Java source 里的 SQL 字符串書寫格式例strSQL = insert into Sn d_Fi nan ceHistory_Tb + (DEALERCODE,+ REQUESTSEQUECE,+ HANDLETIME,!-+ J

4、OBFLAG,+ FRAMENO,+ INMONEY ,+ REMAINMONEY ,+ DELETEFLAG ,+ UPDATECOUNT,+ CREUSER,+ CREDATE,+ HONORCHECKNO,+ SEQ)+ values ( + draftl nputDetail.dealerCode + ,+ ” + draftI nputDetail.requestsequece + ,+ sysdate,+ 07,+ + frameNO + ,+ requestM oney + ,+ rema inMoney + ,+ 0,+ ” + draftStruct.employeeCode

5、 + ,+ sysdate,+ ” + draftI nputDetail.h on orCheckNo + ,+ in dex + );1）縮進(jìn)對(duì)于存儲(chǔ)過(guò)程文件，縮進(jìn)為 8 個(gè)空格對(duì)于 Java source 里的 SQL 字符串，不可有縮進(jìn)，即每一行字符串不可以空 格開頭2）.換行 1.Select/From/Where/Order by/Group by 等子句必須另其一行寫2.Select 子句內(nèi)容如果只有一項(xiàng)，與 Select 同行寫3.Select 子句內(nèi)容如果多于一項(xiàng)，每一項(xiàng)單獨(dú)占一行，在對(duì)應(yīng) Select 的基礎(chǔ) 上向右縮進(jìn) 8 個(gè)空格（Java source 無(wú)縮進(jìn)）4.Fr

6、om 子句內(nèi)容如果只有一項(xiàng)，與 From 同行寫5.From 子句內(nèi)容如果多于一項(xiàng)，每一項(xiàng)單獨(dú)占一行，在對(duì)應(yīng)From 的基礎(chǔ)上向右縮進(jìn) 8 個(gè)空格（Java source 無(wú)縮進(jìn)）6.Where 子句的條件如果有多項(xiàng)，每一個(gè)條件占一行，以AND 開頭，且無(wú)縮進(jìn)7.（Update）Set 子句內(nèi)容每一項(xiàng)單獨(dú)占一行，無(wú)縮進(jìn)8.I nsert 子句內(nèi)容每個(gè)表字段單獨(dú)占一行，無(wú)縮進(jìn)； values 每一項(xiàng)單獨(dú)占一行，無(wú)縮進(jìn)+ 0,II!-9.SQL 文中間不允許出現(xiàn)空行10.Java sourceM單引號(hào)必須跟所屬的 SQL 子句處在同一行，連接符（+）必須在行首3).空格1.SQL 內(nèi)算數(shù)運(yùn)算符、邏

7、輯運(yùn)算符連接的兩個(gè)元素之間必須用空格分隔2.逗號(hào)之后必須接一個(gè)空格3.關(guān)鍵字、保留字和左括號(hào)之間必須有一個(gè)空格2. 不等于統(tǒng)一使用Oracle 認(rèn)為!=和是等價(jià)的，都代表不等于的意義。為了統(tǒng)一，不等于 一律使用表示3. 使用表的別名數(shù)據(jù)庫(kù)查詢，必須使用表的別名4.SQL 文對(duì)表字段擴(kuò)展的兼容性在 Java source 里使用 Select *時(shí)， 嚴(yán)禁通過(guò) getString的形式得到查詢結(jié)果，必須使用 getString(字段名)的形式使用 Insert 時(shí)，必須指定插入的字段名，嚴(yán)禁不指定字段名直接插入values5. 減少子查詢的使用子查詢除了可讀性差之外，還在一定程度上影響了SQL

8、運(yùn)行效率請(qǐng)盡量減少使用子查詢的使用，用其他效率更高、可讀性更好的方式替代6. 適當(dāng)添加索引以提高查詢效率適當(dāng)添加索引可以大幅度的提高檢索速度請(qǐng)參看 ORACLE SQL 性能優(yōu)化系列7. 對(duì)數(shù)據(jù)庫(kù)表操作的特殊要求本項(xiàng)目對(duì)數(shù)據(jù)庫(kù)表的操作還有以下特殊要求：1).以邏輯刪除替代物理刪除注意：現(xiàn)在數(shù)據(jù)庫(kù)表中數(shù)據(jù)沒(méi)有物理刪除，只有邏輯刪除以 deleteflag 字段作為刪除標(biāo)志，deleteflag=1 代表此記錄被邏輯刪除，因此 在!-查詢數(shù)據(jù)時(shí)必須考慮 deleteflag 的因素deleteflag 的標(biāo)準(zhǔn)查詢條件：NVL(deleteflag, 0) 12) .增加記錄狀態(tài)字段數(shù)據(jù)庫(kù)中的每張表

9、基本都有以下字段：DELETEFLAG、UPDATECOUNT、CREDATE、CREUSER、UPDATETIME、UPDATEUSER要注意在對(duì)標(biāo)進(jìn)行操作時(shí)必須考慮以下字段插入一條記錄時(shí)要置 DELETEFLAG=O, UPDATECOUNT=0,CREDATE=sysdate, CREUSER=登錄 User查詢一條記錄時(shí)要考慮 DELETEFLAG，如果有可能對(duì)此記錄作更新時(shí)還要 取得UPDATECOUNT 作同步檢查修改一條記錄時(shí)要置UPDATETIME=sysdate, UPDATEUSER=登錄 User,UPDATECOUNT=(UPDATECOUNT+1) mod 1000,

10、刪除一條記錄時(shí)要置 DELETEFLAG=13) .歷史表數(shù)據(jù)庫(kù)里部分表還存在相應(yīng)的歷史表，比如 srv_twc_f 和 srv_twchistory_f在查詢數(shù)據(jù)時(shí)除了檢索所在表之外，還必須檢索相應(yīng)的歷史表，對(duì)二者的結(jié) 果做Un io n (或 Un io n All)8.用執(zhí)行計(jì)劃分析 SQL 性能EXPLAIN PLAN 是一個(gè)很好的分析 SQL 語(yǔ)句的工具，它可以在不執(zhí)行 SQL 的情況下分析語(yǔ)句通過(guò)分析，我們就可以知道 ORACLE 是怎樣連接表，使用什么方式掃描表 (索引掃描或全表掃描)，以及使用到的索引名稱按照從里到外，從上到下的次序解讀分析的結(jié)果EXPLAIN PLAN 的分析

11、結(jié)果是用縮進(jìn)的格式排列的，最內(nèi)部的操作將最先 被解讀，如果兩個(gè)操作處于同一層中，帶有最小操作號(hào)的將首先被執(zhí)行目前許多第三方的工具如 PLSQL Developer 和 TOAD 等都提供了極其方便 的EXPLAIN PLAN 工具!-PG 需要將自己添加的查詢 SQL 文記入 log，然后在 EXPLAIN PLAN 中進(jìn)行 分析，盡量減少全表掃描ORACLE SQL 性能優(yōu)化系列1選擇最有效率的表名順序（只在基于規(guī)則的優(yōu)化器中有效）ORACLE 的解析器按照從右到左的順序處理FROM 子句中的表名，因此FROM 子句中寫在最后的表（基礎(chǔ)表 driving table）將被最先處理在 FROM

12、 子句中包含多個(gè)表的情況下，必須選擇記錄條數(shù)最少的表作為基 礎(chǔ)表當(dāng) ORACLE 處理多個(gè)表時(shí)，會(huì)運(yùn)用排序及合并的方式連接它們首先，掃描第一個(gè)表（FROM 子句中最后的那個(gè)表）并對(duì)記錄進(jìn)行排序；然后掃描第二個(gè)表（FROM 子句中最后第二個(gè)表）；最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并例如：表 TAB1 16,384 條記錄表 TAB2 5 條記錄選擇 TAB2 作為基礎(chǔ)表（最好的方法）select count（*） from tab1,tab2 執(zhí)行時(shí)間 0.96 秒選擇 TAB2 作為基礎(chǔ)表（不佳的方法）select count（*） from tab2,tab1 執(zhí)行

13、時(shí)間 26.09 秒如果有 3 個(gè)以上的表連接查詢，那就需要選擇交叉表（in tersection table）作 為基礎(chǔ)表，交叉表是指那個(gè)被其他表所引用的表例如：EMP 表描述了 LOCATION 表和 CATEGORY 表的交集SELECT *FROM LOCATION L,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000!-AND E.CAT_NO = C.CAT_NOAND E 丄 OCN =匚 LOCN將比下列 SQL 更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORYCWHERE E.CA

14、T_NO = C.CAT_NOAND E 丄 OCN =匚 LOCNAND E.EMP_NO BETWEEN 1000 AND 20002.WHERE 子句中的連接順序ORACLE 采用自下而上的順序解析 WHERE 子句根據(jù)這個(gè)原理，表之間的連接必須寫在其他 WHERE 條件之前，那些可以過(guò) 濾掉最大數(shù)量記錄的條件必須寫在 WHERE 子句的末尾例如：(低效，執(zhí)行時(shí)間 156.3 秒)SELECT *FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效

15、，執(zhí)行時(shí)間 10.6 秒)SELECT *FROM EMP EWHERE 25 50000AND JOB = MANAGER;3.SELECT 子句中避免使用*當(dāng)你想在 SELECT 子句中列出所有的 COLUMN 時(shí)， 使用動(dòng)態(tài) SQL 列引用* 是一個(gè)方便的方法，不幸的是，這是一個(gè)非常低效的方法實(shí)際上，ORACLE 在解析的過(guò)程中，會(huì)將*依次轉(zhuǎn)換成所有的列名這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時(shí)間!-4.減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)當(dāng)執(zhí)行每條 SQL 語(yǔ)句時(shí)ORACLE 在內(nèi)部執(zhí)行了許多工作：解析 SQL 語(yǔ)句, 估算索引的利用率，綁定變量，讀數(shù)據(jù)塊等等由此可見，減少訪問(wèn)數(shù)據(jù)庫(kù)的

16、次數(shù)，就能實(shí)際上減少ORACLE 的工作量例如：以下有三種方法可以檢索出雇員號(hào)等于 0342 或 0291 的職員方法 1 (最低效)SELECT EMP_NAME, SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME, SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法 2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY ,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342)

17、;FETCH C1 INTO,，；OPEN C1(291);FETCH C1 INTO,，；CLOSE C1;END;方法 2 (高效)SELECT A.EMP_NAME, A.SALARY , A.GRADE,B.EMP_NAME, B.SALARY , B.GRADEFROM EMP A, EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;!-5.使用 DECODE 函數(shù)來(lái)減少處理時(shí)間使用 DECODE 函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表例如：SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO =

18、0020AND ENAME LIKE SMITH%;SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE SMITH%;你可以用 DECODE 函數(shù)高效地得到相同結(jié)果SELECT COUNT(DECODE(DEPT_NO, 0020, X, NULL) D0020_COUNT,COUNT(DECODE(DEPT_NO, 0030, X, NULL) D0030_COUNT,SUM(DECODE(DEPT_NO, 0020, SAL, NULL) D0020_SAL,SUM(DECODE(DEPT_NO, 0030

19、, SAL, NULL) D0030_SALFROM EMPWHERE ENAME LIKE SMITH%;X表示任何一個(gè)字段類似的，DECODE 函數(shù)也可以運(yùn)用于 GROUP BY 和 ORDER BY 子句中6.用 Where 子句替換 HAVING 子句避免使用 HAVING 子句，HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集 進(jìn)行過(guò)濾，這個(gè)處理需要排序、統(tǒng)計(jì)等操作如果能通過(guò) WHERE 子句限制記錄的數(shù)目，那就能減少這方面的開銷例如：低效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REG

20、ION != SYDNEYAND REGION != PERTH高效SELECT REGION, AVG(LOG_SIZE)FROM LOCATION!-WHERE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION7. 減少對(duì)表的查詢?cè)诤凶硬樵兊?SQL 語(yǔ)句中，要特別注意減少對(duì)表的查詢例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = (SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER = (SELECT D

21、B_VERFROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)Update 多個(gè) Column 例子：低效UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WH

22、ERE EMP_DEPT = 0020;高效UPDATE EMPSET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;!-8. 使用表的別名(Alias)當(dāng)在 SQL 語(yǔ)句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)Column 上這樣可以減少解析的時(shí)間并減少那些由 Column 歧義引起的語(yǔ)法錯(cuò)誤9. 用 EXISTS 替代 IN在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對(duì)另一個(gè)表進(jìn)行 聯(lián)接在這種情況下，使用 EXISTS(

23、或 NOT EXISTS)通常將提高查詢的效率低效SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB)高效SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO 0AND EXISTS (SELECT XFROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB)10. 用 NOT EXISTS 替代 NOT IN在子查詢中，NOT IN 子句將執(zhí)行一個(gè)內(nèi)部的排序和合并無(wú)論在哪種情況下，NOT IN 都是最低效

24、的，因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了 一個(gè)全表遍歷為了避免使用 NOT IN，我們可以把它改寫成外連接(Outer Joi ns)或 NOTEXISTS例如：SELECT FROM EMP!-WHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT = A);為了提高效率改寫為高效SELECT FROM EMP A, DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A最高效SELECT FROM EMP EWHERE NOT EXISTS

25、(SELECT XFROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);11 用表連接替換 EXISTS通常來(lái)說(shuō)，采用表連接的方式比 EXISTS 更有效率例如：SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);更高效SELECT ENAMEFROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A;12. 用 EXISTS 替換 D

26、ISTINCT當(dāng)提交一個(gè)包含多表信息(比如部門表和雇員表)的查詢時(shí)，避免在 SELECT 子句中使用 DISTINCT，一般可以考慮用 EXIST 替換例如：!-低效SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO高效SELECT DEPT_NO, DEPT_NAMEFROM DEPT DWHERE EXISTS (SELECT XFROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);EXISTS 使查詢更為迅速， 因?yàn)?RDBMS 核心模塊將在子查詢的條件一

27、旦滿 足后，立刻返回結(jié)果13. 用索引提高效率索引是表的一個(gè)概念部分，用來(lái)提高檢索數(shù)據(jù)的效率。實(shí)際上，ORACLE使用了一個(gè)復(fù)雜的自平衡 B-tree 結(jié)構(gòu)通常，通過(guò)索引查詢數(shù)據(jù)比全表掃描要快。當(dāng)ORACLE 找出執(zhí)行查詢和Update 語(yǔ)句的最佳路徑時(shí)，ORACLE 優(yōu)化器將使用索引同樣，在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率。另一個(gè)使用索引的好處是，它提供了主鍵(primary key)的唯一性驗(yàn)證除了那些 LONG 或 LONG RAW 數(shù)據(jù)類型，你可以索引幾乎所有的列通常在大型表中使用索引特別有效，當(dāng)然，在掃描小表時(shí)，使用索引同樣能 提高效率雖然使用索引能得到查詢效率的提高，但是我們也必

28、須注意到它的代價(jià)索引需要空間來(lái)存儲(chǔ)，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被 修改時(shí)，索引本身也會(huì)被修改這意味著每條記錄的 INSERT、DELETE、UPDATE 將為此多付出 4、5 次的 磁盤 I/O因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要的索引反而會(huì)使查詢反應(yīng) 時(shí)間變慢ORACLE 對(duì)索引有兩種訪問(wèn)模式：1).索弓 I 唯一掃描(INDEX UNIQUE SCAN)大多數(shù)情況下,優(yōu)化器通過(guò) WHERE 子句訪問(wèn) INDEX!-例如：表 LODGING 有兩個(gè)索引： 建立在 LODGING 列上 的唯一性索引 LODGING_PK和建立在 MANAGER 列上的非唯一性索引 L

29、ODGING$MANAGERSELECT *FROM LODGINGWHERE LODGING = ROSE HILL;在內(nèi)部，上述 SQL 將被分成兩步執(zhí)行：首先，LODGING_PK 索引將通過(guò)索引唯一掃描的方式被訪問(wèn)，獲得相對(duì)應(yīng) 的ROWID ;然后通過(guò) ROWID 訪問(wèn)表的方式執(zhí)行下一步檢索如果被檢索返回的列包括在 INDEX 列中，ORACLE 將不執(zhí)行第二步的處理 （通過(guò) ROWID 訪問(wèn)表）因?yàn)闄z索數(shù)據(jù)保存在索引中，單單訪問(wèn)索引就可以完全滿足查詢結(jié)果2）.索弓 I 范圍查詢（INDEX RANGE SCAN）適用于兩種情況：1.基于唯一性索引的一個(gè)范圍的檢索2.基于非唯一性索引的

30、檢索例 1SELECT LODGINGFROM LODGINGWHERE LODGING LIKE M%;WHERE 子句條件包括一系列值，ORACLE 將通過(guò)索引范圍查詢的方式查詢LODGING_PK由于索引范圍查詢將返回一組值，它的效率就要比索引唯一掃描低一些例 2SELECT LODGINGFROM LODGINGWHERE MANAGER = BILL GATES;這個(gè) SQL 的執(zhí)行分兩步，LODGING$MANAGER 的索引范圍查詢（得到所 有符合條件記錄的 ROWID ），通過(guò) ROWID 訪問(wèn)表得到 LODGING 列的值由于 LODGING$MANAGER 是一個(gè)非唯一性的

31、索引，數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行 索引!-唯一掃描WHERE 子句中，如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符 (WILDCARD )開始，索引將不被采用SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE % HANMAN;在這種情況下，ORACLE 將使用全表掃描14. 避免在索引列上使用計(jì)算WHERE 子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用 全表掃描例如：低效SELECT FROM DEPTWHERE SAL * 12 25000;高效SELECT FROM DEPTWHERE SAL 25000/12;請(qǐng)務(wù)必注意，檢索中不要對(duì)索引列進(jìn)行處理，如：TRIM，TO_DATE，類型轉(zhuǎn)換等操作，破壞索引，使用全表掃描，影