Join Operation Hints
Description
These hints specify the join method, which can be nested loop join, hash join, or merge join.
Syntax
1
|
[no] nestloop|hashjoin|mergejoin([@queryblock] table_list) |
Parameters
- For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block.
- no indicates that the specified hint will not be used for a join.
- table_list specifies the tables to be joined. The values are the same as those of join_table_list but contain no parentheses.
For example:
no nestloop(t1 t2 t3): nestloop is not used for joining t1, t2, and t3. The three tables may be joined in either of the two ways: Join t2 and t3, and then t1; join t1 and t2, and then t3. This hint takes effect only for the last join. If necessary, you can hint other joins. For example, you can add no nestloop(t2 t3) to join t2 and t3 first and to forbid the use of nestloop.
Example
Hint the query plan in Example as follows:
1 2 |
explain select /*+ nestloop(store_sales store_returns item) */ i_product_name product_name ... |
nestloop is used for the last join between store_sales, store_returns, and item. The optimized plan is as follows:
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
