Re: Large set-based UPDATE

On 13 Feb 2003, f1fteen_at_hotmail.com wrote:

> The nature of this update statement is that there would be no logical
> curruption by committing every 1000 rows. Web-based client updates
> different columns to bulk update.

I don't understand. You select for update 150000 rows at one time, to presumably not allow any updates to happen until you are done (ie, the whole 150000 updates are logically updated together). But now, you are saying that you are thinking about committing every 1000 rows? How did these 1000 new rows get special business logic treatment?

First off, I'm pretty sure, on the first commit, the next 149000 rows aren't locked anymore, unless you are planning on performing some trickery with rownum and 150 "select for updates".

Second, assuming that your intermittent commits somehow aren't committing the entire locked set, what do you consider special about the new data such that you can let the client see 1000 committed rows of new data intermixed with old data (Once committed, its new data, but they still see the old 149000), but you couldn't have let them see rows of old data intermixed with newly committed rows (ie, don't select for update but just move through the cursor and update until you are done)?

You say things like:

        The bulk job is entirely re-runnable so no problem there.

        The web users are only interested in one row at a time, and the
        timeliness of the bulk updates is not that crucial. So no problem
        there.

Well, if you commit every 1000, you will have old data intermixed with new data when you rerun. Why is it okay to intermix new with old in that scenario, but not old with new because you feel you must select for update?

Are you expecting some ordering of the updates such that you will guarantee some logical data integrity at the web level?

Your saying that its been studied and "no logical corruption will happen" isn't really washing when one considers what you are planning to do.

This truly sounds like you are working from some flawed assumptions, either at the business rules level or at the Oracle transaction management level.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.