Tag Archives: rowid

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;
SUM(Y)
------
   435

Sum[1..29]=435

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


create or replace function f(r rowid) return number is 
  n number; 
begin 
  select y into n from t where rowid=r; 
  sys.dbms_lock.sleep(1); 
  return n; 
end;
/
select sum(f(rowid)) from t;
SUM(F(ROWID))
-------------
          435

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;
commit;

back to your session you will have something inconsistent


...
SUM(F(ROWID))
-------------
            5

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.