ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle hint

oracle hint

原创 Linux操作系统 作者:season0891 时间:2008-09-26 10:19:59 0 删除 编辑

The following is a list of all hints available in Oracle9i. Many of the hints are also available in earlier releases of Oracle. The purpose of this list is not to exhaustively describe the syntax of each hint, but to show the way each hint is most commonly used.

ALL_ROWS

Optimizes for least resource usageto return all rows required by the query. This hint will sometimes override a NESTED LOOP with a SORT MERGE or a HASH JOIN if applied to a SELECT, UPDATE, or DELETE statement when OPTIMIZER_MODE=CHOOSE.

SELECT /*+ ALL_ROWS */ ... 

 

AND_EQUAL (table index1 index2[... index5])

Explicitly merges single-column indexes. A minimum of two indexes must be specified, and no more than five are allowed. Single-column index merges can be incredibly inefficient if the first index in the WHERE returns a lot of rows.

SELECT /*+ AND_EQUAL(horse_owners ho_ndx1 
ho_ndx2 ho_ndx3) */ 
count(*) 
FROM horse_owners 
WHERE horse_name = 'WILD CHARM' 
AND wner = 'Mr M A Gurry' 
AND identifier = 14; 

 

APPEND

Allows a direct path insert to a table. Data to be inserted bypasses the buffer cache, and is appended to the end of the table. Integrity constraints are ignored during the load, although I have observed that after the load has taken place, the integrity checks are made and your statement can still fail with an integrity constraint error.

INSERT /*+ APPEND */ * INTO y 
SELECT FROM winners; 

 

CACHE (table)

Instructs the optimizer to position all blocks retrieved via a full table scan at the most recently used end of the LRU (Least Recently Used) list in the buffer cache. You would


 

usually use this hint on small tables, but I have seen sites with a very large amount of memory cache very large tables that are infrequently changed.

SELECT /*+ FULL(winners) CACHE(winners) 
*/ count(*) 
FROM winners 

 

CHOOSE

Uses the cost-based optimizer if statistics are available for at least one table; otherwise, uses the rule-based optimizer.

SELECT /*+ CHOOSE */ 

 

CLUSTER(table)

Forces the use of a cluster scan for the specified table. This hint can only be used for objects that are clustered. A cluster is two or more related tables with the parents and the related child records stored physically next to each other. For example, account1 will have its transactions stored in the same physical block as the account record.

SELECT /*+ CLUSTER(a) */ acct_name 
FROM acct a 

 

CURSOR_SHARING_EXACT

Prevents Oracle from translating literals into bind variables even when the CURSOR_SHARING parameter is set to FORCE or SIMILAR. For example:

SELECT /*+ CURSOR_SHARING_EXACT */ name, suburb 
FROM emp 
WHERE surname = 'GURRY'; 

