1. 選 用適合的 ORACLE 優(yōu) 化器
???? ORACLE 的 優(yōu) 化器共有 3 種

???? A 、 RULE ( 基于 規(guī)則 ) b 、 COST ( 基于成本 ) c 、 CHOOSE ( 選擇 性 )

???? 設(shè) 置缺省的 優(yōu) 化器，可以通 過(guò)對(duì) init.ora 文件中 OPTIMIZER_MODE 參數(shù)的各 種 聲明，如 RULE ， COST ， CHOOSE ， ALL_ROWS ， FIRST_ROWS 。 你當(dāng)然也在 SQL 句 級(jí) 或是會(huì) 話 (session) 級(jí)對(duì) 其 進(jìn) 行覆蓋。

???? 為 了使用基于成本的 優(yōu) 化器 (CBO ， Cost-Based Optimizer) ， 你必 須經(jīng) 常運(yùn)行 analyze 命令，以增加數(shù)據(jù) 庫(kù) 中的 對(duì) 象 統(tǒng)計(jì) 信息 (object statistics) 的準(zhǔn)確性。

???? 如果數(shù)據(jù) 庫(kù) 的 優(yōu) 化器模式 設(shè) 置 為選擇 性 (CHOOSE) ，那 么實(shí)際 的 優(yōu) 化器模式將和是否運(yùn)行 過(guò) analyze 命令有 關(guān) 。 如果 table 已 經(jīng) 被 analyze 過(guò) ， 優(yōu) 化器模式將自 動(dòng) 成 為 CBO ， 反之，數(shù)據(jù) 庫(kù) 將采用 RULE 形式的 優(yōu) 化器。

在缺省情況下， ORACLE 采用 CHOOSE 優(yōu) 化器， 為 了避免那些不必要的全表 掃 描 (full table scan) ， 你必 須 盡量避免使用 CHOOSE 優(yōu) 化器，而直接采用基于 規(guī)則 或者基于成本的 優(yōu) 化器。

2. 訪問(wèn) Table 的方式
???? ORACLE 采用兩 種訪問(wèn) 表中 記錄 的方式：

???? A 、 全表 掃 描

????????? 全表 掃 描就是 順 序地 訪問(wèn) 表中 每 條 記錄 。 ORACLE 采用一次 讀 入多個(gè)數(shù)據(jù) 塊 (database block) 的方式 優(yōu) 化全表 掃 描。

???? B 、 通 過(guò) ROWID 訪問(wèn) 表

????????? 你可以采用基于 ROWID 的 訪問(wèn) 方式情況，提高 訪問(wèn) 表的效率， ROWID 包含了表中 記錄 的物理位置信息。 ORACLE 采用索引 (INDEX) 實(shí)現(xiàn) 了數(shù)據(jù)和存放數(shù)據(jù)的物理位置 (ROWID) 之 間 的 聯(lián) 系。通常索引提供了快速 訪問(wèn) ROWID 的方法，因此那些基于索引列的 查詢 就可以得到性 能上的提高。

3. 共享 SQL 語(yǔ) 句
???? 為 了不重 復(fù) 解析相同的 SQL 語(yǔ) 句，在第一次解析之后， ORACLE 將 SQL 語(yǔ) 句存放在內(nèi)存中。 這塊 位于系 統(tǒng) 全局區(qū)域 SGA(system global area) 的共享池 (shared buffer pool) 中的內(nèi)存可以被所有的數(shù)據(jù) 庫(kù) 用 戶 共享。 因此，當(dāng)你 執(zhí) 行一個(gè) SQL 語(yǔ) 句 ( 有 時(shí) 被稱 為 一個(gè)游 標(biāo) ) 時(shí) ，如果它和之前的 執(zhí) 行 過(guò) 的 語(yǔ) 句完全相同， ORACLE 就能很快 獲 得已 經(jīng) 被解析的 語(yǔ) 句以及最好的 執(zhí) 行路徑。 ORACLE 的 這 個(gè)功能大大地提高了 SQL 的 執(zhí) 行性能并 節(jié) 省了內(nèi)存的使用。

???? 可惜的是 ORACLE 只 對(duì)簡(jiǎn)單 的表提供高速 緩 沖 (cache buffering) ， 這 個(gè)功能并不適用于多表 連 接 查詢 。

???? 數(shù)據(jù) 庫(kù) 管理 員 必 須 在 init.ora 中 為這 個(gè)區(qū)域 設(shè) 置合適的參數(shù)，當(dāng) 這 個(gè)內(nèi)存區(qū)域越大，就可以保留更多的 語(yǔ) 句，當(dāng)然被共享的可能性也就越大了。

