Re: Best way for updating EVERY row in a large database?

On Thu, 23 Apr 1998 09:22:13 -0400, "Jim Morgan" <jimmorgan_at_csi.com> wrote:

>I've got an application that is run once every several months which resets
>several columns to zero in a table that contains > 3,000,000 rows. In order
>to take advantage of a variable commit frequency, I allow the frequency to
>be specified as a parameter when I start my program.
>
>What I am doing is opening a cursor which will select the key column of the
>entire table I need to update, fetch the rows one at a time, then issue a
>separate UPDATE statemement for each row I fetch from the cursor. I am

< snip >

Have you considered updating a _range_ in one statement. Say you have a primary key ranging from zero to 4,000,000 more or less evenly distributed in that range, you could then update key 0 to 400,000, commit, key 400,001 to 800,000 etc... You can make the ranges as big as is convenient for you and you'd miss a lot of the overhead associated with the switch from SQL to PL/SQL .

Even if the columns that have to become zero depend on other fields in the same row you can do some rather sophisticated programming by using (nested) decode statement. Say column Zero_A has to become zero if column A_value is less than column A_check else it stays the same, and column Always should always become zero, then you need something like

UPDATE MASTER M
  SET M.Zero_A = DECODE( SIGN( A_check - A_value ), 1,0, M.Zero_A )

       , M.Always = 0
WHERE M.Primary_key BETWEEN Last_Value + 1 AND Increment

Even if you have to do a check with one or to other tables a subselect would cost less performance if the range is big enough.

I hope this helps

Henk de Wilde Received on Sat Apr 25 1998 - 18:01:13 CDT