Sql最佳化(五) hint（提示）介紹

上篇介紹了oracle最佳化器。儘管oracle最佳化器很智慧，但有時候你想自己選擇執行計劃，可以透過hint實現。在開發測試環境中，可以透過hint測試不同執行計劃的效能。Hint的缺點是增加了管理程式碼的額外負擔，當資料庫或環境發生變化時，如果不修改hint，可能導致效能下降。例如，程式碼中用hint指定索引，但重建索引時索引名變化。

因此oracle建議使用hint測試效能後，用其他工具來管理執行計劃，如oracle 10g以後的sql tuning advisor或sql plan baseline。但hint仍舊是很常用的最佳化手段，特別是有些動態sql，表名不固定，就無法使用sql plan等工具，此時需要hint來大顯身手。

[@more@]

(一) 類別

型別	hint
optimization goals	ALL_ROWS,FIRST_ROWS(n)
Access paths	Full,hash,index,no_index,cluster
Join orders	Leading(oracle推薦，更通用) Ordered(按語句中出現次序)
Join operations	USE_NL and NO_USE_NL USE_MERGE and NO_USE_MERGE USE_HASH and NO_USE_HASH
Parallel	Parallel and no_parallel Parallel_index和no_parallel_index
Query transformation	No_query_transformation，Use_concat No_expand，Rewrite and no_rewrite
其他	APPEND，CACHE，DRIVING_SITE

說明

Use_nl (a b c d)和USE_NL(a ,b,c,d)兩種寫法都可以

USE_NL可以和LEADING組合使用

SELECT /*+ USE_NL(d ,a ,b ,c) leading(d ,a ,b ,c) */

(二) Hint使用舉例

1. 透過hint指定訪問路徑和併發

create table table_back as

select /*+ PARALLEL(p,8) */ * from pro_chr_map p where parent_account_no+0 =56719189;

pro_cdr_map有上千萬記錄，該帳號的記錄就佔了30%,訪問索引反而慢，因此選擇全表掃描，並使用併發。

Parent_account_no+0使不走索引，也可透過hint實現：

select /*+ full(p) PARALLEL(p,8) */ * from pro_chr_map p where parent_account_no+0 =56719189;

2. Hints for join operation一例

SQL> set autotrace on

寫法一：不使用hint，oracle最佳化器選擇HASH JOIN

SQL> select count(*)

from PRINT_INVOICE_PO a

where exists (select 1 from print_cdr_data b

where b.account_internal_id = a.account_no

and b.trans_date < a.from_date - 5)

and a.task_sn = 'test_3398'

and a.bill_ref_no >= 181992967766

and a.bill_ref_no <= 181993099582; 2 3 4 5 6 7 8

COUNT(*)

----------

0

Elapsed: 00:02:36.49

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=77535 Card=1 Bytes=5

3)

1 0 SORT (AGGREGATE)

2 1 HASH JOIN (SEMI) (Cost=77535 Card=75 Bytes=3975)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRINT_INVOICE_PO' (C

ost=15862 Card=1495 Bytes=55315)

4 3 INDEX (RANGE SCAN) OF 'IDX_TASK_SN_PO' (NON-UNIQUE)

(Cost=1489 Card=598110)

5 2 INDEX (FAST FULL SCAN) OF 'INDEX_PRINT_CDR2' (NON-UNIQ

UE) (Cost=59870 Card=120489550 Bytes=1927832800)

寫法二：用hint指定nested loop join

SQL> select COUNT(*)

from nprint.PRINT_INVOICE_PO a

where exists (select /*+ NL_SJ */ 1 from print_cdr_data b

where b.account_internal_id = a.account_no

and b.trans_date < a.from_date - 5)

and a.task_sn = 'test_3398'

and a.bill_ref_no >= 181992967766

and a.bill_ref_no <= 181993099582;

2 3 4 5 6 7 8

COUNT(*)

----------

0

Elapsed: 00:00:01.38

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2735267 Card=1 Bytes

=53)

1 0 SORT (AGGREGATE)

2 1 NESTED LOOPS (SEMI) (Cost=2735267 Card=75 Bytes=3975)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRINT_INVOICE_PO' (C

ost=15862 Card=1495 Bytes=55315)

4 3 INDEX (RANGE SCAN) OF 'IDX_TASK_SN_PO' (NON-UNIQUE)

(Cost=1489 Card=598110)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRINT_CDR_DATA' (Cos

t=1819 Card=6024478 Bytes=96391648)

6 5 INDEX (RANGE SCAN) OF 'IDX_ACCOUNT_INTERNAL_ID' (NON

-UNIQUE) (Cost=8 Card=203)