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'