???? 當(dāng)你向 ORACLE 提交一個(gè) SQL 語(yǔ) 句， ORACLE 會(huì)首先在 這塊 內(nèi)存中 查 找相同的 語(yǔ) 句。 這 里需要注明的是， ORACLE 對(duì) 兩者采取的是一 種嚴(yán) 格匹配，要達(dá)成共享， SQL 語(yǔ) 句必 須 完全相同 ( 包括空格， 換 行等 ) 。

???? 數(shù)據(jù) 庫(kù) 管理 員 必 須 在 init.ora 中 為這 個(gè)區(qū)域 設(shè) 置合適的參數(shù)，當(dāng) 這 個(gè)內(nèi)存區(qū)域越大，就可以保留更多的 語(yǔ) 句，當(dāng)然被共享的可能性也就越大了。

???? 共享的 語(yǔ) 句必 須滿 足三個(gè)條件：

???? A 、 字符 級(jí) 的比 較 ： 當(dāng)前被 執(zhí) 行的 語(yǔ) 句和共享池中的 語(yǔ) 句必 須 完全相同。

???? B 、 兩個(gè) 語(yǔ) 句所指的 對(duì) 象必 須 完全相同：

???? C 、 兩個(gè) SQL 語(yǔ) 句中必 須 使用相同的名字的 綁 定 變 量 (bind variables) 。

4. 選擇 最有效率的表名 順 序 ( 只在基于 規(guī)則 的 優(yōu) 化器中有效 )
???? ORACLE 的解析器按照從右到左的 順 序 處 理 FROM 子句中的表名，因此 FROM 子句中寫(xiě)在最后的表 ( 基 礎(chǔ) 表 driving table) 將被最先 處 理。在 FROM 子句中包含多個(gè)表的情況下，你必 須選擇記錄 條數(shù)最少的表作 為 基 礎(chǔ) 表。當(dāng) ORACLE 處 理多個(gè)表 時(shí) ， 會(huì)運(yùn)用排序及合并的方式 連 接它 們 。首先， 掃 描第一個(gè)表 (FROM 子句中最后的那個(gè)表 ) 并 對(duì)記錄進(jìn) 行派序，然后 掃 描第二個(gè)表 (FROM 子句中最后第二個(gè)表 ) ，最后將所有從第二個(gè)表中 檢 索出的 記錄 與第一個(gè)表中合適 記錄進(jìn) 行合并。

???? 如果有 3 個(gè)以上的表 連 接 查詢 ， 那就需要 選擇 交叉表 (intersection table) 作 為 基 礎(chǔ) 表， 交叉表是指那個(gè)被其他表所引用的表。

5.WHERE 子句中的 連 接 順 序
???? ORACLE 采用自下而上的 順 序解析 WHERE 子句，根據(jù) 這 個(gè)原理，表之 間 的 連 接必 須 寫(xiě)在其他 WHERE 條件之前， 那些可以 過(guò)濾 掉最大數(shù)量 記錄 的條件必 須 寫(xiě)在 WHERE 子句的末尾。

6.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í)間 。

7. 減少 訪問(wèn) 數(shù)據(jù) 庫(kù) 的次數(shù)
???? 當(dāng) 執(zhí) 行 每 條 SQL 語(yǔ) 句 時(shí) ， ORACLE 在內(nèi)部 執(zhí) 行了 許 多工作：解析 SQL 語(yǔ) 句，估算索引的利用率， 綁 定 變 量， 讀 數(shù)據(jù) 塊 等等。由此可 見(jiàn) ，減少 訪問(wèn) 數(shù)據(jù) 庫(kù) 的次數(shù)，就能 實(shí)際 上減少 ORACLE 的工作量。

8. 使用 DECODE 函數(shù)來(lái)減少 處 理 時(shí)間
???? 使用 DECODE 函數(shù)可以避免重 復(fù)掃 描相同 記錄 或重 復(fù)連 接相同的表。

9. 整合 簡(jiǎn)單 ，無(wú) 關(guān)聯(lián) 的數(shù)據(jù) 庫(kù)訪問(wèn)
???? 如果你有幾個(gè) 簡(jiǎn)單 的數(shù)據(jù) 庫(kù)查詢語(yǔ) 句，你可以把它 們 整合到一個(gè) 查詢 中 ( 即使它 們 之 間 沒(méi)有 關(guān) 系 )

10. 刪 除重 復(fù)記錄

