Thoughts on ROWID

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

I think ROWID is a good candidate when you want to delete duplicate rows from a table.

- Rajeev

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

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.

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

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.

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

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... http://www.plauder-smilies.de/sad/nixweiss.gif

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

[Edited by marist89 on 05-07-2001 at 05:12 PM]

hm where are those funny faces??? I cant find them :D

Quote:

---

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!

In my openion, rowid can also be used to retrive the available rows when partial block corruption takes place.

any more ideas?

thanks

Hi..
Will please explain how Rowid can be used to find out the duplicate row as well as valid rows partially block courrpted table?

Thanks
Thomas P S

I have no idea about the corrupted block trick, but as for duplicate rows:

Consider a cross-reference table that has Student and Class. Also assume that during a bulk-load, the constraints were dropped for speed, but some duplicate data was loaded. How do we find the duplicate rows? More importantly, how do we delete *only* the duplicates?

DELETE
FROM
---StudentClass_T
WHERE
---(
------Student_ID---,
------Class_ID------,
------ROWID
---)---IN
---(
------SELECT
---------Student_ID---,
---------Class_ID------,
---------MAX(ROWID)
------FROM
---------StudentClass_T
------GROUP BY
---------Student_ID---,
---------Class_ID
------HAVING
---------COUNT(*) > 1
---)

Now, this assumes that there are only ever 2 copies of a single entry, and even for that special case, there are other ways that this can be written. I present this only as the easiest example for understanding the issue.

Make sense?

- Chris

Thank you Chris

-Thomas