關(guān)鍵字 ORACLE PERFORMANCE TUNING PL/SQL
出處 http://www.dbasupport.com
39. 總是使用索引的第一個列
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優(yōu)化器才會選擇使用該索引.
譯者按:
這也是一條簡單而重要的規(guī)則. 見以下實例.
SQL> create table multiindexusage ( inda number , indb number
, descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * 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'
很明顯, 當(dāng)僅引用索引的第二個列時,優(yōu)化器使用了全表掃描而忽略了索引
40. ORACLE內(nèi)部操作
當(dāng)執(zhí)行查詢時,ORACLE采用了內(nèi)部的操作. 下表顯示了幾種重要的內(nèi)部操作.
ORACLE Clause
內(nèi)部操作
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 LOOPS
CONNECT BY
CONNECT BY
41. 用UNION-ALL 替換UNION ( 如果有可能的話)
當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時,這兩個結(jié)果集合會以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)行排序.
如果用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 將重復(fù)輸出兩個結(jié)果集合中相同記錄. 因此各位還是
要從業(yè)務(wù)需求分析使用UNION ALL的可行性.
UNION 將對結(jié)果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存. 對于這
塊內(nèi)存的優(yōu)化也是相當(dāng)重要的. 下面的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的方式特別是當(dāng)訪問大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.
如果一個大表沒有被設(shè)定為緩存(CACHED)表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留
在SGA中,你就可以使用CACHE hint 來告訴優(yōu)化器把數(shù)據(jù)保留在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的情況下, 以上的查詢應(yīng)該也會使用索引,然而,如果該索引的重復(fù)值過多而你的優(yōu)化器是CBO, 優(yōu)化器就可能忽略索引.
在這種情況下, 你可以用INDEX hint強(qiáng)制ORACLE使用該索引.
ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE,
USE_HASH 等等.
譯者按:
使用hint , 表示我們對ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意,需要手工修改.
這是一個很有技巧性的工作. 我建議只針對特定的,少數(shù)的SQL進(jìn)行hint的優(yōu)化.
對ORACLE的優(yōu)化器還是要有信心(特別是CBO) |