Migrated Rows In Oracle Data Warehouse Autonomous Databases (“Sit Down. Stand Up.”)

In all my recent discussions on how Oracle can now update ROWIDs on the fly when a row migrates, I’ve mentioned how this only occurs on tables in which the ENABLE ROW MOVEMENT clause has been set.

So you have the option on whether you wish this new behaviour to occur by simply not setting ENABLE ROW MOVEMENT on tables where you want the previous behavior of the ROWIDs not changing when a row migrates (and for Oracle to simply have a pointer in the original table block to denote the new location of the row). You may not what ROWIDs to suddenly change on you for example if you have an application that explicitly stores ROWIDs and relies on them not changing for the application to correctly fetch data.

However, all my previous tests and examples have been run on Oracle Transaction Processing Autonomous Database environments, but as Phil Goldenberg mentioned in this comment, things unfortunately behave somewhat differently in Oracle Data Warehouse Autonomous Database environments.

To illustrate, a simple little demo as usual, but this time using an Oracle Data Warehouse Autonomous Database environment…

Let’s start by creating and populating a tightly packed table, but without setting the ENABLE ROW MOVEMENT clause:

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.

Let’s now create an index based on the ID column:

SQL> create index bowie_id_i on bowie(id);

Index BOWIE_ID_I created.

SQL> select index_name, num_rows, blevel, leaf_blocks from user_indexes where table_name='BOWIE';

   INDEX_NAME    NUM_ROWS    BLEVEL    LEAF_BLOCKS
_____________ ___________ _________ ______________
BOWIE_ID_I          10000         1             23

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 AAASTdAAAAAAJq0AAp
    424 AAASTdAAAAAAJq0AIH
   4242 AAASTdAAAAAAJq1ADP

If we store these ROWIDs, we can use them to directly access a row of interest very efficiently:

SQL> select id from bowie where rowid='AAASTdAAAAAAJq0AAp';

   ID
_____
   42

Next, I’ll update the table, increasing the size of the rows such that I generate a bunch of migrated 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.

As discussed previously, if this were an Oracle Transaction Processing Autonomous Database environment, because I haven’t set ENABLE ROW MOVEMENT on this table, the ROWIDs of any migrated rows would NOT have changed.

But here in this Oracle Data Warehouse Database environment:

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

     ID                 ROWID
_______ _____________________
     42 AAASTdAAAAAAJq3AAp
    424 AAASTdAAAAAAJq3AIH
   4242 AAASTdAAAAAAJrcAFd

We can see that all these rows now have a new ROWID.

If I now re-run my previous query that relied on the ROWIDs not changing:

SQL> select id from bowie where rowid='AAASTdAAAAAAJq0AAp';

no rows selected

We can see that the query no longer returns the required row.

And yet, if we ANALYZE the table:

SQL> analyze table bowie compute statistics;

Table BOWIE analyzed.

SQL> select table_name, num_rows, blocks, chain_cnt, row_movement from user_tables where table_name='BOWIE';

   TABLE_NAME    NUM_ROWS    BLOCKS    CHAIN_CNT    ROW_MOVEMENT
_____________ ___________ _________ ____________ _______________
BOWIE               10000        28            0 DISABLED

We can see that we have no migrated/chained rows listed, even though ENABLE ROW MOVEMENT is disabled.

I think this a little unfortunate, because I quite like the concept of ROWIDs being updated when a row migrates, but I also like the option of being able to revert to the previous behaviour if necessary.

I have no idea how this works in the other Oracle Autonomous Database environments (other than Transaction Processing), but regardless, this behaviour can potentially change on any of the environments at any time.

So, what’s the moral of the story? Well there’s a couple.

Firstly, in Oracle Autonomous Database environments, you’re meant to have a “hands-off” attitude and just let Oracle handle all this day to day stuff. So you can’t necessarily rely on the behaviour of the database to be as consistent as in environments where you control all the levers. Indexes might come and go, tables might suddenly get partitioned, ROWIDs might change when a row migrates, etc. etc. etc. etc.

And secondly, it’s becoming an even worse idea for applications to explicitly store and rely on ROWIDs not changing for such applications function properly. Especially, if you use Oracle Autonomous Database environments…