Re: Updating rows to current values

In article <3396AE4A.1132_at_interpath.com>, Ken Denny <kdenny_at_interpath.com> wrote:
>Chrys,
>
>I am working with a test database right now and there are only about 200
>rows currently in the table, so it's so fast I can't really tell whether
>there's any difference or not. I'm wondering about when it gets into
>production and there are thousands of rows being affected by this
>update.
>
>Ken Denny
>kdenny_at_interpath.com
>

 Ken Denny wrote:
>
> I was writing a package and in it I wanted to update several rows in the
> database. Let's say the table is called tab1 and I want to update a
> column called status. I had:
> UPDATE tab1 SET status = 'A' WHERE <condition>;
> In this case there may be thousands of rows which satisfy the condition,
> and it is possible that a large portion of them may already have 'A' in
> the status column. I figured that it may perform better if I only update
> those rows which need it so I changed the statement to:
> UPDATE tab1 SET status = 'A' WHERE <condition> AND status != 'A';
> My question is, was this necessary, or would it have performed equally
> well with or without the "AND status != 'A'" clause?
>
> Thanks in advance
> Ken Denny
> kdenny_at_interpath.com

Ken,

My guess would be that adding the != would help but that is only a guess. The reason is that I would expect any data base to update all of the records that match. Updates hit the redo logs which can slow things down (particularly under load). If you are working in a test environment than why not create a large table and rerun the experiment. The best way to create a large test table is with a cartesian join.

hope this helps

mjr Received on Sun Jun 08 1997 - 00:00:00 CDT