2

I wonder is it safe to use rowid for row matching?

I have following query:

select * from a,
    (select a.rowid rid, <some_columns_omitted> from a, b, c where a.some_column = b.some_column ... <joining_omitted>
     union all
     select a.rowid rid, <some_columns_omitted> from a, d, e where a.some_column = d.some_column ... <joining_omitted>
     union all ....) sub_query
where a.rowid = sub_query.rid

Will using rowid for row matching be as safe as using primary key?

michael nesterenko
  • 14,222
  • 25
  • 114
  • 182
  • its an odd query honestly. You'll also return duplicates most likely since you're doing a union all in the sub query (sub query will most likely have multiple rows of same rowid). A union or using IN would solve that, but have you tried just joining a to the other tables as needed? – tbone Sep 04 '13 at 16:56
  • @tbone, I am using such query because I need data from other tables, e.g. `d`. There are no duplicates because of conditions in subqueries, they do not match duplicates. I can not move union out of subquery because in real query I need to lock data and I use `for update` clause which does not work with union – michael nesterenko Sep 04 '13 at 18:07

3 Answers3

4

See this related question:

Oracle guarantees that, as long as the row exists, its rowid does not change. Rowid will change only in very special occasions (table rebuild, partition table with row movement enabled, index-organized table with update to the pk). On heap tables, an update will not cause the rowid to change, even if the row is migrated (because it doesn't fit in the block anymore).

In any cases the rowid is part of the metadata of a row and will be kept consistent for the duration of a query, with the same consistency mechanism that keeps column data consistent (multiversion read consistency...).

Furthermore, it is safe to use rowid accross queries if you lock the row for update (same as primary key). Accessing rows by rowid is also faster than a primary key lookup (since a primary key lookup is an index scan + a rowid access).

Community
  • 1
  • 1
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
1

I believe it is OK to use the rowid, but I do not like that. You have a primary key for that purpose, please use that. I believe Oracle currently guarantees that rowid will not change during query run, but this is a bad practice. For instance if it works perfectly who does guarantee that this will work perfectly on a newer Oracle version when you migrate the database?

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
1

If you consider that under the hood Oracle itself uses ROWIDs to process a query (think "TABLE ACCESS BY ROWID" in an execution plan) you better believe that ROWIDs will be reliable for the duration of a query. (I'm also going by the premise that readers don't block writers so Oracle wouldn't be doing any special locking as it's processing records.)

If it was a case of recording ROWIDs for use in a subsequent SQL statement then I'd be a little wary, but for a self-contained query, I'd say you'll be ok.

Patrick Marchand
  • 3,405
  • 21
  • 13