Using “NOT EXISTS” clause instead of “NOT IN” clause

When we used “NOT IN” clause in a sub-query statements it always causes an internal sort/merge. Which cause or result an expensive query.
To avoid this we can use “NOT EXISTS” clause.
Below example with explain plan using “NOT EXISTS” and “NOT IN” clause :

SELECT *
 FROM hz_party_sites
 WHERE location_id NOT IN (SELECT location_id
 FROM hz_locations) ;

Below is the explain plan for the above SQL statement:

PLAN_TABLE_OUTPUT

Plan hash value: 38171645

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 676K| 109M| | 92396 (1)| 28:36:38 |
|* 1 | HASH JOIN RIGHT ANTI| | 676K| 109M| 205M| 92396 (1)| 28:36:38 |
| 2 | TABLE ACCESS FULL | HZ_LOCATIONS | 11M| 75M| | 34734 (1)| 10:45:19 |
| 3 | TABLE ACCESS FULL | HZ_PARTY_SITES | 12M| 1871M| | 22256 (1)| 06:53:30 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - access("LOCATION_ID"="LOCATION_ID")

MOre efficient SQL query by using “NOT EXISTS” :

SELECT *
 FROM hz_party_sites a
 WHERE NOT EXISTS (SELECT 1
 FROM hz_party_sites b
 WHERE b.location_id = a.location_id) ;

Below is the explain plan after using “NOT EXISTS” clause , where we can see the difference and is more efficient than the earlier code.

PLAN_TABLE_OUTPUT

Plan hash value: 1004671512

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 120K| 19M| | 80109 (1)| 24:48:21 |
|* 1 | HASH JOIN RIGHT ANTI| | 120K| 19M| 218M| 80109 (1)| 24:48:21 |
| 2 | TABLE ACCESS FULL | HZ_PARTY_SITES | 12M| 80M| | 22251 (1)| 06:53:24 |
| 3 | TABLE ACCESS FULL | HZ_PARTY_SITES | 12M| 1871M| | 22256 (1)| 06:53:30 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - access("B"."LOCATION_ID"="A"."LOCATION_ID")

Advertisements

2 thoughts on “Using “NOT EXISTS” clause instead of “NOT IN” clause

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s