基于Oracle數據庫查詢優化策略的研究

黃建軍　龔瑋瑋　肖英劍

摘要：隨著信息的高速發展，我們進入了云計算，大數據的時代，Oracle數據庫作為信息技術發展的重要產物，它是目前市場上占有率最高，使用范圍最廣泛的關系型數據庫。由于數據庫中的數據量日漸龐大，對數據的處理速度提出了更高的要求，如果對數據庫的查詢不進行優化，將會消耗大量的系統資源和影響數據的查詢效率，所以開發人員應該重視數據庫查詢優化的重要性。本文將對Oracle數據庫的查詢優化策略進行探討。

關鍵詞：Oracle數據庫;SQL優化;查詢

中圖分類號：TP311 ? ? ?文獻標識碼：A

文章編號：1009-3044（2019）13-0010-02

大數據的“大”，云計算的“云”都體現了當今世界的信息量之大、數據規模之大，在數據庫的操作中，查詢是最常用的操作，因此我們有必要對數據庫的查詢進行優化，提高系統的查詢效率。影響數據庫的查詢效率有很多因素，最常見的是沒有合理使用索引、查詢語句沒有優化和臨時表的建立。下面我們就開始探討Oracle數據庫的查詢優化策略。

1 Oracle的優化器

優化器是Oracle數據庫中內置的一個核心系統，優化器的目的是按照一定的判斷原則來得到它認為的目標SQL在當前情景下最高效的執行路徑，簡單地說，優化器的目的就是給SQL選擇一種最優的執行計劃。

Oracle在執行一個SQL之前，首先要分析一下語句的執行計劃，然后再按執行計劃去執行，分析語句的執行計劃的工作就是由優化器來完成。不同的情況，一條SQL可能有多種執行計劃，但是在某一個時間點，一定只有一種花費時間最少的計劃。根據選擇執行計劃時所用的判斷原則，Oracle的優化器有兩種，即基于規則的優化器（RBO）和基于成本的優化器（CBO）。

1）基于規則的優化器

Oracle會在代碼里事先給各種類型的執行路徑定一個等級，一共有15個等級，從等級1到等級15。Oracle認為等級值低的執行路徑的執行效率比等級值高的更高，等級1所對應的執行路徑的執行效率最高，等級15所對應的執行路徑的執行效率最低，Oracle根據目標對象在數據字典中緩存的順序判斷選擇哪一種執行計劃。RBO是一種適用于OLTP類型SQL語句的優化器，RBO對數據不“敏感”，就按照優先順序規則進行執行計劃的選擇，因為RBO存在一些先天的缺陷，RBO在Oracle 10g中已經不被支持，但RBO的相關實現代碼并沒有從Oracle數據庫的代碼中移除。

2）基于成本的優化器

CBO選擇執行計劃時，以目標SQL成本為判斷原則，CBO會選擇一條執行成本最小的執行計劃作為SQL的執行計劃，各條執行路徑的成本通過目標SQL語句所涉及的表、索引、列等的統計信息算出。這里的成本是oracle通過相關對象的統計信息計算出來的一個值，它實際上代表目標SQL對應執行步驟所消耗的IO、CPU、網絡資源（針對dblink下的分布式數據庫系統而言）的消耗量，oracle會把網絡資源的消耗量計算在IO成本內，實際上你看到的成本為IO、CPU資源。

2 Oracle的執行計劃

在Oracle數據庫中，執行計劃是SQL優化最為復雜也是最關鍵的部分，因為它實際上代表了目標SQL在Oracle數據庫內部的具體執行步驟，只有知道并了解了這些執行步驟，我們才能知道優化器選擇的執行計劃是否為當前情形下最優的執行計劃。下面將介紹幾種Oracle里常見的執行計劃。

2.1 表訪問相關的執行計劃

Oracle數據庫里與表訪問相關的方法有兩種，即全盤掃描和ROWID掃描。在執行計劃上，與全盤掃描對應的執行計劃的關鍵字是“table access full”，與ROWID掃描對應的執行計劃的關鍵字是“table access by user rowid”或者“table access by user index rowid”。

2.2 B樹索引相關的執行計劃

Oracle數據庫里與B樹索引訪問有關的方法有索引唯一掃描、索引范圍掃描、索引全掃描、索引快速全掃描和索引跳躍式掃描。在執行計劃上，與索引唯一掃描對應的關鍵詞是“index unique scan”，與索引范圍掃描對應的關鍵詞是“index range scan”，與索引全掃描對應的關鍵詞是“index full scan”，與索引快速全掃描對應的關鍵詞是“index fast full scan”，與索引跳躍式掃描對應的關鍵詞是“index skip scan”。

