Oracle Auto Commit during transaction.

#1

Hi,

If I send an Update like this:

UPDATE MyTable SET Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.

#2

"PowerOne" <salazar.willy (AT) gmail (DOT) com> wrote

Quote:

Hi,

If I send an Update like this:

UPDATE MyTable SET Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.
No, why would you want it to?

Jim

#3

"PowerOne" <salazar.willy (AT) gmail (DOT) com> wrote

Quote:

Hi,

If I send an Update like this:

UPDATE MyTable SET Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.
No, why would you want it to?

Jim

#4

"PowerOne" <salazar.willy (AT) gmail (DOT) com> wrote

Quote:

Hi,

If I send an Update like this:

UPDATE MyTable SET Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.
No, why would you want it to?

Jim

#5

"PowerOne" <salazar.willy (AT) gmail (DOT) com> wrote

Quote:

Hi,

If I send an Update like this:

UPDATE MyTable SET Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.
No, why would you want it to?

Jim

#6

On Mar 14, 3:23*pm, PowerOne <salazar.wi... (AT) gmail (DOT) com> wrote:

Quote:

Hi,

If I send an Update like this:

UPDATE * *MyTable * *SET * *Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every *X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.

No, and you don't want to do that. It's a sure technique to generate
ORA-01555 errors and, thus, kill your overall update. Read here:

http://oratips-ddf.blogspot.com/2008...ora-01555.html

then stop thinking about this.

David Fitzjarrell

#7

On Mar 14, 3:23*pm, PowerOne <salazar.wi... (AT) gmail (DOT) com> wrote:

Quote:

Hi,

If I send an Update like this:

UPDATE * *MyTable * *SET * *Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every *X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.

No, and you don't want to do that. It's a sure technique to generate
ORA-01555 errors and, thus, kill your overall update. Read here:

http://oratips-ddf.blogspot.com/2008...ora-01555.html

then stop thinking about this.

David Fitzjarrell

#8

On Mar 14, 3:23*pm, PowerOne <salazar.wi... (AT) gmail (DOT) com> wrote:

Quote:

Hi,

If I send an Update like this:

UPDATE * *MyTable * *SET * *Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every *X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.

No, and you don't want to do that. It's a sure technique to generate
ORA-01555 errors and, thus, kill your overall update. Read here:

http://oratips-ddf.blogspot.com/2008...ora-01555.html

then stop thinking about this.

David Fitzjarrell

#9

On Mar 14, 3:23*pm, PowerOne <salazar.wi... (AT) gmail (DOT) com> wrote:

Quote:

Hi,

If I send an Update like this:

UPDATE * *MyTable * *SET * *Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every *X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.

No, and you don't want to do that. It's a sure technique to generate
ORA-01555 errors and, thus, kill your overall update. Read here:

http://oratips-ddf.blogspot.com/2008...ora-01555.html

then stop thinking about this.

David Fitzjarrell

#10

On Mar 15, 10:00*am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net>
wrote:

Quote:

On Mar 14, 3:23*pm, PowerOne <salazar.wi... (AT) gmail (DOT) com> wrote:

Hi,

If I send an Update like this:

UPDATE * *MyTable * *SET * *Field1 = value
WHERE Field2 = value2;

there is a way that Oracle make a commit every *X records updated?

Note: this update can alter thousand of records at the same time.

Thank you.

No, and you don't want to do that. *It's a sure technique to generate
ORA-01555 errors and, thus, kill your overall update. *Read here:

http://oratips-ddf.blogspot.com/2008...ora-01555.html

then stop thinking about this.

David Fitzjarrell

The primary determining factor for if a large update can be performed
as a single operation providing enough undo space to support the
transaction exists is if there is concurrent update demand for the
rows. If you have online applications that will in fact update rows
that are updated by the long running transaction then having to commit
within the loop is a must. You cannot expect an online transaction to
wait 30 seconds for a row to be committed. It is simply too long of a
delay. Under traditional rollback segment management as long as you
had sufficiently large rbs segments so as not to wrap around too fast
the 1555 was unlikely. With an undo tablespace the error is even less
likely. This providing you use some intelligence in choosing the
commit size.

However, when ever possible a single DML statement should normally be
done as a single transaction. Though you need to watch the consistent
read statistics on any task that revisits table (or index) blocks
because sometimes by placing a few commits in the task you can greatly
cut down on the number of read consistent block views that have to be
built resulting in significant run time improvement. This is an often
overlooked issue since one of the primary reasons to use a single
transaction to perform a DML operation is the performance benefit of
doing so. Sometimes there is actually a performance penalty for using
a single transaction.

Like everything else in Oracle there are trade offs involved in
choosing the size of the unit of work to commit, that is, in choosing
the transaction size. You have to balance available undo space
demand, concurrent access requirements, task restart ability, and
evaluate the performance implications of both approaches.

HTH -- Mark D Powell --