Re: Long Update Transaction

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ß :-)
Received on Tue Jul 01 1997 - 00:00:00 CDT