Oracle資料庫SQL最佳化

標籤：

SQL最佳化的最終目的是使用者體驗-在最短時間內讓使用者在頁面資料。因此，執行時間是SQL最佳化的重要指標。在SQL查詢中，I/O操作最占系統效能的。I/O操作量越大，時間越久。減少I/O操作量是最佳化SQL的目標。同時，CPU,快取命中率也很重要。

並不是所有SQL都需要被最佳化，使用頻率高，效能差的SQL語句需要重點被最佳化。可以通過查看v$sqlarea表，訪問OEM來尋找。

注意不同版本的Oracle使用不同的最佳化器。11g的最佳化器基於代價（Cost Based Optimizer)(考慮SQL執行耗費的資源代價）11g之前是基於規則來最佳化的(Rule Based Optimizer)。最佳化要針對最佳化器來完成。

 

1.合理使用索引

原理:ORACLE是按行儲存資料的(ROWID是行的位置) 而查詢條件是列的，資料結構不同，尋找速度慢。 建立按列儲存的索引可以按列尋找，資料在一個地區內，速度快。（弊端：交易處理起來不方便，增加了Insert, Update的執行時間)

原則:

適合使用索引的列：

1.經常被查詢的列

2.經常排序分組的列

3.經常串連的列

 

不適合使用索引的列

1.資料經常更改的列

2.資料量小、重複性高的列

注意:

1.給索引指定單獨的資料表空間可以增加IO效能

 2.表完全匯入完畢後再創立索引

3.表中總索引數不易過大

 

 

2.SQL語句的最佳化

1.用列名代替*

 使用*，SQL會對每個列進行匹配，降低效率

2.當表與其他表沒有任何關聯時，可以用TRUNCATE代替DELETE

  TRUNCATE沒有事務控制，效能高，但是要注意無法被rollback

 

針對11g之前的最佳化方法 (基於規則的最佳化器)

1. 用EXISTS代替IN，DISTINCT

2. 二連表查詢時，選擇資料少的表作為驅動表(放在FROM的表列表的最後面)。迴圈驅動表中的資料和另一個表中的資料作串連查詢。多表聯查時，選擇關聯最多的表。

3. 表串連條件放前面，過濾記錄多的條件的子句放後面(因為SQL執行順序是從右至左)

 

3.表分區

將資料量大的表分區也可以提升查詢效率，還可以方便表的管理，備份，和恢複，提高表的安全性。

分區方法：

定界分割

散列分區（根據雜湊值）

列表分區 （明確指定列來分區)

指定分區查詢

select * from table partition(pt_rule);

 

Oracle資料庫SQL最佳化