Oracle之sql語句最佳化

Oracle之sql語句最佳化

項目中雖然使用了orm映射，但Oracle系統的最佳化還是很有價值的，這裡從sql語句的角度對常用的語句做下總結。

1、from欄位中的最佳化：

Oracle安照從右至左的順序載入表資料，應該把可以排除資料最多的表放到後面（基礎資料表）。

比如，在關聯查詢中，把課程表放到後面，成績表放到前面，因為課程表資料一般比較少，關聯的時候可以快速的過濾掉一些成績資料。

2、where中的最佳化：

對可以過濾資料最多的，放到後面，原理也是Oracle執行從下到上（從右至左）的順序。

3、使用列名替代*：

省去從字典表中解析的過程 ：

Oracle執行sql語句過程：

在共用池中搜尋sql語句是否已經存在

驗證sql是否文法精確

執行資料字典驗證表和列的定義

擷取對象的分析鎖，以便在語句的分析過程中對象的定義不會改變

檢查使用者是否具有相應的操作許可權

確定語句最佳執行計畫

將語句和執行方案儲存到共用的sql區。

4、使用？參數形式：

盡量不要使用拼接字串的方式，因為帶有？的參數形式會緩衝分析結果，省去上面很多步驟。

5、使用decode函數：

decode不會重複掃描相同記錄或重複串連相同的表，減少表的掃描次數。

6、使用truncate替代delete：

delete：逐條刪除，並儲存復原還原點。DML（資料操作語言insert，update，delete，merge）

truncate不會儲存復原點，是DDL（資料定義語言 (Data Definition Language)，如drop，alter）

在oracle裡DML是可以rollback的，而DDL是不可以的

如果資料量大，差別很大的。

7、盡量多用commit：

Oracle只有在commit後才會提交（區別於sqlserver），如果沒有提交，會在記憶體中儲存很多資料。commit後釋放的資源有：

復原上段用於恢複資料庫的記錄資訊

被程式語句獲得的鎖

Redo log buffer中的空間

為管理上述3種資源的內部花費

8、使用where語句替代having子句：

在分組或者配合group的時候會使用having。

where會直接過濾掉資料，使用having往往會配合group，檢索出資料後，會帶著資料進行排序、統計等。

9、on/where/having排序使用順序：

on：做的是資料對應，在映射時，把沒用的資料直接過濾掉了

where：先進行一個全表的搜尋，之後再進行資料篩選

where又比having要快，having中帶的垃圾資料同樣也做了運算

10、減少對錶的查詢：

在含有子查詢的sql語句中，要特別注意減少對錶的查詢。

Select tab_name from tables where (tab_name,db_ver)=(select tab_name,db_ver from tab_columns where version=604)

11、使用表別名：

減少對sql語句解析的時間並減少由多個表相同的Column名歧義引起的語法錯誤

如果不使用別名，會去字典表中尋找，判斷是否有列名歧義。

12、使用exists替代in not exists替代not in：

Oracle採用命中即返回的方式，在多錶鏈接查詢時，如果使用in會導致子查詢的表全表遍曆，並排序、合并，這時候可以使用外連結或not  exists 替代。

13、識別低效率執行的語句：

各種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;

14、使用索引：

使用於查詢的表，提供了主鍵的唯一性驗證，long或者long raw資料類型，幾乎可以索引所有列

定期重建索引，在刪除和修改多的表，不適用索引，原因，不解釋了。

注意，並不是建立了索引後就一定會走索引，使用索引的時候，不能使用索引進行一些計算，否則失效

使用索引快於全表掃描，在多表串連使用索引提高效率。

避免在索引上使用not：

會停止索引（not表示的是：沒有什麼， 而索引表示的是：有什麼）

用exitsts替代distinct：

在提交一個包含一對多表資訊（部門和僱員表）的查詢時使用。原因：尋找即返回原理

避免在索引列上使用計算：

低效：

Select * from dept  where sal * 12 >25000

高效：

Select * from dept where sal> 25000/12

避免在索引列上使用is null 和 is not null：

因為索引不會對null資料類型進行索引，索引只是記錄有什麼

對於單列索引，不會進行索引

對於符合索引，如果所有列多為空白，不進行索引，只要有一個列不空，就索引。

在Oracle中空不等於空，所以，就會插入若干條相同索引值的記錄，而他們的值都是空，而空值不進行索引，所以，當進行空值比較時，會使用Oracle，停止使用該索引。

低效：

Select …. From dep where dep_code is not null;

高效：

Select …. From dep where dep_code >=0;這裡也會過濾掉null列

總是使用索引的第一個列：

如果索引建立在多個列上，只有咋他的第一個列，被where自居引用時，最佳化器才會選擇使用該索引，當僅使用索引的第二個列是，最佳化器會忽略索引，使用全表索引。

15、>=是直接定位：

如果使用 >還需要一個判斷的過程。

16、使用union替代where字句中的or：

對索引列使用or會導致全表掃描，針對多個索引列有效

17、在oracle8i下，兩者執行路徑似乎相同，但能用in的就別用or。

18、使用union-all：

union-all:不排序，查詢所有的，不過濾重複的

nuion：排序(最佳化sort_raea_size這塊記憶體)，過濾重複的。

19、order by 用where 限定：

order by名中索引的條件比較苛刻

排序列必須包含在相同的索引中，並保持索引中的排列順序，Order by中所有列不能定義為空白。

20、需要當心的where子句：

!=將不使用索引

||字元串連函數，會停用索引

+ - *  /  會停用索引

相同的索引列不能互相比較，否則會啟用全表掃描

21、使用大寫：

Oracle中會把所有的語句轉換成大寫，

有些內部表，如，查詢表名時，判斷某個表是否存在，如果是大寫有效，小寫就是無效的

22、根據磁碟讀寫速率調整塊的大小：

一個Oracle資料庫中，資料表空間、段、區、資料區塊的概念，可以根據伺服器的I/O效能調整塊的大小。

總結：

上面的點分的很細，他們都是根據Oracle的內部原理總結出的常用規律，所以，掌握原理要比記住這些跳跳架構更加重要，常用的規則有：

Oracle按照從右至左，從下至上、由外到內的執行順序

在檢索資料的時候，往往遵循查到即返回的原則

索引，記錄的是有哪些資料，所以，不要在索引列上直接使用排除不存在條件的查詢，也不要在索引上進行計算

Oracle認為空白不等於空

where命中索引的幾率比較高，通常情況下，優先選擇使用where。

由Oracle索引來理解ArcSDE索引

Oracle索引技術之如何建立最佳索引

Oracle索引列NULL值引發執行計畫該表的測試樣本

Oracle索引 主鍵影響查詢速度

Oracle索引掃描