Updating using the ROWID
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Updating using the ROWID

  1. #1
    Join Date
    Mar 2001
    Posts
    2

    Lightbulb

    HI,
    I have a Application which will update any of the field in a table(table with four fields with null as default and have a Unique key which includes all the fields of the table and hence no primary key associated with the table).Application using ROWID to update the record in the table(because table does not have any key field) .Application selects ROWID of the record when it selecting record for the update and uses the same while it updating back to the table. Application is able to update the record in the table as intended and my dought is is there any risk involed in using the ROWID of the record to update.
    Quick suggestions will be appriciated.....
    Thanks
    Mallikb


  2. #2
    Join Date
    Feb 2001
    Posts
    389
    From 8 onwards Rowid in a table may not be unique because of different format of rowid.Why not use Index Organized table.

  3. #3
    Join Date
    Mar 2001
    Posts
    2
    [QUOTE][i]Originally posted by gpsingh [/i]
    [B]From 8 onwards Rowid in a table may not be unique because of different format of rowid.Why not use Index Organized table. [/B][/QUOTE]

    Hi Singh,
    can you please let me know How Index organized table will help to my situation...
    ThanX


    [Edited by mallikds on 03-20-2001 at 04:55 PM]

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    in a normal table with his correspondong indexes the data and indexes are seperated. with index organized table the data is actually stored *inside* index, in a normal b tree index what it contains it's the index and the rowid which points the location of data, so basically oracle does more I/O with normal b tree indexes. With indexed organized table instead of storing index and the rowid the btree index actually stores the index AND the data so in one I/O Oracle can retrieve both index and data therefore reducing I/O, in some way the way this work is similar to a cluster

    MS SQL Server 2000 has a similar (or same) feature called clustered indexes which microsoft claims with this feature they beat TPC benchmark, if you are familiar with SQL Server 2000 may be this might help you a bit to understand indexed organized table, also the main page of dbasupport there is an article about indexed organized tables

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    Hi gpsingh

    you wrote that "From 8 onwards Rowid in a table may not be unique because of different format of rowid.". can you please explan more, in what circumstances? as far as i know if i use a cursor, where i get the rowid, and i us the rowid to update te row from the cursor, like this:

    update ___
    set ___ = ____
    where rowid = rec.row_id

    then everything is ok.!

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    In general, we should not use ROWID for update/delete, we should use PK for those 2 DML statements. The reason is Oracle changes the format of ROWID in each major release. We are not sure that ROWID of 8i will be the same in 9i also. Avoid using ROWID in the application. It is not portable across all RDBMSs.

  7. #7
    Join Date
    Mar 2001
    Posts
    63
    Ahh, but that brings up a point of discussion. Judging by how Mallikds describes his application, it appears that it's possible that he can wind up with rows that are not unique, and using the ROWID is the best way for him to pinpoint a single row.

    Obviously, creating a sequenced column is the most obvious way that he could solve his problem, but is there any other way that one could identify a single row of data in the absence a unique primary key?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    If a row in a table is not uniquely identified, then it is not at all relational table, it is called temporary table.

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Basically, as tamilselvan said, this is a very bad idea. This means that your table is not even in First Normal Form. Not having a PK on a table is a wholly unacceptable design.

    Having said that, the problem that I would see with your routine would be that the ROWID could have changed. If you did not lock the row when you SELECTed it, then other users were free to UPDATE or DELETE said row. Therefore, you must be able to handle the fact that the row may no longer exist, or may actually exist, but with a different row id.

    However, you did say that it had a "Unique key which includes all the fields of the table ". Then why not use all the fields in the WHERE clause? This has the same issues with records moving or deleting, but allows you to bypass use of the row id. Using the ROWID, IMHO, is a very bad idea. I personally don't think Oracle should ever have exposed that column. Creating a sequenced PK on this table would really be your best move.


    Just my .02,

    - Chris

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by chrisrlong [/i]
    [B].... If you did not lock the row when you SELECTed it, then other users were free to UPDATE or DELETE said row. Therefore, you must be able to handle the fact that the row may no longer exist, or may actually exist, but with a different row id. ....
    - Chris [/B][/QUOTE]
    There is even another posibility (more or less theoretical, but it is possible): a totaly new row might have been *inserted* in the meantime, with the *same rowid* you have previously selected from a different row! If you do not lock the row before a SELECT, this same row might be deleted and a totaly new row inserted that might get the same rowid that was used by deleted row!
    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