Oracle EXISTS vs DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list when you submit queries that are used to determine information at the owner end of a one-to-many relationship (e.g., departments that have employees). The SQL will actually fetch all rows satisfying the table join and then sort and filter out duplicate values.

An example of such a query is shown below:

SELECT DISTINCT D.DEPTNO, DNAME DEPTNAME
 FROM DEPT D, EMP E
 WHERE D.DEPTNO = E.DEPTNO;

Output:

DEPTNO    DEPTNAME
-------   ----------
10        ACCOUNTING
20        RESEARCH
30        SALES

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     9 |   144 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |     9 |   144 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   224 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

EXISTS is a faster alternative because the RDBMS optimizer realizes that when the sub-query has been satisfied once, there is no need to proceed any further, and the next driving row can be fetched.

An example of such a query is shown below:

SELECT DEPTNO, DNAME DEPTNAME
 FROM DEPT D
 WHERE EXISTS (SELECT 'X'
 FROM EMP E
 WHERE E.DEPTNO = D.DEPTNO);

Output:

DEPTNO    DEPTNAME
-------   ----------
20        RESEARCH
30        SALES
10        ACCOUNTING

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    48 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     3 |    48 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

As shown above, Query Plan generated with EXISTS clause is also much better than the one generated with DISTINCT clause.

Summary
Oracle EXISTS vs DISTINCT
Article Name
Oracle EXISTS vs DISTINCT
Description
Avoid joins that require the DISTINCT qualifier on the SELECT list when you submit queries that are used to determine information at the owner end of a one-to-many relationship (e.g., departments that have employees). The SQL will actually fetch all rows satisfying the table join and then sort and filter out duplicate values.
Author
Publisher Name
Technocityhub
Publisher Logo

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.