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


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.

By Laurent Schneider

Oracle Certified Master

7 replies on “On using ROWID”

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?

… 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.

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.

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.

Leave a Reply

Your email address will not be published.