会话级:
ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;
修改会话级的优化提示。
Hints提示:
-
提示将使语句强制执行基于成本的优化器
(除了提示
RULE
外)
- 在hints中使用表名的别名
- 确保表都被分析过了
语法
: /*+ HINT HINT ... */
(
在PLSQL
中,
'+'
与
hint
的第一个字符之间的空格是很重要的
/*+ ALL_ROWS */
是正确的,但
/*+ALL_ROWS */
是错误的
)
1 Optimizer Mode
优化模式:
FIRST_ROWS,ALL_ROWS
|
Force CBO first rows or all rows
|
RULE
|
Force Rule if possible
|
ORDERED
|
按照在FROM 子句中的先后顺序访问表
|
ORDERED_PREDICATES
|
Use in the WHERE clause to apply predicates in the order that they appear.
Does not apply predicate evaluation on index keys
|
.
2
子查询或视图:
PUSH_SUBQ
|
Causes all subqueries in a query block to be executed at the earliest possible time.
Normally subqueries are executed as the last is applied is outerjoined or remote or joined with a merge join. (>=7.2)
|
NO_MERGE(v)
|
Use this hint in a VIEW to PREVENT it being merged into the parent query. (>=7.2) or use NO_MERGE(v) in parent query block to prevent view V being merged
|
MERGE(v)
|
Do merge view V
|
MERGE_AJ(v) }
|
Put hint in a NOT IN subquery to perform (>=7.3)
|
HASH_AJ(v) }
|
SMJ anti-join or hash anti-join. (>=7.3)
Eg: SELECT .. WHERE deptno is not null AND deptno NOT IN(SELECT /*+ HASH_AJ */ deptno ...)
|
HASH_SJ(v) }
|
Transform EXISTS subquery into HASH or MERGE
|
MERGE_SJ(v) }
|
semi-join to access "v"
|
PUSH_JOIN_PRED(v)
|
Push join predicates into view V
|
NO_PUSH_JOIN_PRED(v)
|
Do NOT push join predicates
|
3
读取方式:
FULL(tab)
|
对表实行全表扫描(FTS)
|
CACHE(tab)
|
If table within <Parameter:CACHE_SIZE_THRESHOLD> treat as if it had the CACHE option set. See <Parameter:CACHE_SIZE_THRESHOLD>. Only applies if FTS used.
|
NOCACHE(tab)
|
Do not cache table even if it has CACHE option set. Only relevant for FTS
|
ROWID(tab)
|
Access tab by ROWID directly
SELECT /*+ ROWID( table ) */ ... FROM tab WHERE ROWID between '&1' and '&2';
|
CLUSTER(tab)
|
Use cluster scan to access 'tab'
|
HASH(tab)
|
使用hash scan来访问表
|
INDEX( tab ndex )
|
使用索引来访问表
|
INDEX_ASC( tab ndex )
|
Use 'index' to access 'tab' for range scan.
|
INDEX_DESC( tab ndex )
|
Use descending index range scan (Join problems pre 7.3)
|
INDEX_FFS( tab index)
|
Index fast full scan - rather than FTS.
|
INDEX_COMBINE( tab i1.. i5 )
|
Try to use some boolean combination of bitmap index/s i1,i2 etc
|
AND_EQUAL(tab i1.. i5 )
|
Merge scans of 2 to 5 single column indexes.
|
USE_CONCAT
|
Use concatenation (Union All) for OR (or IN) statements. (>=7.2). See(7.2 requires <Event:10078>, 7.3 no hint req)
|
NO_EXPAND
|
Do not perform OR-expansion (Ie: Do not use Concatenation).
|
DRIVING_SITE(table)
|
Forces query execution to be done at the site where "table" resides
|
4
连接:
USE_NL(tab)
|
Use table 'tab' as the driving table in a Nested Loops join. If the driving row source is a combination of tables name one of the tables in the inner join and the NL should drive off the entire row-source. Does not work unless accompanied by an ORDERED hint.
|
USE_MERGE(tab..)
|
Use 'tab' as the driving table in a sort-merge join. Does not work unless accompanied by an ORDERED hint.
|
USE_HASH(tab1 tab2)
|
Join each specified table with another row source with a hash join. 'tab1' is joined to previous row source using a hash join. (>=7.3)
|
STAR
|
Force a star query plan if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least 3 tables and the large table's concatenated index has at least 3 columns and there are no conflicting access or join method hints. (>=7.3)
|
STAR_TRANSFORMATION
|
Use best plan containing a STAR transformation(if there is one)
|
5
并行查询选项:
PARALLEL ( table, <degree> [, <instances>] )
|
Use parallel degree / instances as specified
|
PARALLEL_INDEX(table, [ index, [ degree [,instances] ] ] )
|
Parallel range scan for partitioned index
|
PQ_DISTRIBUTE(tab,out,in)
|
How to distribute rows from tab in a PQ(out/in may be HASH/NONE/BROADCAST/PARTITION)
|
NOPARALLEL(table)
|
No parallel on "table"
|
NOPARALLEL_INDEX(table [,index])
|
|
6 Miscellaneous
APPEND
|
Only valid for INSERT .. SELECT. Allows INSERT to work like direct load or to perform parallel insert.
|
NOAPPEND
|
Do not use INSERT APPEND functionality
|
REWRITE(v1[,v2])
|
8.1+ With a view list use eligible materialized view Without view list use any eligible MV
|
NOREWRITE
|
8.1+ Do not rewrite the query
|