The Oracle (tm) Users' Co-Operative FAQ

Tuning UPDATE/DELETE statements with subqueries

Back to index of questions

The tuning of UPDATE or DELETE statement referencing a single table is no different from SELECT, however some performance problem arises when a number of tables are involved. In major number of cases there are 2 tables when one of them must be updated based on some criteria from another.

We are going to analyze 2 cases (both with RULE and COST based optimizers) of UPDATE statement execution but the same reasoning and methods apply to DELETE statement as well:

Case 1. EMP and DEPT tables are involved. We would like to update salaries of all employees (lets give them 5% raise) working for ‘SALES’ department. There is always a possibility of using 2 statements:
  a. SELECT from DEPT to get a value of DEPTNO column.
  b. UPDATE EMP SET SAL = SAL * 1.05 WHERE DEPTNO = :DNO

However lets see what happens when we try to do it by using single UPDATE statement (and Rule Based Optimizer - RBO):

   UPDATE EMP SET SAL = SAL * 1.05 
   WHERE DEPTNO IN (SELECT DEPTNO FROM DEPTWHERE DNAME = ‘SALES’)

As we can see the optimizer performs:
  a. FULL TABLE SCAN of EMP table (about 20000 rows).
  For EACH row it :
  b. Accesses an INDEX of DEPT table by DEPTNO value to get a ROWID
  c. Accesses a DATA part of DEPT table by using ROWID retrieved from an
      index to get value of DNAME column.
      Only now it performs a final elimination of unnecessary rows by filter on
      DNAME column (DNAME = ‘SALES’)

It’s quite obvious that it’s may be more efficient to access DEPT table first and EMP table at later stage.
In order to force the optimizer to access the tables in specified order we have to supply USE_NL hint. It tells optimizer to use EMP table as an inner table and, by-product, DEPT table will be used as outer (driving) table.

 
  UPDATE /*+ USE_NL(EMP) */ EMP SET SAL = SAL * 1.05
  WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’)

Now we can compare the results:

As expected there is a major performance gain.

Now let’s see what happens with the cost based optimizer (CBO).

   UPDATE EMP SET SAL = SAL * 1.05 
   WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’)

As we can see the CBO performs much better then RBO (it uses HASH JOIN instead of NESTED LOOP JOIN of RBO). However it’s interesting to see what will happen when we access EMP table by index instead of performing FTS on it. In order to do it we add a hint to our statement once again and another hint to enforce index access:

   UPDATE /*+ USE_NL(EMP) INDEX(EMP) */ EMP
   SET SAL = SAL * 1.05
   WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’)

Now (using CBO) there is almost no performance difference between hinted and non-hinted statements, however we have to remember a number of facts:
  a. We update about 10 percent of rows in EMP table
  b. Our rows are very short (so the number of block in the table EMP is relatively small)

My conclusion is that there is still a place to use hints when:
  a. FTS of updated table is performed and
  b. Only small percentage of rows are updated

We are going to verify that conclusion in our next example.

Case 2. Let’s check a bit more complicated statement.
  EMP table must be updated based on values in EMP_LOAD table that contains relatively small number of rows ( about 20000 rows in EMP table and 100 rows in EMP_LOAD table):

RBO:
  UPDATE emp e
  SET (ename, job, mgr, hiredate, sal, comm, deptno) =
    (SELECT ename, job, mgr, hiredate, sal, comm, deptno
     FROM emp_load el
     WHERE e.empno = el.empno)
  WHERE e.empno IN (SELECT empno FROM emp_load)

             Once again FULL TABLE SCAN raises it’s ugly head. We are going to use the previous approach and try to access EMP as an INNER table.

     UPDATE /*+ USE_NL(e) INDEX(e) */ emp
     SET (ename, job, mgr, hiredate, sal, comm, deptno) = 
          (SELECT ename, job, mgr, hiredate, sal, comm, deptno
           FROM emp_load el
           WHERE e.empno = el.empno )
     WHERE e.empno IN ( SELECT empno FROM emp_load)

          CBO:

    UPDATE emp e 
    SET (ename, job, mgr, hiredate, sal, comm, deptno) =
      (SELECT ename, job, mgr, hiredate, sal, comm, deptno
       FROM emp_load el
       WHERE e.empno = el.empno)
    WHERE e.empno IN (SELECT empno FROM emp_load)

The result is identical to the default RBO decision, so let’s try and improve it:

    UPDATE /*+ USE_NL(e) INDEX(e) */ emp
    SET (ename, job, mgr, hiredate, sal, comm, deptno) =
        (SELECT ename, job, mgr, hiredate, sal, comm, deptno
         FROM emp_load el
         WHERE e.empno = el.empno )
    WHERE e.empno IN ( SELECT empno FROM emp_load)

Now we can compare the results:

As we can see there is huge performance improvement in these cases just waiting to be executed.

Further reading: N/A

Back to top

Back to index of questions