RE: looking for doc with diagrams on redo and undo for java developers

Here's a simple example to illustrate.

Given the following:
Table t has 80,000 rows
 1,600 rows have flag = 'A' where status = 2 78,400 rows have flag = 'B' where status = 2

UPDATE statement:
update t

   set flag='B'
 where status=2;

Generates 19M of redo and runs in 4.53 seconds.

If you change the statement to:
update t

   set flag='B'
 where status=2
   and flag != 'B';

Generates 500K of redo and runs in .83 seconds.

(Tests done on Windows XP with version 9.2.0.4)

Doing any work that you don't absolutely need to do is waste. The best way to improve response time and to make your application more scalable: don't do anything you don't need to do.

Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events at http://www.hotsos.com/education/schedule.html  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F Sent: Tuesday, September 28, 2004 8:47 AM To: 'ryan_gaffuri_at_comcast.net'; oracle-l_at_freelists.org Subject: RE: looking for doc with diagrams on redo and undo for java developers

Oh and PS.
Explain to him that updating the primary key column will throw database locks all over the place. And this will cost time.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: ryan_gaffuri_at_comcast.net [mailto:ryan_gaffuri_at_comcast.net] Sent: Tuesday, September 28, 2004 11:37 AM To: oracle-l_at_freelists.org
Subject: looking for doc with diagrams on redo and undo for java developers

Object Oriented programming is all about reuse so our java guys made a universal update statement that updates all columns in a table, even unchanged columns. I know its bad, but its a major re-write to change this. I was digging through the otn docs and some books and everything that leads me to this requires a significant amount of reading. Multiple chapters etc... anyone know a short article that explains undo and redo along with diagrams and why its bad to update unchanged columns that is easy for a non-oracle person to follow?

checked the concepts doc, but they would probably have to read 50-70 pages to get the basic idea.

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2004 - 11:00:10 CDT