When Does A ROWID Change? Part V (“The Wedding”)

It’s been a busy period. First Christmas, then the wedding of my beautiful daughter, then a nice get-a-way to get over the wedding of my beautiful daughter, and then a busy period with work.

But now I’m back 🙂

In this series on when does a ROWID change, I previously discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit within its current block. Hence the general rule has always been that the ROWID of a row does not change (although I also previously discussed various exceptions to this general rule).

However, things change in an Oracle Autonomous Database, when looking at the behaviour of the ROWID after a row migrates…

To illustrate, I’m going to run a similar demo as previously, but this time within (one of my free) Transaction Processing Autonomous Databases. I start by creating and populating a basic table, with the PCTFREE set to 0 to ensure my data blocks are initially nicely filled:

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

Table BOWIE created.

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

10,000 rows inserted.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

 

Let’s just take note of a few random ROWID values:

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

     ID                 ROWID
_______ _____________________
     42 AAApUqAAAAACKD/AAp
    424 AAApUqAAAAACKD/AGn
   4242 AAApUqAAAAACKGEAHF

 

I’ll next update the rows with the NAME column value that is significantly larger than previously, to force the migration of many of my existing rows:

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

10,000 rows updated.

SQL> commit;

Commit complete.

 

If we now look at the ROWID of these same rows:

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

     ID                 ROWID
_______ _____________________
     42 AAApUqAAAAACKD/AAp
    424 AAApUqAAAAACKD/AGn
   4242 AAApUqAAAAACKGEAHF

 

We notice that they have NOT changed.

So the default behaviour in an Autonomous Database is as it has always been, that even though rows are migrated, it does NOT change the resultant ROWIDs.

This is an important point if you do NOT want your ROWIDs to change when a row is migrated (in the example perhaps that you have applications that explicitly use stored ROWIDs and are dependant on them not changing).

I’ll next run the same demo again, but with one key difference. This time, I’m explicitly setting ENABLE ROW MOVEMENT in the creation of my non-partitioned table:

 

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

Table BOWIE2 created.

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

10,000 rows inserted.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> create index bowie2_id_i on bowie2(id);

Index BOWIE2_ID_I created.

 

Let’s again have a look at the current ROWID of a few random rows:

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

     ID                 ROWID
_______ _____________________
     42 AAApUxAAAAACKIfAAp
    424 AAApUxAAAAACKIfAGn
   4242 AAApUxAAAAACKIkAHF

 

Let’s now perform the same update as before, forcing the migration of rows in the table:

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

10,000 rows updated.

SQL> commit;

Commit complete.

 

Now, as I discussed previously, in a non-autonomous environment, on a non-partitioned table, ENABLE ROW MOVEMENT would have no impact in this scenario and the ROWIDs would NOT have changed for any of these migrated rows.

But if we look at the ROWIDs in this autonomous database environment:

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

     ID                 ROWID
_______ _____________________
     42 AAApUxAAAAACKJqABX
    424 AAApUxAAAAACKJuAAJ
   4242 AAApUxAAAAACKMJABN

We can see that they have all indeed changed.

When a row migrates in an autonomous database environment AND we set the ENABLE ROW MOVEMENT on a non-partitioned table, the ROWIDs are indeed updated on the fly.

If we had an application that relied on these ROWIDs not changing:

SQL> select id from bowie2 where rowid in ('AAApUxAAAAACKIfAAp', 'AAApUxAAAAACKIfAGn', 'AAApUxAAAAACKIkAHF');

no rows selected

Well, the results would be “disappointing” (or downright disastrous if they then happen to select completed different rows)…

However, if we use an indexed key to fetched the required rows:

SQL> select * from bowie2 where id in (42, 424, 4242);

     ID                                                             NAME
_______ ________________________________________________________________
     42 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
    424 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS
   4242 THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________________________
SQL_ID atz1zbtyptu6n, child number 0
-------------------------------------
select * from bowie2 where id in (42, 424, 4242)

Plan hash value: 1734578469

--------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name        | Starts | E-Rows | A-Rows | A-Time     | Buffers  |
--------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |             |      1 |        |      3 |00:00:00.01 |        8 |
|  1 |  INLIST ITERATOR                     |             |      1 |        |      3 |00:00:00.01 |        8 |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE2      |      3 |      3 |      3 |00:00:00.01 |        8 |
|* 3 |    INDEX RANGE SCAN                  | BOWIE2_ID_I |      3 |      3 |      3 |00:00:00.01 |        5 |
--------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________

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

   3 - access(("ID"=42 OR "ID"=424 OR "ID"=4242))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

 

They thankfully have indeed been correctly updated within the index and can successfully access the required rows.

So the decision is entirely yours. If you want to keep to the existing behaviour in relation to the non-changing of ROWIDs of migrated rows, do NOT set ENABLE ROW MOVEMENT on the tables in the autonomous database environments.

If you do want to adopt this new behaviour, then simply set ENABLE ROW MOVEMENT.

I’ll discuss the advantages and disadvantages of this new behaviour in future posts…