Oracle常用優化16個技巧

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秒

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 >=4

低效：

SELECT *
     FROM EMP
   WHERE DEPTNO >3

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”

低效：

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

8.用IN替換OR

低效：

SELECT….
  FROM LOCATION
WHERE LOC_ID = 10
       OR  LOC_ID = 20
       OR  LOC_ID = 30

高效：

SELECT…
  FROM LOCATION
WHERE LOC_IN IN （10，20，30）

SELECT…
  FROM LOCATION
WHERE LOC_IN IN （10，20，30）

9.SQL語句執行步驟

文法分析> 語義分析> 視圖轉換 >表達式轉換> 選擇優化器 >選擇連接配接方式 >選擇連接配接順序 >選擇資料的搜尋路徑 >運作“執行計劃”

10.Where子句中的連接配接順序

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

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

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

10.用表連接配接替換EXISTS

通常來說，采用表連接配接的方式比EXISTS更有效率。

低效：

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

高效：

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

11.用索引提高效率

（1）特點

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

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

重構索引：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（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 EMP
WHERE EMPNO = 2326  
     AND DEPTNO  = 20 ;

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

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

不使用索引：

SELECT ACCOUNT_NAME
      FROM TRANSACTION
   WHERE AMOUNT !=0；

使用索引：

SELECT ACCOUNT_NAME
      FROM TRANSACTION
    WHERE AMOUNT > 0；

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

不使用索引：

SELECT ACCOUNT_NAME，AMOUNT
  FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’；

使用索引：

SELECT ACCOUNT_NAME，AMOUNT
  FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
     AND ACCOUNT_TYPE=’ A’；

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

不使用索引：

SELECT ACCOUNT_NAME，AMOUNT
  FROM TRANSACTION
WHERE AMOUNT + 3000 >5000；

使用索引：

SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ；

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

不使用索引：

SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME, ACCOUNT_NAME)

使用索引：

SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, ’%’)

13.避免使用耗費資源的操作

帶有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 JOB
HAVING JOB = ‘PRESIDENT’
         OR JOB = ‘MANAGER’

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

15.使用日期

SELECT TO_DATE（‘01-JAN-93’+.99999）
  FROM DUAL
Returns：
’01-JAN-93 23:59:59’

SELECT TO_DATE（‘01-JAN-93’+.999999）
  FROM DUAL
Returns：
’02-JAN-93 00:00:00’

Oracle常用優化16個技巧

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

2.使用Truncate而非Delete

3. SELECT子句中避免使用“*”

4. 用Where子句替換Having子句

5. 使用表的别名（Alias）

6.用 >= 替代 >

7.用Union替換OR（适用于索引列）

8.用IN替換OR

9.SQL語句執行步驟

10.用表連接配接替換EXISTS

11.用索引提高效率

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

13.避免使用耗費資源的操作

14. 優化GROUP BY

15.使用日期

16.分離表和索引

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List