自己對(duì)oracle sql的一些優(yōu)化總結(jié)，自己也記錄下來(lái)，也希望對(duì)大家有幫助：

一：使用where少使用having;

二：查兩張以上表時(shí)，把記錄少的放在右邊；

三：減少對(duì)表的訪問(wèn)次數(shù)；

四：有where子查詢時(shí)，子查詢放在最前；

五：select語(yǔ)句中盡量避免使用*（執(zhí)行時(shí)會(huì)把*依次轉(zhuǎn)換為列名）；

六：盡量多的使用commit；

七：Decode可以避免重復(fù)掃描相同的記錄或重復(fù)連接相同的表；

八：通過(guò)內(nèi)部函數(shù)也可提高sql效率；

九：連接多個(gè)表時(shí)，使用別名并把別名前綴于每個(gè)字段上；

十：用exists代替in

十一：not exists代替 not in（not in 字句將執(zhí)行一個(gè)內(nèi)部的排序和合并，任何情況下，not in是最低效的，子查詢中全表掃描了。為了避免使用not in，可以改寫(xiě)成outer joins或not exists）;

十二：表連接比exists更高效；

十三：用exists替換distinct

       例： 

             低：                                                                    高：

             select distinct dept_no, dept_name                        select dept_no, dept_name

               from dept d, emp e                                               from dept d

            where d.dept_no = e.dept_no;                               where exists (select 1 from emp e where e.dept_no = d.dept_no);

十四：使用TKPROF工具來(lái)查詢sql性能狀態(tài)；

十五：用索引提高效率（代價(jià)是：索引需要空間，而且定期重構(gòu)索引很有必要：ALTER INDEX<INDEXNAME> REBUILD<TABLESPACENAME）；

先介紹下索引的原理，方便接下來(lái)對(duì)索引的優(yōu)化的理解：

通過(guò)索引找到rowid，然后通過(guò)rowid訪問(wèn)表。但如果查詢的列包括在index中，將不在執(zhí)行第二部操作，因?yàn)闄z索數(shù)據(jù)保存在索引中，單單訪問(wèn)索引就可以完全滿足查詢要求。

前提提要：在十六例中，LODGING列有唯一索引；MANAGER列上有非唯一性索引。

十六：索引范圍查詢（INDEX RANGE SACEN):

        適用于兩種情況：

        1）基于一個(gè)范圍的查詢：

              SELECT LODGING FROM LODGING WHERE LODGING LIKE 'M%'

        （where字句條件包括一系列的值，oracle將通過(guò)索引范圍查詢方式查詢LODGING_PK）

        2) 基于非唯一性索引的檢索：

              SELECT LODGING FROM LODGING WHERE MANAGER = 'LI';

         (此查詢分兩步：LODGING$MANAGER的索引范圍查詢得到所有符合條件記錄的rowid，然后通過(guò)rowid訪問(wèn)表得到LODGING列的值。該索引為非唯一性索引，數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描）

where字句中，如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符開(kāi)始，索引將不被采用，而會(huì)全表掃描，如 SELECT..... WHERE MANAGER LIKE '%LI'

十七：基礎(chǔ)表的選擇：

        基礎(chǔ)表：最先訪問(wèn)的表（通常以全表掃描的方式被訪問(wèn)）。

        根據(jù)優(yōu)化器的不同，SQL語(yǔ)句中基礎(chǔ)表的選擇是不一樣的：

        如果使用CBO，優(yōu)化器會(huì)檢查SQL語(yǔ)句中的每個(gè)表的物理大小，索引的狀態(tài)，然后選用話費(fèi)最低的路徑。

        如果使用RBO，并且所有的連接條件都有索引對(duì)應(yīng)，這種情況下基礎(chǔ)表就是FROM字句中列在最后的表

        例：

             SELECT A.NAME, B.MANAGER FROM WOKER A, LODGING B WHERE A.LODGING = B.LODGING;

             由于LODGING列上有一個(gè)索引，而且WORKER表中沒(méi)有相比較的索引，WORKER表將被作為查詢基礎(chǔ)表。

十八：多個(gè)平等的索引：

        當(dāng)SQL語(yǔ)句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí)，oracle會(huì)同事使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄合并，檢索僅對(duì)全部索引有效的記錄。

        oracle選擇執(zhí)行路徑是，唯一索引等級(jí)高于非唯一索引，只有當(dāng)where字句中索引列和常量比較才有效。如果索引列和其它表的索引列相比較，這種字句在優(yōu)化器中等級(jí)非常低；

        如果不同表中兩個(gè)相同等級(jí)的索引將被引用，根據(jù)FROM字句中表的順序決定哪個(gè)先被使用。FROM字句中最后的表索引優(yōu)先級(jí)高。如果相同表中兩個(gè)相同等級(jí)的索引將被引用，where字句中最先被引用的索引將有最高的優(yōu)先級(jí)。

       例：DEPTNO上有非唯一性索引，EMP_CAT也有非唯一性索引

            SELECT ENAME FROM EMP WHERE DEPT_NO = 20 AND EMP_CAT = 'A'; 

            DEPTNO索引將被先檢索，然后同EMP_CAT索引檢索出的結(jié)果合并，執(zhí)行路徑如下：

              TABLE ACCESS BY ROWID ON EMP

              AND _EQUAL

              INDEX RANGE SCAN ON  DEPT_IDX

              INDEX RANGE SCAN ON CAT_IDX

十九：等式比較與范圍比較：

       先上例子：

           SELECT ENAME FROM EMP WHERE DEPT_NO > 20 AND EMP_CAT = 'A';

         （在兩個(gè)非唯一性索引前提下）此時(shí)范圍索引不被使用，通過(guò)EMP_CAT索引查詢出記錄再與DEPT_NO條件進(jìn)行比較

      注意：唯一性所以做范圍比較時(shí)，等級(jí)要比非唯一性索引的等式比較低；

二十：強(qiáng)制索引失效：

        如果兩個(gè)或兩個(gè)以上索引具有相同的等級(jí)，可以強(qiáng)制命令oracle優(yōu)化器使用其中的一個(gè)。 那何時(shí)使用此種策略呢？如果一個(gè)索引已接近于唯一，而另一索引有很多重復(fù)的值，排序與合并反而會(huì)成為負(fù)擔(dān)，此時(shí)可以屏蔽后者使其索引失效。

       （失效方式：對(duì)索引列加入計(jì)算'+0'或'||""'）;