which situation EXIST condition is better than IN, and vice versa.
ANSWER:
Result of the subquery is small Then "IN" is typicaly more appropriate.
and
Result of the subquery is big/large/long Then "EXIST" is more appropriate.
suppose :-
select * from table_1
where id in (select Id from table_2)
Is normaly processed as:
select * from table_1 , ( select distinct y from table_2 ) t2
where table_1.x = table_2.y;
Means the subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table
and oposite is (EXIST)
select * from table_1 where exists ( select null from table_2 where table_2.y = table_1.x )
That is processed more like:
for x in ( select * from table_1 ) loop
if ( exists ( select null from table_2 where table_2.y = x.x ) then
OUTPUT THE RECORD end
end if
end loop
It always results in a full scan of Table_1 whereas the first query can make use of an index on Table_1(x).
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment