exists versus 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 dept_no, dept_name
FROM   dept D,
       emp E
WHERE  D.dept_no = E.dept_no ;
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     SORT (UNIQUE)
       NESTED LOOPS
         TABLE ACCESS (FULL) OF 'EMP'
         TABLE ACCESS (BY ROWID) OF 'DEPT'
           INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
EXISTS is a faster alternative because the RDBMS optimizer realizes that when the subquery has been satisfied once, there is no need to proceed any further, and the next driving row can be fetched.

   SELECT dept_no, dept_name
   FROM   dept D
   WHERE  EXISTS ( SELECT 'X'
                   FROM   emp E
                   WHERE  E.dept_no = D.dept_no );
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     FILTER
       TABLE ACCESS (FULL) OF 'DEPT'
       TABLE ACCESS (BY ROWID) OF 'EMP'
           INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX'