SQL Tuning - Minimizing Table Lookups

SQL Tuning - Minimizing Table Lookups -
To improve performance, minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.

For example, instead of specifying:

SELECT emp_name
FROM   emp
WHERE  emp_cat   = ( SELECT MAX(category)
                        FROM   emp_categories )
AND    sal_range = ( SELECT MAX(sal_range)
                        FROM   emp_categories )
AND    emp_dept  = 0020;

specify the following:

SELECT emp_name
FROM   emp
WHERE (emp_cat, sal_range)
                    = ( SELECT MAX(category), MAX(sal_range)
                        FROM   emp_categories )
AND    emp_dept  = 0020;

For multicolumn UPDATE statements, instead of specifying:

UPDATE emp
SET    emp_cat   = ( SELECT MAX(category)
                     FROM   emp_categories ),
       sal_range = ( SELECT MAX(sal_range)
                     FROM   emp_categories )
WHERE  emp_dept  = 0020;

try using:

UPDATE emp
SET   (emp_cat, sal_range)
                 = ( SELECT MAX(category), MAX(sal_range)
                     FROM   emp_categories )
WHERE  emp_dept  = 0020;