Oracle Database Discussions

Hi All,

I have an update statement which uses the rowid column to perform the selection on the target table. Given that a rowid represents the physical location of a row on disk we know that this reference can change when various activities are performed on the database/table/row etc...

Here is an example of the statement I am issuing:

UPDATE tabA outertab SET col1 = 'Value'
WHERE EXISTS (SELECT 1 FROM tabA innertab WHERE outertab.ROWID = outertab.ROWID AND ...)

Obviously the inner query is more complicated and uses other tables etc... but for the purposes of the example we dont need to include the detail.

My question is: When using rowid in a single SQL statement as a reference from a subquery to the outer statement is there a risk that external activities can change the rowid and those changes be reflected within the database session that my query is executing thus causing an inconsistency between the inner and outer SQL clause?

In response to the question which will follow this post: "Why don't you just use a PK", We would like to avoid maintaining a PK on the table as we are talking about very large volumes of data and we dont want to have to issue a call to a sequence if we can avoid it when we are inserting the data. If however there is a risk that this Update statement could fail or update the wrong rows then we may have to use a PK.

I know there is a lot of threads about this but I havnt been able to find one that someone has answered with any kind of confidence or clarity, any help would be much appreciated.

Thanks

Keith

Edited by: The Turtle on Mar 5, 2009 5:24 AM