hints

Description

Introduces and groups items in a partial abstract plan.

Syntax

( hints ( derived_table ) 
     ...
)

Parameters

derived_table: is one or more expressions that generate an abstract plan derived table.

Returns

An abstract plan derived table.

Examples

Example 1

select * 
from t1, t2
where c12 = c21
        and c11 > 0
        and c22 < 1000

( hints
    ( g_join 
        ( t_scan t2 )
        ( i_scan () t1 )
    )
)

Specifies a partial plan, including a table scan on t2, the use of some index on t1, and the join order t1-t2. The index choice for t1 and the type of join (nested-loop or sort-merge) is left to the optimizer.

Usage

The specified hints are used during query optimization.
The hints operator appears as the root of a partial abstract plan that includes multiple steps. If a partial plan contains only one expression, hints is optional.
The hints operator does not appear in plans generated by the optimizer; these are always full plans.
Hints can be associated with queries:
- By changing the plan for an existing query with sp_set_qplan.
- By specifying the plan for a query with the plan clause. To save the query and hints, set plan dump must be enabled.
- By using the create plan command.
When hints are specified in the plan clause for a SQL statement, the plans are checked to be sure they are valid. When hints are specified using sp_set_qplan, plans are not checked before being saved.