How to Disable Subquery De-correlation Using Optimizer Hints

Translated
1

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

Original topic: 如何在Optimizer Hints禁止子查询去关联

| username: ryans

[TiDB Usage Environment]
tidb v5.3.1

[Overview] Scenario + Problem Overview
I am using the following SQL in TiKV mode, where my t1 and t2 tables have tens of millions of rows, and the t3 table has only a few hundred rows.
If I run the following SQL, my execution plan performs the hash join operator twice:

select 
  * 
from 
  t1 inner join t2 
on t1.id = t2.id 
where 
  t2.dat in (select dat from t3)

If I run the following SQL, my execution plan performs the hash join operator once:

select 
  * 
from 
  t1 inner join t2 
on t1.id = t2.id 
where 
  t2.dat in (1,2,3,4)

There is no need for t3 to join with t1 and t2, which greatly affects its performance.
How can I prevent the optimizer from expanding the IN clause with t3 into a join from the perspective of Optimizer Hints?

2
| username: buddyyuan | Original post link

According to my rewritten method, please send the execution plan to have a look.

3
| username: ddhe9527 | Original post link

Try this hint

select /*+USE_TOJA(FALSE)*/
  * 
from 
  t1 inner join t2 
on t1.id = t2.id 
where 
  t2.dat in (select dat from t3)