When Does A ROWID Change? Part I (“Fearless”)

Recently, my mate Connor McDonald caused a tad of a storm when he disclosed that the once sacred, (almost) unchangeable ROWID can now indeed potentially easily change, without the DBA doing a thing.

You can watch his excellent video on the subject here.

As the humble ROWID is a critical component of any index, I thought it worthwhile to have a deep dive discussion on when a ROWID can and can’t change and some of the key changes that have been introduced within Oracle’s Autonomous database environments.

If you’re a developer who explicitly uses the ROWID in your applications, you might want to pay extra attention to these changes.

I thought I’ll begin first though by discussing how the ROWID currently doesn’t generally change…

The ROWID is basically just a pointer stored in indexes that effectively points to the physical location of a row within a table that’s associated with the specific indexed key. It consists of the Data Object ID (if it’s a Global Index associated to a Partitioned Table, as the Relative File ID is no longer a unique value and so needs this to determine the appropriate tablespace), a Relative File ID, a Data Block ID within the Relative File and the Row Location ID within the Data Block.

Now although it has never been a completely risk free approach to manually store and directly use these ROWIDs to fetch a row from a table, it’s a technique that has been frequently used by developers for 3 very good reasons.

The first reason is that it’s one of the most efficient ways to fetch a required row, because the database can go directly to the physical location and directly access the required row, without having to even read a single index block.

The second reason is because it has always been fully supported by Oracle to do so, with the required syntax well documented. Indeed, Oracle APEX includes the base functionality to store and access rows directly via their associated ROWIDs.

The third reason is because it’s generally well understood that the ROWID doesn’t change, except for a very few (generally) well known scenarios, and so using the ROWID to access data within an application is viewed as being reasonable safe.

When a row is inserted into a table, each corresponding index on the table (generally) has a new index entry also inserted, including its associated ROWID. Now, if the row never moves from its current physical location, there is no need to ever worry about the ROWID subsequently changing.

So the big question is, when can a row physically move AND the associated ROWID change?

Logically, a scenario that springs to mind is when a row is updated and made bigger and there’s no longer room within the current data block to store the larger row. Does the row move to another block with sufficient free space and result in the ROWID to change?

To check out this scenario, I’ll create and populate a basic table, with PCTFREE set to 0, so once the blocks within the table are filled, there is precious little space for rows to subsequently grow:

SQL> create table bowie (id number, name varchar2(142)) pctfree 0 enable row movement;

Table created.

SQL> insert into bowie select rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_id_i on bowie(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

 

For good measure, I’ve also included the ENABLE ROW MOVEMENT clause, which is usually associated with Partitioned Tables (as I’ll discuss in Part II in this series).

Let’s have a look at the ROWIDs of a few random rows:

SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id;

ID         ROWID
---------- ------------------
42         AAASe4AAMAAAACHAAp
424        AAASe4AAMAAAACHAGn
4242       AAASe4AAMAAAACNAHV

If we look at a partial block dump of one of the table blocks:

tab 0, row 0, @0x1312
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 42 4f 57 49 45
tab 0, row 1, @0x131e
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 42 4f 57 49 45
tab 0, row 2, @0x132a
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 5] 42 4f 57 49 45
tab 0, row 3, @0x1336
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 42 4f 57 49 45
tab 0, row 4, @0x1342
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06
col 1: [ 5] 42 4f 57 49 45

The above partial block dump shows the first 5 rows within the block, with the contents of the 2 table columns listed (in hex format).

If we access a row via an index:

SQL> select * from bowie where id=42;

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
--------------------------------------------------------------------------------------------------
|  Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |    1 |    66 |       2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    66 |       2 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |    1 |       |       1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        702 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see in this example that Oracle requires 4 consistent gets, 3 of which are accesses to the index.

If we access this table via its ROWID:

SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    10 |       1 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY USER ROWID | BOWIE |    1 |    10 |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          1 consistent gets
          0 physical reads
          0 redo size
        698 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that this only requires just the 1 consistent get (vs. 4 when using the index) to access the row.

So we can see the appeal of using the ROWID to access a row.

If we now update the rows within the table and make them substantially larger so they can no longer fit within the currently filled blocks:

SQL> update bowie set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

10000 rows updated.

SQL> commit;

Commit complete.

And now look again at the ROWIDs of these selected rows:

SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id;

ID         ROWID
---------- ------------------
42         AAASe4AAMAAAACHAAp
424        AAASe4AAMAAAACHAGn
4242       AAASe4AAMAAAACNAHV

We notice that the ROWIDs all remain the same.

This has always historically been the behaviour here. If we update a row and the updated row can no longer fit within the current block, the row “migrates” to another table block with sufficient free space. BUT the associated ROWIDs do NOT change and the associated indexes are NOT updated.

Rather, the row data within the updated table is replaced with a “pointer”, that points to the new physical location of the migrated row. The advantage here being that Oracle only has to update the table with this new pointer, rather having to update the associated ROWIDs of all the (possibly many) associated indexes (noting that such an update would actually result in a delete followed by a re-insert of each index entry).

The disadvantage of course is that to now access this migrated row via an index requires an extra hop, to first read the initial table block and then to follow the pointer and access the actual block that now contains the row. Note if this row is forced to be migrated again because it grows again and can’t be housed in the current block, this pointer in the initial block is updated to reflect the newer location, so at least there is only ever the one extra hop.

If we look at a new partial block dump of the previously accessed block:

tab 0, row 0, @0x1f8f
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4b
tab 0, row 1, @0x1f86
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4c
tab 0, row 2, @0x1f7d
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4d
tab 0, row 3, @0x1f74
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4e
tab 0, row 4, @0x1f6b
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000b8.0

We notice that the rows with their 2 columns have been replaced with a logical nrid pointer (consisting of a relative block address and row location within the block), that effectively points to the new physical location of the row.

Note we can still use the same, unchanged ROWID to access the same table rows:

SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    10 |       1 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY USER ROWID | BOWIE |    1 |    10 |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          2 consistent gets
          0 physical reads
          0 redo size
        698 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

This ROWID access still works fine, except it has now increased to 2 consistent gets, one to access the initial block referenced by the ROWID and the extra consistent get to follow the pointer and access the new physical location of the row.

So historically, we haven’t had to worry about updates changing the ROWID of a row (except perhaps at looking at reducing the number of these migrated rows).

Well, except for one clear example as I’ll discuss in Part II…