If the hint was not used on this SQL statement, and CURSOR_SHARING was set to SIMILAR or FORCE, the `GURRY' value in this example would be translated into a bind variable.

 

FACT(table)

Tells the cost-based optimizer that the table listed is a fact table and should be treated as such. This hint is used with the STAR_TRANSFORMATION operation.

SELECT /*+ FACT(results) */ 

 

FIRST_ROWS or FIRST_ROWS(n)

Optimizes for best response time to return the first n rows required by a query. Statistics do not have to be available for any table involved in the SQL statement; their statistics can be estimated by the optimizer. Other "access path hints" can be included with the FIRST_ ROWS hint, and may override FIRST_ROWS. If you use the (n) option to specify the exact number of rows to be returned, Oracle can make a more precise execution plan decision. The (n) option is only available with Oracle9i and later.


 

For example:

SELECT /*+ FIRST_ROWS(100) */ 

This hint:

      Will always choose an index over a full table scan.

      Uses nested loop joins over sort/merge joins, where possible.

      Uses an index to satisfy an ORDER BY clause, where possible.

 

The optimizer ignores the hints for DELETE and UPDATE statement blocks, and for any SELECT statement block that contains a "grouping" operation (UNION, INTERSECT, MINUS, GROUP BY, DISTINCT, MAX, MIN, SUM, etc.) or a FOR UPDATE clause. Such statements cannot be optimized for best response time, because all rows must be accessed before the first row can be returned.

 

FULL(table)

Forces the use of a full table scan on the specified table.

SELECT /*+ FULL(emp) */ ename 
FROM emp 
WHERE commencement_date > sysdate - 7 

If a table has an alias, you must specify the alias name in the hint:

SELECT /*+ FULL(a) */ ename 
FROM emp a 
WHERE a.commencement_date > sysdate - 7 

 

HASH(table)

Forces the use of a hash table scan for the specified table. This hint applies only to tables stored in a cluster.

SELECT /*+ HASH(a) */ acct_name 
FROM acct a 

A lot of people get this hint mixed up with USE_HASH, which forces a hash join. This is not the same hint!

 

HASH_AJ

Provides substantial performance improvements by turning a nested loop operation for a NOT IN into a hash join operation. This hint needs to be placed against the SELECT statement in the subquery, not in the main select clause.

SELECT count(*) 
FROM horses 
WHERE horse_name LIKE 'M%' 
AND horse_name NOT IN 

 
( SELECT /*+ HASH_AJ */ horse_name 
FROM horse_owners 
WHERE owner LIKE '%Lombardo%'); 

 

HASH_SJ

Often speeds response times in an EXISTS subquery by returning the rows in the subquery only once.

SELECT count(*) 
FROM horses 
WHERE horse_name LIKE 'M%' 
AND EXISTS 
( SELECT /*+ HASH_SJ */ horse_name 
FROM horse_owners 
WHERE owner LIKE '%Lombardo%' 
AND horses.horse_name= horse_owners.horse_name) 

There are some restrictions on this hint:

      1. There must be only one table listed in the subquery.

      2. The hint can't be used in a subquery within a subquery.

      3. The subquery must be correlated with an equality predicate, which is a requirement for all hash joins.

      4. The subquery must have no GROUP BY clause, CONNECT BY clause, or ROWNUM reference.

 

INDEX(table [index [index...]])

Forces the use of an indexed table scan for the specified table. You can optionally specify one or more indexes in the hint. If no indexes are included, the optimizer calculates the cost of all indexes for the table, and uses the most efficient (several indexes may be used in tandem). If several indexes are listed, the optimizer calculates the cost of only those indexes that are specified, and uses the most efficient (several indexes from the list may be used in tandem if they are single-column indexes). If a single index is specified, the optimizer performs a scan using that index.

SELECT /*+ INDEX(EMP EMP_NDX1) */ 
SELECT /*+ INDEX(EMP) */ 

 

INDEX_ASC(table [index])

Forces the use of an ascending indexed table scan for the specified table. Oracle will scan indexes in ascending order by default anyway. So why use this hint? Good question! I suppose this hint guarantees that the index will be traversed in ascending order, even if


 

Oracle decides to behave differently. The exception to the rule is if the index has been created as a reverse key index, e.g., CREATE INDEX POST ON OWNERS (ZIPCODE) REVERSE.

SELECT /*+ INDEX_ASC(EMP EMP_NDX1) */...
 

INDEX_COMBINE(table [index [index...]])

Explicitly chooses bitmap indexes to access the table information.

SELECT /*+ INDEX_COMBINE(ACCT_TRAN AT_STATE_BMI AT_TYPE_BMI) */ 

 

INDEX_DESC(table [index])

Forces the use of a descending indexed table scan for the specified table. By default, Oracle scans indexes in ascending sequence. This hint guarantees that the index will be traversed in descending order. A typical usage of this hint would be to retrieve the latest transactions on your bank account in descending order by date. This hint can be of great value in distributed queries.

SELECT /*+ INDEX_DESC(ACCT_TRANS ACCT_TRANS_DATE_NDX) */... 

 

INDEX_FFS(table [index])

Instructs the optimizer to do a full scan of an index rather than a full scan of a table. The index scan can sometimes run faster, but if and only if every column in the WHERE clause for the specified table exists in the index.

SELECT /*+ INDEX_FFS(ACCT_TRAN AT_STATE_NDX1) */ 

 

INDEX_JOIN(table [index] table [index2)]

This hint tells the optimizer to join two indexes as the access path. Typically the execution plan will include a hash join of the two indexes, which can return some performance improvements. In the following example, two of the table's three primary key columns have been used in the WHERE clause (HORSE_NAME and OWNER), as has the leading column (IDENTIFIER) of a non-primary key index.

SELECT /*+ INDEX_JOIN(HORSE_OWNERS HO_NDX2 HO_PK) */ 
Horse_name, owner 
FROM HORSE_OWNERS 
WHERE horse_name = 'WILD CHARM' 
AND wner = 'Mr M A Gurry' 
AND identifier = 10; 

As a matter of interest, without the INDEX_JOIN hint, the optimizers will usually only join the single-column indexes.

 

MERGE(table)


 

Used to force the merging of a nested (inline) view with the main driving query. In the example given, the GROUP BY inline view is merged with the selection from the OWNERS table.

The hint can also be used for subqueries if the IN statement is uncorrelated; that is, it does not reference join columns in the main query.

SELECT /*+ MERGE(w) */ o.owner, 
w.num_wins, o.suburb 
FROM owners o, 
(SELECT owner, count(*) num_wins 
FROM winners 
WHERE position = 1 
GROUP BY owner) w 
WHERE o.owner = w.owner 
AND w.num_wins > 15 
ORDER BY w.num_wins desc 

 

MERGE_AJ

Provides substantial performance improvements by turning a nested loop operation for a NOT IN into a merge join operation (similar to HASH_AJ). This hint needs to be placed against the SELECT statement in the subquery, not in the main select clause.

SELECT count(*) 
FROM horses 
WHERE horse_name LIKE 'M%' 
AND horse_name NOT IN 
( SELECT /*+ MERGE_AJ */ horse_name 
FROM horse_owners 
WHERE owner LIKE '%Lombardo%'); 

 

MERGE_SJ

This hint will often speed response times in an EXISTS subquery by returning the rows in the subquery only once.

SELECT count(*) 
FROM horses 
WHERE horse_name LIKE 'M%' 
AND EXISTS 
( SELECT /*+ MERGE_SJ */ horse_name 
FROM horse_owners 
WHERE owner LIKE '%Lombardo%' 

 
AND horses.horse_name= horse_owners.horse_name) 

There are some restrictions on this hint:

      1. There must be only one table in the subquery.

      2. The subquery can't be a subquery within a subquery.

      3. The subquery must be correlated with an equality predicate.

      4. The subquery must have no GROUP BY clause, CONNECT BY clause, or ROWNUM reference.

 

NL_AJ

Occasionally provides some performance improvements by forcing a nested loop operation for a NOT IN. However, nested loop performance is often inferior to that of the hash join and the sort merge join. The hint needs to be placed against the SELECT statement in the subquery, not in the main select clause.

SELECT count(*) 
FROM horses 
WHERE horse_name LIKE 'M%' 
AND horse_name NOT IN 
( SELECT /*+ NL_AJ */ horse_name 
FROM horse_owners 
WHERE owner LIKE '%Lombardo%'); 

 

NL_SJ

This hint is similar to the HASH_SJ and MERGE_SJ hints, but uses the nested loop operation for the semi join.

SELECT count(*) 
FROM horses 
WHERE horse_name LIKE 'M%' 
AND EXISTS 
( SELECT /*+ NL_SJ */ horse_name 
FROM horse_owners 
WHERE owner LIKE '%Lombardo%' 
AND horses.horse_name= 
horse_owners.horse_name) 

There are some restrictions on this hint:

      1. There must be only one table in the subquery.

      2. It can't be a subquery within a subquery.


 

      3. The subquery must be correlated with an equality predicate.

      4. The subquery must have no GROUP BY, CONNECT BY, or ROWNUM reference.

 

NO_EXPAND

Prevents a query from being broken up into separate pieces, which is almost the reverse of the USE_CONCAT hint.

SELECT /*+ NO_EXPAND */ COUNT(*) 
FROM horse_owners 
WHERE identifier < 10 OR identifier > 20
 

NO_FACT(table)

Tells the cost-based optimizer that the table listed is not a fact table and should not be treated as such. This hint is used with STAR_TRANSFORMATION processing.

SELECT /*+ NO_FACT(results) */ 

 

NO_MERGE(table)

Prevents the merging of a nested (inline) view.

SELECT /*+ NO_MERGE(w) */ o.owner, 
w.num_wins, o.suburb 
FROM owners o, 
(SELECT owner, count(*) num_wins 
FROM winners 
WHERE position = 1 
GROUP BY owner) w 
WHERE o.owner = w.owner 
AND w.num_wins > 15 
ORDER BY w.num_wins desc 

 

NO_PUSH_PRED(table)

Prevents the join predicate from being pushed into an inline view.

SELECT /*+ NO_PUSH_PRED(v) */ count(*) 
FROM horses h, 
(SELECT w.horse_name, o.owner, 

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

下一篇: oracle hint (续)
请登录后发表评论 登录
全部评论