Re: Long Update Transaction

For Mercy's sake!
One should pray never to have to update a primary key.

  I only suggest that you adopt the folowing plan: 1) disable the primary key constraint (or drop the index if you haven't defined it as a constraint)

2) disable all foreign key constraints which reference it (if any)
3) update the foreign key columns
4) update the primary key column(s)
5) re-enable the primary key / re-built its index
6) re-enable the foreign key constraints (if any)

Also, I hope you are not doing something "reflexive" like: update table_1 X
set col_1 =
(select ... from table_1 Y
 where Y.col_a = X.col_a)
or some such.

I guess probably not, or you would *definitely* have run out of rollback segments.

Chrysalis
FABRICATI DIEM, PVNC
('To Protect and to Serve')
Motto of the Night Watch
Terry Pratchett: "Guards, Guards"

In article <33B41F14.D08DDD87_at_isdsa.pgh.wec.com>, "Lisa M. Lewis" <lmg_at_isdsa.pgh.wec.com> 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
>
Received on Sat Jun 28 1997 - 00:00:00 CDT