Exists and in Oracle

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. In
The where clause can use subqueries in two formats.
The first format is to use the in OPERATOR:
... Where column in (select * from... where ...);
The second format is to use the exist OPERATOR:
... Where exists (select 'x' from... where ...);
 
I believe that most people will use the first format because it is easier to write. In fact, the second format is far
A format is highly efficient. In Oracle, almost all in operator subqueries can be rewritten to subqueries using exists.
.

In the second format, the subquery starts with 'select. Use the exists clause to query the number of items extracted from the table without a pipe
View only the WHERE clause. In this way, the optimizer does not have to traverse the entire table, but can only complete the work based on the index (Here we assume that
The column used in the where statement has an index ). Compared with the in clause, exists uses connected subqueries to construct
It is more difficult than the in subquery.

By using exist, the Oracle system first checks the master query, and then runs the subquery until it finds the first match.
This saves time. When executing an in subquery, the Oracle system first executes the subquery and lists the obtained results.
Stored in a temporary table with an index. Before executing a subquery, the system suspends the primary query.
The primary query is executed after it is stored in the temporary table. This means that exists is faster than in queries.
Because.

At the same time, do not exists should be used as much as possible to replace not in, although both use not (the index cannot be used ).
), Not exists is more efficient than not in query.

However, the following exceptions must be noted:

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.
============================================
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, which is about the difference between exists and in,
In performs hash join on the External table, while exists performs loop on the External table. Each loop then queries the table.
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'
)
The result runs 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.