ORACLE SQL效能最佳化系列 (二) (轉)

ORACLE SQL效能最佳化系列 (二) (轉)[@more@]

4. 選擇最有的表名順序(只在基於規則的器中有效)

:namespace prefix = o ns = "urn:schemas--com::office" />

的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合併的方式連線它們.首先,掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併.

例如:

  表 TAB1 16,384 條記錄

  表 TAB2 1  條記錄

  選擇TAB2作為基礎表 (最好的方法)

  count(*) from tab1,tab2  時間0.96秒

 

  選擇TAB2作為基礎表 (不佳的方法)

  select count(*) from tab2,tab1  執行時間26.09秒

如果有3個以上的表連線查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.

例如:

  EMP表描述了LOCATION表和CATEGORY表的交集.

 SELECT *

FROM LOCATION L ,

  CATEGORY C,

  EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

將比下列更有效率

SELECT *

FROM EMP E ,

LOCATION L ,

  CATEGORY C

WHERE  E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

5.  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’;

6.  SELECT子句中避免使用 ‘ * ‘

當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 ‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉換成所有的列名, 這個工作是透過查詢資料字典完成的, 這意味著將耗費更多的時間.

 

7.  減少訪問的次數

當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算的利用率, 繫結變數 , 讀資料塊等等. 由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量.

例如,

  以下有三種方法可以檢索出僱員號等於0342或0291的職員.

方法1 (最低效)

  SELECT EMP_NAME , SALARY , GRADE

  FROM EMP

  WHERE EMP_NO = 342;

 

  SELECT EMP_NAME , SALARY , GRADE

  FROM EMP

  WHERE EMP_NO = 291;

方法2 (次低效)

 

  DECLARE

  CURSOR C1 (E_NO NUMBER) IS

  SELECT EMP_NAME,SALARY,GRADE

  FROM EMP

  WHERE EMP_NO = E_NO;

  BEGIN

  OPEN C1(342);

  FETCH C1 INTO …,..,.. ;

  …..

  OPEN C1(291);

   FETCH C1 INTO …,..,.. ;

  CLOSE C1;

  END;

方法3 (高效)

  SELECT A.EMP_NAME , A.SALARY , A.GRADE,

  B.EMP_NAME , B.SALARY , B.GRADE

  FROM EMP A,EMP B

  WHERE A.EMP_NO = 342

  AND  B.EMP_NO = 291;

注意:

  在SQL*Plus , SQL*Forms和Pro*C中重新設定ARRAYSIZE引數, 可以增加每次資料庫訪問的檢索資料量 ,建議值為200

 

(待續)