Oracle之sql語句優(yōu)化

    項目中雖然使用了orm映射，但Oracle系統(tǒng)的優(yōu)化還是很有價值的，這里從sql語句的角度對常用的語句做下總結(jié)。

1、from字段中的優(yōu)化：
     Oracle安照從右到左的順序加載表數(shù)據(jù)，應該把可以排除數(shù)據(jù)最多的表放到后面（基礎(chǔ)表）。
     比如，在關(guān)聯(lián)查詢中，把課程表放到后面，成績表放到前面，因為課程表數(shù)據(jù)一般比較少，關(guān)聯(lián)的時候可以快速的過濾掉一些成績數(shù)據(jù)。

2、where中的優(yōu)化：
     對可以過濾數(shù)據(jù)最多的，放到后面，原理也是Oracle執(zhí)行從下到上（從右到左）的順序。

3、使用列名替代*：

     省去從字典表中解析的過程 ：
     Oracle執(zhí)行sql語句過程： 
     在共享池中搜索sql語句是否已經(jīng)存在
     驗證sql是否語法精確
     執(zhí)行數(shù)據(jù)字典驗證表和列的定義
     獲取對象的分析鎖，以便在語句的分析過程中對象的定義不會改變
     檢查用戶是否具有相應的操作權(quán)限
     確定語句最佳執(zhí)行計劃
     將語句和執(zhí)行方案保存到共享的sql區(qū)。

4、使用？參數(shù)形式：
     盡量不要使用拼接字串的方式，因為帶有？的參數(shù)形式會緩存分析結(jié)果，省去上面很多步驟。

5、使用decode函數(shù)：
     decode不會重復掃描相同記錄或重復連接相同的表，減少表的掃描次數(shù)。

6、使用truncate替代delete：
     delete：逐條刪除，并保存回滾還原點。DML（數(shù)據(jù)操作語言insert，update，delete，merge）
     truncate不會保存回滾點，是DDL（數(shù)據(jù)定義語言，如drop，alter）
     在oracle里DML是可以rollback的，而DDL是不可以的

     如果數(shù)據(jù)量大，差別很大的。

7、盡量多用commit：
     Oracle只有在commit后才會提交（區(qū)別于sqlserver），如果沒有提交，會在內(nèi)存中保存很多數(shù)據(jù)。commit后釋放的資源有：
     回滾上段用于恢復數(shù)據(jù)庫的記錄信息
     被程序語句獲得的鎖
     Redo log buffer中的空間
     為管理上述3種資源的內(nèi)部花費

8、使用where語句替代having子句：
     在分組或者配合group的時候會使用having。
     where會直接過濾掉數(shù)據(jù)，使用having往往會配合group，檢索出數(shù)據(jù)后，會帶著數(shù)據(jù)進行排序、統(tǒng)計等。
9、on/where/having排序使用順序：
    on：做的是數(shù)據(jù)映射，在映射時，把沒用的數(shù)據(jù)直接過濾掉了
    where：先進行一個全表的搜索，之后再進行數(shù)據(jù)篩選
    where又比having要快，having中帶的垃圾數(shù)據(jù)同樣也做了運算

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、識別低效率執(zhí)行的語句：
    各種sql優(yōu)化的圖形工具層出不窮，但可以寫出自己的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數(shù)據(jù)類型，幾乎可以索引所有列
定期重建索引，在刪除和修改多的表，不適用索引，原因，不解釋了。

    注意，并不是創(chuàng)建了索引后就一定會走索引，使用索引的時候，不能使用索引進行一些計算，否則失效
使用索引快于全表掃描，在多表連接使用索引提高效率。
   避免在索引上使用not：

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

   用exitsts替代distinct：

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

   避免在索引列上使用計算：
     低效：
     Select * from dept  where sal * 12 >25000
     高效：
     Select * from dept where sal> 25000/12
    避免在索引列上使用is null 和 is not null：
     因為索引不會對null數(shù)據(jù)類型進行索引，索引只是記錄有什么
     對于單列索引，不會進行索引
     對于符合索引，如果所有列多為空，不進行索引，只要有一個列不空，就索引。

    在Oracle中空不等于空，所以，就會插入若干條相同鍵值的記錄，而他們的值都是空，而空值不進行索引，所以，當進行空值比較時，會使用Oracle，停止使用該索引。
    低效：
     Select …. From dep where dep_code is not null;
     高效：
     Select …. From dep where dep_code >=0;這里也會過濾掉null列
     總是使用索引的第一個列：

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

15、>=是直接定位：

      如果使用 >還需要一個判斷的過程。
16、使用union替代where字句中的or：
     對索引列使用or會導致全表掃描，針對多個索引列有效

17、在oracle8i下，兩者執(zhí)行路徑似乎相同，但能用in的就別用or。
18、使用union-all：

    union-all:不排序，查詢所有的，不過濾重復的
    nuion：排序(優(yōu)化sort_raea_size這塊內(nèi)存)，過濾重復的。
19、order by 用where 限定：

     order by名中索引的條件比較苛刻
    排序列必須包含在相同的索引中，并保持索引中的排列順序，Order by中所有列不能定義為空。

20、需要當心的where子句：
     !=將不使用索引
     ||字符連接函數(shù)，會停用索引
     + - *  /  會停用索引 
    相同的索引列不能互相比較，否則會啟用全表掃描

21、使用大寫：
    Oracle中會把所有的語句轉(zhuǎn)換成大寫，
    有些內(nèi)部表，如，查詢表名時，判斷某個表是否存在，如果是大寫有效，小寫就是無效的

22、根據(jù)磁盤讀寫速率調(diào)整塊的大小：

     一個Oracle數(shù)據(jù)庫中，表空間、段、區(qū)、數(shù)據(jù)塊的概念，可以根據(jù)服務器的I/O性能調(diào)整塊的大小。

總結(jié)：

    上面的點分的很細，他們都是根據(jù)Oracle的內(nèi)部原理總結(jié)出的常用規(guī)律，所以，掌握原理要比記住這些跳跳框架更加重要，常用的規(guī)則有：

    Oracle按照從右到左，從下至上的執(zhí)行順序

    在檢索數(shù)據(jù)的時候，往往遵循查到即返回的原則

    索引，記錄的是有哪些數(shù)據(jù)，所以，不要在索引列上直接使用排除不存在條件的查詢，也不要在索引上進行計算

    Oracle認為空不等于空

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

另外推薦一些不錯的鏈接：

http://blog.csdn.net/tianlesoftware/article/details/7008801

http://blog.csdn.net/lk_blog/article/details/7585540

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學習有所幫助,可以手機掃描二維碼進行捐贈