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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18474/viewspace-1060732/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18474/viewspace-1060732/