CODEWITHSUNDEEP
oracle-databaseoracle-autonomous-db
Yasin B
Yasin B

Reputation: 387

ROWID changed after an update operation

I am developing an application on Autonomous Data Warehouse that must use the rowid field in order to identify a specific row that has to be updated. Unfortunately, I noticed that rowid changed when I updated a single row. Is this expected? Is there a way to disable this behavior?

Upvotes: 1

Views: 1410

Answers (1)

Yasin B
Yasin B

Reputation: 387

Autonomous Data Warehouse compresses all table with Hybrid Columnar Compression by default. Updating a row in such a table can change the ROWID. Here's an example that shows how the ROWID changes.

SQL> create table t as select * from all_objects;

Table created.

SQL> select object_id,rowid from t where rownum=1;

 OBJECT_ID ROWID
---------- ------------------
     9 AABYq5AAAAAABDTAAA

SQL> update t set object_name='X' where object_id=9;

1 row updated.

SQL> commit;

Commit complete.

SQL> select object_id,rowid from t where object_id=9;

 OBJECT_ID ROWID
---------- ------------------
     9 AABYq5AAAAAABGVAAA

You can create the table as uncompressed to avoid it.

SQL> create table t nocompress as select * from all_objects;

Table created.

Or, as a better alternative, you can use a column, or columns to identify the rows to be updated rather than relying on ROWID. In the above example, I could use the OBJECT_ID column to find the row as that column has unique values.

Upvotes: 1

Related Questions