SQL Hint Not Effective

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql hint 不生效

| username: Raymond

I would like to ask the teachers why the SQL hint specifying index hash join is not effective. It’s very strange.


there are no matching table names for (s2) in optimizer hint /*+ INL_HASH_JOIN(s1, s2) */. Maybe you can use the table alias name
s2 cannot be matched, but s2 actually exists.

The reproduction steps are as follows:

create table s1(id int not null auto_increment primary key,a_date varchar(8),a_type varchar(8));
alter table s1 add index date_index(a_date);
create table s2(a_type varchar(8) primary key,a_flag varchar(1),a_s varchar(1));

insert into s1(a_date,a_type)values('20230421','1000');
insert into s1(a_date,a_type)values('20230422','1001');
insert into s1(a_date,a_type)values('20230421','1002');
insert into s1(a_date,a_type)values('20230422','1003');
insert into s1(a_date,a_type)values('20230421','1004');
insert into s1(a_date,a_type)values('20230420','1004');

insert into s2 values('1000','1','1');
insert into s2 values('1001','1','2');
insert into s2 values('1002','1','2');
insert into s2 values('1004','1','2');

select /*+ INL_HASH_JOIN(s1, s2) */ * from s1 where a_date='20230421' and a_type in (select a_type from s2 where a_flag='1');
| username: 裤衩儿飞上天 | Original post link

Subquery

| username: Raymond | Original post link

Is it related to this?

| username: Hacker_ufuLjDKs | Original post link

I haven’t tested it myself, but it seems like a problem with the hint’s query block reference description.
Try changing it to the following:
select /*+ INL_HASH_JOIN(@sel_1 s1, s3) */ * from s1 where a_date=‘20230421’ and a_type in (select a_type from s2 where a_flag=‘1’) s3;

| username: BraveChen | Original post link

Without further ado, both index join and index hash join rely on the index of the join fields.

| username: 我是咖啡哥 | Original post link

The warning already indicates: | Warning | 1815 | Optimizer Hint /*+ INL_HASH_JOIN(s1, s2) */ is inapplicable.

| username: tidb菜鸟一只 | Original post link

Just add an index, there is no index on the join field on s1.

| username: Raymond | Original post link

Master, I created an index for a_date and a_type in s1, and it worked fine. But can you please explain why? Also, why is there still a warning?

| username: tidb菜鸟一只 | Original post link

Because you used a subquery, if you write it like this
EXPLAIN ANALYZE SELECT /*+ INL_HASH_JOIN(s1, s2) */ * FROM s1 INNER JOIN s2
ON s1.a_type=s2.a_type
WHERE a_date=‘20230421’ AND s2.a_flag=‘1’;
there will be no problem.

| username: Hacker_ufuLjDKs | Original post link

Eliminate the warning:
Either rewrite the subquery as a join,
or use the correct query block hint (such as sel_1, sel_2 + table alias).

| username: Running | Original post link

Try switching to a different client to connect. Previously, I encountered a similar issue where the client removed the comments from the executed SQL.

| username: Raymond | Original post link

I don’t understand why adding an index would solve the problem.

| username: tidb菜鸟一只 | Original post link

You originally had 2 warnings: 1 was because of the subquery, unable to get the table name s2 (here it is possible that TiDB has already resolved the hint exception of not being able to get the summary table name of the subquery, but the warning information has not been cleared). 2 was because there was no index on the join field, so it couldn’t use INL_HASH_JOIN.

| username: Raymond | Original post link

Thank you for the teacher’s reply,
I already knew the first point.
For the second point, the connection field has no index. Isn’t the a_type field on my s2 table a primary key index? So my understanding is that when the optimizer uses index hash join, it treats the s1 table as the inner table, which means that s1.a_type must have an index. Without it, index hash join cannot be used.
But when I use INL_HASH_JOIN(s2, s1), the optimizer still treats s1 as the inner table for the join. So is there any way to forcibly specify the join order?

| username: Hacker_ufuLjDKs | Original post link

Purely speaking of methods, TiDB has ways to specify join order similar to Oracle:
Method 1: leading(t1, t2, t3) - Joins are performed in the order specified in the leading list.
Method 2: straight_join() - Joins are performed in the order in which table names appear after the FROM clause.
However, there are quite a few prerequisites and limitations for using these hints, which I haven’t looked into in detail yet.
For example, the manual points out situations where leading won’t take effect and will issue a warning if the restrictions are violated:

  • Multiple leading hints are not allowed,
  • Cannot appear simultaneously with straight_join(),
  • Cannot appear simultaneously with any other xxx_join hints,
  • Leading specifies duplicate or non-existent table names,
  • The optimizer cannot join tables in the order specified by leading.