1.選擇最有效率的表名順序

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理。
當ORACLE處理多個表時，會運用排序及合併的方式連線它們。首先，掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序，然後掃描第二個表(FROM子句中最後第二個表)，最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併。
只在基於規則的最佳化器中有效。
舉例：

表 TAB1 16,384 條記錄

表 TAB2 1 條記錄

 /*選擇TAB2作為基礎表 (最好的方法)*/
  select count(*) from tab1,tab2   執行時間0.96秒  
   /*選擇TAB2作為基礎表 (不佳的方法)*/
  select count(*) from tab2,tab1   執行時間26.09秒12345

2.使用Truncate而非Delete

Delete表中記錄的時候，Oracle會在Rollback段中儲存刪除資訊以備恢復。Truncate刪除表中記錄的時候不儲存刪除資訊，不能恢復。因此Truncate刪除記錄比Delete快，而且佔用資源少。
刪除表中記錄的時候，如果不需要恢復的情況之下應該儘量使用Truncate而不是Delete。
Truncate僅適用於刪除全表的記錄。
3. SELECT子句中避免使用“*”

Oracle在解析SQL語句的時候，對於“ ”將透過查詢資料庫字典來將其轉換成對應的列名。
如果在Select子句中需要列出所有的Column時，建議列出所有的Column名稱，而不是簡單的用“”來替代，這樣可以減少多於的資料庫查詢開銷。
4. 用Where子句替換Having子句

避免使用HAVING子句，HAVING 只會在檢索出所有記錄之後才對結果集進行過濾。這個處理需要排序、總計等操作。如果能透過WHERE子句限制記錄的數目，就能減少這方面的開銷。
5. 使用表的別名（Alias）

當在SQL語句中連線多個表時, 請使用表的別名並把別名字首於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬。
6.用 >= 替代 >

如果DEPTNO上有一個索引

高效:

SELECT *
     FROM EMP   WHERE DEPTNO >=4123

低效：

SELECT *
     FROM EMP   WHERE DEPTNO >3123

7.用Union替換OR（適用於索引列）

通常情況下，用UNION替換WHERE子句中的OR將會起到較好的效果。對索引列使用OR將造成全表掃描。注意，以上規則只針對多個索引列有效。

高效：

SELECT LOC_ID , LOC_DESC , REGION     FROM LOCATION   WHERE LOC_ID = 10
   UNION
   SELECT LOC_ID , LOC_DESC , REGION     FROM LOCATION   WHERE REGION = “MELBOURNE”1234567

低效：

SELECT LOC_ID , LOC_DESC , REGION     FROM LOCATION   WHERE LOC_ID = 10 OR REGION = “MELBOURNE”123

8.用IN替換OR

低效：

SELECT….
  FROM LOCATIONWHERE LOC_ID = 10
       OR  LOC_ID = 20
       OR  LOC_ID = 3012345

高效：

SELECT…  FROM LOCATIONWHERE LOC_IN IN （10，20，30）123

SELECT…  FROM LOCATIONWHERE LOC_IN IN （10，20，30）123

9.SQL語句執行步驟

語法分析> 語義分析> 檢視轉換 >表示式轉換> 選擇最佳化器 >選擇連線方式 >選擇連線順序 >選擇資料的搜尋路徑 >執行“執行計劃”
10.Where子句中的連線順序

Oracle採用自下而上的順序解析WHERE子句。根據這個原理,表之間的連線必須寫在其他WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

/*低效,執行時間156.3秒*/SELECT … 
  FROM EMP EWHERE  SAL > 50000
     AND  JOB = ‘MANAGER’     AND  25 < (SELECT COUNT(*) FROM EMP                         WHERE MGR = E.EMPNO)1234567

/*高效,執行時間10.6秒*/SELECT … 
  FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMP                        WHERE MGR=E.EMPNO)
     AND SAL > 50000
     AND JOB = ‘MANAGER’1234567

10.用表連線替換EXISTS

通常來說，採用表連線的方式比EXISTS更有效率。

低效：

SELECT ENAME   FROM EMP EWHERE EXISTS （SELECT ‘X’ 
                  FROM DEPT              WHERE DEPT_NO = E.DEPT_NO                           AND DEPT_CAT = ‘A’） 
123456

高效：

SELECT ENAME   FROM DEPT D，EMP EWHERE E.DEPT_NO = D.DEPT_NO     AND DEPT_CAT = ‘A’ 
1234

11.用索引提高效率

（1）特點

優點：提高效率主鍵的唯一性驗證

