Note:This information is also included in the Oracle Compatibility Developer’s Guide.
When a DELETE, SELECT, or UPDATE command is issued, the Postgres Plus Advanced Server database server goes through a process to produce the result set of the command which is the final set of rows returned by the database server. How this result set is produced is the job of the query planner, also known as the query optimizer. Depending upon the specific command, there may be one or more alternatives, called query plans; the planner may consider as possible ways to create the result set. The selection of the plan to be used to actually execute the command is dependent upon various factors including:
● Costs assigned to various operations to retrieve the data (see the Planner Cost Constants in the postgresql.conf file).
● Settings of various planner method parameters (see the Planner Method Configuration section in the postgresql.conf file).
● Column statistics that have been gathered on the table data by the ANALYZE command (see the Postgres Plus documentation set for information on the ANALYZE command and column statistics).
Generally speaking, of the various feasible plans, the query planner chooses the one of least estimated cost for actual execution.
However, it is possible in any given DELETE, SELECT, or UPDATE command to directly influence selection of all or part of the final plan by using optimizer hints. Optimizer hints are directives embedded in comment-like syntax immediately following the DELETE, SELECT, or UPDATE key words that tell the planner to utilize or not utilize a certain approach for producing the result set.
Optimizer hints may be given in two different formats as shown above. Note that in both formats, a plus sign (+) must immediately follow the /* or -- opening comment symbols with no intervening space in order for the following tokens to be interpreted as hints.
In the first format, the hint and optional comment may span multiple lines. In the second format, all hints and comments must be on a single line. The remainder of the statement must start on a new line.
The following points regarding the usage of optimizer hints should be noted:
● The database server will always try 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 will not be 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_indexscan, enable_seqscan, enable_hashjoin, enable_mergejoin, and enable_nestloop. These are all Boolean parameters.
● Remember that the hint is embedded within 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 non-existent in the SQL command, there will be no indication that any sort of error has occurred. No syntax error will be given and the entire hint is simply ignored.
● If an alias is used for a table or view name in the SQL command, then the alias name, not the original object name, must be used in the hint. For example, in the command, SELECT /*+ FULL(acct) */ * FROM accounts acct ..., acct, the alias for accounts, must be specified in the FULL hint, not the table name, accounts.
● Use the EXPLAIN command to ensure that the hint is correctly formed and the planner is using the hint. See the Postgres Plus documentation set for information on the EXPLAIN command.
● In general, optimizer hints should not be used in production applications. Typically, the table data changes throughout the life of the application. By ensuring that the more dynamic columns are ANALYZEd frequently, the column statistics will be updated to reflect value changes and the planner will use such information to produce the least cost plan for any given command execution. Use of optimizer hints defeats the purpose of this process and will result in the same plan regardless of how the table data changes.
An optimizer hint directive.
A string with additional information. Note that there are restrictions as to what characters may be included in the comment. Generally, comment may only consist 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 will be ignored if the comment is not in this form.
The remainder of the DELETE, SELECT, or UPDATE command.
The following sections describe the various optimizer hint directives in more detail.