Thoughts on ROWID
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Thoughts on ROWID

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, it seems nobody has any thoughts on NOCOPY, but we are not discouraged...

    What are people's thoughts on ROWID?

    Should it never be used?

    When should it be used?

    - Chris

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    I think ROWID is a good candidate when you want to delete duplicate rows from a table.

    - Rajeev
    Rajeev Suri

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    I have used ROWID in stored procedures where I may
    be updating the same row several times in the procedure.

    the first time I needed the row, I selected the rowid into a variable called v_rowid, and the later I did all my updates
    like this :

    update table BLAH
    set col1=whatever, col2 = whatever
    where rowid = v_rowid;

    I don't think rowid should be used in tables to reference rows
    in other tables because the data will get messed up during
    alter table MOVEs and export/import. I guess they are OK
    in views though, if you have a need for that.

    -John

  4. #4
    Join Date
    May 2001
    Posts
    31
    One common use of rowid is to workaround the restriction of fetching across commits when using the FOR UPDATE and CURRENT OF clause. This is described in detail in the PL/SQL documentation.

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    Cheland,

    IMHO, you really should avoid fetching across commits.
    Doing so increases your odds of getting the 'ORA-1555
    snapshot too old' error. Why not just save your commits
    until you are done with your changes? It might even
    run faster that way! :-)

    -John

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I don't advocate the use of the ROWID for a couple of reasons:
    1. The rowid can change depending on where the table resides. If you move a table from tablespace a to tablespace b, the rowid changes.
    2. The rowid format changes from one version of oracle to the next.

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Alright! We have feedback!

    rsuri - excellent example

    My take on ROWIDs:

    The only times that I think the use of ROWIDs are acceptable:

    - Internal to a single statement, such as rsuri's example

    - After a SELECT FOR UPDATE or UPDATE...RETURNING, such as cheland's reference to the standard cursor commit work-around. ( However, I must add that to do a FOR UPDATE on a cursor is not such a good idea to begin with, IMHO - too many locks )

    This is because how volatile ROWIDs are. If you don't have a lock on the record, you have absolutely no idea if that ROWID will be valid the next time you go to use it. Right?

    However, I wonder if this is even 100% safe. Consider:

    The ROWID basically says where the row is... block, offset and all that.
    In an index-organized table, someone can move your record by inserting/deleting other records without even having a lock on your record. Of course, in this case, there is no *real* ROWID - Oracle 'fakes' one, so this example should be safe.

    Are there any other possible examples, however? Is it possible in *any* circumstance for a ROWID to change *while* the row is locked?

    So, the questions now are:
    - Do my two use cases represent the only times that the ROWID should be used? Are there others?
    - Is my second use case safe 100% of the time?

    - Chris

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I wonder if the rowid will change during an update of a partition key? For example, say you have a table partitioned on trasaction_dt by month. Each of your months is in a seperate tablespace. If you update the value of transaction_dt so that it moves from one partition to the next, I wonder if that would change the rowid of the row that is locked? Probably not until the change is committed, but something to think about...

    (sambavan: you're really started something now... )

    [Edited by marist89 on 05-07-2001 at 05:12 PM]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hm where are those funny faces??? I cant find them

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by chrisrlong
    ...SNIP...
    This is because how volatile ROWIDs are. If you don't have a lock on the record, you have absolutely no idea if that ROWID will be valid the next time you go to use it. Right?
    ...SNIP...
    Not only that you don't know if rowid will be valid the next time you'll use it - it can happen that the rowid will be a valid one, but it will represent a *totaly different* record as the one you've got it from initialy! Consider the following example:

    time1 - you select a record REC1 and store its rowid into RID1 (without locking the row)
    time2 - someone deletes record REC1, so at that time your RID1 is not valid
    time3 - someone inserts new record REC2 which might get stored exactly on the place where REC1 used to be (if that block was on the free list).
    time4 - you perform an operation on record represented by RID1, thinking that this is record REC1, but it is in fact totaly different record REC2. Not changed REC1, but *new* record REC2!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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