SQL TUNING - Consider EXISTS in Place of Table Joins

SQL TUNING -  Consider EXISTS in Place of Table Joins :
Consider breaking some table joins out to separate subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is small. When two tables are joined, all rows need to be matched from the driving table to the second table. If a large number of rows can be filtered from the driving table before having to perform the validation against the second table, the number of total physical reads can be dramatically reduced.

Consider the following example:

SELECT . . .                         SELECT . . .
FROM   dept D,                       FROM   dept D,
       emp  E                               emp E
WHERE  E.dept_no  = D.dept_no        WHERE  E.dept_no = D.dept_no
AND    E.emp_type = 'MANAGER'        AND    ( E.emp_type = 'MANAGER'
AND    D.dept_cat = 'A';             OR     D.dept_cat = 'A'     );
To improve performance, specify:

SELECT . . .
FROM   emp E
WHERE  EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )
AND    E.emp_type = `MANAGER'
SELECT . . .
FROM   emp E
WHERE  E.emp_type = 'MANAGER'
OR     EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )
Note:  The EXISTS clause must be positioned first in the WHEN clause when using an AND operator and last in the WHEN clause when using an OR operator.