（ 1 ） ????? 選擇 最有效率的表名 順 序 ( 只在基于 規則 的 優 化器中有效 ) ：

ORACLE 的解析器按照從右到左的 順 序 處 理 FROM 子句中的表名， FROM 子句中寫在最后的表 ( 基 礎 表 driving table) 將被最先 處 理，在 FROM 子句中包含多個表的情況下 , 你必 須選擇記錄 條數最少的表作 為 基 礎 表。如果有 3 個以上的表 連 接 查詢 , 那就需要 選擇 交叉表 (intersection table) 作 為 基 礎 表 , 交叉表是指那個被其他表所引用的表 .

（ 2 ） ????? WHERE 子句中的 連 接 順 序．：

ORACLE 采用自下而上的 順 序解析 WHERE 子句 , 根據 這 個原理 , 表之 間 的 連 接必 須 寫在其他 WHERE 條件之前 , 那些可以 過濾 掉最大數量 記錄 的條件必 須 寫在 WHERE 子句的末尾 .

（ 3 ） ????? SELECT 子句中避免使用 ‘ * ‘ ：

ORACLE 在解析的 過 程中 , 會將 '*' 依次 轉換 成所有的列名 , 這 個工作是通 過查詢 數據字典完成的 , 這 意味著將耗 費 更多的 時間

（ 4 ） ????? 減少 訪問 數據 庫 的次數：

ORACLE 在內部 執 行了 許 多工作 : 解析 SQL 語 句 , 估算索引的利用率 , 綁 定 變 量 , 讀 數據 塊 等；

（ 5 ） ????? 在 SQL*Plus , SQL*Forms 和 Pro*C 中重新 設 置 ARRAYSIZE 參數 , 可以增加 每 次數據 庫訪問 的 檢 索數據量 , 建 議值為 200

（ 6 ） ????? 使用 DECODE 函數來減少 處 理 時間 ：

使用 DECODE 函數可以避免重 復掃 描相同 記錄 或重 復連 接相同的表 .

（ 7 ） ????? 整合 簡單 , 無 關聯 的數據 庫訪問 ：

如果你有幾個 簡單 的數據 庫查詢語 句 , 你可以把它 們 整合到一個 查詢 中 ( 即使它 們 之 間 沒有 關 系 )

（ 8 ） ????? 刪 除重 復記錄 ：

最高效的 刪 除重 復記錄 方法 ( 因 為 使用了 ROWID) 例子：

DELETE ?FROM ?EMP E ?WHERE ?E.ROWID > (SELECT MIN(X.ROWID)
FROM ?EMP X ?WHERE ?X.EMP_NO = E.EMP_NO);

（ 9 ） ????? 用 TRUNCATE 替代 DELETE ：

當 刪 除表中的 記錄時 , 在通常情況下 , 回 滾 段 (rollback segments ) 用來存放可以被恢 復 的信息 . 如果你沒有 COMMIT 事 務 ,ORACLE 會將數據恢 復 到 刪 除之前的狀 態 ( 準確地 說 是恢 復 到 執 行 刪 除命令之前的狀況 ) 而當運用 TRUNCATE 時 , 回 滾 段不再存 放任何可被恢 復 的信息 . 當命令運行后 , 數據不能被恢 復 . 因此很少的 資 源被 調 用 , 執 行 時間 也會很短 . ( 譯 者按 : TRUNCATE 只在 刪 除全表適用 ,TRUNCATE 是 DDL 不是 DML)

（ 10 ） ? 盡量多使用 COMMIT ：

只要有可能 , 在程序中盡量多使用 COMMIT, 這樣 程序的性能得到提高 , 需求也會因 為 COMMIT 所 釋 放的 資 源而減少 :
COMMIT 所 釋 放的 資 源 :
a. 回 滾 段上用于恢 復 數據的信息 .
b. 被程序 語 句 獲 得的 鎖
c. redo log buffer 中的空 間
d. ORACLE 為 管理上述 3 種資 源中的內部花 費

（ 11 ） ? 用 Where 子句替 換 HAVING 子句：

