QREF: SQL Statement HINTS (Doc ID 29236.1)

最新推荐文章于 2023-02-02 11:24:19 发布

张玉龙

最新推荐文章于 2023-02-02 11:24:19 发布

阅读量445

点赞数

分类专栏： oracle优化学习

原文链接：https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=556581721593260&parent=DOCUMENT&sourceId=67522.1&id=29236.1&_afrWindowMode=0&_adf.ctrl-state=663tkgcef_926

版权

oracle优化学习专栏收录该内容

62 篇文章 4 订阅

订阅专栏

QREF: SQL Statement HINTS (Doc ID 29236.1)

PURPOSE

List and explain some of the hints for the CBO 列出并解释有关CBO的一些Hint

DETAILS

General

"A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword." . For documentation on hints see: SQL语句块只能有一个包含注释的Hint，必须遵循SELECT，UPDATE，INSERT，MERGE，DELETE关键字。有关提示的文档，请参见
Oracle Database 12c Release 2 / Administration
Database SQL Language Reference
Hints
http://docs.oracle.com
Syntax: /*+ HINT HINT ... */ 语法

Note: (In PLSQL the space between the '+' and the first letter of the hint is vital as otherwise the hint may be ignored.
So /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause problems. See Bug:697121 注意：(在PLSQL中，'+'和Hint的第一个字母之间的空格至关重要，否则可能会忽略提示。因此 /*+ ALL_ROWS */很好，但是/*+ALL_ROWS */会引起问题。请参见Bug:697121
Hints always force the use of the cost based optimizer (Exception: the RULE hint). Hint始终会强制使用基于成本的优化器（例外：RULE Hint）。
Hints must reference the table alias, if aliases are in use. 如果使用别名，则Hint必须引用表别名。

For Example:

Incorrect: 错误的
```
SELECT /*+ FULL ( emp ) */ empno FROM emp myalias WHERE empno > 10;
```
Correct: 正确的
```
SELECT /*+ FULL ( myalias ) */ empno FROM emp myalias WHERE empno > 10;
```
Hints should not reference the schema name. For example: 提示不应引用schema名称。例如：
```
SELECT /*+ index(schmaname.emp emp1) */ ...
```
should not be used. Alias the table name instead and use the alias in the hint. 不应该使用。并在提示中使用别名。
Invalid hints cause the hint to be ignored without warning 无效的Hint会导致提示被忽略而不会发出警告
Invalid hints may not be immediately obvious. For example: FIRST_ROWS as a hint when the statement has an ORDER BY clause 无效的Hint可能不会立即显而易见。例如：FIRST_ROWS作为该语句具有ORDER BY子句的Hint
(since the data has to be ordered prior to the first row being returned the first_rows hint may not have the desired effect). （由于必须在返回第一行之前对数据进行排序，因此first_rows Hint可能没有预期的效果）
The access path to be HINT(ed) must be an available access path. For example an index hint referencing a non-existant index with fail silently. 要提示的访问路径必须是可用的访问路径。例如，一个索引Hint引用一个不存在的索引而失败。
If third party tools do not contain the logic to support hints, then a potential workaround is to embed hints in a view and then reference that view. 如果第三方工具不包含支持Hint的逻辑，则潜在的解决方法是将Hint嵌入视图中，然后引用该视图。
Refer to the Oracle Database Performance Tuning Guide for more on hints. 有关Hint的更多信息，请参考《 Oracle数据库性能调优指南》。
There is a view from 11.1 on wards that shows obsolete hints: 从11.1开始，一个视图显示了过时的Hint：
```
SELECT NAME,INVERSE,SQL_FEATURE,VERSION FROM V$SQL_HINT ORDER BY NAME;
```

Hint Reference: - 参考

Hints referring to Sub-Queries/views: 涉及子查询/视图的Hint：

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
PUSH_JOIN_PRED(v)	Push join predicates into view V
NO_PUSH_JOIN_PRED(v)	Do NOT push join predicates

Access Hints 访问Hint

FULL(tab)	Use Full Table Scan FTS on table “tab”
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.
CLUSTER(tab)	Use cluster scan to access 'tab'
HASH(tab)	Use hash scan to access 'tab'
INDEX(tab [ind])	Use 'ind' scan to access 'tab' - Disables index_ffs. See Document 50607.1 How to specify an INDEX Hint
NO_INDEX (tab [ind])	Do not use 'ind' to access 'tab'
INDEX_ASC(tab [ind])	Use 'ind' to access 'tab' for range scan.
INDEX_DESC(tab {ind])	Use descending index range scan (Join problems pre 7.3)
INDEX_FFS(tab [ind])	Index fast full scan - rather than FTS.
NO_INDEX_FFS(tab [ind])	Exclude the option of using Index fast full scan - rather than FTS.
INDEX_RRS(tab [ind])	Index Rowid Range scan
INDEX_COMBINE( tab i1.. i5 )	Try to use some boolean combination of bitmap index/s i1,i2 etc
INDEX_SS(tab [ind])	Use 'ind' to access 'tab' with an index skip scan
INDEX_SS_ASC(tab [ind])	Use 'ind' to access 'tab' with an index skip scan in Ascending order
INDEX_SS_DESC(tab [ind])	Use 'ind' to access 'tab' with an index skip scan in Descending order
NO_INDEX_SS(tab [ind])	Exclude the option of using 'ind' to access 'tab' with an index skip scan
USE_CONCAT	Use concatenation (Union All) for OR (or IN) statements. (>=7.2). See Document 17214.1 (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

Joining 连接

USE_NL(tab)	Use table 'tab' as the inner table in a Nested Loops join. May not work unless by a hint that forces the correct join order (e.g. ORDERED hint).
NO_USE_NL(tab)	Excludes the use of table 'tab' as the inner table in a Nested Loops join
USE_NL_WITH_INDEX(tab)	The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table but only under the following condition: If no index is specified, the optimizer must be able to use some index with at least one join predicate as the index key. If an index is specified, the optimizer must be able to use that index with at least one join predicate as the index key.
USE_MERGE(tab..)	Join each specified table with another row source using a sort-merge join.
NO_USE_MERGE(tab..)	Excludes the option of joining each specified table with another row source using a sort-merge join.
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)
NO_USE_HASH(tab1 tab2)	Excludes the option of joining each specified table with another row source with a hash join.
STAR_TRANSFORMATION	Use best plan containing a STAR transformation (if there is one)
NO_STAR_TRANSFORMATION	Exclude the option of using the best plan containing a STAR transformation
ORDERED	Access tables in the order of the FROM clause
LEADING	This hint specifies only the driving table. From there CBO is free to investigate multiple join orders

Parallel Query Option 并行查询选项

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)
NO_PARALLEL(table)	Starting from 10g this syntax should be used
NO_PARALLEL_INDEX(table [,index])	Starting from 10g this syntax should be used

Miscellaneous 杂

APPEND	Only valid for INSERT .. SELECT. Allows INSERT to work like direct load or to perform parallel insert. See Document 50592.1
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
NO_REWRITE	Starting from 10g this syntax should be used
NO_UNNEST	Add to a subquery to prevent it from being unnested
UNNEST	Unnests specified subquery block if possible
SWAP_JOIN_INPUTS	Allows the user to switch the inputs of a join. See Document 171940.1
CARDINALITY(t1 [,..],n)	Makes the CBO to use different assumptions about cardinality at the table level

Optimizer Mode 优化器模式

FIRST_ROWS	Force CBO first rows
ALL_ROWS	Force CBO all rows
RULE	Force RBO if possible

Deprecated Hints 不建议使用的提示

NOTE: Starting from Oracle version 10.1.0.2 the following hints have been deprecated and should not be used:

AND_EQUAL, HASH_AJ, MERGE_AJ, NL_AJ, HASH_SJ, MERGE_SJ, NL_SJ, ORDERED_PREDICATES, ROWID, STAR.

Descriptions for deprecated hints are included below for historical reference:

AND_EQUAL(tab i1.. i5 )	Merge scans of 2 to 5 single column indexes.
MERGE_AJ(v)	Put hint in a NOT IN subquery to perform sort-merge anti-join or hash anti-join or nested loops antijoin (>=7.3). For example: SELECT .. WHERE deptno is not null AND deptno NOT IN (SELECT /+ HASH_AJ / deptno ...)
ASH_AJ(v)
NL_AJ(v)
HASH_SJ(v)	Transform EXISTS subquery into HASH or MERGE or nested loops semi-join to access "v"
MERGE_SJ(v)
NL_SJ(v)
ORDERED_PREDICATES	Forces optimizer to apply predicates as they appear in the WHERE clause, except for predicates used as index keys
ROWID(tab)	Access tab by ROWID directly. For example: SELECT /+ ROWID( table ) / ... FROM tab WHERE ROWID between '&1' and '&2';
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)
NOPARALLEL(table)	No parallel on "table"
NOPARALLEL_INDEX(table [,index])	Opposite to PARALLEL_INDEX
NOREWRITE	8.1+ Do not rewrite the query