OraFAQ Forum: Performance Tuning » NOT IN Vs. NOT EXISTS

Home » RDBMS Server » Performance Tuning » NOT IN Vs. NOT EXISTS (Oracle 9i/ 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NOT IN Vs. NOT EXISTS [message #338974]

Wed, 06 August 2008 06:26

Olivia
Messages: 519
Registered: June 2008

Senior Member

I have found the same explain plan using NOT IN and NOT EXISTS.
Want to know which one is feasible?Do we need to avoid NOT IN? why?

Thanks in advance,
Oli



SET AUTOTRACE TRACEONLY

select i.item_cd from item i where i.item_cd  NOT IN (SELECT  d.item_cd
                                        FROM item_activity d
                                        WHERE i.item_cd = d.item_cd
                                        and d.status_cd = 'CL'
                                        );

112479 rows selected.



Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE (Cost=2770806 Card=691 K Bytes=4 M)
   1    0    FILTER
   2    1      INDEX FAST FULL SCAN DB0DAFX1.XPKITEM (Cost=3326 Card=691 K Bytes=4 M)
   3    1      INDEX RANGE SCAN DB0DAFX1.Xitem_cd_TMSCD (Cost=4 Card=1 Bytes=10)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     397230  consistent gets
          0  physical reads
          0  redo size
          0  PX remote messages sent
          0  PX remote messages recv'd
          0  buffer is pinned count
          0  workarea memory allocated
          4  workarea executions - optimal
     112479  rows processed
	 
	 
112479 rows selected.


SET AUTOTRACE TRACEONLY	 
	 select i.item_cd from item i where   NOT EXISTS (SELECT  'X'
                                        FROM item_activity d
                                        WHERE i.item_cd = d.item_cd
                                        and d.status_cd = 'CL'
                                        );
	 
	 
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE (Cost=2770806 Card=691 K Bytes=4 M)
   1    0    FILTER
   2    1      INDEX FAST FULL SCAN DB0DAFX1.XPKITEM (Cost=3326 Card=691 K Bytes=4 M)
   3    1      INDEX RANGE SCAN DB0DAFX1.Xitem_cd_TMSCD (Cost=4 Card=1 Bytes=10)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     397230  consistent gets
          0  physical reads
          0  redo size
          0  PX remote messages sent
          0  PX remote messages recv'd
          0  buffer is pinned count
          0  workarea memory allocated
          4  workarea executions - optimal
     112479  rows processed
	 
	 
112479 rows selected.

[Updated on: Wed, 06 August 2008 06:27]

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #338985 is a reply to message #338974]

Wed, 06 August 2008 06:56

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oracle is smart to convert from one to the other one that is more efficient if it is equivalent.
So it is normal you get the same plan.

Regards
Michel

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #338988 is a reply to message #338985]

Wed, 06 August 2008 07:00

Olivia
Messages: 519
Registered: June 2008

Senior Member

Michel Cadot wrote on Wed, 06 August 2008 06:56

Oracle is smart to convert from one to the other one that is more efficient if it is equivalent.
So it is normal you get the same plan.

Regards
Michel

That means if the query is using NOT IN and NOT EXISTS also gives
the same performance then there is no need to replace NOT IN by NOT EXISTS?

Regards,
Oli

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #338990 is a reply to message #338988]

Wed, 06 August 2008 07:04

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I mean you don't have to care to use NOT IN or NOT EXISTS (when they are equivalent) as Oracle will rewrite it in the most efficient way.

Regards
Michel

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #339013 is a reply to message #338990]

Wed, 06 August 2008 08:48

Olivia
Messages: 519
Registered: June 2008

Senior Member

Thanks for the update Michel Smile

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #339091 is a reply to message #338974]

Wed, 06 August 2008 13:51

coleing
Messages: 213
Registered: February 2008

Senior Member

Also, an outer join is also a good option to not in and not exists. (it can be quicker as well converting FILTER conditions to hash joins). Oracle will sometimes convert "not in" to hash joins automatically, but an outer join would halp oracle make an easier decision.

[Updated on: Wed, 06 August 2008 13:53]

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #342758 is a reply to message #338974]

Sun, 24 August 2008 21:51

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Yes but remember, as Michel points out

NOT IN and NOT EXISTS are not the same thing in the more recent versions of Oracle. Consider this:

SQL> drop table t1
  2  /

Table dropped.

SQL> drop table t2
  2  /

Table dropped.

SQL> 
SQL> create table t1 (key number)
  2  /

Table created.

SQL> create table t2 (key number)
  2  /

Table created.

SQL> 
SQL> insert into t1 values (1)
  2  /

1 row created.

SQL> insert into t1 values (2)
  2  /

1 row created.

SQL> insert into t2 values (1)
  2  /

1 row created.

SQL> commit
  2  
SQL> --
SQL> -- variations of existential exists
SQL> --
SQL> select *
  2  from t1
  3  where exists (
  4                 select null
  5                 from t2
  6                 where t2.key = t1.key)
  7  /

       KEY
----------
         1

1 row selected.

SQL> 
SQL> select *
  2  from t1
  3  where key in (
  4                 select key
  5                 from t2
  6               )
  7  /

       KEY
----------
         1

1 row selected.

SQL> 
SQL> select t1.*
  2  from t1
  3      ,t2
  4  where t1.key = t2.key
  5  /

       KEY
----------
         1

1 row selected.

SQL> 
SQL> --
SQL> -- variations of existential not exists
SQL> --
SQL> select *
  2  from t1
  3  where not exists (
  4                 select null
  5                 from t2
  6                 where t2.key = t1.key)
  7  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6               )
  7  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> select t1.*
  2  from t1
  3      ,t2
  4  where t1.key = t2.key(+)
  5  and t2.key is null
  6  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> --
SQL> -- trouble awaits, not intuitive that this row should not appear
SQL> --
SQL> select * from t1;

       KEY
----------
         1
         2

2 rows selected.

SQL> select * from t2;

       KEY
----------
         1

1 row selected.

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6               )
  7  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> insert into t2 values (null);

1 row created.

SQL> select * from t2;

       KEY
----------
         1


2 rows selected.

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6               )
  7  /

no rows selected

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6                 where key is not null
  7               )
  8  /

       KEY
----------
         2

1 row selected.

SQL>

Certain conditions must be met before NOT IN and NOT EXISTS can be considered equivelant, most notably, the assurance that there will be no nulls in the nested list.

Good luck, Kevin

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #343113 is a reply to message #342758]

Tue, 26 August 2008 05:45

Olivia
Messages: 519
Registered: June 2008

Senior Member

Good one!

Thanks,
Oli

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #343121 is a reply to message #343113]

Tue, 26 August 2008 06:01

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is why I posted "when they are equivalent" in bold.

Regards
Michel

Report message to a moderator

Re: NOT IN Vs. NOT EXISTS [message #343124 is a reply to message #343121]

Tue, 26 August 2008 06:12

Olivia
Messages: 519
Registered: June 2008

Senior Member

Michel Cadot wrote on Tue, 26 August 2008 06:01

This is why I posted "when they are equivalent" in bold.

Regards
Michel

Great! You hardly put a hole in your answer..
Thanks to you but I will give this credit to Kelvin to make me understand for giving his valuable time.
Regards,
Oli

Report message to a moderator

Previous Topic:	Query taking different time in tqo schemas
Next Topic:	ANSI - SQL or Traditional SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:47:54 CDT 2024