Using JOINS in place of EXISTS

It is always preferred to use JOINS in place of EXISTS clause for unique scan indexes and also to small tables.
Below example will show the difference for the Unique indexes based on the explain plan, so rather than using sub-queries we can use JOIN conditions
for getting more efficient:

Less effective query by using EXISTS clause :

SELECT *
 FROM emp a
 WHERE EXISTS (SELECT 1
 FROM dept b
 WHERE b.deptno = a.deptno );

Explain plan for the above exists clause :

PLAN_TABLE_OUTPUT

Plan hash value: 3692042512

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 867 | 3 (0)| 00:00:04 |
| 1 | NESTED LOOPS SEMI | | 17 | 867 | 3 (0)| 00:00:04 |
| 2 | TABLE ACCESS FULL| EMP | 17 | 816 | 3 (0)| 00:00:04 |
|* 3 | INDEX UNIQUE SCAN| SYS_C00489592 | 4 | 12 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

 3 - access("B"."DEPTNO"="A"."DEPTNO")

More effective query by using JOIN conditions :

SELECT a.*
 FROM emp a, dept b
 WHERE b.deptno = a.deptno

Explain plan for the above query which used JOINS :

 PLAN_TABLE_OUTPUT

Plan hash value: 831606636

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 867 | 3 (0)| 00:00:04 |
| 1 | NESTED LOOPS | | 17 | 867 | 3 (0)| 00:00:04 |
| 2 | TABLE ACCESS FULL| EMP | 17 | 816 | 3 (0)| 00:00:04 |
|* 3 | INDEX UNIQUE SCAN| SYS_C00489592 | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

 3 - access("B"."DEPTNO"="A"."DEPTNO")

Advertisements

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