USE EXISTS IN PLACE OF DISTINCT visit for more information "allmyneed.blogspot . com" 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 Code (SQL): 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 Code (SQL): 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.