avoid unnecessary updates – Laurent Schneider

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

6 thoughts on “avoid unnecessary updates”

Paweł Barut July 6, 2007 at 19:16

Laurent,

I would rather use this:

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

Paweł

Laurent Schneider Post authorJuly 7, 2007 at 06:06

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

Adrian Smith July 7, 2007 at 12:02

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!

Paweł Barut July 8, 2007 at 10:56

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)

Sokrates July 9, 2007 at 08:55

I don’t assume full table scan situation

Laurent Schneider Post authorJuly 9, 2007 at 09:00

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 😕

Comments are closed.