avoid unnecessary updates

I do update t set x=:a;

If I do it twice, I am doing a lot of unnecessary updates. This is true in an update, and also in the update clause of a MERGE.

I need to take care of null, I can update null with something, or something with null, but update null with null is also unnecessary.


SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.34
SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.32
SQL> update t set x=:a
  2  where x!=:a
  3  or (x is null and :a is not null)
  4  or (x is not null and :a is null);

0 rows updated.

Elapsed: 00:00:00.04

Put your code in <code> and </code> tags

6 Responses to “avoid unnecessary updates”

  1. Paweł Barut Says:

    Laurent,

    I would rather use this:

    update t set x=:a
    where decode(x,:a,’Y',’N') = ‘N’;

    Paweł

  2. Laurent Schneider Says:

    :-) yes decode is possible too, but is it better? Well at least it is faster to type…

  3. Adrian Smith Says:

    Not only is the query faster (as it updates fewer rows), but it also places write-locks on fewer rows. This is normally a good thing too!

  4. Paweł Barut Says:

    In my opinion decode is better because:
    1. it’s shorter :)
    2. it’s easier to maintain ‘and’ & ‘or’ conditions in complex queries
    3. it runs at the same speed (assuming full table scan situation)

  5. Sokrates Says:

    I don’t assume full table scan situation

  6. Laurent Schneider Says:

    Pawel,
    I agree with 1 :-) and probably 3. I have not found a case where decode would be slower, but it may exists.

    I am not convinced about maintanability. Decode is a trick after all :?

Leave a Reply

Use <code> and </code> to post code