Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: leading hint指定多表关联顺序不生效
Why does specifying the join order with leading not take effect, while forcing the use of an index works? The warnings don’t indicate where the problem is. However, if the join order of the tables is changed, the join order does change, indicating that it should be adjustable. But I don’t understand why leading doesn’t take effect. Is there a restriction on outer joins?
Have you tried using the index without forcing it?
Forcing the use of an index is just for testing to ensure it’s not an issue with the environment that prevents using hints. Without hints, the index definitely won’t be used here. These are actually empty tables.
The LEADING hint will become invalid in the following situations:
- Multiple
LEADING hints are specified
- The table name specified in the
LEADING hint does not exist
- Duplicate table names are specified in the
LEADING hint
- The optimizer cannot join tables in the order specified by the
LEADING hint
- A
straight_join() hint already exists
- The query contains an outer join
- It is used simultaneously with hints for selecting join algorithms (i.e.,
MERGE_JOIN, INL_JOIN, INL_HASH_JOIN, HASH_JOIN)
When any of the above invalid situations occur, a warning will be output.
The optimizer might consider other orders to be more optimal, so it chose a different join order.
When using outer joins, the join order depends on the order of your outer joins, and leading cannot specify it.
I’m using version 6.5.1. The documentation states that “the query will only fail if it contains an outer join and also specifies a Cartesian product.” Could it be that this issue hasn’t actually been fixed?
My version is 6.5.1. The documentation for versions above 6.5 states that it will only be invalid if “the query statement contains an outer join and simultaneously specifies a Cartesian product.”
I tested it in version 6.6, and it still doesn’t work.
Shouldn’t a left join be associated in the order it is written?
I tested the leading function in version 7.1 and it works.
The image is not available for translation. Please provide the text content directly.
Thank you, it was indeed this parameter causing the issue. It was upgraded from 6.1, and it was originally set to off. Now, changing it to on works.
However, this is an ideal situation in the test environment. In actual production, when there are many associated tables, it still doesn’t work. In the end, I still have to arrange them in the desired order and use STRAIGHT_JOIN() to fix the order.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: yeminhua
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: