Configuration Parameter Hints
Function
A hint, or a GUC hint, specifies the value of a configuration parameter when a plan is generated.
Precautions
- If the configuration parameter set by a hint takes effect at the statement level, the hint must be written in the top-level query instead of the subquery. For UNION, INTERSECT, EXCEPT, and MINUS statements, statement-level GUC hints can be written on any SELECT clause that participates in set calculation, the configuration parameters set by this GUC hint take effect for each SELECT clause involved in set calculation.
- When a subquery is lifted, all GUC hints on that subquery are discarded.
- If a configuration parameter is set by both a statement-level GUC hint and a subquery-level GUC hint, the subquery-level GUC hint takes effect in the corresponding subquery, statement-level GUC hints take effect in other subqueries of the statement.
Syntax
set [global]([@block_name] guc_name guc_value)
Parameter Description
- global indicates that the parameter set by a hint takes effect at the statement level. If global is not specified, the parameter takes effect only in the subquery where the hint is.
- block_name indicates the block name of the statement block. For details, see block_name.
- guc_name indicates the name of the configuration parameter specified by a hint.
- guc_value indicates the value of a configuration parameter specified by a hint.
Currently, GUC hints support only certain configuration parameters, and some configuration parameters cannot be set at the subquery level but only at the statement level. Below is the list of supported parameters.
Parameter |
Configurable at Subquery Level |
---|---|
agg_redistribute_enhancement |
Yes |
best_agg_plan |
Yes |
cost_model_version |
No |
cost_param |
No |
enable_broadcast |
Yes |
enable_redistribute |
Yes |
enable_extrapolation_stats |
Yes |
enable_hashagg |
Yes |
enable_hashjoin |
Yes |
enable_nestloop |
Yes |
from_collapse_limit |
Yes |
join_collapse_limit |
Yes |
join_num_distinct |
Yes |
qual_num_distinct |
Yes |
query_dop |
No |
rewrite_rule |
No |
skew_option |
Yes |
Examples
Hint the query plan in Examples as follows:
explain select /*+ set global(query_dop 0) */ i_product_name product_name ...
This hint indicates that the query_dop parameter is set to 0 during the plan generation of the entire statement, meaning SMP adaptation is enabled.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot