Re: When to Commit?

Comments in-line.

Mark D Powell wrote:
> The statement that "there shouldn't be any intermittent commits, EVER"
> is just plan wrong, as is the idea that all long running tasks can be
> moved to "outside office hours".

You didn't indicate whether your comments address logical or physical processes.

If you are saying it is reasonable to have a commit in the middle of a data load I would agree. But if you are saying it is reasonable to take a single logical transaction and break it up I'd say no.

This is a reasoned "no" as we all no there are exceptions to any rule. But as a rule ... it is one that should not be violated until and unless there is a compelling reason that proves itself in testing and stands the test of examining redesign options.

> What outside hours? We support a world wide dealer distributor system.
> We would like Order Entry to pretty much always be available when
> someone might want our product.

Ok but lets go back to something that is so huge it takes 36 hours which is, I believe, the red herring floated earlier in this thread.

If you have something that takes 36 hours then you have a design issue. And my solution, granted it wouldn't be lacking in expense but then what is, would be to temporarily add a RAC node dedicated to the task and to confine the task to that single node.

> In the real world the choice of commit frequency has to be based on the
> competing DML demands on the data.
>
> IMO -- Mark D Powell --

I agree. But also on good architecture and design which all to often does not exist.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)