On using ROWID

I have been challenged to assert the safety of rowid in a sql statement.

Against all my beliefs, it is not safe to assume ROWID is consistent over one sql statement. If the ROWID changes, and you use ROWID in your query, you may get inconsistent results.

Obviously I would not write such a post without a test case 😉

create table t(x, y) partition by hash(x) partitions 32 enable row movement
as select rownum, rownum from dual connect by level<30;

select sum(y) from t;


Let’s write the query with a slow function using rowid

create or replace function f(r rowid) return number is 
  n number; 
  select y into n from t where rowid=r; 
  return n; 
select sum(f(rowid)) from t;

Elapsed: 00:00:29.12

The query took 29.1 seconds for 29 rows and returned the same result.

Let’s update the partition key during the select

select sum(f(rowid)) from t;
... hurry up to a new session ...

update t set x=x+1;

back to your session you will have something inconsistent


Elapsed: 00:00:02.04

Not only the query was faster than the expected 29 seconds, but it is also inconsistent.

Probably not a bug, rowid is just not constant within the same transaction.