Oracle 10g : in vs exists

in vs exists

The 10000th article on when to use "in" ? when to use "exists" ?

There is a huge difference of using in/exists on Oracle 8i and Oracle 10g.

Oracle 8i

/*
The two are processed very very differently.

IN :

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table.

EXISTS :
;
select * from t1 where exists ( select 1 from t2 where y = x )

That is processed more like:

for x in ( select * from t1 )
loop
if ( exists ( select 1 from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index
on T1(x).
*/
create table big as select * from all_objects where rownum <= 15000; insert /*+ append */ into big select * from big; insert /*+ append */ into big select * from big; insert /*+ append */ into big select * from big; commit; create index big_idx on big(object_id); create table small as select * from all_objects where rownum < ownname =""> 'idcscd',tabname => 'big',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);
exec dbms_stats.gather_table_stats(ownname => 'idcscd',tabname => 'small',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

Case 1 :

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.281 secs
-- IFS or IFFS, it does not need to touch the table - index is sufficient.

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id ) -- 4.066 secs
-- IRS

Case 2 :

--Let's drop the index on small table and see what happens.

drop index small_idx;

--Run the queries again, verify the query execution times and plans.

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.29 secs
-- Full table Access

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id )
-- Full table Access

--That shows if the outer query is "big" and the inner query is "small", in is generally more efficient than EXISTS

Case 3 :

-- Re create the dropped index.
create index small_idx on small(object_id);
--Let's do a look up into the big table for small table.
select count(subobject_name)
from small
where object_id in ( select object_id from big ) -- 0.661 secs
-- IFFS for the big table and Full table access for the small table


select count(subobject_name)
from small
where exists ( select null from big where small.object_id = big.object_id ) -- 0.02 secs
-- IRS for the big table and Full table access for the small.

-- shows that if the outer query is "small" and the inner query is "big" EXISTS can be quite efficient.

-- drop the tables
drop table big;
drop table small;

Oracle 10g

Try the following on Oracle 10g. You will see 10g is smart and rewrites the sql automatically, irrespective of the size of the data you have.

/*
The two are processed very very differently.

IN :

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table.

EXISTS :
;
select * from t1 where exists ( select 1 from t2 where y = x )

That is processed more like:

for x in ( select * from t1 )
loop
if ( exists ( select 1 from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index
on T1(x).
*/
drop table big;
create table big as select * from all_objects where rownum <= 15000; insert /*+ append */ into big select * from big; commit; insert /*+ append */ into big select * from big; commit; insert /*+ append */ into big select * from big; commit; create index big_idx on big(object_id); drop table small; create table small as select * from all_objects where rownum < ownname =""> 'idcscd',tabname => 'big',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);
exec dbms_stats.gather_table_stats(ownname => 'idcscd',tabname => 'small',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

Case 1 :

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.281 secs on 8i , 0.09 secs on 10g
-- IFS or IFFS, it does not need to touch the table - index is sufficient.

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id ) -- 4.066 secs on 8i, 0.1 secs on 10g
-- IRS on 8i and IFS on 10g.

Case 2 :

--Let's drop the index on small table and see what happens.

drop index small_idx;

--Run the queries again, verify the query execution times and plans.

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.29 secs on 8i, 0.11 secs on 10g
-- Full table Access

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id ) -- 0.1 secs on 10g
-- Full table Access

--That shows if the outer query is "big" and the inner query is "small", in is generally more efficient than EXISTS

Case 3 :

-- Re create the dropped index.
create index small_idx on small(object_id);
--Let's do a look up into the big table for small table.
select count(subobject_name)
from small
where object_id in ( select object_id from big ) -- 0.661 secs on 8i, 0.03 secs on 10g
-- IFFS for the big table and Full table access for the small table


select count(subobject_name)
from small
where exists ( select null from big where small.object_id = big.object_id ) -- 0.02 secs on 8i, 0.03 secs on 10g
-- IRS for the big table and Full table access for the small on 8i and IFFS on 10g

-- shows that if the outer query is "small" and the inner query is "big" EXISTS can be quite efficient.

-- drop the tables
drop table big;
drop table small;

Refer : http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10752/sql_1016.htm#30972

The examples are taken from the guru's web site : asktom.oracle.com

Long live "Tom Kyte".

Good Luck !!

r-a-v-i