常用HITS說明文件

sunwgneuqsoft發表於2008-01-02
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. [@more@]

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 owner = '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 owner = '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, 
w.position 
FROM winners w, owners o 
WHERE w.owner = o.owner) v 
WHERE h.horse_name = v.horse_name 
AND v.position = 1

NO_UNNEST

Prevents the merging of a subquery into the main statement body. Can only be used when UNNEST_SUBQUERY=TRUE. 

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

NOAPPEND

The opposite of APPEND; results in a conventional insert into a table. There is no guarantee that the data will be appended at the end of the table. The rows to be inserted do not bypass the buffer cache, and integrity constraints are respected. 

INSERT /*+ NOAPPEND */ * INTO y 
SELECT FROM winners; 
SELECT /*+ FULL(winners) NOCACHE(winners) 
*/ count(*) 
FROM winners

NOCACHE(table)

Instructs the optimizer to position all blocks fetched for the specified table at the leastrecently used end of the LRU list in the buffer cache when performing a full table scan. This is the normal behavior for a full table scan.

NOINDEX(table [index [index...]])

Eliminates the indexes listed from usage in the execution plan for a query. 

SELECT /*+ NOINDEX(EMP EMP_NDX1) */

If a table is specified without an index, no indexes on the table can be used. 

SELECT /*+ NOINDEX(EMP) */

NOPARALLEL(table)

Prevents Oracle from using parallelism (multiple processes) to scan the specified table. For example, assume you enable parallelism as follows: 

ALTER TABLE x PARALLEL 2;

Oracle now attempts to use two processes in parallel whenever the table needs to be scanned. The following statement uses the NOPARALLEL hint to prevent that parallelism from occurring: 

SELECT /*+ NOPARALLEL(x) */ COUNT(*) 
FROM x; 
NOPARALLEL_INDEX(table, index) 
Ensures that parallel index processing does not occur for a partitioned index. 
SELECT /*+ NOPARALLEL_INDEX(emp, emp_ndx) */ 
NOREWRITE 
Prevents Oracle from utilizing materialized views based on a selected table. It is the exact reverse of the REWRITE hint. 
SELECT /*+ NOREWRITE */ horse_name, owner, position, COUNT(*) 
FROM results 
GROUP BY horse_name, owner, position 
ORDERED 
Forces the optimizer to join tables in the same order as that in which they are specified in the FROM clause (left to right). This hint can give enormous performance gains in a reporting environment. It is also usually the case that the larger the number of tables in the FROM clause, the larger the benefits from this hint. Following is an example of its use: 
SELECT /*+ ORDERED */ 
acct_name, trans_date, amount, 
dept, address 
FROM trans t, account a, category c , 
branch b, zip z 
WHERE t.trans_date > sysdate - 30 
AND a.zip = z.zip 
AND z.state = 'WA' 
AND t.account between 700000 and 
799999 
AND t.account = a.account 
AND a.account = 'ACTIVE' 
AND a.category = c.category 
AND c.catgory = 'RETAIL' 
AND t.branch_id = b.branch_id 
AND b.branch = 'BELLEVUE' 
Usually the driving index, and thus the driving table, for a query are determined by the type of index, how many columns are in the index, the number of rows in the index, and so on. For example, a table that has a UNIQUE index column equality check in the WHERE clause will become a driving table over a table that has a NON-UNIQUE column specified in the WHERE clause.

Interestingly, if all things are equal, the cost-based optimizer will use the left to right order in the FROM clause, which is the exact reverse of the rule-based optimizer. However, in a complex query, it is rare to find all things equal in the WHERE clause. Use this hint to guarantee the join order.

ORDERED_PREDICATES

Causes WHERE clause predicates to be evaluated in the order in which they are listed in the WHERE clause. If you do not specify ORDERED_PREDICATES, Oracle will evaluate subqueries and user functions first. 

SELECT ... 
... 
WHERE /*+ ORDERED_PREDICATES */

This is the only hint that goes in the WHERE clause rather than after the keyword that begins the statement.

PARALLEL(table [,integer] [,integer])

Explicitly specifies the actual number of concurrent query servers that will be used to service the query. The first optional value specifies the degree of parallelism (number of query servers) for the table. This is the number of processes assigned to perform the scan of the specified table in parallel on a single instance. The second optional value specifies the number of Oracle parallel server instances to split the query across. If you specify PARALLEL(EMP, 4 2), there will be four parallel query processes running on two separate parallel server instances. If no parameters are specified, the default (calculated) degree of parallelism and number of parallel servers is sourced from the parameters specified in the INIT.ORA file.

The hint can be used for selects, updates, deletes, and inserts. To get performance improvements using the parallel hint, your datafiles must be striped across multiple disks. Don't set the degree of parallelism higher than the number of disks that the table is striped over. Having multiple processors will make the operation run even faster, but only if the table is striped. 

SELECT /*+ PARALLEL (x 4) */ COUNT(*) 
FROM x; 
SELECT /*+ PARALLEL (x 4 2) */ COUNT(*) 
FROM x; 
UPDATE /*+ PARALLEL (x 4) */ x 
SET position = position+1; 
DELETE /*+ parallel(x 4) */ from x; 
INSERT INTO x 
SELECT /*+ PARALLEL(winners 4) */ * 
FROM winners; 
PARALLEL_INDEX(table, index, degree of parallelism, cluster split) 
Allows you to parallelize index range scans for partitioned indexes. Also allows the work to be done across multiple instances of a parallel server architecture. The following example tells the optimizer to utilize parallel index processing on the EMP table, which is partitioned, to use the EMP_NDX index, and to run at a parallel degree of four over two Oracle parallel server instances. 
SELECT /*+ PARALLEL_INDEX(emp, emp_ndx, 4, 2) */ 
... 
PQ_DISTRIBUTE(table [Outer Distribution] [Inner Distribution]) 
Used to improve parallel join performance. There are six possibilities for distribution hints, as listed in Table 1-6. 
SELECT /*+ USE_HASH(o) 
PQ_DISTRIBUTE(o HASH, HASH) */ COUNT(*) 
FROM winners w, owners o 
WHERE w.owner = o.owner;

Table 1-6. PQ_DISTRIBUTE combinations

Distribution combination

Meaning

HASH, HASH

Uses a hash function on the join keys for each query server process. Can be used for a hash join or sort merge join. Works best when tables are approximately the same size.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8394333/viewspace-996330/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： 關於函式索引(function-based index)
下一篇： oracle中的hash函式效率比較(續)
常用HITS說明文件
請登入後發表評論 登入
全部評論
sunwgneuqsoft
  • 博文量
    56
  • 訪問量
    769902

最新文章

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備16024965號-8 北京市公安局海淀分局網監中心備案編號：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人舉報專區

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員

相關文章