oracle hints说明

更新时间:2023-06-15 00:28:46 阅读: 评论:0

万物与虚无团日活动心得收获之秋发行是什么意思
Hints for Optimization Approaches and Goals
ALL_ROWS
The ALL_ROWS hint explicitly choos the cost-bad approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
FIRST_ROWS
The FIRST_ROWS hint explicitly choos the cost-bad approach to optimize a statement block with a goal of best respon time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast respon for returning the first n rows.
CHOOSE
The CHOOSE hint caus the optimizer to choo between the rule-bad approach and the cost-bad approach for a SQL statement bad on the prence of statistics for the tables accesd by the statement
RULE
The RULE hint explicitly choos rule-bad optimization for a statement block. This hint also caus the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g.
Hints for Access Paths
FULL
The FULL hint explicitly choos a full table scan for the specified table. The syntax of the FULL hint is FULL(table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed.
ROWID
The ROWID hint explicitly choos a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID(table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g)
CLUSTER
The CLUSTER hint explicitly choos a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER(table) where table specifies the name or alias of the table to be accesd by a cluster scan.
HASH
The HASH hint explicitly choos a hash scan to access the specified table. The syntax of the HASH hint is HASH(table) where table specifies the name or alias of the table to be accesd by a hash scan.
HASH_AJ
The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ(table) where table specifies the name or alias of the table to be accesd.(depricated in Oracle 10g)
INDEX
The INDEX hint explicitly choos an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes:
NO_INDEX
The NO_INDEX hint explicitly disallows a t of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX(table index)
INDEX_ASC
The INDEX_ASC hint explicitly choos an index scan for the specified table. If the statement us an index range scan, Oracle scans the index entries in ascending order of their indexed values.
INDEX_COMBINE
If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will u on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to u some boolean combination of tho particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index).
INDEX_JOIN
Explicitly instructs the optimizer to u an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
INDEX_DESC
The INDEX_DESC hint explicitly choos an index scan for the specified table. If the statement us an index range scan, Oracle scans the index entries in descending order of their indexed values.
INDEX_FFS
This hint caus a fast full index scan to be performed rather than a full table.
NO_INDEX_FFS
Do not u fast full index scan (from Oracle 10g)
INDEX_SS
Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_ASC
Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_DESC
Exclude range scan from query plan (from Oracle 10g)
NO_INDEX_SS
The NO_INDEX_SS hint caus the optimizer to exclude a skip scan of the specified indexes on the specified table. (from Oracle 10g)
Hints for Query Transformations
NO_QUERY_TRANSFORMATION
Prevents the optimizer performing query transformations. (from Oracle 10g)
USE_CONCAT
The USE_CONCAT hint forces combined OR conditions in the WHERE clau of a query to be transformed into a compound query using the UNION ALL t operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
NO_EXPAND
The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clau. Usually, the optimizer considers using OR expansion and us this method if it decides that the cost is lower than not using it.
REWRITE
The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. U the REWRITE hint with or without a view list. If you u REWRITE with a view list and the list contains an eligible materialized view, then Oracle us that view regardless of its cost.
NOREWRITE / NO_REWRITE
In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the tting of the parameter QUERY_REWRITE_ENABLED.
MERGE
The MERGE hint lets you merge views in a query.
NO_MERGE
The NO_MERGE hint caus Oracle not to merge mergeable views. This hint is most often ud to reduce the number of possible permutations for a query and make optimization faster.
FACT
The FACT hint indicated that the table should be considered as a fact table. This is ud in the context of the star transformation.
NO_FACT
The NO_FACT hint is ud in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
STAR_TRANSFORMATION
The STAR_TRANSFORMATION hint makes the optimizer u the best plan in which the transformation has been ud. Without the hint, the optimizer could make a query optimization decision to u the best plan generated without the transformation, instead of the best plan for the transformed query.
NO_STAR_TRANSFORMATION
Do not u star transformation (from Oracle 10g)
UNNEST
The UNNEST hint specifies subquery unnesting.
NO_UNNEST
U of the NO_UNNEST hint turns off unnesting for specific subquery blocks.
Hints for Join Orders
LEADING
Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can u the ORDERED hint. Syntax: LEADING(table)
ORDERED
The ORDERED hint caus Oracle to join tables in the order in which they appear in the FROM clau. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer choos the order in which to join the tables. You may want to u the ORDERED hint to specify a join order if you know something about the number of rows lected from each table that the optimizer does not. Such information would allow you to choo an inner and outer table better than the optimizer could.
Hints for Join Operations
USE_NL
The USE_NL hint caus Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be ud as the inner table of a nested loops join.
NO_USE_NL
Do not u nested loop (from Oracle 10g)
USE_NL_WITH_INDEX
Specifies a nested loops join. (from Oracle 10g)
USE_MERGE
The USE_MERGE hint caus Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join.
NO_USE_MERGE
Do not u merge (from Oracle 10g)
USE_HASH
The USE_HASH hint caus Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
NO_USE_HASH
Do not u hash (from Oracle 10g)
Hints for Parallel Execution
PARALLEL 质量管理体系
The PARALLEL hint allows you to specify the desired number of concurrent query rvers that can be ud for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must u the table alias if an alias is specified in the query. The PARALLEL hint can then take two values parated by commas after the table name. The first value specifies the degree of parallelism for the given table, the cond value specifies how the table is to be split among the instances of a parallel rver. Specifying DEFAULT or no value signifies the query coordinator should examine the ttings of the initialization parameters (described in a later ction) to determine the default degree of parallelism.
NOPARALLEL / NO_PARALLEL
The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clau. In Oracle 10g this hint was renamed to NO_PARALLEL.
PQ_DISTRIBUTE
The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query rvers. Using this hint overrides decisions the optimizer would normally make.
NO_PARALLEL_INDEX
The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute tting on an index to avoid a parallel index scan operation.
Additional Hints
APPEND
When the APPEND hint is ud with the INSERT statement, data is appended to the table. Existing free space in the block is not ud. If a table or an index is specified with nologging, this hint applied with an inrt statement produces a direct path inrt which reduces generation of redo.
NOAPPEND
Overrides the append mode.
CACHE
The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently ud end of the LRU list in the buffer cache when a full table scan is performed. This option is uful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification.
NOCACHE
The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently ud end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
PUSH_PRED
The PUSH_PRED hint forces pushing of a join predicate into the view.
宁愿拼音
NO_PUSH_PRED
澄的多音字组词The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
PUSH_SUBQ
The PUSH_SUBQ hint caus nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
开科取士NO_PUSH_SUBQ
The NO_PUSH_SUBQ hint caus non-merged subqueries to be evaluated as the last step in the execution plan.
QB_NAME
Specifies a name for a query block. (from Oracle 10g)
CURSOR_SHARING_EXACT
Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint caus this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.
DRIVING_SITE
The DRIVING_SITE hint forces query execution to be done for the table at a different site than that lected by Oracle
DYNAMIC_SAMPLING
The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve rver performance by determining more accurate predicate lectivity and statistics for tables and indexes. You can t the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.
SPREAD_MIN_ANALYSIS
This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to lectively populate spreadsheet access structures and limited rule pruning are still ud. (from Oracle 10g)
Hints with unknown status
MERGE_AJ
The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ(table) where table specifies the name or alias of the table to be accesd.(depricated in Oracle 10g)
AND_EQUAL
The AND_EQUAL hint explicitly choos an execution plan that us an access path that merges the scans on veral single-column indexes. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g)
STAR
The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (depricated in Oracle 10g)
BITMAP
Usage: BITMAP(table_name index_name) Us a bitmap index to access the table. (depricated ?)
HASH_SJ
U a Hash Anti-Join to evaluate a NOT IN sub-query. U this hint in the sub-query, not in the main query. U this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refus to work.(depricated in Oracle 10g)
NL_SJ
U a Nested Loop in a sub-query. (depricated in Oracle 10g)
NL_AJ
U an anti-join in a sub-query. (depricated in Oracle 10g)
ORDERED_PREDICATES
(depricated in Oracle 10g)
EXPAND_GSET_TO_UNION
(depricated in Oracle 10g)

本文发布于:2023-06-15 00:28:46,感谢您对本站的认可!

本文链接:https://www.wtabcd.cn/fanwen/fan/82/956322.html

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。

标签:虚无   活动   意思   质量   组词   万物
相关文章
留言与评论(共有 0 条评论)
   
验证码:
推荐文章
排行榜
Copyright ©2019-2022 Comsenz Inc.Powered by © 专利检索| 网站地图