2.3 表連接相關的執行計劃

Oracle數據庫里與表連接相關的方法有排序合并連接、嵌套循環連接、哈希連接等，在執行計劃上，與排序合并連接對應的關鍵詞是“sort join”和“merge join”，與嵌套連接循環連接對應的關鍵詞是“nested loops”，與哈希連接對應的關鍵詞是“hash join”。

3 Oracle數據庫的查詢優化策略的實現

Oracle數據庫的查詢效率受很多因素的影響，以下就一些常用于提高查詢效率的策略進行分析。

3.1 索引優化

索引是表的一個概念部分，用來提高檢索數據的效率，減少I/O操作和消除磁盤排序，ORACLE使用了一個復雜的自平衡B-tree結構。通常，通過索引查詢數據比全表掃描要快，當Oracle找出執行查詢的最佳路徑時， Oracle優化器將使用索引。通常在以下條件使用索引可以提高查詢速度：表的主鍵和外鍵使用索引;對經常與其他表進行連接的表的連接字段使用索引;經常出現在Where子句中的字段使用索引;選擇性高的字段建立索引。一般，在大型表中使用索引特別有效，提高查詢速度特別明顯。

3.2 SQL語句優化

對于Oracle數據庫而言，一個SQL語句的好壞將會直接影響系統的性能，SQL語句的優化是查詢優化的一個重要的方法，下面列出常見的SQL語句優化。

①exits和in的用法

exists對外表做循環，每次循環再對內表進行查詢，而in是把外表和內表做hash連接，先查詢內表，再對外表匹配。當用exists時對內表查詢用了索引，而對外表查詢是全部遍歷，使用in時，對外表使用索引，而內表是有多少都全部遍歷。綜上所述，當兩個表的大小相當時，in和exists的效率差不多;當子查詢表更大的時候，使用exists效率更高;當子查詢表更小的時候，使用in效率更高。

②Where和Having的用法

Where和Having都是給查詢結果做限定條件，兩者的不同之處在于Having常用于分組的查詢，與grounp ?by配合使用。Having和Where在數據量不大的情況下，效率差不多，但當數據量很大時，效果就很明顯了，因為使用Having時，是先分組后條件判斷，需要檢索所有的記錄再條件，判斷而當我們使用Where時，我們可以先判斷再分組，分組的效率就更快了，從而查詢的效率也很明顯地提高了。

③帶通配符“%”的like語句

在我們平時實現系統的某些功能是，往往我們需要對一個表模糊查詢，模糊查詢通常會用到like和‘%，如果模糊查詢的這一列有索引，這時我們就應該小心了，要合理的使用這種方法，如果通配符‘%在搜尋詞首出現，那么索引就沒有用了，這時就要全表搜索，如果在其他位置，就可以利用索引，查詢的效率就會得到提升，例如：查詢student表中name列中包含‘夢的學生學生，并且name列建立了pk_name的索引，第一種方法是select stuid from student where name like ‘%夢%;另一種方法是select stuid from student where name like ‘夢%，方法一不會使用索引pk_name，方法二會使用索引，可以提高查詢的速率。

④用TRUNCATE替代DELETE

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

3.3 共享SQL語句

為了不重復解析相同的SQL語句，在第一次解析之后， Oracle將SQL語句存放在內存中.這塊位于系統全局區域SGA（system globalarea）的共享池（shared bufferpool）中的內存可以被所有的數據庫用戶共享.因此，當你執行一個SQL語句（有時被稱為一個游標）時，如果它和之前的執行過的語句完全相同， Oracle就能很快獲得已經被解析的語句以及最好的執行路徑. Oracle的這個功能大大地提高了SQL的執行性能并節省了內存的使用.

4 結語

綜上所述，開發人員應該充分地理解Oracle數據庫的優化器的工作原理和執行計劃，充分利用索引、合理使用SQL語句和臨時表，結合不同的實例選擇不同的優化方式，從而提高系統的查詢效率。

參考文獻：

[1] 韓峰.SQL優化最佳實踐構建高效率Oracle數據庫的方法與技巧[M].北京：機械工業出版社，2016.

[2] 崔華.基于Oracle的SQL優化[M]. 北京：電子工業出版社，2018.

[3] 蓋國強，李軼楠 主編.oracle性能優化與診斷案例精選[M]. 北京：人民郵電出版社，2016.

[4] 師慶棟.Oracl查詢優化改寫技巧與案例2.0[M]. 北京：電子工業出版社，2018

[5] [美]Karen MortonKerry OsborneRobyn SandsRiyaj ShamsudeenJared Still.精通Oracle SQL [M]. 2版. 北京：人民郵電出版社，2014.

【通聯編輯：光文玲】