Home » RDBMS Server » Performance Tuning » NOT IN Vs. NOT EXISTS (Oracle 9i/ 10g)
NOT IN Vs. NOT EXISTS [message #338974] Wed, 06 August 2008 06:26 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: NOT IN Vs. NOT EXISTS [message #338988 is a reply to message #338985] Wed, 06 August 2008 07:00 Go to previous messageGo to next message
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
Re: NOT IN Vs. NOT EXISTS [message #338990 is a reply to message #338988] Wed, 06 August 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: NOT IN Vs. NOT EXISTS [message #339013 is a reply to message #338990] Wed, 06 August 2008 08:48 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the update Michel Smile
Re: NOT IN Vs. NOT EXISTS [message #339091 is a reply to message #338974] Wed, 06 August 2008 13:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: NOT IN Vs. NOT EXISTS [message #343113 is a reply to message #342758] Tue, 26 August 2008 05:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Good one!

Thanks,
Oli
Re: NOT IN Vs. NOT EXISTS [message #343121 is a reply to message #343113] Tue, 26 August 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is why I posted "when they are equivalent" in bold.

Regards
Michel
Re: NOT IN Vs. NOT EXISTS [message #343124 is a reply to message #343121] Tue, 26 August 2008 06:12 Go to previous message
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
Previous Topic: Query taking different time in tqo schemas
Next Topic: ANSI - SQL or Traditional SQL
Goto Forum:
  


Current Time: Thu Dec 08 08:40:07 CST 2016

Total time taken to generate the page: 0.06758 seconds