Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    370

    Question Unanswered: Does Oracle really rewrite all columns in an UPDATE statement?

    Hi,
    I have developed a JDBC application that has UPDATE statements that has ability to update any combination of columns (except of pk):
    Code:
    pStmt = conn.prepareStatement("UPDATE attachments "
    										+ "SET VALUES "
    										+ "encoding = ?"
    										+ " ,mimeType = ?"
    										+ " ,data = ?"
    										+ " ,creationTime = ?"
    										+ " ,lastModificationTime = ?"
    										+ " ,creator = ?"
    										+ " ,lastEditedBy = ?"
    										+ " ,description = ?"
    										+ " ,originalFileName = ?"
    										+ " ,thumbnail = ?"
    										+ " ,order = ?"
    										+ " ,docCode = ?"
    										+ " WHERE filerID = ?");
    			
    			pStmt.setString(1, attachment.getEncoding());
    			pStmt.setString(2, attachment.getMimeType());
    			pStmt.setBytes(3, attachment.getData());
    			pStmt.setDate(4, (Date) attachment.getCreationTime());
    			pStmt.setDate(5, (Date) attachment.getLastModificationTime());
    			pStmt.setString(6, attachment.getCreator());
    			pStmt.setString(7, attachment.getLastEditedBy());
    			pStmt.setString(8, attachment.getDescription());
    			pStmt.setString(9, attachment.getOriginalFileName());
    			pStmt.setBytes(10, attachment.getThumbnail());
    			pStmt.setInt(11, attachment.getOrder());
    			pStmt.setLong(12, attachment.getDocCode());
    			
    			pStmt.setLong(13, attachment.getFilerId());
    			pStmt.executeUpdate();
    As you see this is a general update statement. My question is:
    If I only update a char(1) column and leave others unchanged, does oracle notices this or it will map and rewrite unchaged columns too?
    This is not a efficient approach if there are other heavy columns such as LOBs b/c the blind rewrite will cause a lot of overhead and redo generation, ... for my system.
    -Thanks in advance

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    the way you wrote it, it look slike it will overwrite every column in there.
    I think you already knew the answer to this.

    why not ONLY update the column that changes?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Posts
    370
    Thnx dear Duck,
    I can update using only modified columns but I do not know which columns are going to modify by business layer. I could write an UPDATE statement for each combination of columns but you see it is NOT feasible in this case! There are a lot of combinations!
    I could build update statement by appending each modified column and I think this is the only way I have.
    -Thanks again for your help

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Oracle will do what it is told. If you update one column, it will ONLY modify that column. If you update two columns, only those two and etc...
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Ach
    Thnx dear Duck,
    I can update using only modified columns but I do not know which columns are going to modify by business layer. I could write an UPDATE statement for each combination of columns but you see it is NOT feasible in this case! There are a lot of combinations!
    I could build update statement by appending each modified column and I think this is the only way I have.
    -Thanks again for your help
    I see what you are talking about, BUT you DO KNOW what values will
    get passed to you correct? If colA is changed then update colA only.
    etc.

    coding in a little logic cannot be THAT difficult (although it might give you
    additional lines of code)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •