Updating a lot of records .........

[jballum]

I have been given the task of converting a lot of data in a database. I am a little fuzzy concerning updating a field (fields) with approx. 1,000,000 records and how the rollback segment will fill until a commit is done.

Is there a way to set it to update all while setting it to commit every 10,000 records for example?

As well if I was cursoring through a result set of columns in tables to be updated will a commit close the cursor in Oracle?

I am just about to dig into the documentation but I just wanted to check here to see if I get any quick answers.

Thanx,

JB

 

[ninfan]

I run many multi-million record updates. Here are some guidelines I follow:

- Use indexes if possible
- Update the table in a nologging tablespace if possible
- Pass through the records as few times as possible
- Perform an "analyze" on the table prior to updating it
- You can periodically commit if you have a logical way of breaking up the records (subset of records, not subsets of columns)

I know there are issues with rollback segments but my DBA deals with that for me. There needs to be a process in place to keep the rollback segments cleaned out if it they start to fill up.

HTH

Troy

 

[MikeJones]

Specifically on the rollback issue there are a couple of things you can do...

1. You can get your DBA to create a large rollback segment
for you (Probably 100's of MB in size) and then use the
SET TRANSACTION USE ROLLBACK SEGMENT XXX command to
explicitly use this rollback segment for the update.
It's worth noting though that while Oracle garentees you
will use this rollback segment, others may use it aswell

2. You can cut your Update down into several smaller chunks
and commit periodically. Here's a simple example.

Table Def
---------
table_a
pk_column number
data_column varchar2(20)
processed_yn char(1) default 'N'

at the start of the update it is assumed that all records have processed_yn set to N.

declare
c_get_record is
select 'x'
from table_a
where processed_yn = 'N';

begin
while true loop
open c_get_record
fetch c_get_record into l_dummy;
if c_get_record%found then
close c_get_record;
-- if there are any records to process then do
-- a batch
-- of 50,000 then commit
update table_a
set data_record = 'Some Value'
where rownum < 50001;
--
commit;
else
close c_get_record;
exit
end if;
end loop;
end;

It's not the cleanest of ways, but hopefully illistrates that you can update update chunks OK. BTW, Oracle used to (may still) recommend that after a commit cursors are closed and re-opened, but I've never had a problem with it.


HTH,

Mike.

 

[RJSHA1]

Basically I would merge the above two posts. They summarize all the techniques that you need so I'm not going to say anymore except make sure that the ROLLBACK SEGMENT is big enough.



Good luck