Oracle Scratchpad

January 2, 2007

Superfluous Updates ?

Filed under: Infrastructure,Performance — Jonathan Lewis @ 9:50 pm GMT Jan 2,2007

In a recent article (which has prompted several excellent comments) I highlighted the suggestion raised by Don Burleson that:

We would have expected (at least, I did, anyway) that Oracle would check to see if a SQL update statement failed to result in any row changes. A simple test could protect against “false updates”, and perhaps bypass the update and send an alert log message that a DML statement is doing superfluous updates.

Mr. Burleson’s comment was made with reference to an earlier page of mine explaining that I had clawed back some time from an update statement by changing it from



update HISTORY SET FLAG = 0 WHERE CLASS = 'X' and FLAG != 0; 

thus avoiding the undo and redo overheads of roughly 5,000,000 redundant row updates per night.

At first sight, Mr. Burleson’s comment might seem quite sensible – after all it shouldn’t take much CPU to work out which rows should and should not be updated, and it ought to be quite simple to skip the rows that aren’t going to change. (I have had to make a few guesses about what Mr. Burleson is trying to say as he fails to distinguish between updates to columns, updates to rows, and the entire update statement. My initial assumption is that he is trying to say that Oracle should not update a row when it sees that none of the columns specified in the update statement have changed their values).

This, however, is a very naive piece of analysis that demonstrates a common lack of attention to the concept of generalisation. The one specific statement that you are looking at right now is not typical of everything you do. The “obvious” enhancement for this statement may have a horrid impact on everything else you do. In fact, the “obvious” enhancement may cause logical errors because the statement is no longer behaving according to its specification.

It’s the classic “silver bullet” issue – it’s often easy to come up with a “quick fix” for a local problem, but you then have to demonstrate that the fix caters for all possible variations that might occur, and that the global consequences of applying the fix aren’t going to be worse than the impact of the original problem.

Logical Error

Let’s start with the critical reason why Oracle must not try using clever tricks to avoid the “false updates”. If I update a row in a way that changes none of the columns I have still managed to lock the row and no-one else can modify it.  If Oracle bypasses my update simply because it appears to be redundant then it may be subverting my intentions by leaving rows modifiable by other sessions when I want them locked.

Of course, you could argue that Oracle should therefore simply lock the row but not update it. However, in simple cases like the example above, most of the undo and redo overhead relates to the tracking and locking information – the volume of data relating to the before and after images of the data is really a small percentage of the total. So for the increased complexity, which becomes apparent in the more general case, we gain very little benefit. (In passing , when I advised the client to add the extra predicate, I first made sure that the “excess” 5 million rows were being updated by accident rather than by design – I also made sure that the column was not allowed to hold nulls.)

The General Case

The original example had just one column in the update statement – but think what Oracle would have to deal with to handle an update like:

update t1
	col1	= :b1,
	col2	= :b2,
	col3	= :b3,
	col50	= :b50
	rowid	= :rowid

This is the type of code you get from many GUI screen-painters – the “one statement fits all changes” feature.

On the plus side you tend to get only one SQL statement in the shared pool to cover every possible update (though there may be multiple child cursors if you have columns declared as varchar2() or nvarchar2() with a length of more than 32 bytes), on the minus side you generate undo (and redo) for every column used in the screen. So an approach similar to Mr. Burleson’s suggestion definitely has some appeal, but would it be the “simple test” that he thinks it is.

Interestingly there already seems to be some kernel code in place to detect “no-change” updates. If you update an indexed column so that the value does not change Oracle doesn’t seem to go through the full cycle of work that would normally go into updating the indexes.

Side note: the amount of work done in this case varies with version of Oracle and, in some scenarios, seems to have gone up significantly in 10g. (I haven’t worked out all the details, and I can’t be certain that some of the optimisation isn’t simply an accidental side-effect rather than deliberate code – and it is possible that the increase in 10g is a bug).

So why doesn’t Oracle perform a “simple test” to avoid updating columns that haven’t changed. I don’t know, I haven’t seen the kernel specifications and I didn’t write the kernel code, but I’ll take a couple of wild guesses.

Maybe it’s just not so simple to unravel the existing code that creates the undo and redo records – and remember in the case of my 50 column update above we have to test every single incoming field and (presumably) set a flag that says “we finally changed one column so we need undo and redo so that the redo and undo will be generated.

And when it comes to flagging the “false update” into the alert log, should we do this for the row when we find a single column that doesn’t change, for every column that doesn’t change, only if the entire set of columns in a row doesn’t change, or just once for the entire statement irrespective of how little, or how much, isn’t changed? Whichever option you pick, it’s arguably the wrong option for some (possibly most) people.

And then you have to consider just what the overhead of this “simple test” might be. It has to operate for every single update you ever do. Most developers (we hope) will write code that does exactly what it’s supposed to do, efficiently, and with no redundant activity. So if we install this “simple test” we may have to pay for it millions of time a day in order to get a notification occasionally about a piece of silly code. Is this a sensible trade? Possibly not, especially if there is some complexity involved that introduces a risk of error in a critical piece of the Oracle kernel.

