Document ID:   10653.1
Subject:       USING 'EXPLAIN PLAN': SUBQUERIES IN UPDATE STATEMENTS
Last Revision Date: 11 October   1994
Author:        Jay Rostosky


The EXPLAIN PLAN statement allows one to see how the RDBMS goes about
accessing the requested data.  It is discussed in detail in Chapter 7 of the
ORACLE RDBMS Performance Tuning Guide.

An UPDATE statement that invokes a subquery in the SET clause will only have
the table(s) UPDATEd listed in the EXPLAIN PLAN output.

Example:  UPDATE dept
          SET total_comp = (SELECT sum(sal) + sum(nvl(comm,0))   |
                            FROM emp, dept                       | SET clause
                            WHERE emp.deptno = dept.deptno       | subquery
                            AND dept.deptno = &&dept_number)     |
          WHERE dept.deptno = &&dept_number;

The EXPLAIN PLAN output is as follows:

OPERATION                     OPTIONS                 OBJECT_NAME
------------------------------------------------------------------------------
   TABLE ACCESS               FULL                    DEPT

To see the path taken by the SET clause subquery, simply do an EXPLAIN PLAN
explicitly on the subquery itself.  That EXPLAIN PLAN output is as follows:

OPERATION                     OPTIONS                 OBJECT_NAME
------------------------------------------------------------------------------
   SORT                       GROUP BY
     MERGE JOIN
       SORT                   JOIN
         TABLE ACCESS         FULL                    DEPT
       SORT                   JOIN
         TABLE ACCESS         FULL                    EMP

An UPDATE statement that invokes a subquery in the WHERE clause will have both
the UPDATEd table(s) and the table(s) subqueried listed in the EXPLAIN PLAN
output.

Example:  UPDATE emp
          SET deptno = 31
          WHERE emp.deptno = (SELECT DISTINCT dept.deptno      |
                              FROM emp, dept                   |  WHERE clause
                              WHERE dept.deptno = emp.deptno   |  subquery
                              AND dept.deptno = 30);           |

The EXPLAIN PLAN output is as follows:

OPERATION                     OPTIONS                 OBJECT_NAME
------------------------------------------------------------------------------
  FILTER                                                        | to perform
    TABLE ACCESS              FULL                    EMP       | UPDATE
    SORT                      UNIQUE                            |- - - - - - -
      MERGE JOIN                                                |
        SORT                  JOIN                              | to perform
          TABLE ACCESS        FULL                    DEPT      | WHERE clause
        SORT                  JOIN                              | subquery
          TABLE ACCESS        FULL                    EMP       |

In the most general case, with a SET clause subquery and a WHERE clause
subquery in the UPDATE statement, again, the table(s) queried in the SET clause
subquery will not be included in the EXPLAIN PLAN output.

Example:  UPDATE emp
          SET deptno = (SELECT min(deptno) FROM dept)    | SET clause subquery
          WHERE ename in (SELECT ename FROM emp          | WHERE clause
                          WHERE length(ename) = 5);      | subquery

The EXPLAIN PLAN output is as follows:

OPERATION                     OPTIONS                 OBJECT_NAME
------------------------------------------------------------------------------
  MERGE JOIN                                                 | to perform
    SORT                      JOIN                           | UPDATE
      TABLE ACCESS            FULL                    EMP    |- - - - - - - -
    SORT                      JOIN                           |
      VIEW                                                   | to perform
        SORT                  JOIN                           | WHERE clause
          TABLE ACCESS        FULL                    EMP    | subquery

Notice the SET clause subquery that looks at the DEPT table is not included.