Optimizer hints v14
When you invoke a
UPDATE command, the server generates a set of execution plans. After analyzing those execution plans, the server selects a plan that generally returns the result set in the least amount of time. The server's choice of plan depends on several factors:
- The estimated execution cost of data handling operations
- Parameter values assigned to parameters in the
Query Tuningsection of the
- Column statistics that were gathered by the ANALYZE command
As a rule, the query planner selects the least expensive plan. You can use an optimizer hint to influence the server as it selects a query plan. An optimizer hint is one or more directives embedded in a comment-like syntax that immediately follows a
UPDATE command. Keywords in the comment instruct the server to use or avoid a specific plan when producing the result set.
You can incude optimizer hints in either of these forms:
In both forms, a plus sign (+) must immediately follow the
-- opening comment symbols, with no intervening space. Otherwise the server doesn't interpret the tokens that follow as hints.
If you're using the first form, the hint and optional comment might span multiple lines. In the second form, all hints and comments must occupy a single line. The rest of the statement must start on a new line.
- The database server always tries to use the specified hints if at all possible.
- If a planner method parameter is set so as to disable a certain plan type, then this plan isn't used even if it is specified in a hint, unless there are no other possible options for the planner. Examples of planner method parameters are
enable_nestloop. These are all Boolean parameters.
- The hint is embedded in a comment. As a consequence, if the hint is misspelled or if any parameter to a hint such as view, table, or column name is misspelled or nonexistent in the SQL command, there's no indication that an error occurred. No syntax error is given. The entire hint is silently ignored.
- If an alias is used for a table name in the SQL command, then you must use the alias name in the hint, not the original table name. For example, in the command
SELECT /*+ FULL(acct) */ * FROM accounts acct ..., acct, you must specify the alias for
FULLhint, not in the table name
EXPLAIN command to ensure that the hint is correctly formed and the planner is using the hint.
In general, don't use optimizer hints in a production application, where table data changes throughout the life of the application. By ensuring that dynamic columns are analyzed frequently via the
ANALYZE command, the column statistics are updated to reflect value changes. The planner uses such information to produce the lowest-cost plan for any given command execution. Use of optimizer hints defeats the purpose of this process and results in the same plan regardless of how the table data changes.
An optimizer hint directive.
A string with additional information. Comments have restrictions as to what characters you can include. Generally,
comment can consist only of alphabetic, numeric, the underscore, dollar sign, number sign, and space characters. These must also conform to the syntax of an identifier. Any subsequent hint is ignored if the comment isn't in this form.
The remainder of the
default_optimization_modes access_method_hints specifying_a_join_order joining_relations_hints global_hints using_the_append_optimizer_hint parallelism_hints conflicting_hints