Efficiency of exists and in Oracle

Syntax Comparison Between in and exist:
Select × from data table t where t. x in (...)
T. x field type value set, such as ('1', '2', '3'), but if t. when x is of the number type, it seems that such a write method will have a problem. It can also be a set of values queried through another select statement, for example, (select y from data table 2 where ...).

Select * from data table t where [...] and exist (...)
Other query conditions are in square brackets. Exist can be any condition in the brackets. This condition can be irrelevant to the query outside, or can be combined with the condition outside. For example: (select * from data table 2 where 1 = 1) or (select * from data table 2 where y = t. x)

Example:

SQL statement of in
SELECT id, category_id, htmlfile, title, convert (varchar (20), begintime, 112) as pubtime
FROM tab_oa_pub WHERE is_check = 1 and
Category_id in (select id from tab_oa_pub_cate where no = '1 ')
Order by begintime desc

Exists SQL statement
SELECT id, category_id, htmlfile, title, convert (varchar (20), begintime, 112) as pubtime
FROM tab_oa_pub WHERE is_check = 1 and
Exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id = convert (int, no) and no = '1 ')
Order by begintime desc

Efficiency Comparison:

First, we will discuss IN and EXISTS.
Select * from t1 where x in (select y from t2)
In fact, it can be understood:
Select * from t1, (select distinct y from t2) t2 where t1.x = t2.y
If you have some experience in SQL optimization, You can naturally think that t2 cannot be a big table, because it is necessary to perform "unique sorting" on the entire table of t2 ", if t2 is large, the sorting performance is intolerable. But T1. why? The most common understanding is that t1.x = t2.y can be indexed. But this is not a good explanation. Imagine if t1.x and t2.y both have indexes, we know that indexes are ordered. Therefore, the best solution between t1 and t2 is merge join. In addition, if t2.y has an index, the sorting performance of T2.

Select * from t1 where exists (select null from t2 where y = x)
It can be understood:
For x in (select * from t1)
Loop
If (exists (select null from t2 where y = x. x) then
Output the record!
End if
End loop
This is easier to understand. t1 is always a table scan! Therefore, t1 cannot be a large table, while t2 can be large, because y = x. x can be indexed by t2.y.

Based on the above discussions on IN/EXISTS, we can draw a general conclusion: IN is suitable for situations where the external table is large but the internal table is small; EXISTS is suitable for situations where the external table is small but the internal table is large.

 

This article is from "nesta2001zhang"