Oracle Hints to Override the Optimizer


Directing Cost Based Optimisation with Hints

Hints can be used to force the creation of a query execution plan in terms of over-riding the default query plan. This option should only be used as a last resort. Queries retrieving less than 10% of rows will generally use an index if one exists. With small static tables it may be better to avoid index use by suppressing index use and forcing a full table-scan using a hint. Not collecting statistics and not adding hints will force use of rule-based optimisation. Keep statistics up-to-date. Hints apply to INSERT, UPDATE, DELETE and SELECT statements, ie. all DML statements. Also all hints apply to the cost-based approach except for the RULE hint which applies to the rule-based approach.

Note that incorrectly or inappropriately specified or coded hints do not produce errors but are simply ignored. Be sure that hints are syntactically correct. Also when using aliases for table objects in SQL statements then the alias must be used otherwise the hint will be ignored.

Hints are local to the SQL statements into which they are placed. Thus a hint does not apply to a subquery unless present in the subquery aswell. Therefore a subquery has a separate hint even if that hint is the same. A hint is effectively a comment and is denoted as being between the /*+ and */ character sequences. Effective hints for use with SQL are FULL, INDEX, ORDERED, ALL_ROWS, FIRST_ROWS and USE_NL.

Optimisation Hints

Data Access

Join Orders