-->

Spiga

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;




































OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

14659
SORTUNIQUE
14659
HASH JOIN

1000
TABLE ACCESSFULLDEPT1
TABLE ACCESSFULLEMP822

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































OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

1
FILTER


TABLE ACCESSFULLDEPT1
TABLE ACCESSFULLEMP1822

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.

There was an error in this gadget