【正文】 t gets 　0 physical reads 　0 redo size 　63735 bytes sent via SQL*Net to client 　7751 bytes received via SQL*Net from client 　68 SQL*Net roundtrips to/from client 1 sorts （memory） 0 sorts （disk） 1003 rows processed 　用UNION的效果可以從consistent gets和 SQL*NET的數(shù)據(jù)交換量的減少看出 37. 用IN來替換OR 下面的查詢可以被更有效率的語句替換： 低效： SELECT… FROM LOCATION 　WHERE LOC_ID = 10 　OR LOC_ID = 20 　OR LOC_ID = 30 高效： 　SELECT… FROM LOCATION 　WHERE LOC_IN IN （10，20，30）； 譯者按：這是一條簡單易記的規(guī)則，但是實際的執(zhí)行效果還須檢驗，在ORACLE8i下，兩者的執(zhí)行路徑似乎是相同的。 38. 避免在索引列上使用IS NULL和IS NOT NULL 　避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引。對于單列索引，如果列包含空值，索引中將不存在此記錄。 對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此記錄?！∪绻辽儆幸粋€列不為空，則記錄存在于索引中。 舉例： 　如果唯一性索引建立在表的A列和B列上， 并且表中存在一條記錄的A，B值為（123，null） ， ORACLE將不接受下一條具有相同A，B值（123，null）的記錄（插入）。 然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等于空。 因此你可以插入1000條具有相同鍵值的記錄，當(dāng)然它們都是空！ 因為空值不存在于索引列中，所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引。 舉例： 低效： （索引失效） SELECT … FROM DEPARTMENT 　WHERE DEPT_CODE IS NOT NULL； 高效： （索引有效） SELECT … FROM DEPARTMENT 　WHERE DEPT_CODE =0； 　39. 總是使用索引的第一個列 　如果索引是建立在多個列上， 只有在它的第一個列（leading column）被where子句引用時，優(yōu)化器才會選擇使用該索引。 譯者按：這也是一條簡單而重要的規(guī)則。 見以下實例。 SQL create table multiindexusage （ inda number ， indb number ， descr varchar2（10））； 　Table created. 　SQL create index multindex on multiindexusage（inda，indb）； 　Index created. 　SQL set autotrace traceonly SQL select * from multiindexusage where inda = 1； 　Execution Plan 　 0 SELECT STATEMENT Optimizer=CHOOSE 　1 0 TABLE ACCESS （BY INDEX ROWID） OF 39。MULTIINDEXUSAGE39。 　2 1 INDEX （RANGE SCAN） OF 39。MULTINDEX39。 （NONUNIQUE） SQL select * from multiindexusage where indb = 1； 　Execution Plan 　 0 SELECT STATEMENT Optimizer=CHOOSE 　1 0 TABLE ACCESS （FULL） OF 39。MULTIINDEXUSAGE39。 很明顯， 當(dāng)僅引用索引的第二個列時，優(yōu)化器使用了全表掃描而忽略了索引 40. ORACLE內(nèi)部操作 　當(dāng)執(zhí)行查詢時，ORACLE采用了內(nèi)部的操作。 下表顯示了幾種重要的內(nèi)部操作。 ORACLE Clause 內(nèi)部操作 ORDER BY SORT ORDER BY UNION UNIONALL MINUS MINUS INTERSECT INTERSECT DISTINCT,MINUS,INTERSECT,UNION SORT UNIQUE MIN,MAX,COUNT SORT AGGREGATE GROUP BY SORT GROUP BY ROWNUM COUNT or COUNT STOPKEY Queries involving Joins SORT JOIN,MERGE JOIN,NESTED LOOPS CONNECT BY CONNECT BY 41. 用UNIONALL 替換UNION （ 如果有可能的話） 當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以UNIONALL的方式被合并， 然后在輸出最終結(jié)果前進行排序。 如果用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會因此得到提高。 舉例： 　低效： 　SELECT ACCT_NUM， BALANCE_AMT 　FROM DEBIT_TRANSACTIONS 　WHERE TRAN_DATE = ‘31DEC95’ 　UNION 　SELECT ACCT_NUM， BALANCE_AMT 　FROM DEBIT_TRANSACTIONS 　WHERE TRAN_DATE = ‘31DEC95’ 　高效： 　SELECT ACCT_NUM， BALANCE_AMT 　FROM DEBIT_TRANSACTIONS 　WHERE TRAN_DATE = ‘31DEC95’ 　UNION ALL 　SELECT ACCT_NUM， BALANCE_AMT 　FROM DEBIT_TRANSACTIONS 　WHERE TRAN_DATE = ‘31DEC95’ 譯者按：需要注意的是，UNION ALL 將重復(fù)輸出兩個結(jié)果集合中相同記錄。 因此各位還是要從業(yè)務(wù)需求分析使用UNION ALL的可行性。 UNION 將對結(jié)果集合排序，這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存。 對于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的。 下面的SQL可以用來查詢排序的消耗量 Select substr（name，1，25） Sort Area Name， 　substr（value，1，15） Value 　from v$sysstat 　where name like 39。sort%39。 42. 使用提示（Hints） 對于表的訪問，可以使用兩種Hints. 　FULL 和 ROWID FULL hint 告訴ORACLE使用全表掃描的方式訪問指定表。 例如： 　SELECT /*+ FULL（EMP） */ * 　FROM EMP 　WHERE EMPNO = 7893； ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問表。 通常， 你需要采用TABLE ACCESS BY ROWID的方式特別是當(dāng)訪問大表的時候， 使用這種方式， 你需要知道ROIWD的值或者使用索引。 如果一個大表沒有被設(shè)定為緩存（CACHED）表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留在SGA中，你就可以使用CACHE hint 來告訴優(yōu)化器把數(shù)據(jù)保留在SGA中。 通常CACHE hint 和 FULL hint 一起使用。 例如： 　SELECT /*+ FULL（WORKER） CACHE（WORKER）*/ * 　FROM WORK； 索引hint 告訴ORACLE使用基于索引的掃描方式。 你不必說明具體的索引名稱 　例如： 　SELECT /*+ INDEX（LODGING） */ LODGING 　FROM LODGING 　WHERE MANAGER = ‘BILL GATES’； 在不使用hint的情況下， 以上的查詢應(yīng)該也會使用索引，然而，如果該索引的重復(fù)值過多而你的優(yōu)化器是CBO， 優(yōu)化器就可能忽略索引。 在這種情況下， 你可以用INDEX hint強制ORACLE使用該索引。 ORACLE hints 還包括ALL_ROWS， FIRST_ROWS， RULE，USE_NL， USE_MERGE， USE_HASH 等等。 譯者按：使用hint ， 表示我們對ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意，需要手工修改。這是一個很有技巧性的工作。 我建議只針對特定的，少數(shù)的SQL進行hint的優(yōu)化。對ORACLE的優(yōu)化器還是要有信心（特別是CBO） 43. 用WHERE替代ORDER BY 　ORDER BY 子句只在兩種嚴(yán)格的條件下使用索引。 ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序。 ORDER BY中所有的列必須定義為非空。 WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。 例如： 　表DEPT包含以下列： DEPT_CODE PK NOT NULL 　DEPT_DESC NOT NULL 　DEPT_TYPE NULL 非唯一性的索引（DEPT_TYPE） 低效： （索引不被使用） SELECT DEPT_CODE 　FROM DEPT 　ORDER BY DEPT_TYPE EXPLAIN PLAN： 　　SORT ORDER BY 　　　　TABLE ACCESS FULL 　高效： （使用索引） SELECT DEPT_CODE 　FROM DEPT 　WHERE DEPT_TYPE 0 　EXPLAIN PLAN： 　　TABLE ACCESS BY ROWID ON EMP 　　　INDEX RANGE SCAN ON DEPT_IDX 　譯者按：ORDER BY 也能使用索引！ 這的確是個容易被忽視的知識點。 我們來驗證一下： SQL select * from emp order by empno； Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 　1 0 TABLE ACCESS （BY INDEX ROWID） OF 39。EMP39。 　2 1 INDEX （FULL SCAN） OF 39。EMPNO39。 （UNIQUE） 44. 避免改變索引列的類型。 當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時， ORACLE自動對列進行簡單的類型轉(zhuǎn)換。 假設(shè) EMPNO是一個數(shù)值類型的索引列。 SELECT … FROM EMP 　WHERE EMPNO = ‘123’ 　實際上，經(jīng)過ORACLE類型轉(zhuǎn)換， 語句轉(zhuǎn)化為： 　SELECT … FROM EMP 　WHERE EMPNO = TO_NUMBER（‘123’） 幸運的是，類型轉(zhuǎn)換沒有發(fā)生在索引列上，索引的用途沒有被改變。 現(xiàn)在，假設(shè)EMP_TYPE是一個字符類型的索引列。 SELECT … FROM EMP WHERE EMP_TYPE = 123 　這個語句被ORACLE轉(zhuǎn)換為： 　SELECT … FROM EMP 　WHERE TO_NUMBER（EMP_TYPE）=123 　因為內(nèi)部發(fā)生的類型轉(zhuǎn)換， 這個索引將不會被用到！ 譯者按：為了避免ORACLE對你的SQL進行隱式的類型轉(zhuǎn)換， 最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來。 注意當(dāng)字符和數(shù)值比較時， ORACLE會優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型。 45. 需要當(dāng)心的WHERE子句 　某些SELECT 語句中的WHERE子句