Still, given all the other work that goes on around an update, the overhead of checking whether the old and new values match and not generating redundant undo and redo does sound like a cost-effective run-time trade-off – especially in the light of the GUI-generated screen. But I’m not going to make any guesses about the complexity of the task, or the impact it might have in the library cache if some component of the cursor heap has to be restructured as a consequence.

Special Considerations

When thinking about “simple tests” you always need to be aware that some things can go on that fall outside the scope of your initial “simple example”. For example:

What should you do about triggers – consider the code fragment:

create or replace trigger t1_bru
before update of n01, n02 on t1
for each row

If you do a “no-change” update on the columns referenced in a row-level trigger, should the trigger still fire ?

If you say no, you’ll have to explain how to cope with the case where the trigger changes the incoming value; if you say yes, you’ll have to explain what to do if the trigger is being used to summarize the rows that have been updated – and according to the “simple test” approach this row may have fallen into the “false update” category and not have been updated at all – and then compare the logic of your argument with the home-grown (and slightly unsafe) logic I’ve seen occasionally which does the equivalent of the following pseudo-code:

SQL> select rowid from history where flag = 'X' for update;
5000000 rows selected
SQL> update history set status = 0 where flag = 'X';
120 rows updated

if select_count != update_count rollback and try again.

Consider all the flavours of using supplemental logging (for streams, change data capture, etc.). Supplemental logging works by adding to the undo records (yes, that does say undo, not redo, that is the way it works) a set of columns that can act as a unique identifier for a row – even if those columns have not changed. If someone does decide to add that “simple test”, I hope they don’t make it happen after the supplemental logging bit has been generated – or the supplemental log will disappear and streams et. al. will break.

No doubt there will be people reading this note who will come up with other example of the less commonly used features that also need special consideration before the “simple test” could work safely.

Bottom line

Most of the code in the Oracle kernel behaves as if application developers understand exactly what they are doing and know how to use relational databases. This is, of course, a serious design flaw; but from time to time bits of the kernel code do get modified to cope with the worst excesses of bad applications.

Perhaps the time will come when someone modifies the kernel to eliminate the redundant undo and redo generated by the default behaviour of most GUI screen generators. When they do they had better not change the locking behaviour, and they had better be very careful that they allow for all the variations of what else could be going on in all the peripheral activities involved with row updates.

