USE EXISTS IN PLACE OF DISTINCT

The DISTINCT combination of deptno and dname columns have to be displayed. Only those rows need be displayed, whose deptno exists in the EMP table.
Query 1
SELECT DISTINCT E.deptno,D.dname
FROM emp E,dept D
WHERE D.deptno = E.deptno;

OPERATION	OPTIONS	OBJECT_NAME	COST
SELECT STATEMENT			14659
SORT	UNIQUE		14659
HASH JOIN			1000
TABLE ACCESS	FULL	DEPT	1
TABLE ACCESS	FULL	EMP	822

Query 2
SELECT D.deptno,D.dname
FROM dept D
WHERE EXISTS ( SELECT 'X'
FROM emp E
WHERE E.deptno = D.deptno );

OPERATION	OPTIONS	OBJECT_NAME	COST
SELECT STATEMENT			1
FILTER
TABLE ACCESS	FULL	DEPT	1
TABLE ACCESS	FULL	EMP1	822

Inference
There is a reduction of more than 97% in estimated cost.In the query using the DISTINCT clause, the explain plan shows that there is a HASH join between the two tables. In this, the smaller of the two tables (in this case the DEPT table) is converted into a hash table and stored in memory. For every row of the larger table (EMP table) retrieved, the hash table is scanned to check for the join condition.
The query using EXISTS uses a FILTER operation. The FILTER operation accepts a set of rows, eliminates some of them, and returns the rest. In the above case, the rows from the DEPT and EMP table are filtered out based on the join condition. EXISTS is a faster and a cheaper alternative because the 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. In the example shown only one row needs to be returned from dept even though many rows in emp might match the subquery.