海鷗航際

39.       總是使用索引的第一個(gè)列

如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引.

譯者按:

這也是一條簡(jiǎn)單而重要的規(guī)則. 見以下實(shí)例.

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)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引

40.       ORACLE內(nèi)部操作

當(dāng)執(zhí)行查詢時(shí),ORACLE采用了內(nèi)部的操作. 下表顯示了幾種重要的內(nèi)部操作.

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

當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)行排序.

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

舉例:

   低效：

　　　　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ù)輸出兩個(gè)結(jié)果集合中相同記錄. 因此各位還是

要從業(yè)務(wù)需求分析使用UNION ALL的可行性.

UNION 將對(duì)結(jié)果集合排序,這個(gè)操作會(huì)使用到SORT_AREA_SIZE這塊內(nèi)存. 對(duì)于這

塊內(nèi)存的優(yōu)化也是相當(dāng)重要的. 下面的SQL可以用來(lái)查詢排序的消耗量

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

       substr(value,1,15)   "Value"

from v$sysstat

where name like 'sort%'

42.       使用提示(Hints)

對(duì)于表的訪問(wèn),可以使用兩種Hints.

FULL 和 ROWID

FULL hint 告訴ORACLE使用全表掃描的方式訪問(wèn)指定表.

例如:

   SELECT /*+ FULL(EMP) */ *

   FROM EMP

   WHERE EMPNO = 7893;

   ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問(wèn)表.

   通常, 你需要采用TABLE ACCESS BY ROWID的方式特別是當(dāng)訪問(wèn)大表的時(shí)候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.

   如果一個(gè)大表沒(méi)有被設(shè)定為緩存(CACHED)表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留

在SGA中,你就可以使用CACHE hint 來(lái)告訴優(yōu)化器把數(shù)據(jù)保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.

例如:

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

FROM WORK;

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

例如:

   SELECT /*+ INDEX(LODGING) */ LODGING

   FROM LODGING

   WHERE MANAGER = ‘BILL GATES’;

   在不使用hint的情況下, 以上的查詢應(yīng)該也會(huì)使用索引,然而,如果該索引的重復(fù)值過(guò)多而你的優(yōu)化器是CBO, 優(yōu)化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強(qiáng)制ORACLE使用該索引.

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

譯者按:

   使用hint , 表示我們對(duì)ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意,需要手工修改.

這是一個(gè)很有技巧性的工作. 我建議只針對(duì)特定的,少數(shù)的SQL進(jìn)行hint的優(yōu)化.

對(duì)ORACLE的優(yōu)化器還是要有信心(特別是CBO)