And there had better be a parameter to turn the feature off; because when you start to think carefully, you realise that there could be all sorts of unexpected side-effects which make the “simple test” not so simple after all.


  1. As you mentioned on a previous posting, one of the joys of blogging is that it allows others to conjure up boundary cases or less common situations to conjure with. :)

    “Compressed tables”: Now updating a compressed block of a table is not something that one wants to get involved with really, but in the case of superfluous updates the effects really are severe in proportion to the “superfluousness” …


    Comment by David Aldridge — January 3, 2007 @ 2:09 am GMT Jan 3,2007 | Reply

  2. David, good example. As you say, of course, you shouldn’t be updating compressed tables – although one of the “officially unofficial” comments from Oracle at OW2006 was that you would be able to mix normal DML with compressed tables without penalty in 11g. (Perhaps they’ll include the undo and redo elimination in the same release – and maybe for just this feature).

    Of course, your comment also re-inforces the point that often you need to do the right thing with Oracle before you worry about the very fine detail of how Oracle is doing what you tell it to.

    Comment by Jonathan Lewis — January 3, 2007 @ 8:17 am GMT Jan 3,2007 | Reply

  3. Very interesting!

    You write a more deep an REALLY more clear version of my ideas! (well, not all mine :) )

    As you say, this check is dangerous staff: I really hope no one and never will try to implement it.

    About the index update. I found quite logical the fact that Oracle try to minimize the work on the index. A single update can generate a lot of disk work.

    A question: when Oracle work out an update, I don’t think it undo the very single row but it mark (in some way) the block that contain the entire row. Am I right? (Perhaps the block is seen like a matrix…)

    All this seems like the check was not introduced (years ago…) because CPU was not so powerful and now it’s not introduced because DISK are so fast…


    compressed table are a nasty beasts and the update is slower because of the extra work: decompress and re-insert the (non compressed) row.

    Bye! :-)

    Comment by Antonio — January 3, 2007 @ 9:03 am GMT Jan 3,2007 | Reply

  4. Great Jonathan,

    And I’ve used the

    “update HISTORY SET FLAG=0 WHERE CLASS = ‘X’ and FLAG != 0;”

    more than just a few times, though usually as NVL(FLAG,-1) != 0 or similar, where it wasn’t possible to not-null the column, or just in case that not-null constraint would disappear at some future point.

    Not so straightforward when we’re updating multiple columns of course, and it does get messy OR-checking each.

    And yes, an ‘off-switch’ would be a very good idea should Oracle introduce such a smart update ‘feature’.

    Comment by SeanMacGC — January 3, 2007 @ 10:01 am GMT Jan 3,2007 | Reply

  5. Antonio, I don’t quite understand your question – to update a row Oracle rewrites it with the new values, copying the old values to the undo record and (implicitly) to the redo vector for the undo record, and copying the new values to the redo vector for the table.

    Comment by Jonathan Lewis — January 3, 2007 @ 6:47 pm GMT Jan 3,2007 | Reply

  6. I was trying to figure if (for some reason) Oracle move to the undo/redo the entire db-block (i.e some rows) or only the row and then mark (in some way) the row and the value to modify.

    Comment by Antonio — January 4, 2007 @ 8:46 am GMT Jan 4,2007 | Reply

  7. Not sure if this is interesting but a TRUNCATE on an empty table leaves the LAST_DDL_TIME field intact so it seems to be skipped. But altering a table and modifying the datatype of a column to the same datatype does reset the LAST_DDL_TIME (on



    Comment by Colin 't Hart — January 4, 2007 @ 10:22 am GMT Jan 4,2007 | Reply

  8. Please consider this off-topic comment in response to a single, isolated statement of yours (ie. not in any way a response to your overall point). This one:

    “If I update a row in a way that changes none of the columns I have still managed to lock the row and no-one else can modify it. If Oracle bypasses my update simply because it appears to be redundant then it may be subverting my intentions by leaving rows modifiable by other sessions when I want them locked.”

    Wait a minute. You think that an update statement should GUARANTEE a certain row gets locked?

    Update means update. It doesn’t mean lock. Locking is a means to an end.

    Oracle promises you that the data integrity and consistency will be maintained while you do an update. It does not promise you that it will lock the rows. Locking the rows is simply the method in which they fulfill that data consistency promise.

    When you say “I want to update these rows” I don’t think Oracle should imply “I want you to lock these rows.” Maybe more like “I accept that you may deem it necessary to lock these rows.”


    Comment by Robert V — January 4, 2007 @ 10:40 pm GMT Jan 4,2007 | Reply

  9. Robert, that’s a valid point – and once upon a time (ca. 5.1/6.0) there were noises coming out of Oracle about “column level locking” – i.e. you could update one column in a row whilst I was updating another column in the same row. Nothing ever came of it but if they had developed the theme then your comments would be more than just theoretically correct.

    In fact my statement was concerned more about what currently happens and how I wouldn’t want it to change. That, of course, introduces the old argument – how much of our code works because of side effects of the current implementation rather than the specified effects. (Shades of the “group by”/”order by” argument – except the manuals were always pretty strong on warning that “group by” does not imply “order by”.)

    Comment by Jonathan Lewis — January 4, 2007 @ 11:02 pm GMT Jan 4,2007 | Reply

  10. […] variables and some of the peripheral details that can introduce surprises; and in the article on superfluous updates I made a throwaway comment about getting multiple child cursors for a single statement if you had […]

    Pingback by Bind Variables « Oracle Scratchpad — January 5, 2007 @ 10:17 pm GMT Jan 5,2007 | Reply

  11. Why use many not-so-powerful processors working in parallel? Why not just design a single, really powerful processor?

    Comment by Wirasat J. Paracha — March 13, 2007 @ 7:59 am GMT Mar 13,2007 | Reply

  12. @Wirasat J. Paracha

    Because, sometimes, “divede et impera” is a good idea. :)

    Comment by Antonio — March 13, 2007 @ 11:33 am GMT Mar 13,2007 | Reply

  13. This is obviously an old post, but thought I would enter my 2 cents…

    There are some very specific cases where “only updating modified rows” is very valuable, but I agree that (in general) it is not advisable.
    Currently the only method to guarantee that a large update (think 60 columns) only updates rows where values change is to compare all of the columns in the “WHERE” clause. Developers have to do this themselves, which gets nasty with a large number of columns. I can’t help but think that if this were built into the kernal (and added as an optional keyword or hint, similar to how the APPEND() hint significantly alters how Oracle performs its work), that Oracle would probably be able to do this somewhat more efficiently internally (and would save developers the hassle of manually checking every column)…

    While on the subject of annoying Oracle things – when Oracle is rolling back data, why does it not provide any way to either TRUNCATE or DROP the affected object and abort the rollback? Oracle can obviously detect which objects are affected (based on the locks), so why not let the affected objects be dropped and save the database resources for something useful?

    Comment by Nathan — July 17, 2007 @ 11:08 pm BST Jul 17,2007 | Reply

  14. I think one of the BIGGEST problems with only updating rows which really change is DATA INTEGRITY!

    Imagine someone else on the same table having some uncommited changes on rows – You can read all rows without problem and mark only the ones matching your criteria for locking – then your update will succeed. But some rows may match your criteria and are not updated after your command – because the other user commited his change… NIGHTMARE!!!

    Comment by Falco — April 7, 2014 @ 2:04 pm BST Apr 7,2014 | Reply

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by