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.
