Re: Long Update Transaction

Lisa M. Lewis wrote:
>
> My update query was:
>
> update users u
> set cookie = (select substr(cookie,3,LENGTH(Cookie)) ||
> substr(cookie,2,1) || substr(cookie,1,1)
> from users u1
> where u.cookie = u1.cookie);
>
> I guess that this is a really bad thing to do! I ended up killing the
> query after letting it run over the weekend. I've learned my lession
> the hard way!
>
> Lisa
>
> Matthias Gresz wrote:
>
> > Lisa M. Lewis wrote:
> > >
> > > Help!
> > >
> > > Background:
> > > I am using Oracle Workgroup server on an NT machine. I have a 200
 mhz
> > > Pentium Pro with 128 megs of memory. I am doing an update to all
 the
> > > rows in a User table (2.1 million records). The size of the table
 is
> > > approx 80 megs. The db is not in archive log mode. I am doing an
> > > update for every user in the database modifying a particular field
 using
> > > the SUBSTR function.
> > >
> > > My query has been running for the last 3.5 hours. I don't have a
 clue
> > > as to why it is taking so long. I am not an expert so I don't have
 a
> > > feel for how long it should take. I used the 'SET TRANSACTION USE
> > > ROLLBACK SEGMENT' command to ensure that this transaction uses a
 very
> > > large rollback segment ( 500M ). This is the only transaction that
 is
> > > running against the database. The rollback segment that I assigned
 does
> > > not use the OPTIMAL setting ( I heard that this could be very bad in
 

> > > some cases ).
> > >
> > > Can anyone out there give me any insight as to why the query might
 be
> > > taking so long and if this is a normal amount of time due to the
 number
> > > of records that are being updated. Also, I might add that the field
 

> > > that is being updated is the primary key -- maybe this is very bad,
 I
> > > really don't know. Any insight/experiences would be greatly
> > > appreciated.
> > >
> > > P.S. I checked for extents on the rollback_segment and there are
> > > currently 14. I don't know if the extents are a result of this
 query or
> > > not?
> > >
> > > Thanks a million!
> > >
> > > Lisa
> > Could you post your UPDATE-Statement?
> > --
> > M.Greß :-)

Since you are trying to update every record in the table you might try the following:

update users u

       set cookie = substr(cookie,3,LENGTH(Cookie)) ||
                    substr(cookie,2,1)              || 
                    substr(cookie,1,1);

If "cookie" is a UNIQUE index for the "users" table, you are probably taking a heavy performance hit because in addition to updating every row of the table you are also growing the index. I'm not sure how long it takes to index 2 million records on a pentium, but you might come out ahead to drop the index, do the update, then recreate the index.

good luck!

-rick Received on Wed Jul 02 1997 - 00:00:00 CDT