避免使用 HAVING 子句 , HAVING 只會在 檢 索出所有 記錄 之后才 對結 果集 進 行 過濾 . 這 個 處 理需要排序 , 總計 等操作 . 如果能通 過 WHERE 子句限制 記錄 的數目 , 那就能減少 這 方面的 開銷 . ( 非 oracle 中 ) on 、 where 、 having 這 三個都可以加條件的子句中， on 是最先 執 行， where 次之， having 最后，因 為 on 是先把不符合條件的 記錄過濾 后才 進 行 統計 ，它就可以減少中 間 運算要 處 理的數據，按理 說應該 速度是最快的， where 也 應該 比 having 快點的，因 為 它 過濾 數據后才 進 行 sum ，在兩個表 聯 接 時 才用 on 的，所以在一個表的 時 候，就剩下 where 跟 having 比 較 了。在 這單 表 查詢統計 的情況下，如果要 過濾 的條件沒有 涉 及到要 計 算字段，那它 們 的 結 果是一 樣 的，只是 where 可以使用 rushmore 技 術 ，而 having 就不能，在速度上后者要慢如果要 涉 及到 計 算的字段，就表示在沒 計 算之前， 這 個字段的 值 是不確定的，根據上篇寫的工作流程， where 的作用 時間 是在 計 算之前就完成的，而 having 就是在 計 算后才起作用的，所以在 這種 情況下，兩者的 結 果會不同。在多表 聯 接 查詢時 ， on 比 where 更早起作用。系 統 首先根據各個表之 間 的 聯 接條件，把多個表合成一個 臨時 表后，再由 where 進 行 過濾 ，然后再 計 算， 計 算完后再由 having 進 行 過濾 。由此可 見 ，要想 過濾 條件起到正確的作用，首先要明白 這 個條件 應該 在什 么時 候起作用，然后再決定放在那里

（ 12 ） ? 減少 對 表的 查詢 ：

在含有子 查詢 的 SQL 語 句中 , 要特 別 注意減少 對 表的 查詢 . 例子：

???? SELECT ?TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER? FROM ?TAB_COLUMNS ? WHERE ?VERSION = 604)

（ 13 ） ? 通 過 內部函數提高 SQL 效率 . ：

復雜 的 SQL 往往 犧 牲了 執 行效率 . 能 夠 掌握上面的運用函數解決 問題 的方法在 實際 工作中是非常有意 義 的

（ 14 ） ? 使用表的 別 名 (Alias) ：

當在 SQL 語 句中 連 接多個表 時 , 請 使用表的 別 名并把 別 名前 綴 于 每 個 Column 上 . 這樣 一來 , 就可以減少解析的 時間 并減少那些由 Column 歧 義 引起的 語 法 錯誤 .

（ 15 ） ? 用 EXISTS 替代 IN 、用 NOT EXISTS 替代 NOT IN ：

在 許 多基于基 礎 表的 查詢 中 , 為 了 滿 足一個條件 , 往往需要 對 另一個表 進 行 聯 接 . 在 這種 情況下 , 使用 EXISTS( 或 NOT EXISTS) 通常將提高 查詢 的效率 . 在子 查詢 中 ,NOT IN 子句將 執 行一個內部的排序和合并 . 無 論 在哪 種 情況下 ,NOT IN 都是最低效的 ( 因 為 它 對 子 查詢 中的表 執 行了一個全表遍 歷 ). 為 了避免使用 NOT IN , 我 們 可以把它改寫成外 連 接 (Outer Joins) 或 NOT EXISTS.

例子：

（ 高效 ） SELECT * FROM ?EMP ( 基 礎 表 ) ? WHERE ?EMPNO > 0 ? AND ? EXISTS ( SELECT ‘X' ? FROM DEPT ? WHERE ?DEPT.DEPTNO = EMP.DEPTNO ? AND ?LOC = ‘MELB')

