Re: Updating rows to current values

Mark Rosenbaum wrote:
>
> 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

You might do better still if you set status column to a known value like 'B' and do a clustered index (if don't already have a cluster). The !='A' will probably cause a table scan forcing a read of the whole table. By using AND status = 'B', you can make use of the index to locate the rows. If you have a clustered index, then the rows could be in the same Oracle block which could save on disc I/O.

There could be some othe considerations:

If the table is already heavily indexed, adding an index will give you a performance hit on inserts, plus the change in the indexed value could also cause some performance penalty.

So, there is no real way to tell without testing. If you have access to Oracle Performance Tuning by Peter Corrigan and Mark Gurry Published by O'Reilly & Associates, Inc., Page 111 has more detailed and more accurate information than my answer.

-Allan Received on Mon Jun 09 1997 - 00:00:00 CDT