Using Optimizer Hints

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#i8327

Optimizer hints can be used with SQL statements to alter execution plans.

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. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.
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 instruct the optimizer to use the optimal execution plan.

Types of Hints

Hints can be of the following general types:

·         Single-table

Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints.

·         Multi-table

Multi-table hints are like single-table hints, except that the hint can specify one or more tables or views. LEADING is an example of a multi-table hint. Note that USE_NL(table1 table2) is not considered a multi-table hint because it is actually a shortcut for USE_NL(table1) and USE_NL(table2).

·         Query block

Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.

·         Statement

Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint.

Hints by Category

Optimizer hints are grouped into the following categories:

·         Hints for Optimization Approaches and Goals

·         Hints for Access Paths

·         Hints for Query Transformations

·         Hints for Join Orders

·         Hints for Join Operations

·         Hints for Parallel Execution

·         Additional Hints

Specifying Hints : (examples in below link)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDCCFCC

Using Hints with Views

Oracle does not encourage the use of hints inside or on views (or subqueries). This is because you can define views in one context and use them in another. Also, such hints can result in unexpected execution plans.