CODEWITHSUNDEEP
sqloracle-database
RaceBase
RaceBase

Reputation: 18868

Database Update Query for Huge Records

We hare having around 20,80,000 records in the table. We needed to add new column to it and we added that.

Since this new column needs to be primary key and we want to update all rows with Sequence Here's the query

BEGIN
FOR loop_counter IN 1 .. 211 LOOP
update user_char set id = USER_CHAR__ID_SEQ.nextval where user_char.id is null and rownum<100000;
commit;
END LOOP;
end;

But it'w now almost 1 day completed. still the query is running. Note: I am not db developer/programmer.

Is there anything wrong with this query or any other query solution (quick) to do the same job?

Upvotes: 1

Views: 289

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

First, there does not appear to be any reason to use PL/SQL here. It would be more efficient to simply issue a single SQL statement to update every row

UPDATE user_char
   SET id = USER_CHAR__ID_SEQ.nextval
 WHERE id IS NULL;

Depending on the situation, it may also be more efficient to create a new table and move the data from the old table to the new table in order to avoid row migration, i.e.

ALTER TABLE user_char
  RENAME TO user_char_old;

CREATE TABLE user_char
AS
SELECT USER_CHAR__ID_SEQ.nextval, <<list of other columns>>
  FROM user_char;

<<Build indexes on user_char>>
<<Drop and recreate any foreign key constraints involving user_char>>

If this was a large table, you could use parallelism in the CREATE TABLE statement. It's not obvious that you'd get a lot of benefit from parallelism with a small 2 million row table but that might shave a few seconds off the operation.

Second, if it is taking a day to update a mere 2 million rows, there must be something else going on. A 2 million row table is pretty small these days-- I can populate and update a 2 million row table on my laptop in somewhere between a few seconds and a few minutes. Are there triggers on this table? Are there foreign keys? Are there other sessions updating the rows? What is the query waiting on?

Upvotes: 2

Related Questions