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: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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 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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous messageGo to next message
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 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: 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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 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

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 22:43:38 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.