Update table performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Update table performance

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Update table performance

    Is it faster to update a row in the table by using the primary key in the where clause or the rowid???

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Rowid
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Even though using rowid to update a table is possible, you should not use it in your application.

    Example:

    In an IOT, rowid changes depending upon the row insertions in the leaf block. In that case, if you use rowid for update, you will be updating wrong rows.

    Tamil

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    Even though using rowid to update a table is possible, you should not use it in your application.

    Example:

    In an IOT, rowid changes depending upon the row insertions in the leaf block. In that case, if you use rowid for update, you will be updating wrong rows.

    Tamil
    Better to say that there are limited situations where it is inadvisable ... for example when updating IOT's or rows in partitioned tables where row migration is possible. In the case of IOT's however an update on PK will be faster than with a heap table so there is less incentive to use an update by rowid anyway.

    Other than that though, there's nothing wrong with using rowid.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Should I say "Using ROWID for UPDATE is NOT portable across different RDBMSs" ?

    Tamil

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why should it matter? you shouldnt be writing RDBMS independant code - a big cause of performance problems

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    Should I say "Using ROWID for UPDATE is NOT portable across different RDBMSs" ?

    Tamil
    You can say it, but as Davey says I wouldn't consider it a reason not to do it .
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ==========
    why should it matter? you shouldnt be writing RDBMS independant code - a big cause of performance problems

    ==========

    Peoplesoft and Siebel do not write independent codes for each RDBMS.

    Tamil

  9. #9
    Join Date
    Oct 2002
    Posts
    807
    No wonder Siebel performance is pretty disgusting often times.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I believe I read Tom Kyte saying that "Database independence is a way of getting poor performance on one database and worse performance on others", or something like that. Summed it up pretty well.

    Anyway, I missed the bit where lesstjm said that he wanted database independant code.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width