Tejaswini Mohanty

• Sep 12th, 2006

 

HINTS  are nothing but the comments used in a SQL statement to pass instructions to the Oracle optimizer.The optimizer uses these hints as suggestions for choosing an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword..

Hints can be categorized as follows:

• Hints for Optimization Approaches and Goals (eg: ALL_ROWS)
• Hints for Access Paths,
• Hints for Query Transformations (eg:NO_EXPAND ) 
• Hints for Join Orders,
• Hints for Join Operations,
• Hints for Parallel Execution,
• Additional Hints (eg:NOCACHE ,APPEND)

giridhar

• Nov 3rd, 2006

 

Generally oracle uses Cost_based optimisation. In this method it will calculate all the execution plans for the query to execute and chooses the best optimal plan. Sometimes we may know the best execution plan to you rather which is selected by optimisation technique. We use HINTS to force the optimiser to use the optimal execution plan