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.

7 thoughts on “On using ROWID”

  1. Chadders

    Hi Laurent, good post, but isn’t it really to do with you summing using a PL/SQL function which operates outside of the “main” transaction of the SQL statement. The issue is not really ROWID (although, it highlights the design fault in assuming ROWID is constant), but it could be anything that affects the set of data that the function is using to query with.

    If you’d have written the SQL using pure SQL as opposed to PL/SQL as :

    SELECT SUM((SELECT y FROM t t2 WHERE t2.rowid = t1.rowid))
    FROM t t1;

    Then it would have been fine wouldn’t it?

  3. Franck Pachot

    … thinking a bit more about it, the rowid has nothing to do with that behavior. You have the same behavior if you use the X column instead of the rowid. The only way to have a consistent result with that code (without error) is to set the transaction read only so that the select in the function reads data consistent to the same SCN as the query.

  4. Stew Ashton

    Franck is right. I did four tests:
    1) Laurent’s test – rowid and no special transaction setting. Got inconsistent result as Laurent did
    2) rowid and read only transaction. Got consistent result = 435
    3) Used x value instead of rowid with no special transaction setting. Got inconsistent result.
    4) Used x value and read only transaction. Get consistent result – 435.

    So the test only shows that statement-level read consistency does not extend to other statements called via functions. rowid is no different from anything else.

  5. Dominic Brooks

    Unless I’m mistaken, not a flaw with rowid.
    Using functions within SQL to do other SQL lookups – this is what bypasses the read consistency mechanism and that’s why they’re BAD, BAD, BAD.

  7. Gary

    On a related note, if you use a WHERE CURRENT of cursor style UPDATE in PL/SQL, that does an UPDATE..WHERE ROWID=… under the covers.

    Just sayin’

Comments are closed.