On the face of it, this feature appears to be quite useful, especially in reducing the amount of code we need to write and maintain. However, there are two (major) shortcomings with this feature, as follows.

the target table's primary key is updated on each record touched by the SET ROW clause, even though its value has probably not changed (or at least it shouldn't change); and

it is unlikely that many of the columns will have changed their values, so for updates to a small number of columns in a target table, this clause does far too much work. Most of the updates will be performed against columns that were not intended (i.e. their values haven't changed).

This article will outline two techniques to avoid both of these issues while using the SET ROW clause. For readers who are unfamiliar with the SET ROW syntax and its shortcomings, read this oracle-developer.net article.

setup

For the examples in this article, we will create a simple table with 5 columns based on 1,000 records from ALL_OBJECTS, as follows. Note that we have a single-column primary key. This table will serve as both the source and target table for our examples.

a note on the examples

The examples that follow are deliberately simplified. We will assume that the sample table has been partially loaded and 2 of the 5 columns in each record must be updated by some "post-processing" (this potentially expensive technique is surprisingly quite common in many ETL systems). We suppose that these 2 columns must undergo some complex transformations that make this operation too complicated for a single SQL UPDATE.

All examples will use bulk fetching and binding. Again, to keep it simple, we will bulk fetch our data in one pass, rather than use LOOP and LIMIT (an absolute necessity when dealing with large volumes of data).

set row limitations

We will begin with an example of the limitations of the SET ROW clause. We will fetch the source data into an array of records and transform the two target columns in each record, before applying a single bulk update with the SET ROW clause.

This trace file clearly highlights the problem with SET ROW. We have updated the primary key ID column (even though we did not modify it). We have also needlessly updated the CREATED and OWNER columns (also using their existing values). We have potentially high levels of redundancy and resource waste here. Not only do we update the primary key (which will generate locks on child tables) but we also generate more undo and redo than is necessary, thereby reducing the efficiency, concurrency and overall scalability of our system.

solutions to the problem

There are two methods we can adopt to make the SET ROW clause useful and avoid the issues described above. We will see both methods below, starting with a technique that will work on 10g upwards (this will compile on 9i but a known bug generates an ORA-03113 error on execution). Both methods take advantage of the ability to perform DML on an in-line view, introduced in Oracle 9i. We will use this technique to project only the columns we wish to update and use the SET ROW clause on these partial records accordingly.

solution for 10g+

As stated above, this workaround will generate an ORA-03113 on 9i so should be only be used for 10g upwards. In this workaround, we will fetch the source data and create a primary key array in the same manner as before. In addition, we will copy the transformed data into a new array of records based on just the two columns we wish to update. This third array will be used in the SET ROW clause against an in-line projection of the UPDATE_TABLE. The example is as follows.

Lines 10-16: we define a record type based on the columns we wish to update and then base an associative array type on this structure;

Lines 43-44: once the transformations are complete, we copy the update data to an array that will be used in our SET ROW clause later on;

Lines 53-55: we update an in-line projection of just the target columns, limiting each in-line view by primary key; and

Line 56: we update the projection of two columns using SET ROW based on a PL/SQL record of two attributes.

If we examine the SQL Trace file, we can see that Oracle only updates the columns we have modified. We have avoided both the primary key updates and the redundant updates against columns that do not change.

Note that this 10g+ solution targets the updates via the primary key values. This means, therefore, that the source data doesn't necessarily have to come from the table that is to be updated, even though this is how the example is structured.

solution for 9i release 2+

We will now see a similar technique that will work on 9i Release 2 (9.2) upwards. This alternative solution uses ROWIDs, rather than primary keys, to target the updates. This method works when the source and target tables are the same or the target table is at least included in the source cursor (i.e. to be able to fetch the ROWIDs). There are some subtle differences to the previous 10g+ solution, described after the following example.

Lines 3-5: the source cursor includes the ROWID of the table that is to be updated;

Lines 7-8: we base our associative array type on the source cursor%ROWTYPE as in the 10g+ example. Note that support for this this was introduced in the 9.2.0.3 patchset. Readers running 9.2.0.1 or 9.2.0.2 will therefore need to create a record type that matches the cursor columns and base the array type on this instead;

Lines 18-19: we create an associative array type of ROWID, rather than the primary key column;

Line 40: the ROWID from each element is copied to a separate array for use in the update; and

Line 53-56: we update the projection of two columns with SET ROW based on a PL/SQL record of two attributes, but this time the predicate (based on ROWID) is outside the in-line view.

This technique works on 9i for two main reasons. First, the predicate is outside the in-line view, which avoids the ORA-03113 issue. Second, ROWID is a pseudo-column so does not need to be projected in the in-line view to be referenced outside. If we wished to use the primary key instead of the ROWID, then we would need to project it. Of course, this would in turn require us to include the primary key in the PL/SQL update records, thereby negating the value of this technique.

To confirm that this technique works as designed, we can examine the SQL Trace file, which shows us the following recursive SQL. Again we confirm that only the two target columns are updated.

validating the techniques

The recursive SQL statments in the trace files are reasonably conclusive that no superfluous updates are occurring when we execute our SET ROW solutions. For further validation that this technique avoids all primary key updates, readers can try the following tests.

third, in a new session, attempt to run each of the examples in this article.

Readers will find that the two solutions presented in this article will complete without issue because they do not update the primary key. If they did, they would be blocked by the lock on the child table in the other session, because a primary key update will attempt to take share locks on child tables (which is one of the reasons why indexed foreign keys are so important). The "limitations" example will be blocked by the lock on the child table because it updates every column including the primary key, as we saw earlier.

acknowledgements

Thanks to David Sharp for the ROWID workaround for 9i.

source code

The source code for the examples in this article can be downloaded from here.