Oracle Optimizer Hint优化器提示分类表

Oracle Optimizer Hint优化器提示分类表 分类 9i R1 9i R2 10g R1 10g R2 11g R1 11g R2 优化器模式 ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS ALL_ROWS FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) FIRST_ROWS(n) CHOOSE

Oracle Optimizer Hint优化器提示分类表

分类	9i R1	9i R2	10g R1	10g R2	11g R1	11g R2
优化器模式	ALL_ROWS	ALL_ROWS	ALL_ROWS	ALL_ROWS	ALL_ROWS	ALL_ROWS
FIRST_ROWS(n)	FIRST_ROWS(n)	FIRST_ROWS(n)	FIRST_ROWS(n)	FIRST_ROWS(n)	FIRST_ROWS(n)
CHOOSE	CHOOSE
RULE	RULE	RULE ※askmaclean.com
Hints	OPTIMIZER_FEATURES_ENABLE
针对访问路径	FULL	FULL	FULL	FULL	FULL	FULL
access path的HINT	ROWID	ROWID
CLUSTER	CLUSTER	CLUSTER	CLUSTER	CLUSTER	CLUSTER
HASH	HASH	HASH	HASH	HASH	HASH
INDEX	INDEX	INDEX	INDEX	INDEX	INDEX
NO_INDEX	NO_INDEX	NO_INDEX	NO_INDEX	NO_INDEX	NO_INDEX
INDEX_ASC	INDEX_ASC	INDEX_ASC	INDEX_ASC	INDEX_ASC	INDEX_ASC
INDEX_COMBINE	INDEX_COMBINE	INDEX_COMBINE	INDEX_COMBINE	INDEX_COMBINE	INDEX_COMBINE
INDEX_JOIN	INDEX_JOIN	INDEX_JOIN	INDEX_JOIN	INDEX_JOIN	INDEX_JOIN
INDEX_DESC	INDEX_DESC	INDEX_DESC	INDEX_DESC	INDEX_DESC	INDEX_DESC
INDEX_FFS	INDEX_FFS	INDEX_FFS	INDEX_FFS	INDEX_FFS	INDEX_FFS
AND_EQUAL	AND_EQUAL	NO_INDEX_FFS	NO_INDEX_FFS	NO_INDEX_FFS	NO_INDEX_FFS
INDEX_SS	INDEX_SS	INDEX_SS	INDEX_SS
NO_INDEX_SS	NO_INDEX_SS	NO_INDEX_SS	NO_INDEX_SS
INDEX_SS_ASC	INDEX_SS_ASC	INDEX_SS_ASC	INDEX_SS_ASC
INDEX_SS_DESC	INDEX_SS_DESC	INDEX_SS_DESC	INDEX_SS_DESC
关于转换的HINT	NO_QUERY_TRANSFORMATION	NO_QUERY_TRANSFORMATION	NO_QUERY_TRANSFORMATION	NO_QUERY_TRANSFORMATION
USE_CONCAT	USE_CONCAT	USE_CONCAT	USE_CONCAT	USE_CONCAT	USE_CONCAT
NO_EXPAND	NO_EXPAND	NO_EXPAND	NO_EXPAND	NO_EXPAND	NO_EXPAND
REWRITE	REWRITE	REWRITE	REWRITE	REWRITE	REWRITE
EXPAND_GSET_TO_UNION
NOREWRITE	NOREWRITE	NO_REWRITE	NO_REWRITE	NO_REWRITE	NO_REWRITE
MERGE	MERGE	MERGE	MERGE	MERGE	MERGE
NO_MERGE	NO_MERGE	NO_MERGE	NO_MERGE	NO_MERGE	NO_MERGE
STAR_TRANSFORMATION	STAR_TRANSFORMATION	STAR_TRANSFORMATION	STAR_TRANSFORMATION	STAR_TRANSFORMATION	STAR_TRANSFORMATION
NO_STAR_TRANSFORMATION	NO_STAR_TRANSFORMATION	NO_STAR_TRANSFORMATION	NO_STAR_TRANSFORMATION
FACT	FACT	FACT	FACT	FACT	FACT
NO_FACT	NO_FACT	NO_FACT	NO_FACT	NO_FACT	NO_FACT
UNNEST	UNNEST	UNNEST	UNNEST
NO_UNNEST	NO_UNNEST	NO_UNNEST	NO_UNNEST
JOIN order HINT	LEADING	LEADING	LEADING	LEADING
ORDERED	ORDERED	ORDERED	ORDERED	ORDERED	ORDERED
STAR	STAR
JOIN操作HINT	USE_NL	USE_NL	USE_NL	USE_NL	USE_NL	USE_NL
NO_USE_NL	NO_USE_NL	NO_USE_NL	NO_USE_NL
USE_NL_WITH_INDEX	USE_NL_WITH_INDEX	USE_NL_WITH_INDEX	USE_NL_WITH_INDEX
USE_MERGE	USE_MERGE	USE_MERGE	USE_MERGE	USE_MERGE	USE_MERGE
NO_USE_MERGE	NO_USE_MERGE	NO_USE_MERGE	NO_USE_MERGE
USE_HASH	USE_HASH	USE_HASH	USE_HASH	USE_HASH	USE_HASH
NO_USE_HASH	NO_USE_HASH	NO_USE_HASH	NO_USE_HASH
DRIVING_SITE	DRIVING_SITE	（参见其他HINT）	（参见其他HINT）	（参见其他HINT）	（参见其他HINT）
LEADING	LEADING
HASH_AJ、MERGE_AJ、NL_AJ	HASH_AJ、MERGE_AJ、NL_AJ
HASH_SJ、MERGE_SJ、NL_SJ	HASH_SJ、MERGE_SJ、NL_SJ
特殊	CHANGE_DUPKEY_ERROR_INDEX
IGNORE_ROW_ON_DUPKEY_INDEX
RETRY_ON_ROW_CHANGE
并行执行HINT	PARALLEL	PARALLEL	PARALLEL	PARALLEL	PARALLEL	PARALLEL
NOPARALLEL	NOPARALLEL	NO_PARALLEL	NO_PARALLEL
PQ_DISTRIBUTE	PQ_DISTRIBUTE	PQ_DISTRIBUTE	PQ_DISTRIBUTE	PQ_DISTRIBUTE	PQ_DISTRIBUTE
PARALLEL_INDEX	PARALLEL_INDEX	PARALLEL_INDEX	PARALLEL_INDEX	PARALLEL_INDEX	PARALLEL_INDEX
NOPARALLEL_INDEX	NOPARALLEL_INDEX	NO_PARALLEL_INDEX	NO_PARALLEL_INDEX	NO_PARALLEL_INDEX	NO_PARALLEL_INDEX
其他HINT	APPEND	APPEND	APPEND	APPEND	APPEND	APPEND
NOAPPEND	NOAPPEND	NOAPPEND	NOAPPEND	NOAPPEND	NOAPPEND
APPEND_VALUES
CACHE	CACHE	CACHE 诗檀软件	CACHE	CACHE	CACHE
NOCACHE	NOCACHE	NOCACHE	NOCACHE	NOCACHE	NOCACHE
UNNEST	UNNEST
NO_UNNEST	NO_UNNEST
PUSH_PRED	PUSH_PRED	PUSH_PRED	PUSH_PRED	PUSH_PRED	PUSH_PRED
NO_PUSH_PRED	NO_PUSH_PRED	NO_PUSH_PRED	NO_PUSH_PRED	NO_PUSH_PRED	NO_PUSH_PRED
PUSH_SUBQ	PUSH_SUBQ	PUSH_SUBQ	PUSH_SUBQ	PUSH_SUBQ	PUSH_SUBQ
NO_PUSH_SUBQ	NO_PUSH_SUBQ	NO_PUSH_SUBQ	NO_PUSH_SUBQ	NO_PUSH_SUBQ
QB_NAME	QB_NAME	QB_NAME	QB_NAME
ORDERED_PREDICATES	ORDERED_PREDICATES
CURSOR_SHARING_EXACT	CURSOR_SHARING_EXACT	CURSOR_SHARING_EXACT	CURSOR_SHARING_EXACT	CURSOR_SHARING_EXACT	CURSOR_SHARING_EXACT
DYNAMIC_SAMPLING	DYNAMIC_SAMPLING	DYNAMIC_SAMPLING	DYNAMIC_SAMPLING	DYNAMIC_SAMPLING
SPREAD_MIN_ANALYSIS
MODEL_MIN_ANALYSIS	MODEL_MIN_ANALYSIS	MODEL_MIN_ANALYSIS
DRIVING_SITE	DRIVING_SITE	DRIVING_SITE	DRIVING_SITE

Related posts:

1. EVENT: 10060 dump predicates in optimizer (kko)
2. Oracle SQL Optimizer IN VS Exists Again
3. Lnmp+WordPress出现控制台页面No Input File Specified
4. Segment in recyclebin? Is it free?
5. Too many fragmentation in LMT?
6. 【性能优化】optimizer statistics统计信息管理技巧
7. 【转】为何在查询中索引未被使用 (Doc ID 1549181.1)
8. 分享一张Oracle CBO(cost based optimizer)的图 从APP到DB到HW

原文地址：Oracle Optimizer Hint优化器提示分类表, 感谢原作者分享。