Optimizer Hints！

doc上的这段话对Optimizer Hints介绍的非常清楚，记录一下!

当然更重要的是能够熟练的使用各种hint来改变sql的执行计划从而优化sql

这里对下面一段做一点解释：

You can use hints to specify the following:

• The optimization approach for a SQL statement
• --The optimization approach 指的是cbo和rbo
• The goal of the cost-based optimizer for a SQL statement
• --The goal of the cost-based optimizer 指的是语句追求的是最大吞吐量( all_rows)还是最快响应时间(first_rows(n))
• The access path for a table accessed by the statement
• --The access path for a table accessed是指full table scan or index scan
• The join order for a join statement
• --是指表的连接顺序
• A join operation in a join statement
• --A join operation 指的应该是表与表之间的连接方法：nl，merge or hash

[@more@]

Optimizer Hints

Optimizer hints can be used with SQL statements to alter execution plans. This chapter explains how to use hints to force various approaches.

The chapter contains the following sections:

• Understanding Optimizer Hints
• Using Optimizer Hints

Understanding Optimizer Hints

Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:

• The optimization approach for a SQL statement
• The goal of the cost-based optimizer for a SQL statement
• The access path for a table accessed by the statement
• The join order for a join statement
• A join operation in a join statement

  Note: The use of hints involves extra code that must be managed, checked, and controlled.

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:

• Join order
• Join method
• Access path
• Parallelization

Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.

See Also: Chapter 3, "Gathering Optimizer Statistics" for more information on default values

Specifying Hints

Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:

• A simple SELECT, UPDATE, or DELETE statement.
• A parent statement or subquery of a complex statement.
• A part of a compound query.

For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement.

See Also: Oracle9i SQL Reference for more information on comments

A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.

Exception: The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

• DELETE, INSERT, SELECT, and UPDATE are keywords that begin a statement block. Comments containing hints can appear only after these keywords.
• + causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter; no space is permitted.
• hint is one of the hints discussed in this section. If the comment contains multiple hints, then each hint must be separated from the others by at least one space.
• text is other commenting text that can be interspersed with the hints.

If you specify hints incorrectly, then Oracle ignores them but does not return an error:

• Oracle ignores hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
• Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
• Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
• Oracle ignores hints in all SQL statements in those environments that use PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.

The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.

See Also: "Using Optimizer Hints" for the syntax of each hint "INDEX" and following sections, for conditions specific to index type

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

转载于:http://blog.itpub.net/19602/viewspace-1003107/