代價：需要空間儲存定期維護

重構索引：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>1

（2）Oracle對索引有兩種訪問模式

索引唯一掃描 (Index Unique Scan)
索引範圍掃描 (Index Range Scan)
（3）基礎表的選擇

基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問)。根據最佳化器的不同，SQL語句中基礎表的選擇是不一樣的。
如果你使用的是CBO (COST BASED OPTIMIZER)，最佳化器會檢查SQL語句中的每個表的物理大小，索引的狀態，然後選用花費最低的執行路徑。
如果你用RBO (RULE BASED OPTIMIZER)，並且所有的連線條件都有索引對應，在這種情況下，基礎表就是FROM 子句中列在最後的那個表。
（4）多個平等的索引

當SQL語句的執行路徑可以使用分佈在多個表上的多個索引時，ORACLE會同時使用多個索引並在執行時對它們的記錄進行合併，檢索出僅對全部索引有效的記錄。
在ORACLE選擇執行路徑時，唯一性索引的等級高於非唯一性索引。然而這個規則只有當WHERE子句中索引列和常量比較才有效。如果索引列和其他表的索引類相比較。這種子句在最佳化器中的等級是非常低的。
如果不同表中兩個相同等級的索引將被引用，FROM子句中表的順序將決定哪個會被率先使用。 FROM子句中最後的表的索引將有最高的優先順序。
如果相同表中兩個相同等級的索引將被引用，WHERE子句中最先被引用的索引將有最高的優先順序。
（5）自動選擇索引

如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性索引。在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引。

SELECT ENAME  FROM EMPWHERE EMPNO = 2326  
     AND DEPTNO  = 20 ;1234

12.幾種不能使用索引的WHERE子句

（1）下面的例子中，‘!=’ 將不使用索引，索引只能告訴你什麼存在於表中，而不能告訴你什麼不存在於表中。

不使用索引：

 SELECT ACCOUNT_NAME      FROM TRANSACTION
   WHERE AMOUNT !=0；123

使用索引：

SELECT ACCOUNT_NAME      FROM TRANSACTION
    WHERE AMOUNT > 0；123

（2）下面的例子中，‘||’是字元連線函式。就象其他函式那樣，停用了索引。

不使用索引：

SELECT ACCOUNT_NAME，AMOUNT  FROM TRANSACTIONWHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’；123

使用索引：

SELECT ACCOUNT_NAME，AMOUNT  FROM TRANSACTIONWHERE ACCOUNT_NAME = ‘AMEX’     AND ACCOUNT_TYPE=’ A’；1234

（3）下面的例子中，‘+’是數學函式。就象其他數學函式那樣，停用了索引。

不使用索引：

SELECT ACCOUNT_NAME，AMOUNT  FROM TRANSACTIONWHERE AMOUNT + 3000 >5000；123

使用索引：

SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE AMOUNT > 2000 ；123

（4）下面的例子中，相同的索引列不能互相比較，這將會啟用全表掃描。

不使用索引：

SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME = NVL(:ACC_NAME, ACCOUNT_NAME)123

使用索引：

SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, ’%’)123

避免使用耗費資源的操作

帶有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要執行兩次排序。
通常，帶有UNION，MINUS，INTERSECT的SQL語句都可以用其他方式重寫。
14. 最佳化GROUP BY

提高GROUP BY語句的效率，可以透過將不需要的記錄在GROUP BY之前過濾掉。

低效：

SELECT JOB ，AVG（SAL）    FROM EMP  GROUP BY JOBHAVING JOB = ‘PRESIDENT’         OR JOB = ‘MANAGER’12345

高效：

SELECT JOB，AVG（SAL）   FROM EMPWHERE JOB = ‘PRESIDENT’        OR JOB = ‘MANAGER’GROUP BY JOB12345

使用日期

當使用日期時，需要注意如果有超過5位小數加到日期上，這個日期會進到下一天!

SELECT TO_DATE（‘01-JAN-93’+.99999）  FROM DUALReturns：
’01-JAN-93 23:59:59’SELECT TO_DATE（‘01-JAN-93’+.999999）  FROM DUALReturns：
’02-JAN-93 00:00:00’123456789

16.分離表和索引

總是將你的表和索引建立在不同的表空間內（TABLESPACES）。
決不要將不屬於ORACLE內部系統的物件存放到SYSTEM表空間裡。
確保資料表空間和索引表空間置於不同的硬碟上。

Oracle常用的16個最佳化技巧

相關文章