海鷗航際

39.       總是使用索引的第一個列

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

譯者按:

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

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'

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

40.       ORACLE內部操作

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

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)