( 低效 ) SELECT ?* FROM ?EMP ( 基 礎 表 ) ? WHERE ?EMPNO > 0 ? AND ?DEPTNO IN (SELECT DEPTNO ? FROM ?DEPT ? WHERE ?LOC = ‘MELB' )

（ 16 ） ? 識別 ' 低效 執 行 ' 的 SQL 語 句：

雖 然目前各 種關 于 SQL 優 化的 圖 形化工具 層 出不 窮 , 但是寫出自己的 SQL 工具來解決 問題 始 終 是一個最好的方法：

SELECT ?EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2 ) Hit_radio,
ROUND (DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM ?V$SQLAREA
WHERE ?EXECUTIONS>0
AND ?BUFFER_GETS > 0
AND ?(BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY ? 4 DESC ;

（ 17 ） ? 用索引提高效率：

索引是表的一個概念部分 , 用來提高 檢 索數據的效率， ORACLE 使用了一個 復雜 的自平衡 B-tree 結 構 . 通常 , 通 過 索引 查詢 數據比全表 掃 描要快 . 當 ORACLE 找出 執 行 查詢 和 Update 語 句的最佳路徑 時 , ORACLE 優 化器將使用索引 . 同 樣 在 聯結 多個表 時 使用索引也可以提高效率 . 另一個使用索引的好 處 是 , 它提供了主 鍵 (primary key) 的唯一性 驗證 . 。那些 LONG 或 LONG RAW 數據 類 型 , 你可以索引幾乎所有的列 . 通常 , 在大型表中使用索引特 別 有效 . 當然 , 你也會 發現 , 在 掃 描小表 時 , 使用索引同 樣 能提高效率 . 雖 然使用索引能得到 查詢 效率的提高 , 但是我 們 也必 須 注意到它的代價 . 索引需要空 間 來存 儲 , 也需要定期 維護 , 每 當有 記錄 在表中增減或索引列被修改 時 , 索引本身也會被修改 . 這 意味著 每 條 記錄 的 INSERT , DELETE , UPDATE 將 為 此多付出 4 , 5 次的磁 盤 I/O . 因 為 索引需要 額 外的存 儲 空 間 和 處 理 , 那些不必要的索引反而會使 查詢 反 應時間變 慢 . 。 定期的重構索引是有必要的 . ：

ALTER ? INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（ 18 ） ? 用 EXISTS 替 換 DISTINCT ：

當提交一個包含一 對 多表信息 ( 比如部 門 表和雇 員 表 ) 的 查詢時 , 避免在 SELECT 子句中使用 DISTINCT. 一般可以考 慮 用 EXIST 替 換 , EXISTS 使 查詢 更 為 迅速 , 因 為 RDBMS 核心模 塊 將在子 查詢 的條件一旦 滿 足后 , 立刻返回 結 果 . 例子：

?????? ( 低效 ):
SELECT ? DISTINCT ?DEPT_NO,DEPT_NAME ? FROM ?DEPT D , EMP E

WHERE ?D.DEPT_NO = E.DEPT_NO
( 高效 ):
SELECT ?DEPT_NO,DEPT_NAME ? FROM ?DEPT D ? WHERE ? EXISTS ( SELECT ‘X'
FROM ?EMP E ? WHERE ?E.DEPT_NO = D.DEPT_NO ) ;

（ 19 ） ? sql 語 句用大寫的 ；因 為 oracle 總 是先解析 sql 語 句，把小寫的字母 轉換 成大寫的再 執 行

（ 20 ） ? 在 java 代 碼 中盡量 少用 連 接符 “ ＋ ” 連 接字符串 ！

（ 21 ） ? 避免在索引列上使用 NOT 通常 ，　

我 們 要避免在索引列上使用 NOT, NOT 會 產 生在和在索引列上使用函數相同的影響 . 當 ORACLE” 遇到 ”NOT, 他就會停止使用索引 轉 而 執 行全表 掃 描 .

（ 22 ） ? 避免在索引列上使用 計 算．
WHERE 子句中，如果索引列是函數的一部分． 優 化器將不使用索引而使用全表 掃 描．
舉 例 :
低效：
SELECT … FROM ?DEPT ?WHERE SAL * 12 > 25000;
高效 :
SELECT … FROM DEPT WHERE SAL > 25000/12;

（ 23 ） ? 用 >= 替代 >

高效 :
SELECT * FROM ?EMP ?WHERE ?DEPTNO >=4
低效 :
SELECT * FROM EMP WHERE DEPTNO >3
兩者的區 別 在于 , 前者 DBMS 將直接跳到第一個 DEPT 等于 4 的 記錄 而后者將首先定位到 DEPTNO=3 的 記錄 并且向前 掃 描到第一個 DEPT 大于 3 的 記錄 .

（ 24 ） ? 用 UNION 替 換 OR ( 適用于索引列 )

通常情況下 , 用 UNION 替 換 WHERE 子句中的 OR 將會起到 較 好的效果 . 對 索引列使用 OR 將造成全表 掃 描 . 注意 , 以上 規則 只 針對 多個索引列有效 . 如果有 column 沒有被索引 , 查詢 效率可能會因 為 你沒有 選擇 OR 而降低 . 在下面的例子中 , LOC_ID 和 REGION 上都建有索引 .
高效 :
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效 :
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你 堅 持要用 OR, 那就需要返回 記錄 最少的索引列寫在最前面 .

（ 25 ） ? 用 IN 來替 換 OR ?

這 是一條 簡單 易 記 的 規則 ，但是 實際 的 執 行效果 還須檢驗 ，在 ORACLE8i 下，兩者的 執 行路徑似乎是相同的．　

低效 :
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);

（ 26 ） ? 避免在索引列上使用 IS NULL 和 IS NOT NULL

避免在索引中使用任何可以 為 空的列， ORACLE 將無法使用 該 索引． 對 于 單 列索引，如果列包含空 值 ，索引中將不存在此 記錄 . 對 于 復 合索引，如果 每 個列都 為 空，索引中同 樣 不存在此 記錄 . 　如果至少有一個列不 為 空， 則記錄 存在于索引中． 舉 例 : 如果唯一性索引建立在表的 A 列和 B 列上 , 并且表中存在一條 記錄 的 A,B 值為 (123,null) , ORACLE 將不接受下一條具有相同 A,B 值 （ 123,null ）的 記錄 ( 插入 ). 然而如果所有的索引列都 為 空， ORACLE 將 認為 整個 鍵值為 空而空不等于空 . 因此你可以插入 1000 條具有相同 鍵值 的 記錄 , 當然它 們 都是空 ! 因 為 空 值 不存在于索引列中 , 所以 WHERE 子句中 對 索引列 進 行空 值 比 較 將使 ORACLE 停用 該 索引 .

低效 : ( 索引失效 )
SELECT … FROM ?DEPARTMENT ? WHERE ?DEPT_CODE IS NOT NULL ;
高效 : ( 索引有效 )
SELECT … FROM ?DEPARTMENT ? WHERE ?DEPT_CODE >= 0;

（ 27 ） ? 總 是使用索引的第一個列：

如果索引是建立在多個列上 , 只有在它的第一個列 (leading column) 被 where 子句引用 時 , 優 化器才會 選擇 使用 該 索引 . 這 也是一條 簡單 而重要的 規則 ，當 僅 引用索引的第二個列 時 , 優 化器使用了全表 掃 描而忽略了索引

（ 28 ） ? 用 UNION-ALL 替 換 UNION ( 如果有可能的 話 ) ：

當 SQL 語 句需要 UNION 兩個 查詢結 果集合 時 , 這 兩個 結 果集合會以 UNION-ALL 的方式被合并 , 然后在 輸 出最 終結 果前 進 行排序 . 如果用 UNION ALL 替代 UNION, 這樣 排序就不是必要了 . 效率就會因此得到提高 . 需要注意的是 ， UNION ALL 將重 復輸 出兩個 結 果集合中相同 記錄 . 因此各位 還 是要從 業務 需求分析使用 UNION ALL 的可行性 . UNION 將 對結 果集合排序 , 這 個操作會使用到 SORT_AREA_SIZE 這塊 內存 . 對 于 這塊 內存的 優 化也是相當重要的 . 下面的 SQL 可以用來 查詢 排序的消耗量

低效：
SELECT ?ACCT_NUM, BALANCE_AMT
FROM ?DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效 :
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'

（ 29 ） ? 用 WHERE 替代 ORDER BY ：

ORDER BY 子句只在兩 種嚴 格的條件下使用索引 .
ORDER BY 中所有的列必 須 包含在相同的索引中并保持在索引中的排列 順 序 .
ORDER BY 中所有的列必 須 定 義為 非空 .
WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能并列 .

例如 :
表 DEPT 包含以下列 :
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL

低效 : ( 索引不被使用 )
SELECT DEPT_CODE? FROM ?DEPT ? ORDER BY ?DEPT_TYPE
高效 : ( 使用索引 )
SELECT DEPT_CODE ? FROM ?DEPT ? WHERE ?DEPT_TYPE > 0

（ 30 ） ? 避免改 變 索引列的 類 型 .:

當比 較 不同數據 類 型的數據 時 , ORACLE 自 動對 列 進 行 簡單 的 類 型 轉換 .

假 設 EMPNO 是一個數 值類 型的索引列 .
SELECT … ? FROM EMP ? WHERE ? EMPNO = ‘123'
實際 上 , 經過 ORACLE 類 型 轉換 , 語 句 轉 化 為 :
SELECT … ? FROM EMP ?WHERE ?EMPNO = TO_NUMBER(‘123')
幸運的是 , 類 型 轉換 沒有 發 生在索引列上 , 索引的用途沒有被改 變 .
現 在 , 假 設 EMP_TYPE 是一個字符 類 型的索引列 .
SELECT … ? FROM EMP ? WHERE EMP_TYPE = 123
這 個 語 句被 ORACLE 轉換為 :
SELECT … ? FROM EMP ? WHERETO_NUMBER(EMP_TYPE)=123
因 為 內部 發 生的 類 型 轉換 , 這 個索引將不會被用到 ! 為 了避免 ORACLE 對 你的 SQL 進 行 隱 式的 類 型 轉換 , 最好把 類 型 轉換 用 顯 式表 現 出來 . 注意當字符和數 值 比 較時 , ORACLE 會 優 先 轉換 數 值類 型到字符 類 型

（ 31 ） ? 需要當心的 WHERE 子句 :

某些 SELECT 語 句中的 WHERE 子句不使用索引 . 這 里有一些例子 .
在下面的例子里 , (1) ‘!=' 將不使用索引 . 記 住 , 索引只能告 訴 你什 么 存在于表中 , 而不能告 訴 你什 么 不存在于表中 . (2) ‘||' 是 字符 連 接函數 . 就象其他函數那 樣 , 停用了索引 . (3) ‘+' 是數學函數 . 就象其他數學函數那 樣 , 停用了索引 . (4) 相同的索引列不能互相比 較 , 這 將會啟用全表 掃 描 .

（ 32 ） ? a. 如果 檢 索數據量超 過 30% 的表中 記錄 數 . 使用索引將沒有 顯 著的效率提高 .
b. 在特定情況下 , 使用索引也 許 會比全表 掃 描慢 , 但 這 是同一個數量 級 上的區 別 . 而通常情況下 , 使用索引比全表 掃 描要 塊 幾倍乃至幾千倍 !

（ 33 ） ? 避免使用耗 費資 源的操作 :

帶 有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的 SQL 語 句會啟 動 SQL 引擎
執 行耗 費資 源的排序 (SORT) 功能 . DISTINCT 需要一次排序操作 , 而其他的至少需要 執 行兩次排序 . 通常 , 帶 有 UNION, MINUS , INTERSECT 的 SQL 語 句都可以用其他方式重寫 . 如果你的數據 庫 的 SORT_AREA_SIZE 調 配得好 , 使用 UNION , MINUS, INTERSECT 也是可以考 慮 的 , 畢 竟它 們 的可 讀 性很 強

（ 34 ） ? 優 化 GROUP BY:

提高 GROUP BY 語 句的效率 , 可以通 過 將不需要的 記錄 在 GROUP BY 之前 過濾 掉 . 下面兩個 查詢 返回相同 結 果但第二個明 顯 就快了 許 多 .

低效 :
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效 :
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP JOB