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

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

39.  總是使用的第一個列:namespace prefix = o ns = "urn:schemas--com::office" />

如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,器才會選擇使用該索引.

譯者按:

這也是一條簡單而重要的規則. 見以下例項.

> create table multiindexusage ( inda number , in number , descr varchar2(10));

Table created.

SQL> create index multindex on multiindexusage(inda,indb);

Index created.

SQL> set autotrace traceonly

SQL>  * from  multiindexusage where inda = 1;

Execution Plan

----------------------------------------------------------

  0  SELECT STATEMENT Optimizer=CHOOSE

  1  0  TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'

  2   1  INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)

SQL> select * from  multiindexusage where indb = 1;

Execution Plan

----------------------------------------------------------

  0  SELECT STATEMENT Optimizer=CHOOSE

  1  0  TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'

 

很明顯, 當僅引用索引的第二個列時,最佳化器使用了全表掃描而忽略了索引

40.  內部操作

當查詢時,ORACLE採用了內部的操作. 下表顯示了幾種重要的內部操作.

ORACLE Clause

內部操作

ORDER BY

SORT ORDER BY

UNION

UNION-ALL

MINUS

MINUS

INTERSECT

INTERSECT

DISTINCT,MINUS,INTERSECT,UNION

SORT UNIQUE

MIN,MAX,COUNT

SORT AGGREGATE

GROUP BY

SORT GROUP BY

ROWNUM

COUNT or COUNT STOPKEY

Queries involving Joins

SORT JOIN,MERGE JOIN,NESTED LS

CONNECT BY

CONNECT BY

41.  用UNION-ALL 替換UNION ( 如果有可能的話)

當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合併, 然後在輸出最終結果前進行排序.

如果用UNION ALL替代UNION, 這樣排序就不是必要了. 就會因此得到提高.

舉例:

  低效：

　　　　SELECT ACCT_NUM, BALANCE_AMT

  FROM DEBIT_TRANSACTIONS

  WHERE TRAN_DATE = ’31-DEC-95’

  UNION

  SELECT ACCT_NUM, BALANCE_AMT

  FROM DEBIT_TRANSACTIONS

   WHERE TRAN_DATE = ’31-DEC-95’

高效:

  SELECT ACCT_NUM, BALANCE_AMT

  FROM DEBIT_TRANSACTIONS

  WHERE TRAN_DATE = ’31-DEC-95’

  UNION ALL

  SELECT ACCT_NUM, BALANCE_AMT

  FROM DEBIT_TRANSACTIONS

  WHERE TRAN_DATE = ’31-DEC-95’

譯者按:

需要注意的是，UNION ALL 將重複輸出兩個結果集合中相同記錄. 因此各位還是

要從業務需求分析使用UNION ALL的可行性.

UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊. 對於這

塊記憶體的最佳化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量

Select substr(name,1,25)  "Sort Area Name",

     substr(value,1,15)  "Value"

from v$sysstat

where name like 'sort%'

 

42.  使用提示(Hints)

對於表的訪問,可以使用兩種Hints.

FULL 和 ROWID

FULL hint 告訴ORACLE使用全表掃描的方式訪問指定表.

例如:

  SELECT /*+ FULL(EMP) */ *

  FROM EMP

  WHERE EMPNO = 7893;

  ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問表.

  通常, 你需要採用TABLE ACCESS BY ROWID的方式特別是當訪問大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.

  如果一個大表沒有被設定為快取(CACHED)表而你希望它的資料在查詢結束是仍然停留

在SGA中,你就可以使用CACHE hint 來告訴最佳化器把資料保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.

例如:

SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *

FROM WORK;

  索引hint 告訴ORACLE使用基於索引的掃描方式. 你不必說明具體的索引名稱

例如:

  SELECT /*+ INDEX(LODGING) */ LODGING

  FROM LODGING

  WHERE MANAGER = ‘BILL GATES’;

 

  在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重複值過多而你的最佳化器是CBO, 最佳化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強制ORACLE使用該索引.

  ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.

 

譯者按:

  使用hint , 表示我們對ORACLE最佳化器預設的執行路徑不滿意,需要手工修改.

這是一個很有技巧性的工作. 我建議只針對特定的,少數的SQL進行hint的最佳化.

對ORACLE的最佳化器還是要有信心(特別是CBO)