Re: How to update a huge table in smaller chunks

On Aug 22, 10:46 am, Veeru71 <m_ad...@hotmail.com> wrote: > Is there any command in Informix that is equivalent to "SET ROWCOUNT" > of Sybase/MS Sql Server ? > > Basically I am trying to update a column in a huge table (50 million > rows) without a WHERE clause (need to set a flag in all the rows). > > In Sybase, I can do "SET ROWCOUNT 1000" and write a small WHILE loop > as below..... > > SET ROWCOUNT 1000; > WHILE << some condition>> > UPDATE <table> SET flag = 'Y'; > commit; > END WHILE; > > Effectively, this updates 1000 rows at a time so that we will not run > into transaction log fillup issues. > What is the best way of doing this in Informix ? > Thanks in advance. A couple of other options come to mind. You can lock the table in exclusive mode and then set the field to whatever you like without generating any other locks. You can create the field with the initial value that you want: Here is example SQL because I always have to test this stuff myself: create table testme( id serial, f1 varchar(64) ) ; insert into testme values (0, "This is field 1 record 1"); insert into testme values (0, "This is field 1 record 2"); insert into testme values (0, "This is field 1 record 3"); alter table testme add(f2 varchar(64) default "This is field 2"); select * from testme; All the records will have f2 with "This is field 2" for a value. This may even do an in place alter in some cases. (If it does it would be great because the command wouldn't take long.) I just checked it does do an in place alter so it could take seconds. Here is part of the output from oncheck -pT notice none of the pages are actually in the new format because I haven't updated anything but it returned the correct value for field 2 (nice going Informix engineers). Before update: Home Data Page Version Summary Version Count 0 (oldest) 1 1 (current) 0 (notice no pages are in the current format.) I then run the update update testme set f1 = f1 where 1 = 1; Home Data Page Version Summary Version Count 0 (oldest) 0 1 (current) 1 (Now all pages are in the current format.) You can also do alter table testme modify(f2 varchar(64)); If you can't leave the default on the table permanently. field f2 will still contain the default text. I checked again but didn't include it because of time constraints. Finally, if you are running a version of 10 you can set the table to raw and do your updates very fast with no locks, but also it won't log the changes so you won't be able to roll it back. Also, on some versions of 10 you will have to drop the indexes before you can convert the table to raw. But the total time to do the update may be less because you aren't updating any indexes and informix builds indexes very quickly with PDQPRIORITY turned on. Of course with the default method if you don't want all of the records set to the same value just pick the value which is predominant as the default and then update the others. If you have an 80/20 split which is the golden rule of the universe you'll save a lot of work.