11. 用 TRUNCATE 替代 DELETE
???? 當(dāng) 刪 除表中的 記錄時(shí) ，在通常情況下， 回 滾 段 (rollback segments ) 用來(lái)存放可以被恢 復(fù) 的信息。 如果你沒(méi)有 COMMIT 事 務(wù) ， ORACLE 會(huì)將數(shù)據(jù)恢 復(fù) 到 刪 除之前的狀 態(tài) ( 準(zhǔn)確地 說(shuō) 是恢 復(fù) 到 執(zhí) 行 刪 除命令之前的狀況 ) 。

???? 而當(dāng)運(yùn)用 TRUNCATE 時(shí) ， 回 滾 段不再存放任何可被恢 復(fù) 的信息。當(dāng)命令運(yùn)行后，數(shù)據(jù)不能被恢 復(fù) 。因此很少的 資 源被 調(diào) 用， 執(zhí) 行 時(shí)間 也會(huì)很短。

12. 盡量多使用 COMMIT
???? 只要有可能，在程序中盡量多使用 COMMIT ， 這樣 程序的性能得到提高，需求也會(huì)因 為 COMMIT 所 釋 放的 資 源而減少

???? COMMIT 所 釋 放的 資 源：

???? A 、 回 滾 段上用于恢 復(fù) 數(shù)據(jù)的信息。

???? B 、被程序 語(yǔ) 句 獲 得的 鎖 。

???? C 、 redo log buffer 中的空 間 。

???? D 、 ORACLE 為 管理上述 3 種資 源中的內(nèi)部花 費(fèi) 。

13. 計(jì) 算 記錄 條數(shù)
???? 和一般的 觀 點(diǎn)相反， count(*) 比 count(1) 稍快，當(dāng)然如果可以通 過(guò) 索引 檢 索， 對(duì) 索引列的 計(jì) 數(shù)仍舊是最快的。例如 COUNT(EMPNO)

14. 用 Where 子句替 換 HAVING 子句
???? 避免使用 HAVING 子句， HAVING 只會(huì)在 檢 索出所有 記錄 之后才 對(duì)結(jié) 果集 進(jìn) 行 過(guò)濾 。 這 個(gè) 處 理需要排序， 總計(jì) 等操作。如果能通 過(guò) WHERE 子句限制 記錄 的數(shù)目，那就能減少 這 方面的 開(kāi)銷 。

15. 減少 對(duì) 表的 查詢
???? 在含有子 查詢 的 SQL 語(yǔ) 句中，要特 別 注意減少 對(duì) 表的 查詢 。

16. 通 過(guò) 內(nèi)部函數(shù)提高 SQL 效率。

17. 使用表的 別 名 (Alias)
???? 當(dāng)在 SQL 語(yǔ) 句中 連 接多個(gè)表 時(shí) ， 請(qǐng) 使用表的 別 名并把 別 名前 綴 于 每 個(gè) Column 上。 這樣 一來(lái)，就可以減少解析的 時(shí)間 并減少那些由 Column 歧 義 引起的 語(yǔ) 法 錯(cuò)誤 。

18. 用 EXISTS 替代 IN
???? 在 許 多基于基 礎(chǔ) 表的 查詢 中， 為 了 滿 足一個(gè)條件，往往需要 對(duì) 另一個(gè)表 進(jìn) 行 聯(lián) 接。在 這種 情況下，使用 EXISTS( 或 NOT EXISTS) 通常將提高 查詢 的效率。

19. 用 NOT EXISTS 替代 NOT IN
???? 在子 查詢 中， NOT IN 子句將 執(zhí) 行一個(gè)內(nèi)部的排序和合并。 無(wú) 論 在哪 種 情況下， NOT IN 都是最低效的 ( 因 為 它 對(duì) 子 查詢 中的表 執(zhí) 行了一個(gè)全表遍 歷 ) 。 為 了避免使用 NOT IN ，我 們 可以把它改寫(xiě)成外 連 接 (Outer Joins) 或 NOT EXISTS 。

20. 用表 連 接替 換 EXISTS
???? 通常來(lái) 說(shuō) ， 采用表 連 接的方式比 EXISTS 更有效率

21. 用 EXISTS 替 換 DISTINCT
???? 當(dāng)提交一個(gè)包含一 對(duì) 多表信息 ( 比如部 門(mén) 表和雇 員 表 ) 的 查詢時(shí) ，避免在 SELECT 子句中使用 DISTINCT 。 一般可以考 慮 用 EXIST 替 換

Oracle SQL 性能優(yōu)化技巧