【正文】 沒有顯著的性能差別) 14. 用Where子句替換HAVING子句 避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷. 例如:低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’ 高效SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’GROUP BY REGION(譯者按: HAVING 中的條件一般用于對(duì)一些集合函數(shù)的比較,如COUNT() 等等. 除此而外,一般的條件應(yīng)該寫在WHERE子句中) 15. 減少對(duì)表的查詢?cè)诤凶硬樵兊腟QL語句中,要特別注意減少對(duì)表的查詢. 例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND　DB_VER= ( SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604) 高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM 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)WHERE EMP_DEPT = 0020。 高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020。 16. 通過內(nèi)部函數(shù)提高SQL效率. SELECT ,COUNT(*)FROM HISTORY_TYPE T,EMP E,EMP_HISTORY HWHERE = AND = GROUP BY ,。 通過調(diào)用下面的函數(shù)可以提高效率.FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2ASTDESC VARCHAR2(30)。CURSOR C1 ISSELECT TYPE_DESCFROM HISTORY_TYPEWHERE HIST_TYPE = TYP。BEGINOPEN C1。FETCH C1 INTO TDESC。CLOSE C1。RETURN (NVL(TDESC,’?’))。END。 FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2ASENAME VARCHAR2(30)。CURSOR C1 ISSELECT ENAMEFROM EMPWHERE EMPNO=EMP。BEGINOPEN C1。FETCH C1 INTO ENAME。CLOSE C1。RETURN (NVL(ENAME,’?’))。END。 SELECT ,LOOKUP_EMP(),LOOKUP_HIST_TYPE(),COUNT(*)FROM EMP_HISTORY HGROUP BY , 。 ORACLE SQL性能優(yōu)化系列 (六) 20. 用表連接替換EXISTS通常來說 , 采用表連接的方式比EXISTS更有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT ‘X’FROM DEPTWHERE DEPT_NO = AND DEPT_CAT = ‘A’)。 (更高效)SELECT ENAMEFROM DEPT D,EMP EWHERE = AND DEPT_CAT = ‘A’ 。 21. 用EXISTS替換DISTINCT當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門表和雇員表)的查詢時(shí),避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換 例如:低效:SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP EWHERE = 高效:SELECT DEPT_NO,DEPT_NAMEFROM DEPT DWHERE EXISTS ( SELECT ‘X’FROM EMP EWHERE = )。 EXISTS 使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果. 22. 識(shí)別’低效執(zhí)行’的SQL語句 用下列SQL工具找出低效SQL: SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS,2) Hit_radio,ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXTFROM V$SQLAREAWHERE EXECUTIONS0AND BUFFER_GETS 0AND (BUFFER_GETSDISK_READS)/BUFFER_GETS ORDER BY 4 DESC。 (譯者按: 雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個(gè)最好的方法) 23. 使用TKPROF 工具來查詢SQL性能狀態(tài) SQL trace 工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中. 這個(gè)跟蹤文件提供了許多有用的信息,. 設(shè)置SQL TRACE在會(huì)話級(jí)別: 有效 ALTER SESSION SET SQL_TRACE TRUE 設(shè)置SQL TRACE 在整個(gè)數(shù)據(jù)庫有效仿, , USER_DUMP_DEST參數(shù)說明了生成跟蹤文件的目錄ORACLE SQL性能優(yōu)化系列 (七 ) 24. 用EXPLAIN PLAN 分析SQL語句 EXPLAIN PLAN 是一個(gè)很好的分析SQL語句的工具,它甚至可以在不執(zhí)行SQL的情況下分析語句. 通過分析,我們就可以知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.你需要按照從里到外,從上到下的次序解讀分析的結(jié)果. EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的, 最內(nèi)部的操作將被最先解讀, 如果兩個(gè)操作處于同一層中,帶有最小操作號(hào)的將被首先執(zhí)行.NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作, 正確的執(zhí)行路徑是檢查對(duì)NESTED LOOP提供數(shù)據(jù)的操作,其中操作號(hào)最小的將被最先處理. 譯者按: 通過實(shí)踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.舉例: SQL list1 SELECT *2 FROM dept, emp3* WHERE = SQL set autotrace on exp。 /*traceonly 可以不顯示執(zhí)行結(jié)果*/或者SQL set autotrace traceonly exp。SQL /14 rows selected.Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (FULL) OF 39。EMP39。3 1 TABLE ACCESS (BY INDEX ROWID) OF 39。DEPT39。4 3 INDEX (UNIQUE SCAN) OF 39。PK_DEPT39。 (UNIQUE) Statistics0 recursive calls2 db block gets30 consistent gets0 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed 通過以上分析,可以得出實(shí)際的執(zhí)行步驟是:1. TABLE ACCESS (FULL) OF 39。EMP39。2. INDEX (UNIQUE SCAN) OF 39。PK_DEPT39。 (UNIQUE)3. TABLE ACCESS (BY INDEX ROWID) OF 39。DEPT39。4. NESTED LOOPS (JOINING 1 AND 3) ORACLE SQL性能優(yōu)化系列 (八) 25. 用索引提高效率 索引是表的一個(gè)概念部分,用來提高檢索數(shù)據(jù)的效率. 實(shí)際上,ORACLE使用了一個(gè)復(fù)雜的自平衡Btree結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率. 另一個(gè)使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證.除了那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小表時(shí),使用索引同樣能提高效率.雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià). 索引需要空間來存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.譯者按:定期的重構(gòu)索引是有必要的.ALTER INDEX INDEXNAME REBUILD TABLESPACENAME 26. 索引的操作ORACLE對(duì)索引有兩種訪問模式. 索引唯一掃描 ( INDEX UNIQUE SCAN) 大多數(shù)情況下, 優(yōu)化器通過WHERE子句訪問INDEX. 例如:表LODGING有兩個(gè)索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER. SELECT *FROM LODGINGWHERE LODGING = ‘ROSE HILL’。 在內(nèi)部 , 上述SQL將被分成兩步執(zhí)行, 首先 , LODGING_PK 索引將通過索引唯一掃描的方式被訪問 , 獲得相對(duì)應(yīng)的ROWID, 通過ROWID訪問表的方式執(zhí)行下一步檢索.如果被檢索返回的列包括在INDEX列中,ORACLE將不執(zhí)行第二步的處理(通過ROWID訪問表). 因?yàn)闄z索數(shù)據(jù)保存在索引中, 單單訪問索引就可以完全滿足查詢結(jié)果.下面SQL只需要INDEX UNIQUE SCAN 操作. SELECT LODGINGFROM LODGINGWHERE LODGING = ‘ROSE HILL’。 索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:1. 基于一個(gè)范圍的檢索2. 基于非唯一性索引的檢索 例1: SELECT LODGINGFROM LODGINGWHERE LODGING LIKE ‘M%’。 WHERE子句條件包括一系列值, ORACLE將通過索引范圍查詢的方式查詢LODGING_