SQL TUNING - Consider Table Joins in Place of EXISTS

SQL TUNING - Consider Table Joins in Place of EXISTS -

In general, consider joining tables rather than specifying 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 high. For example, if we are selecting records from the EMP table and are required to filter those records that have a department category of "A", then a table join will be more efficient.

Consider the following example:

SELECT emp_name
FROM   emp E
WHERE  EXISTS ( SELECT 'X'
                   FROM   dept
                   WHERE  dept_no  = E.dept_no
                   AND    dept_cat = 'A');
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
      FILTER
        TABLE ACCESS (FULL) OF 'EMP'
        TABLE ACCESS (BY ROWID) OF 'DEPT'
          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
To improve performance, specify:

   SELECT emp_name
   FROM   dept D,
          emp  E
   WHERE  E.dept_no  = D.dept_no
   AND    D.dept_cat = 'A';
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     NESTED LOOPS
        TABLE ACCESS (FULL) OF 'EMP'
        TABLE ACCESS (BY ROWID) OF 'DEPT'
          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)