Oracle development topic: Efficiency of exists and in

There are two simple examples to illustrate the efficiency of "exists" and "in ".

1) Select * from T1 where exists (select * From T2 where t1.a = t2.a );

Small Data size of T1 and large data size of T2, t1 <t2, 1) high query efficiency.

2) Select * from T1 where t1.a in (select t2.a from T2 );

T1 has a large data volume and T2 has an hour, T1> T2, 2) High query efficiency.

Exists usage:

1) "select * From T2 where t1.a = t2.a" in a sentence is equivalent to a join Table query,

Equivalent to "select * from T1, T2 where t1.a = t2.a ";

"Exists (XXX)" only cares about whether the data in the brackets can be searched out and whether such a record exists. If so, the where condition of the sentence 1) is true.

Usage of in:

2) "select * from T1 where t1.a in (select t2.a from T2 )", the content of the field searched by the statement following the "in" must correspond to each other. Generally, the expression of field a in Table T1 and table t2 must be the same, otherwise, this query is meaningless.

---------------------------------------------------------------

++Reprinted below++

---------------------------------------------------------------

Today, the market report has an SQL statement and is slow. It takes more than 20 minutes to run it, as shown below:
Update p_container_decl CD
Set CD. annul_flag = \ '000000', annul_date = sysdate
Where exists (
Select 1
From (
Select TC. decl_no, TC. goods_no
From p_transfer_cont TC, p_affirm_do ad
Where TC. goods_decl_no = AD. decl_no
And ad. decl_no = \ 'sssssssssssssssss \'
)
Where a. decl_no = CD. decl_no
And a. goods_no = CD. goods_no
)
The number of records in the three tables involved in the preceding process is large, and the number of records is around one million. According to this situation, I thought of an article about Tom recently.ArticleThe difference between exists and in is that in performs hash join on the External table, while exists performs loop on the External table, and queries the table after each loop.
In this case, in is suitable for situations where both the internal and external tables are large, and exists is suitable for situations where the external result set is small.

However, I am suitable for using in for queries, so I have rewritten the SQL statement as follows:
Update p_container_decl CD
Set CD. annul_flag = \ '000000', annul_date = sysdate
Where (decl_no, goods_no) in
(
Select TC. decl_no, TC. goods_no
From p_transfer_cont TC, p_affirm_do ad
Where TC. goods_decl_no = AD. decl_no
And ad. decl_no = 'sssssssssssssss'
)

Let the market personnel test the results within 1 minute. After the problem is solved, it seems that exists and in must be determined based on the data volume of the table.